You are asking Oracle to retrieve data from 4 tables, and you have applied restrictions on the rows to be returned from at least 2 of the tables, possibly 3 tables. The reason for the Cartesian join is that you did not tell Oracle how the tables were related to each other. Usually, there will be one (two, three, four, etc.) columns that the tables will have in common. You need to list these relationships in the WHERE clause.

Also, why are you specifying TO_NUMBER('200001'), and not just 200001?

WHERE X.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
AND Y.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212')
AND BUSINESS_UNIT = '33011' AND LOB_VALUE = TO_NUMBER('2920')

The reason we used TO_NUMBER as the column needs to convert date as below.

Notice in the execution plan that there are two MERGE JOIN CARTESIAN operations - that basically states that if there are 2 rows matching the restrictions on the first table, and 2 rows matching the restriction on the second and third tables, 2 * 2 * 2 = 8 rows will be produced. If each of the restrictions on the tables produced 1 row, or a Cartesian join appears between a table row source with a single row and another table with more than one row, that is not an indication of a problem.

Now, let's fix the SQL statement to indicate how the tables are related:

SELECT
*
FROM
T1,
T2,
T3
WHERE
T1.COL2 BETWEEN 100 AND 101
AND T2.COL2 BETWEEN 1000 AND 1001
AND T3.COL2 BETWEEN 2000 AND 2001
AND T1.COL1 = T2.COL1
AND T2.COL2 = T3.COL1;

If a column is defined as a DATE column, it is much better to treat it as a DATE column in WHERE clauses.

If you want to do a little reading, take a look at a couple of articles that I wrote that will probably be helpful for you:
http://hoopercharles.wordpress.com/2010/01/24/sql-basics-working-with-erp-data/
http://hoopercharles.wordpress.com/2010/01/06/date-datatype-or-number-data-type-which-should-be-used/