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						         1007544	  60714747	3278596338
 SORT UNIQUE									         355	           4	        80
  UNION-ALL					
   PARTITION LIST SINGLE							         346	           3		60
    TABLE ACCESS FULL	ADMIN_COSTPL_OWNER	E2EC_ECAPS_BOM_TMP		         346	           3		60
   TABLE ACCESS BY GLOBAL INDEX ROWID	ADMIN_COSTPL_OWNER	E2EC_ECAPS_BOM_TMP	 7		   1		20
    INDEX RANGE SCAN	ADMIN_COSTPL_OWNER	TEST					 3		   4	 
WINDOW SORT								                 [b]1007544[/b]	60714747	3278596338
  NESTED LOOPS										 [b]201967[/b]	60714747	3278596338
   PARTITION LIST SINGLE								 68		7433		386516
    TABLE ACCESS FULL	ADMIN_COSTPL_OWNER	E2EC_ECAPS_MOD_DTL			 68		7433		386516
   COLLECTION ITERATOR SUBQUERY FETCH					
    CONNECT BY WITHOUT FILTERING					
     FAST DUAL										 2		1