|
SELECT THREE.SETID , THREE.BUSINESS_UNIT_PO , THREE.PO_ID , THREE.PO_DT , TO_DATE(TO_CHAR(THREE.DATETIME_DISP , 'DD-MON-YYYY') , 'DD-MON-YYYY') , THREE.ACKNOWLEGE_DT , THREE.VENDOR_ID , CASE WHEN THREE.DUE_DTTM IS NULL THEN TO_DATE(TO_CHAR(THREE.DUE_DTTM , 'DD-MON-YYYY') , 'DD-MON-YYYY') ELSE TO_DATE(TO_CHAR(THREE.DUE_DTTM , 'DD-MON-YYYY') , 'DD-MON-YYYY') END AS POA_DUE_DT , CASE WHEN THREE.DUE_DTTM IS NULL THEN THREE.DUE_DTTM ELSE THREE.DUE_DTTM END AS PO_POA_DTTM , CASE WHEN THREE.ACKNOWLEGE_DT <= TO_DATE(TO_CHAR(THREE.DUE_DTTM , 'DD-MON-YYYY') , 'DD-MON-YYYY') THEN 'On-Time' WHEN ACKNOWLEGE_DT > TO_DATE(TO_CHAR(THREE.DUE_DTTM , 'DD-MON-YYYY') , 'DD-MON-YYYY') THEN 'Late' ELSE NULL END AS PERFORMED_BY_POA FROM ( SELECT TWO.SETID , TWO.BUSINESS_UNIT_PO , TWO.PO_ID , TWO.PO_DT , TWO.DATETIME_DISP , TWO.ACKNOWLEGE_DT , TWO.VENDOR_ID ,CASE WHEN TWO.DATETIME_MODIFIED > TWO.SHIP_EARLY_DTTM THEN TWO.SHIP_EARLY_DTTM ELSE TWO.DATETIME_MODIFIED END AS DUE_DTTM FROM ( SELECT ONE.SETID , ONE.BUSINESS_UNIT_PO , ONE.PO_ID , ONE.PO_DT , ONE.DATETIME_DISP , ONE.ACKNOWLEGE_DT , ONE.VENDOR_ID , CASE WHEN ONE.DAYS_HRS_AFTER = 'D' THEN TO_TIMESTAMP ( to_char(ONE.DATETIME_DISP + ONE.ACK_ALERT_DISP , 'DD-MON-YYYY HH24:MI:SS') ,'DD-MON-YYYY HH24:MI:SS') WHEN ONE.DAYS_HRS_AFTER = 'H' THEN TO_TIMESTAMP ( to_char(ONE.DATETIME_DISP + (1/24*ONE.ACK_ALERT_DISP) , 'DD-MON-YYYY HH24:MI:SS') , 'DD-MON-YYYY HH24:MI:SS') ELSE NULL END AS DATETIME_MODIFIED , CASE WHEN ONE.DAYS_HRS_BEFORE = 'D' THEN %DateAdd (ONE.DUE_DT, (-ONE.ACK_ALERT_SHIP)) ELSE NULL END AS SHIP_EARLY_DTTM FROM ( SELECT AC.SETID , AC.BUSINESS_UNIT_PO , AC.PO_ID , AC.PO_DT , AC.DUE_DT , AC.DATETIME_DISP , AC.ACKNOWLEGE_DT , AC.VENDOR_ID , CASE LC.POA_REQUIRED WHEN 'Y' THEN LC.ACK_ALERT_DISP WHEN 'D' THEN PM.ACK_ALERT_DISP ELSE LC.ACK_ALERT_DISP END AS ACK_ALERT_DISP , CASE LC.POA_REQUIRED WHEN 'Y' THEN LC.ACK_ALERT_SHIP WHEN 'D' THEN PM.ACK_ALERT_SHIP ELSE LC.ACK_ALERT_SHIP END AS ACK_ALERT_SHIP , CASE LC.POA_REQUIRED WHEN 'Y' THEN LC.DAYS_HRS_AFTER WHEN 'D' THEN PM.DAYS_HRS_AFTER ELSE LC.DAYS_HRS_AFTER END AS DAYS_HRS_AFTER , CASE LC.POA_REQUIRED WHEN 'Y' THEN LC.DAYS_HRS_BEFORE WHEN 'D' THEN PM.DAYS_HRS_BEFORE ELSE LC.DAYS_HRS_BEFORE END AS DAYS_HRS_BEFORE FROM PS_SCP_ACKPOLST_FL AC , PS_VENDOR_LOC LC , PS_BUS_UNIT_OPT_PM PM WHERE AC.SETID = LC.SETID AND AC.VENDOR_ID = LC.VENDOR_ID AND AC.VNDR_LOC = LC.VNDR_LOC AND %EffdtCheck(VENDOR_LOC LC1, LC,%CurrentDateIn) AND PM.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = AC.BUSINESS_UNIT_PO AND RECNAME = 'BUS_UNIT_OPT_PM' ) AND PM.PRCS_OPTION_ID IN ( SELECT PM1.PRCS_OPTION_ID FROM PS_BUS_UNIT_TBL_PM PM1 WHERE PM1.BUSINESS_UNIT = AC.BUSINESS_UNIT_PO) AND %EffdtCheck(BUS_UNIT_OPT_PM PM2, PM,%CurrentDateIn) AND CASE LC.POA_REQUIRED WHEN 'D' THEN PM.POA_REQUIRED WHEN 'Y' THEN LC.POA_REQUIRED END = 'Y' AND (CASE LC.POA_REQUIRED WHEN 'Y' THEN (%NumToChar(LC.ACK_ALERT_DISP)%Concat %NumToChar(LC.ACK_ALERT_SHIP) ) WHEN 'D' THEN (%NumToChar(PM.ACK_ALERT_DISP)%Concat %NumToChar(PM.ACK_ALERT_SHIP) ) END ) <> 999999) ONE) TWO) THREE
|