1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
|
CREATE OR REPLACE FUNCTION
F_CALCULATION(CALCULATION IN VARCHAR2) RETURN NUMBER
IS
REQUETE VARCHAR2(512) ;
TOTAL NUMBER ;
L_TXT VARCHAR2 (4000) := CALCULATION;
BEGIN
REQUETE := 'SELECT ' || L_TXT || ' FROM DUAL' ;
EXECUTE IMMEDIATE REQUETE INTO TOTAL ;
RETURN TOTAL ;
EXCEPTION
WHEN OTHERS THEN
RETURN 0 ;
END ;
/
with TMP_PROD_FG as
(
select to_date('08312010','MMDDYYYY') w_date,1 w_item,3 w_parent_mfg,0.5 w_UNIT_QTY_USED,5 W_PARENT_QTY_UOM2,'PURCHASE' W_SOURCING_TYPE from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),2,3,1,10,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),7,5,0.25,50,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),3,4,2,20,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),5,4,1,5,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),6,4,0.5,10,'PURCHASE' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),4,9,3,50,'PRODUCTION' from DUAL UNION ALL
select to_date('08312010','MMDDYYYY'),10,9,2.5,50,'PURCHASE' from DUAL
)
SELECT T.W_DATE
,T.W_ORIG_RM
, (SUM (T.W_UNIT_QTY_USED_FINAL) * T.W_PARENT_QTY_UOM2) "W_QTY_USED"
,T.W_PARENT_MFG
,T.W_PARENT_QTY_UOM2
,T.W_LVL
,T.W_LEAF
FROM (SELECT CONNECT_BY_ROOT (T2.W_ITEM) "W_ORIG_RM"
,T2.W_DATE
,T2.W_PARENT_MFG
,T2.W_PARENT_QTY_UOM2
,F_CALCULATION (SUBSTR (SYS_CONNECT_BY_PATH (T2.W_UNIT_QTY_USED, '*'), 2))
"W_UNIT_QTY_USED_FINAL"
,LEVEL "W_LVL"
,CONNECT_BY_ISLEAF "W_LEAF"
FROM TMP_PROD_FG T2
START WITH T2.W_SOURCING_TYPE = 'PURCHASE'
CONNECT BY NOCYCLE PRIOR T2.W_DATE = T2.W_DATE
AND PRIOR T2.W_PARENT_MFG = T2.W_ITEM
AND T2.W_SOURCING_TYPE <> 'PURCHASE'
) T
GROUP BY T.W_DATE
,T.W_ORIG_RM
,T.W_PARENT_MFG
,T.W_PARENT_QTY_UOM2
,T.W_LVL
,T.W_LEAF
order by w_leaf desc , w_parent_mfg |
Partager