SELECT seq,CCN,ProcessorPart,root_item,comp_path,Item,comp_item,comp_item_type,
lag(comp_item_type,1,'PART') over(PARTITION BY seq ORDER BY lvl)Nxt_comp_item_type,lvl,bom_qty,
        ROUND(CASE min(abs(bom_qty)) OVER (PARTITION BY seq ORDER BY lvl)
        WHEN 0 THEN 0 ELSE 1 END * EXP (SUM (LN (nullif(abs(bom_qty),0))) OVER (PARTITION BY seq ORDER BY lvl))) Ulti_qty,
        'AMER'
        FROM
        (
        SELECT y.seq,y.CCN,y.ProcessorPart,y.root_item,y.comp_path,y.Item,y.comp_item,y.lvl,y.bom_qty,
        (
        SELECT comp_item_type
        FROM E2EC_ECAPS_BOM_TMP a
        WHERE Region= 'AMER'
        AND y.comp_item=a.comp_item
        AND a.CCN=y.CCN
        union
        SELECT item_type
        FROM E2EC_ECAPS_BOM_TMP a
        WHERE Region= 'AMER'
        AND y.comp_item=a.item
        AND a.CCN=y.CCN
        ) comp_item_type
        FROM
        (
        select Seq,CCN,root_item,root_comp_item ProcessorPart,comp_path,
        substr (t.comp_path, instr (t.comp_path, '/', -1, 2)+ 1,instr (t.comp_path, '/', -1, 1)- instr (t.comp_path, '/', -1, 2)-1) Item,
        SUBSTR(t.comp_path, instr (t.comp_path, '/', 1, x.column_value) + 1,instr (t.comp_path, '/', 1, x.column_value + 1)- instr (t.comp_path, '/', 1, x.column_value) - 1) comp_item,
        SUBSTR(t.ultimate_qty, instr (t.ultimate_qty, '*', 1, x.column_value) + 1,instr (t.ultimate_qty, '*', 1, x.column_value + 1)- instr (t.ultimate_qty, '*', 1, x.column_value) - 1) bom_qty,
        x.column_value lvl
        from ( SELECT t.root_item,t.ccn,t.seq,t.root_comp_item,'*1'||t.ultimate_qty||'*' ultimate_qty,t.comp_path||'/'||t.item||'/' comp_path
               FROM E2EC_ECAPS_MOD_DTL t
               WHERE t.Region = 'AMER') t,
               TABLE(CAST(MULTISET(select LEVEL from dual connect by level <= regexp_count (t.comp_path, '/') - 1)as sys.odcinumberlist)) x
        )y
        );

SELECT STATEMENT, GOAL = ALL_ROWS										23673303	        66464701	        112258879989
 SORT UNIQUE													713			12			3576
  UNION-ALL					
   PARTITION LIST SINGLE											356			6			2088
    TABLE ACCESS FULL	ADMIN_COSTPL_OWNER	E2EC_ECAPS_BOM_TMP			                        356	                6	                2088
   PARTITION LIST SINGLE										        356	                6	                1488
    TABLE ACCESS FULL	ADMIN_COSTPL_OWNER	E2EC_ECAPS_BOM_TMP			                        356	                6	                1488
 WINDOW SORT													23673303	        66464701	        112258879989
  NESTED LOOPS													221089	                66464701	        112258879989
   PARTITION LIST SINGLE										        68	                8137	                13727119
    TABLE ACCESS FULL	ADMIN_COSTPL_OWNER	E2EC_ECAPS_MOD_DTL			                        68	                8137	                13727119
   COLLECTION ITERATOR SUBQUERY FETCH					
    CONNECT BY WITHOUT FILTERING					
     FAST DUAL													2	                 1

SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> 

Plan hash value: 1350433263

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |    60M|  3126M|       |  1007K  (1)| 03:21:31 |       |       |
|   1 |  SORT UNIQUE                         |                    |     4 |    80 |       |   355   (4)| 00:00:05 |       |       |
|   2 |   UNION-ALL                          |                    |       |       |       |            |          |       |       |
|   3 |    PARTITION LIST SINGLE             |                    |     3 |    60 |       |   346   (2)| 00:00:05 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL                | E2EC_ECAPS_BOM_TMP |     3 |    60 |       |   346   (2)| 00:00:05 |     1 |     1 |
|   5 |    TABLE ACCESS BY GLOBAL INDEX ROWID| E2EC_ECAPS_BOM_TMP |     1 |    20 |       |     7   (0)| 00:00:01 |     1 |     1 |
|*  6 |     INDEX RANGE SCAN                 | TEST               |     4 |       |       |     3   (0)| 00:00:01 |       |       |
|   7 |  WINDOW SORT                         |                    |    60M|  3126M|  3487M|  1007K  (1)| 03:21:31 |       |       |
|   8 |   NESTED LOOPS                       |                    |    60M|  3126M|       |   201K  (1)| 00:40:24 |       |       |
|   9 |    PARTITION LIST SINGLE             |                    |  7433 |   377K|       |    68   (0)| 00:00:01 |   KEY |   KEY |
|  10 |     TABLE ACCESS FULL                | E2EC_ECAPS_MOD_DTL |  7433 |   377K|       |    68   (0)| 00:00:01 |     1 |     1 |
|  11 |    COLLECTION ITERATOR SUBQUERY FETCH|                    |       |       |       |            |          |       |       |
|* 12 |     CONNECT BY WITHOUT FILTERING     |                    |       |       |       |            |          |       |       |
|  13 |      FAST DUAL                       |                    |     1 |       |       |     2   (0)| 00:00:01 |       |       |