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
| insert into contrat16.TEST_LTD(COMMANDE, COL_ELT, COL_SERV, DELAI_MOY_COM, PER_GEL, DATE_CREATION,
DATE_CREATION_LTDT, DATE_PRE, DATE_PREQUA_LTDT, FLAG_COMMANDE_REAL, DATE_QUALIF, DATE_QUALIF_LTDT)
SELECT COMMANDE,
NULLIF(DELAI_MOY_ELT_SRV,-1) COL_ELT,
NULLIF(DELAI_MOY_SRV,-1) COL_SERV,
NULLIF(GREATEST(DELAI_MOY_ELT_SRV,DELAI_MOY_SRV),-1) DELAI_MOY_COM,
PER_GEL,
DATE_CREATION,
decode(GREATEST(DELAI_MOY_ELT_SRV,DELAI_MOY_SRV),-1,null,Pkg_Calc_Order_Lt.F_Calc_date(GREATEST(DELAI_MOY_ELT_SRV,DELAI_MOY_SRV),DATE_CREATION)) DATE_CREATION_LTDT,
DATE_PRE,
decode(GREATEST(DELAI_MOY_ELT_SRV,DELAI_MOY_SRV),-1,null,Pkg_Calc_Order_Lt.F_Calc_date(GREATEST(DELAI_MOY_ELT_SRV,DELAI_MOY_SRV),DATE_PRE)) DATE_PREQUA_LTDT,
decode(DATE_PRE,null,1,0) flag_commande_real,
DATE_QUALIF,
decode(GREATEST(DELAI_MOY_ELT_SRV,DELAI_MOY_SRV),-1,null,Pkg_Calc_Order_Lt.F_Calc_date(GREATEST(DELAI_MOY_ELT_SRV,DELAI_MOY_SRV),DATE_QUALIF)) DATE_QUAlIF_LTDT
FROM (
SELECT A.ID_COMMANDE COMMANDE,
PKG_CALC_ORDER_LT.F_CALC_ELT_SRV_LEAD_TIME(B.ID_OPERATION,A.ID_COMMANDE,'CONTRAT16') DELAI_MOY_SRV,
PKG_CALC_ORDER_LT.F_CALC_SRV_LEAD_TIME(B.ID_OPERATION,A.ID_COMMANDE,'CONTRAT16') DELAI_MOY_ELT_SRV,
CAL_PERIODE_GEL('CONTRAT16',A.ID_COMMANDE) PER_GEL,
A.DATE_CREATION DATE_CREATION,
CAL_DATE_PREQUALIF('CONTRAT16',A.ID_COMMANDE) DATE_PRE,
CAL_DATE_QUALIF('CONTRAT16',A.ID_COMMANDE) DATE_QUALIF
FROM CONTRAT16.PC_COMMANDE A ,CONTRAT16.PC_GROUPEMENT_COMMANDE B
WHERE A.TYPE_COMMANDE NOT IN (3,4,5,10,11,12,13) AND A.FLAG_INJECTION <> 1
AND A.ID_GROUPEMENT=B.ID_GROUPEMENT
) ; |