VIEW: APPS.DDR_DMNTRA_V

Source

View Text - Preformatted

SELECT SRC.SDATE SDATE, SRC.LEVEL1 LEVEL1, SRC.LEVEL2 LEVEL2, SRC.SHIP_QTY_POS SHIP_QTY_POS, SRC.ORDR_QTY_POS ORDR_QTY_POS, SRC.SLS_QTY_POS SLS_QTY_POS, SRC.FRCST_SLS_QTY_POS FRCST_SLS_QTY_POS, SRC.FRCST_SLS_QTY_DC FRCST_SLS_QTY_DC, SRC.ON_HAND_QTY_DC ON_HAND_QTY_DC, SRC.ON_HAND_QTY_POS ON_HAND_QTY_POS, SRC.IN_TRANSIT_QTY_DC IN_TRANSIT_QTY_DC, DDR_ITEM.MFG_ORG_CD MFG_ORG_CD, DDR_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, DDR_ITEM.MFG_ITEM_EXT_ID MFG_ITEM_EXT_ID, DDR_LOC.MFG_ORG_CD MFG_ORG_CD_1, DDR_LOC.ORG_CD ORG_CD, DDR_LOC.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_LOC.MFG_LOC_EXT_ID MFG_LOC_EXT_ID FROM (SELECT AGGREGATOR.WK_STRT_DT SDATE, AGGREGATOR.LEVEL1$1 LEVEL1, AGGREGATOR.LEVEL2$1 LEVEL2, AGGREGATOR.SHIP_QTY_POS$1 SHIP_QTY_POS, AGGREGATOR.ORDR_QTY_POS$1 ORDR_QTY_POS, AGGREGATOR.SLS_QTY_POS$1 SLS_QTY_POS, AGGREGATOR.FRCST_SLS_QTY_POS$1 FRCST_SLS_QTY_POS, AGGREGATOR.FRCST_SLS_QTY_DC$1 FRCST_SLS_QTY_DC, AGGREGATOR.ON_HAND_QTY_DC$1 ON_HAND_QTY_DC, AGGREGATOR.ON_HAND_QTY_POS$1 ON_HAND_QTY_POS, AGGREGATOR.IN_TRANSIT_QTY_DC$1 IN_TRANSIT_QTY_DC, AGGREGATOR.RTL_ORG_CD ORG_CD, AGGREGATOR.MFG_ORG_CD$1 MFG_ORG_CD FROM (SELECT AGG_INPUT.LEVEL2$2 LEVEL2$1, AGG_INPUT.LEVEL1$2 LEVEL1$1, AGG_INPUT.WK_STRT_DT$1 WK_STRT_DT, MAX(AGG_INPUT.MFG_ORG_CD$2) MFG_ORG_CD$1, MAX(AGG_INPUT.BSNS_UNIT_TYP$1) BSNS_UNIT_TYP, SUM(AGG_INPUT.ON_HAND_QTY_DC$2) ON_HAND_QTY_DC$1, SUM(AGG_INPUT.ON_HAND_QTY_POS$2) ON_HAND_QTY_POS$1, SUM(AGG_INPUT.IN_TRANSIT_QTY_DC$2) IN_TRANSIT_QTY_DC$1, MAX(AGG_INPUT.RTL_ORG_CD$1) RTL_ORG_CD, SUM(AGG_INPUT.FRCST_SLS_QTY_POS$2) FRCST_SLS_QTY_POS$1, SUM(AGG_INPUT.FRCST_SLS_QTY_DC$2) FRCST_SLS_QTY_DC$1, SUM(AGG_INPUT.ORDR_QTY_POS$2) ORDR_QTY_POS$1, SUM(AGG_INPUT.SHIP_QTY_POS$2) SHIP_QTY_POS$1, SUM(AGG_INPUT.SLS_QTY_POS$2) SLS_QTY_POS$1 FROM (SELECT (AGGREGATOR_4.MFG_ORG_CD$3 ) MFG_ORG_CD$2, (AGGREGATOR_4.LEVEL2$3 ) LEVEL2$2, (AGGREGATOR_4.LEVEL1$3 ) LEVEL1$2, (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) BSNS_UNIT_TYP$1, (AGGREGATOR_4.WK_STRT_DT$2 ) WK_STRT_DT$1, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) ='DC' THEN ( CASE WHEN AGGREGATOR_4.ON_HAND_QTY_ALT != 0 THEN AGGREGATOR_4.ON_HAND_QTY_ALT END ) END ON_HAND_QTY_DC$2, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) != 'DC' THEN ( CASE WHEN AGGREGATOR_4.ON_HAND_QTY_ALT != 0 THEN AGGREGATOR_4.ON_HAND_QTY_ALT END ) END ON_HAND_QTY_POS$2, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) ='DC' THEN ( CASE WHEN AGGREGATOR_4.IN_TRANSIT_QTY_ALT != 0 THEN AGGREGATOR_4.IN_TRANSIT_QTY_ALT END ) END IN_TRANSIT_QTY_DC$2, (AGGREGATOR_4.RTL_ORG_CD$2 ) RTL_ORG_CD$1, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) != 'DC' THEN ( CASE WHEN AGGREGATOR_4.FRCST_SLS_QTY_ALT != 0 THEN AGGREGATOR_4.FRCST_SLS_QTY_ALT END ) END FRCST_SLS_QTY_POS$2, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) ='DC' THEN ( CASE WHEN AGGREGATOR_4.FRCST_SLS_QTY_ALT != 0 THEN AGGREGATOR_4.FRCST_SLS_QTY_ALT END ) END FRCST_SLS_QTY_DC$2, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) != 'DC' THEN ( CASE WHEN AGGREGATOR_4.ORDR_QTY_ALT != 0 THEN AGGREGATOR_4.ORDR_QTY_ALT END ) END ORDR_QTY_POS$2, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) ='DC' THEN ( CASE WHEN AGGREGATOR_4.SHIP_QTY_ALT != 0 THEN AGGREGATOR_4.SHIP_QTY_ALT END ) END SHIP_QTY_POS$2, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) != 'DC' THEN ( CASE WHEN AGGREGATOR_4.SLS_QTY_ALT != 0 THEN AGGREGATOR_4.SLS_QTY_ALT END ) END SLS_QTY_POS$2 FROM (SELECT AGG_INPUT$1.MFG_ORG_CD$4 MFG_ORG_CD$3, AGG_INPUT$1.LEVEL2$4 LEVEL2$3, AGG_INPUT$1.LEVEL1$4 LEVEL1$3, AGG_INPUT$1.BSNS_UNIT_TYP$3 BSNS_UNIT_TYP$2, AGG_INPUT$1.BSNS_WK_ID$1 BSNS_WK_ID, AGG_INPUT$1.WK_STRT_DT$3 WK_STRT_DT$2, SUM(AGG_INPUT$1.ON_HAND_QTY_ALT$1) ON_HAND_QTY_ALT, SUM(AGG_INPUT$1.IN_TRANSIT_QTY_ALT$1) IN_TRANSIT_QTY_ALT, MIN(DISTINCT AGG_INPUT$1.RTL_ORG_CD$3) RTL_ORG_CD$2, SUM(AGG_INPUT$1.FRCST_SLS_QTY_ALT$1) FRCST_SLS_QTY_ALT, SUM(AGG_INPUT$1.SLS_QTY_ALT$1) SLS_QTY_ALT, SUM(AGG_INPUT$1.ORDR_QTY_ALT$1) ORDR_QTY_ALT, SUM(AGG_INPUT$1.SHIP_QTY_ALT$1) SHIP_QTY_ALT FROM (SELECT SET_OPERATION.MFG_ORG_CD$5 MFG_ORG_CD$4, SET_OPERATION.RTL_ORG_CD$4 RTL_ORG_CD$3, SET_OPERATION.LEVEL1$5 LEVEL1$4, SET_OPERATION.ON_HAND_QTY_ALT$2 ON_HAND_QTY_ALT$1, SET_OPERATION.IN_TRANSIT_QTY_ALT$2 IN_TRANSIT_QTY_ALT$1, SET_OPERATION.BSNS_UNIT_TYP$4 BSNS_UNIT_TYP$3, SET_OPERATION.LEVEL2$5 LEVEL2$4, SET_OPERATION.BSNS_WK_ID$2 BSNS_WK_ID$1, SET_OPERATION.WK_STRT_DT$4 WK_STRT_DT$3, SET_OPERATION.FRCST_SLS_QTY_ALT$2 FRCST_SLS_QTY_ALT$1, SET_OPERATION.SLS_QTY_ALT$2 SLS_QTY_ALT$1, SET_OPERATION.ORDR_QTY_ALT$2 ORDR_QTY_ALT$1, SET_OPERATION.SHIP_QTY_ALT$2 SHIP_QTY_ALT$1 FROM (SELECT MFG_ORG_CD MFG_ORG_CD$5, RTL_ORG_CD RTL_ORG_CD$4, LEVEL1 LEVEL1$5, BSNS_UNIT_TYP BSNS_UNIT_TYP$4, LEVEL2 LEVEL2$5, BSNS_WK_ID BSNS_WK_ID$2, WK_STRT_DT WK_STRT_DT$4, ON_HAND_QTY_ALT ON_HAND_QTY_ALT$2, IN_TRANSIT_QTY_ALT IN_TRANSIT_QTY_ALT$2, FRCST_SLS_QTY_ALT FRCST_SLS_QTY_ALT$2, SLS_QTY_ALT SLS_QTY_ALT$2, ORDR_QTY_ALT ORDR_QTY_ALT$2, SHIP_QTY_ALT SHIP_QTY_ALT$2 FROM (SELECT SRC.MFG_ORG_CD MFG_ORG_CD, SRC.RTL_ORG_CD RTL_ORG_CD, SRC.MFG_SKU_ITEM_NBR LEVEL1, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.RTL_BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT, SRC.ON_HAND_QTY_ALT ON_HAND_QTY_ALT, SRC.IN_TRANSIT_QTY_ALT IN_TRANSIT_QTY_ALT, 0 FRCST_SLS_QTY_ALT, 0 SLS_QTY_ALT, 0 ORDR_QTY_ALT, 0 SHIP_QTY_ALT FROM (SELECT DDR_B_RTL_INV_ITEM_DAY.MFG_ORG_CD MFG_ORG_CD, DDR_B_RTL_INV_ITEM_DAY.RTL_ORG_CD RTL_ORG_CD, BSNS_UNIT.BSNS_UNIT_CD RTL_BSNS_UNIT_CD, DDR_B_RTL_INV_ITEM_DAY.ON_HAND_QTY_PRMRY ON_HAND_QTY_ALT, DDR_B_RTL_INV_ITEM_DAY.IN_TRANSIT_QTY_PRMRY IN_TRANSIT_QTY_ALT, DDR_R_DAY.CLNDR_DT CLNDR_DT, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, BSNS_UNIT.DFLT_WHSE DFLT_WHSE FROM DDR_B_RTL_INV_ITEM_DAY DDR_B_RTL_INV_ITEM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_RTL_INV_ITEM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID )) AND (( DDR_B_RTL_INV_ITEM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_INV_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT )) AND (( DDR_B_RTL_INV_ITEM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_INV_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_RTL_INV_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) AND (( DDR_B_RTL_INV_ITEM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_INV_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) >= SKU.EFF_FROM_DT )) AND (( DDR_B_RTL_INV_ITEM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_RTL_INV_ITEM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) ) WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD ) AND (DDR_R_DAY.CLNDR_DT BETWEEN add_months(SYSDATE, -12) AND add_months(SYSDATE, +12) ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD )) AND (( SRC.CLNDR_DT = DDR_R_BSNS_WK.WK_END_DT )) ) WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) UNION ALL SELECT INGRP1.MFG_ORG_CD MFG_ORG_CD, INGRP1.RTL_ORG_CD RTL_ORG_CD, INGRP1.LEVEL1 LEVEL1, INGRP1.BSNS_UNIT_TYP BSNS_UNIT_TYP, INGRP1.LEVEL2 LEVEL2, INGRP1.BSNS_WK_ID BSNS_WK_ID, INGRP1.WK_STRT_DT WK_STRT_DT, INGRP2.ON_HAND_QTY_ALT ON_HAND_QTY_ALT, INGRP2.IN_TRANSIT_QTY_ALT IN_TRANSIT_QTY_ALT, INGRP2.FRCST_SLS_QTY_ALT FRCST_SLS_QTY_ALT, INGRP2.SLS_QTY_ALT SLS_QTY_ALT, INGRP2.ORDR_QTY_ALT ORDR_QTY_ALT, INGRP2.SHIP_QTY_ALT SHIP_QTY_ALT FROM (SELECT AGGREGATOR_5.MFG_ORG_CD$6 MFG_ORG_CD, AGGREGATOR_5.LEVEL2$6 LEVEL2, AGGREGATOR_5.LEVEL1$6 LEVEL1, AGGREGATOR_5.BSNS_UNIT_TYP$5 BSNS_UNIT_TYP, AGGREGATOR_5.BSNS_WK_ID$3 BSNS_WK_ID, AGGREGATOR_5.WK_STRT_DT$5 WK_STRT_DT, AGGREGATOR_5.RTL_ORG_CD$5 RTL_ORG_CD, AGGREGATOR_5.FRCST_VRSN FRCST_VRSN, AGGREGATOR_5.CLNDR_DT CLNDR_DT, AGGREGATOR_5.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID FROM (SELECT AGG_INPUT$2.MFG_ORG_CD$7 MFG_ORG_CD$6, MAX(AGG_INPUT$2.LEVEL2$7) LEVEL2$6, AGG_INPUT$2.LEVEL1$7 LEVEL1$6, AGG_INPUT$2.BSNS_UNIT_TYP$6 BSNS_UNIT_TYP$5, AGG_INPUT$2.CLNDR_DT$1 CLNDR_DT, MAX(AGG_INPUT$2.FRCST_VRSN$1) FRCST_VRSN, MAX(AGG_INPUT$2.RTL_ORG_CD$6) RTL_ORG_CD$5, MAX(AGG_INPUT$2.BSNS_WK_ID$4) BSNS_WK_ID$3, MAX(AGG_INPUT$2.WK_STRT_DT$6) WK_STRT_DT$5, AGG_INPUT$2.ORG_BSNS_UNIT_ID$1 ORG_BSNS_UNIT_ID FROM (SELECT SRC.MFG_ORG_CD MFG_ORG_CD$7, SRC.RTL_ORG_CD RTL_ORG_CD$6, SRC.FRCST_VRSN FRCST_VRSN$1, SRC.MFG_SKU_ITEM_NBR LEVEL1$7, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2$7, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP$6, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID$4, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT$6, SRC.CLNDR_DT CLNDR_DT$1, SRC.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID$1 FROM (SELECT DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD MFG_ORG_CD, DDR_B_SLS_FRCST_ITEM_DAY.RTL_ORG_CD RTL_ORG_CD, DDR_B_SLS_FRCST_ITEM_DAY.FRCST_VRSN FRCST_VRSN, BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_R_DAY.CLNDR_DT CLNDR_DT, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, BSNS_UNIT.DFLT_WHSE DFLT_WHSE, DDR_B_SLS_FRCST_ITEM_DAY.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID FROM DDR_B_SLS_FRCST_ITEM_DAY DDR_B_SLS_FRCST_ITEM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_SLS_FRCST_ITEM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID )) AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT )) AND (( DDR_B_SLS_FRCST_ITEM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD )) AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) >= SKU.EFF_FROM_DT )) AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) ) WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD ) AND (DDR_R_DAY.CLNDR_DT BETWEEN add_months(SYSDATE, -12) AND add_months(SYSDATE, +12) ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD )) AND (( ( SRC.CLNDR_DT BETWEEN DDR_R_BSNS_WK.WK_STRT_DT AND DDR_R_BSNS_WK.WK_END_DT ) )) ) WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) ) AGG_INPUT$2 GROUP BY AGG_INPUT$2.MFG_ORG_CD$7 , AGG_INPUT$2.LEVEL1$7 , AGG_INPUT$2.BSNS_UNIT_TYP$6 , AGG_INPUT$2.CLNDR_DT$1, AGG_INPUT$2.ORG_BSNS_UNIT_ID$1 ) AGGREGATOR_5 ) INGRP1 JOIN (SELECT SRC.MFG_ORG_CD MFG_ORG_CD, SRC.RTL_ORG_CD RTL_ORG_CD, SRC.FRCST_VRSN FRCST_VRSN, SRC.MFG_SKU_ITEM_NBR LEVEL1, SRC.FRCST_SLS_QTY_ALT FRCST_SLS_QTY_ALT, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT, SRC.CLNDR_DT CLNDR_DT, SRC.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID, 0 ON_HAND_QTY_ALT, 0 IN_TRANSIT_QTY_ALT, 0 SLS_QTY_ALT, 0 ORDR_QTY_ALT, 0 SHIP_QTY_ALT FROM (SELECT DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD MFG_ORG_CD, DDR_B_SLS_FRCST_ITEM_DAY.RTL_ORG_CD RTL_ORG_CD, DDR_B_SLS_FRCST_ITEM_DAY.FRCST_VRSN FRCST_VRSN, BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_B_SLS_FRCST_ITEM_DAY.FRCST_SLS_QTY_PRMRY FRCST_SLS_QTY_ALT, DDR_R_DAY.CLNDR_DT CLNDR_DT, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, BSNS_UNIT.DFLT_WHSE DFLT_WHSE, DDR_B_SLS_FRCST_ITEM_DAY.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID FROM DDR_B_SLS_FRCST_ITEM_DAY DDR_B_SLS_FRCST_ITEM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_SLS_FRCST_ITEM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID )) AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT )) AND (( DDR_B_SLS_FRCST_ITEM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD )) AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) >= SKU.EFF_FROM_DT )) AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) ) WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD ) AND (DDR_R_DAY.CLNDR_DT BETWEEN add_months(SYSDATE, -12) AND add_months(SYSDATE, +12) /* OPERATOR JOINER_6 JOIN CONDITION */ ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD )) AND (( ( SRC.CLNDR_DT BETWEEN DDR_R_BSNS_WK.WK_STRT_DT AND DDR_R_BSNS_WK.WK_END_DT ) )) ) WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) ) INGRP2 ON ( (( INGRP1.ORG_BSNS_UNIT_ID = INGRP2.ORG_BSNS_UNIT_ID )) AND (( INGRP1.BSNS_UNIT_TYP = INGRP2.BSNS_UNIT_TYP )) AND (( INGRP1.BSNS_WK_ID = INGRP2.BSNS_WK_ID )) AND (( INGRP1.MFG_ORG_CD = INGRP2.MFG_ORG_CD )) AND (( INGRP1.CLNDR_DT || NULL = INGRP2.CLNDR_DT || NULL )) AND (( INGRP1.LEVEL1 = INGRP2.LEVEL1 )) AND (( INGRP1.RTL_ORG_CD = INGRP2.RTL_ORG_CD )) AND (( INGRP1.FRCST_VRSN = INGRP2.FRCST_VRSN )) ) UNION ALL SELECT SRC.MFG_ORG_CD MFG_ORG_CD, SRC.RTL_ORG_CD RTL_ORG_CD, SRC.MFG_SKU_ITEM_NBR LEVEL1, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT, 0 ON_HAND_QTY_ALT, 0 IN_TRANSIT_QTY_ALT, 0 FRCST_SLS_QTY_ALT, SRC.SLS_QTY_ALT SLS_QTY_ALT, 0 ORDR_QTY_ALT, 0 SHIP_QTY_ALT FROM (SELECT DDR_B_RTL_SL_RTN_ITM_DAY.MFG_ORG_CD MFG_ORG_CD, DDR_B_RTL_SL_RTN_ITM_DAY.RTL_ORG_CD RTL_ORG_CD, BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_B_RTL_SL_RTN_ITM_DAY.SLS_QTY_PRMRY SLS_QTY_ALT, DDR_R_DAY.CLNDR_DT CLNDR_DT, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, BSNS_UNIT.DFLT_WHSE DFLT_WHSE FROM DDR_B_RTL_SL_RTN_ITM_DAY DDR_B_RTL_SL_RTN_ITM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_RTL_SL_RTN_ITM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID )) AND (( DDR_B_RTL_SL_RTN_ITM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_SL_RTN_ITM_DAY.DAY_CD , 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT )) AND (( DDR_B_RTL_SL_RTN_ITM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_SL_RTN_ITM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_RTL_SL_RTN_ITM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) AND (( DDR_B_RTL_SL_RTN_ITM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_SL_RTN_ITM_DAY.DAY_CD , 'YYYYMMDD' ) >= SKU.EFF_FROM_DT )) AND (( DDR_B_RTL_SL_RTN_ITM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_RTL_SL_RTN_ITM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) ) WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD ) AND (DDR_R_DAY.CLNDR_DT BETWEEN add_months(SYSDATE, -12) AND add_months(SYSDATE, +12)) AND ( BSNS_UNIT.BSNS_UNIT_TYP != 'DC' ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD )) AND (( ( SRC.CLNDR_DT BETWEEN DDR_R_BSNS_WK.WK_STRT_DT AND DDR_R_BSNS_WK.WK_END_DT ) )) ) WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) UNION ALL SELECT SRC.MFG_ORG_CD MFG_ORG_CD, SRC.RTL_ORG_CD RTL_ORG_CD, SRC.MFG_SKU_ITEM_NBR LEVEL1, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.RTL_BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT, 0 ON_HAND_QTY_ALT, 0 IN_TRANSIT_QTY_ALT, 0 FRCST_SLS_QTY_ALT, 0 SLS_QTY_ALT, SRC.ORDR_QTY_ALT ORDR_QTY_ALT, 0 SHIP_QTY_ALT FROM (SELECT DDR_B_RTL_ORDR_ITEM_DAY.MFG_ORG_CD MFG_ORG_CD, DDR_B_RTL_ORDR_ITEM_DAY.RTL_ORG_CD RTL_ORG_CD, BSNS_UNIT.BSNS_UNIT_CD RTL_BSNS_UNIT_CD, DDR_B_RTL_ORDR_ITEM_DAY.ORDR_QTY_PRMRY ORDR_QTY_ALT, DDR_R_DAY.CLNDR_DT CLNDR_DT, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, BSNS_UNIT.DFLT_WHSE DFLT_WHSE FROM DDR_B_RTL_ORDR_ITEM_DAY DDR_B_RTL_ORDR_ITEM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_RTL_ORDR_ITEM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID )) AND (( DDR_B_RTL_ORDR_ITEM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_ORDR_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT )) AND (( DDR_B_RTL_ORDR_ITEM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_ORDR_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_RTL_ORDR_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) AND (( DDR_B_RTL_ORDR_ITEM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_ORDR_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) >= SKU.EFF_FROM_DT )) AND (( DDR_B_RTL_ORDR_ITEM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_RTL_ORDR_ITEM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) ) WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD ) AND (DDR_R_DAY.CLNDR_DT BETWEEN add_months(SYSDATE, -12) AND add_months(SYSDATE, +12)) AND ( BSNS_UNIT.BSNS_UNIT_TYP != 'DC' ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD )) AND (( ( SRC.CLNDR_DT BETWEEN DDR_R_BSNS_WK.WK_STRT_DT AND DDR_R_BSNS_WK.WK_END_DT ) )) ) WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) UNION ALL SELECT SRC.MFG_ORG_CD MFG_ORG_CD, SRC.RTL_ORG_CD RTL_ORG_CD, SRC.MFG_SKU_ITEM_NBR LEVEL1, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.RTL_BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT, 0 ON_HAND_QTY_ALT, 0 IN_TRANSIT_QTY_ALT, 0 FRCST_SLS_QTY_ALT, 0 SLS_QTY_ALT, 0 ORDR_QTY_ALT, SRC.SHIP_QTY_ALT SHIP_QTY_ALT FROM (SELECT DDR_B_RTL_SHIP_ITEM_DAY.MFG_ORG_CD MFG_ORG_CD, DDR_B_RTL_SHIP_ITEM_DAY.RTL_ORG_CD RTL_ORG_CD, BSNS_UNIT.BSNS_UNIT_CD RTL_BSNS_UNIT_CD, DDR_B_RTL_SHIP_ITEM_DAY.SHIP_QTY_PRMRY SHIP_QTY_ALT, DDR_R_DAY.CLNDR_DT CLNDR_DT, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, BSNS_UNIT.DFLT_WHSE DFLT_WHSE, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR FROM DDR_B_RTL_SHIP_ITEM_DAY DDR_B_RTL_SHIP_ITEM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_RTL_SHIP_ITEM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID )) AND (( DDR_B_RTL_SHIP_ITEM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_SHIP_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT )) AND (( DDR_B_RTL_SHIP_ITEM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_SHIP_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_RTL_SHIP_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT , TO_DATE ( '31-DEC-9999' , 'DD-MON-YYYY' ) ) )) AND (( DDR_B_RTL_SHIP_ITEM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD )) AND (( TO_DATE ( DDR_B_RTL_SHIP_ITEM_DAY.DAY_CD , 'YYYYMMDD' ) >= SKU.EFF_FROM_DT )) AND (( DDR_B_RTL_SHIP_ITEM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_RTL_SHIP_ITEM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) ) WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD ) AND (DDR_R_DAY.CLNDR_DT BETWEEN add_months(SYSDATE, -12) AND add_months(SYSDATE, +12)) AND ( BSNS_UNIT.BSNS_UNIT_TYP = 'DC' ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD )) AND (( ( SRC.CLNDR_DT BETWEEN DDR_R_BSNS_WK.WK_STRT_DT AND DDR_R_BSNS_WK.WK_END_DT ) )) ) WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) ) ) SET_OPERATION ) AGG_INPUT$1 GROUP BY AGG_INPUT$1.MFG_ORG_CD$4 , AGG_INPUT$1.LEVEL2$4 , AGG_INPUT$1.LEVEL1$4 , AGG_INPUT$1.BSNS_UNIT_TYP$3 , AGG_INPUT$1.BSNS_WK_ID$1, AGG_INPUT$1.WK_STRT_DT$3 ) AGGREGATOR_4 ) AGG_INPUT GROUP BY AGG_INPUT.LEVEL2$2 , AGG_INPUT.LEVEL1$2, AGG_INPUT.WK_STRT_DT$1 ) AGGREGATOR ) SRC LEFT OUTER JOIN (SELECT DDR_DMNT_ITEM_MAP.MFG_ORG_CD MFG_ORG_CD, DDR_DMNT_ITEM_MAP.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR, DDR_DMNT_ITEM_MAP.MFG_ITEM_EXT_ID MFG_ITEM_EXT_ID FROM DDR_DMNT_ITEM_MAP DDR_DMNT_ITEM_MAP ) DDR_ITEM ON ( (( SRC.MFG_ORG_CD = DDR_ITEM.MFG_ORG_CD )) AND (( SRC.LEVEL1 = DDR_ITEM.MFG_SKU_ITEM_NBR )) ) LEFT OUTER JOIN (SELECT DDR_DMNT_LOCATION_MAP.MFG_ORG_CD MFG_ORG_CD, DDR_DMNT_LOCATION_MAP.ORG_CD ORG_CD, DDR_DMNT_LOCATION_MAP.BSNS_UNIT_CD BSNS_UNIT_CD, DDR_DMNT_LOCATION_MAP.MFG_LOC_EXT_ID MFG_LOC_EXT_ID FROM DDR_DMNT_LOCATION_MAP DDR_DMNT_LOCATION_MAP ) DDR_LOC ON ( (( SRC.LEVEL2 = DDR_LOC.BSNS_UNIT_CD )) AND (( SRC.MFG_ORG_CD = DDR_LOC.MFG_ORG_CD )) )

View Text - HTML Formatted

SELECT SRC.SDATE SDATE
, SRC.LEVEL1 LEVEL1
, SRC.LEVEL2 LEVEL2
, SRC.SHIP_QTY_POS SHIP_QTY_POS
, SRC.ORDR_QTY_POS ORDR_QTY_POS
, SRC.SLS_QTY_POS SLS_QTY_POS
, SRC.FRCST_SLS_QTY_POS FRCST_SLS_QTY_POS
, SRC.FRCST_SLS_QTY_DC FRCST_SLS_QTY_DC
, SRC.ON_HAND_QTY_DC ON_HAND_QTY_DC
, SRC.ON_HAND_QTY_POS ON_HAND_QTY_POS
, SRC.IN_TRANSIT_QTY_DC IN_TRANSIT_QTY_DC
, DDR_ITEM.MFG_ORG_CD MFG_ORG_CD
, DDR_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, DDR_ITEM.MFG_ITEM_EXT_ID MFG_ITEM_EXT_ID
, DDR_LOC.MFG_ORG_CD MFG_ORG_CD_1
, DDR_LOC.ORG_CD ORG_CD
, DDR_LOC.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_LOC.MFG_LOC_EXT_ID MFG_LOC_EXT_ID
FROM (SELECT AGGREGATOR.WK_STRT_DT SDATE
, AGGREGATOR.LEVEL1$1 LEVEL1
, AGGREGATOR.LEVEL2$1 LEVEL2
, AGGREGATOR.SHIP_QTY_POS$1 SHIP_QTY_POS
, AGGREGATOR.ORDR_QTY_POS$1 ORDR_QTY_POS
, AGGREGATOR.SLS_QTY_POS$1 SLS_QTY_POS
, AGGREGATOR.FRCST_SLS_QTY_POS$1 FRCST_SLS_QTY_POS
, AGGREGATOR.FRCST_SLS_QTY_DC$1 FRCST_SLS_QTY_DC
, AGGREGATOR.ON_HAND_QTY_DC$1 ON_HAND_QTY_DC
, AGGREGATOR.ON_HAND_QTY_POS$1 ON_HAND_QTY_POS
, AGGREGATOR.IN_TRANSIT_QTY_DC$1 IN_TRANSIT_QTY_DC
, AGGREGATOR.RTL_ORG_CD ORG_CD
, AGGREGATOR.MFG_ORG_CD$1 MFG_ORG_CD
FROM (SELECT AGG_INPUT.LEVEL2$2 LEVEL2$1
, AGG_INPUT.LEVEL1$2 LEVEL1$1
, AGG_INPUT.WK_STRT_DT$1 WK_STRT_DT
, MAX(AGG_INPUT.MFG_ORG_CD$2) MFG_ORG_CD$1
, MAX(AGG_INPUT.BSNS_UNIT_TYP$1) BSNS_UNIT_TYP
, SUM(AGG_INPUT.ON_HAND_QTY_DC$2) ON_HAND_QTY_DC$1
, SUM(AGG_INPUT.ON_HAND_QTY_POS$2) ON_HAND_QTY_POS$1
, SUM(AGG_INPUT.IN_TRANSIT_QTY_DC$2) IN_TRANSIT_QTY_DC$1
, MAX(AGG_INPUT.RTL_ORG_CD$1) RTL_ORG_CD
, SUM(AGG_INPUT.FRCST_SLS_QTY_POS$2) FRCST_SLS_QTY_POS$1
, SUM(AGG_INPUT.FRCST_SLS_QTY_DC$2) FRCST_SLS_QTY_DC$1
, SUM(AGG_INPUT.ORDR_QTY_POS$2) ORDR_QTY_POS$1
, SUM(AGG_INPUT.SHIP_QTY_POS$2) SHIP_QTY_POS$1
, SUM(AGG_INPUT.SLS_QTY_POS$2) SLS_QTY_POS$1
FROM (SELECT (AGGREGATOR_4.MFG_ORG_CD$3 ) MFG_ORG_CD$2
, (AGGREGATOR_4.LEVEL2$3 ) LEVEL2$2
, (AGGREGATOR_4.LEVEL1$3 ) LEVEL1$2
, (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) BSNS_UNIT_TYP$1
, (AGGREGATOR_4.WK_STRT_DT$2 ) WK_STRT_DT$1
, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) ='DC' THEN ( CASE WHEN AGGREGATOR_4.ON_HAND_QTY_ALT != 0 THEN AGGREGATOR_4.ON_HAND_QTY_ALT END ) END ON_HAND_QTY_DC$2
, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) != 'DC' THEN ( CASE WHEN AGGREGATOR_4.ON_HAND_QTY_ALT != 0 THEN AGGREGATOR_4.ON_HAND_QTY_ALT END ) END ON_HAND_QTY_POS$2
, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) ='DC' THEN ( CASE WHEN AGGREGATOR_4.IN_TRANSIT_QTY_ALT != 0 THEN AGGREGATOR_4.IN_TRANSIT_QTY_ALT END ) END IN_TRANSIT_QTY_DC$2
, (AGGREGATOR_4.RTL_ORG_CD$2 ) RTL_ORG_CD$1
, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) != 'DC' THEN ( CASE WHEN AGGREGATOR_4.FRCST_SLS_QTY_ALT != 0 THEN AGGREGATOR_4.FRCST_SLS_QTY_ALT END ) END FRCST_SLS_QTY_POS$2
, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) ='DC' THEN ( CASE WHEN AGGREGATOR_4.FRCST_SLS_QTY_ALT != 0 THEN AGGREGATOR_4.FRCST_SLS_QTY_ALT END ) END FRCST_SLS_QTY_DC$2
, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) != 'DC' THEN ( CASE WHEN AGGREGATOR_4.ORDR_QTY_ALT != 0 THEN AGGREGATOR_4.ORDR_QTY_ALT END ) END ORDR_QTY_POS$2
, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) ='DC' THEN ( CASE WHEN AGGREGATOR_4.SHIP_QTY_ALT != 0 THEN AGGREGATOR_4.SHIP_QTY_ALT END ) END SHIP_QTY_POS$2
, CASE WHEN (AGGREGATOR_4.BSNS_UNIT_TYP$2 ) != 'DC' THEN ( CASE WHEN AGGREGATOR_4.SLS_QTY_ALT != 0 THEN AGGREGATOR_4.SLS_QTY_ALT END ) END SLS_QTY_POS$2
FROM (SELECT AGG_INPUT$1.MFG_ORG_CD$4 MFG_ORG_CD$3
, AGG_INPUT$1.LEVEL2$4 LEVEL2$3
, AGG_INPUT$1.LEVEL1$4 LEVEL1$3
, AGG_INPUT$1.BSNS_UNIT_TYP$3 BSNS_UNIT_TYP$2
, AGG_INPUT$1.BSNS_WK_ID$1 BSNS_WK_ID
, AGG_INPUT$1.WK_STRT_DT$3 WK_STRT_DT$2
, SUM(AGG_INPUT$1.ON_HAND_QTY_ALT$1) ON_HAND_QTY_ALT
, SUM(AGG_INPUT$1.IN_TRANSIT_QTY_ALT$1) IN_TRANSIT_QTY_ALT
, MIN(DISTINCT AGG_INPUT$1.RTL_ORG_CD$3) RTL_ORG_CD$2
, SUM(AGG_INPUT$1.FRCST_SLS_QTY_ALT$1) FRCST_SLS_QTY_ALT
, SUM(AGG_INPUT$1.SLS_QTY_ALT$1) SLS_QTY_ALT
, SUM(AGG_INPUT$1.ORDR_QTY_ALT$1) ORDR_QTY_ALT
, SUM(AGG_INPUT$1.SHIP_QTY_ALT$1) SHIP_QTY_ALT
FROM (SELECT SET_OPERATION.MFG_ORG_CD$5 MFG_ORG_CD$4
, SET_OPERATION.RTL_ORG_CD$4 RTL_ORG_CD$3
, SET_OPERATION.LEVEL1$5 LEVEL1$4
, SET_OPERATION.ON_HAND_QTY_ALT$2 ON_HAND_QTY_ALT$1
, SET_OPERATION.IN_TRANSIT_QTY_ALT$2 IN_TRANSIT_QTY_ALT$1
, SET_OPERATION.BSNS_UNIT_TYP$4 BSNS_UNIT_TYP$3
, SET_OPERATION.LEVEL2$5 LEVEL2$4
, SET_OPERATION.BSNS_WK_ID$2 BSNS_WK_ID$1
, SET_OPERATION.WK_STRT_DT$4 WK_STRT_DT$3
, SET_OPERATION.FRCST_SLS_QTY_ALT$2 FRCST_SLS_QTY_ALT$1
, SET_OPERATION.SLS_QTY_ALT$2 SLS_QTY_ALT$1
, SET_OPERATION.ORDR_QTY_ALT$2 ORDR_QTY_ALT$1
, SET_OPERATION.SHIP_QTY_ALT$2 SHIP_QTY_ALT$1
FROM (SELECT MFG_ORG_CD MFG_ORG_CD$5
, RTL_ORG_CD RTL_ORG_CD$4
, LEVEL1 LEVEL1$5
, BSNS_UNIT_TYP BSNS_UNIT_TYP$4
, LEVEL2 LEVEL2$5
, BSNS_WK_ID BSNS_WK_ID$2
, WK_STRT_DT WK_STRT_DT$4
, ON_HAND_QTY_ALT ON_HAND_QTY_ALT$2
, IN_TRANSIT_QTY_ALT IN_TRANSIT_QTY_ALT$2
, FRCST_SLS_QTY_ALT FRCST_SLS_QTY_ALT$2
, SLS_QTY_ALT SLS_QTY_ALT$2
, ORDR_QTY_ALT ORDR_QTY_ALT$2
, SHIP_QTY_ALT SHIP_QTY_ALT$2
FROM (SELECT SRC.MFG_ORG_CD MFG_ORG_CD
, SRC.RTL_ORG_CD RTL_ORG_CD
, SRC.MFG_SKU_ITEM_NBR LEVEL1
, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP
, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.RTL_BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2
, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID
, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT
, SRC.ON_HAND_QTY_ALT ON_HAND_QTY_ALT
, SRC.IN_TRANSIT_QTY_ALT IN_TRANSIT_QTY_ALT
, 0 FRCST_SLS_QTY_ALT
, 0 SLS_QTY_ALT
, 0 ORDR_QTY_ALT
, 0 SHIP_QTY_ALT
FROM (SELECT DDR_B_RTL_INV_ITEM_DAY.MFG_ORG_CD MFG_ORG_CD
, DDR_B_RTL_INV_ITEM_DAY.RTL_ORG_CD RTL_ORG_CD
, BSNS_UNIT.BSNS_UNIT_CD RTL_BSNS_UNIT_CD
, DDR_B_RTL_INV_ITEM_DAY.ON_HAND_QTY_PRMRY ON_HAND_QTY_ALT
, DDR_B_RTL_INV_ITEM_DAY.IN_TRANSIT_QTY_PRMRY IN_TRANSIT_QTY_ALT
, DDR_R_DAY.CLNDR_DT CLNDR_DT
, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, BSNS_UNIT.DFLT_WHSE DFLT_WHSE
FROM DDR_B_RTL_INV_ITEM_DAY DDR_B_RTL_INV_ITEM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD
, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE
, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID
, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT
, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT
FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_RTL_INV_ITEM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID ))
AND (( DDR_B_RTL_INV_ITEM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_INV_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT ))
AND (( DDR_B_RTL_INV_ITEM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_INV_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID
, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT
, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT
FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_RTL_INV_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) ))
AND (( DDR_B_RTL_INV_ITEM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_INV_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) >= SKU.EFF_FROM_DT ))
AND (( DDR_B_RTL_INV_ITEM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_RTL_INV_ITEM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) )
WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD )
AND (DDR_R_DAY.CLNDR_DT BETWEEN ADD_MONTHS(SYSDATE
, -12)
AND ADD_MONTHS(SYSDATE
, +12) ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD ))
AND (( SRC.CLNDR_DT = DDR_R_BSNS_WK.WK_END_DT )) )
WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) UNION ALL SELECT INGRP1.MFG_ORG_CD MFG_ORG_CD
, INGRP1.RTL_ORG_CD RTL_ORG_CD
, INGRP1.LEVEL1 LEVEL1
, INGRP1.BSNS_UNIT_TYP BSNS_UNIT_TYP
, INGRP1.LEVEL2 LEVEL2
, INGRP1.BSNS_WK_ID BSNS_WK_ID
, INGRP1.WK_STRT_DT WK_STRT_DT
, INGRP2.ON_HAND_QTY_ALT ON_HAND_QTY_ALT
, INGRP2.IN_TRANSIT_QTY_ALT IN_TRANSIT_QTY_ALT
, INGRP2.FRCST_SLS_QTY_ALT FRCST_SLS_QTY_ALT
, INGRP2.SLS_QTY_ALT SLS_QTY_ALT
, INGRP2.ORDR_QTY_ALT ORDR_QTY_ALT
, INGRP2.SHIP_QTY_ALT SHIP_QTY_ALT
FROM (SELECT AGGREGATOR_5.MFG_ORG_CD$6 MFG_ORG_CD
, AGGREGATOR_5.LEVEL2$6 LEVEL2
, AGGREGATOR_5.LEVEL1$6 LEVEL1
, AGGREGATOR_5.BSNS_UNIT_TYP$5 BSNS_UNIT_TYP
, AGGREGATOR_5.BSNS_WK_ID$3 BSNS_WK_ID
, AGGREGATOR_5.WK_STRT_DT$5 WK_STRT_DT
, AGGREGATOR_5.RTL_ORG_CD$5 RTL_ORG_CD
, AGGREGATOR_5.FRCST_VRSN FRCST_VRSN
, AGGREGATOR_5.CLNDR_DT CLNDR_DT
, AGGREGATOR_5.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID
FROM (SELECT AGG_INPUT$2.MFG_ORG_CD$7 MFG_ORG_CD$6
, MAX(AGG_INPUT$2.LEVEL2$7) LEVEL2$6
, AGG_INPUT$2.LEVEL1$7 LEVEL1$6
, AGG_INPUT$2.BSNS_UNIT_TYP$6 BSNS_UNIT_TYP$5
, AGG_INPUT$2.CLNDR_DT$1 CLNDR_DT
, MAX(AGG_INPUT$2.FRCST_VRSN$1) FRCST_VRSN
, MAX(AGG_INPUT$2.RTL_ORG_CD$6) RTL_ORG_CD$5
, MAX(AGG_INPUT$2.BSNS_WK_ID$4) BSNS_WK_ID$3
, MAX(AGG_INPUT$2.WK_STRT_DT$6) WK_STRT_DT$5
, AGG_INPUT$2.ORG_BSNS_UNIT_ID$1 ORG_BSNS_UNIT_ID
FROM (SELECT SRC.MFG_ORG_CD MFG_ORG_CD$7
, SRC.RTL_ORG_CD RTL_ORG_CD$6
, SRC.FRCST_VRSN FRCST_VRSN$1
, SRC.MFG_SKU_ITEM_NBR LEVEL1$7
, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2$7
, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP$6
, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID$4
, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT$6
, SRC.CLNDR_DT CLNDR_DT$1
, SRC.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID$1
FROM (SELECT DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD MFG_ORG_CD
, DDR_B_SLS_FRCST_ITEM_DAY.RTL_ORG_CD RTL_ORG_CD
, DDR_B_SLS_FRCST_ITEM_DAY.FRCST_VRSN FRCST_VRSN
, BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_R_DAY.CLNDR_DT CLNDR_DT
, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, BSNS_UNIT.DFLT_WHSE DFLT_WHSE
, DDR_B_SLS_FRCST_ITEM_DAY.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID
FROM DDR_B_SLS_FRCST_ITEM_DAY DDR_B_SLS_FRCST_ITEM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD
, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE
, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID
, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT
, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT
FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_SLS_FRCST_ITEM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID ))
AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT ))
AND (( DDR_B_SLS_FRCST_ITEM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD ))
AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID
, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT
, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT
FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) ))
AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) >= SKU.EFF_FROM_DT ))
AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) )
WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD )
AND (DDR_R_DAY.CLNDR_DT BETWEEN ADD_MONTHS(SYSDATE
, -12)
AND ADD_MONTHS(SYSDATE
, +12) ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD ))
AND (( ( SRC.CLNDR_DT BETWEEN DDR_R_BSNS_WK.WK_STRT_DT
AND DDR_R_BSNS_WK.WK_END_DT ) )) )
WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) ) AGG_INPUT$2 GROUP BY AGG_INPUT$2.MFG_ORG_CD$7
, AGG_INPUT$2.LEVEL1$7
, AGG_INPUT$2.BSNS_UNIT_TYP$6
, AGG_INPUT$2.CLNDR_DT$1
, AGG_INPUT$2.ORG_BSNS_UNIT_ID$1 ) AGGREGATOR_5 ) INGRP1 JOIN (SELECT SRC.MFG_ORG_CD MFG_ORG_CD
, SRC.RTL_ORG_CD RTL_ORG_CD
, SRC.FRCST_VRSN FRCST_VRSN
, SRC.MFG_SKU_ITEM_NBR LEVEL1
, SRC.FRCST_SLS_QTY_ALT FRCST_SLS_QTY_ALT
, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2
, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP
, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID
, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT
, SRC.CLNDR_DT CLNDR_DT
, SRC.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID
, 0 ON_HAND_QTY_ALT
, 0 IN_TRANSIT_QTY_ALT
, 0 SLS_QTY_ALT
, 0 ORDR_QTY_ALT
, 0 SHIP_QTY_ALT
FROM (SELECT DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD MFG_ORG_CD
, DDR_B_SLS_FRCST_ITEM_DAY.RTL_ORG_CD RTL_ORG_CD
, DDR_B_SLS_FRCST_ITEM_DAY.FRCST_VRSN FRCST_VRSN
, BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_B_SLS_FRCST_ITEM_DAY.FRCST_SLS_QTY_PRMRY FRCST_SLS_QTY_ALT
, DDR_R_DAY.CLNDR_DT CLNDR_DT
, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, BSNS_UNIT.DFLT_WHSE DFLT_WHSE
, DDR_B_SLS_FRCST_ITEM_DAY.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID
FROM DDR_B_SLS_FRCST_ITEM_DAY DDR_B_SLS_FRCST_ITEM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD
, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE
, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID
, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT
, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT
FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_SLS_FRCST_ITEM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID ))
AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT ))
AND (( DDR_B_SLS_FRCST_ITEM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD ))
AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID
, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT
, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT
FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) ))
AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) >= SKU.EFF_FROM_DT ))
AND (( DDR_B_SLS_FRCST_ITEM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_SLS_FRCST_ITEM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) )
WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD )
AND (DDR_R_DAY.CLNDR_DT BETWEEN ADD_MONTHS(SYSDATE
, -12)
AND ADD_MONTHS(SYSDATE
, +12) /* OPERATOR JOINER_6 JOIN CONDITION */ ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD ))
AND (( ( SRC.CLNDR_DT BETWEEN DDR_R_BSNS_WK.WK_STRT_DT
AND DDR_R_BSNS_WK.WK_END_DT ) )) )
WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) ) INGRP2 ON ( (( INGRP1.ORG_BSNS_UNIT_ID = INGRP2.ORG_BSNS_UNIT_ID ))
AND (( INGRP1.BSNS_UNIT_TYP = INGRP2.BSNS_UNIT_TYP ))
AND (( INGRP1.BSNS_WK_ID = INGRP2.BSNS_WK_ID ))
AND (( INGRP1.MFG_ORG_CD = INGRP2.MFG_ORG_CD ))
AND (( INGRP1.CLNDR_DT || NULL = INGRP2.CLNDR_DT || NULL ))
AND (( INGRP1.LEVEL1 = INGRP2.LEVEL1 ))
AND (( INGRP1.RTL_ORG_CD = INGRP2.RTL_ORG_CD ))
AND (( INGRP1.FRCST_VRSN = INGRP2.FRCST_VRSN )) ) UNION ALL SELECT SRC.MFG_ORG_CD MFG_ORG_CD
, SRC.RTL_ORG_CD RTL_ORG_CD
, SRC.MFG_SKU_ITEM_NBR LEVEL1
, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP
, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2
, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID
, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT
, 0 ON_HAND_QTY_ALT
, 0 IN_TRANSIT_QTY_ALT
, 0 FRCST_SLS_QTY_ALT
, SRC.SLS_QTY_ALT SLS_QTY_ALT
, 0 ORDR_QTY_ALT
, 0 SHIP_QTY_ALT
FROM (SELECT DDR_B_RTL_SL_RTN_ITM_DAY.MFG_ORG_CD MFG_ORG_CD
, DDR_B_RTL_SL_RTN_ITM_DAY.RTL_ORG_CD RTL_ORG_CD
, BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_B_RTL_SL_RTN_ITM_DAY.SLS_QTY_PRMRY SLS_QTY_ALT
, DDR_R_DAY.CLNDR_DT CLNDR_DT
, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, BSNS_UNIT.DFLT_WHSE DFLT_WHSE
FROM DDR_B_RTL_SL_RTN_ITM_DAY DDR_B_RTL_SL_RTN_ITM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD
, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE
, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID
, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT
, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT
FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_RTL_SL_RTN_ITM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID ))
AND (( DDR_B_RTL_SL_RTN_ITM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_SL_RTN_ITM_DAY.DAY_CD
, 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT ))
AND (( DDR_B_RTL_SL_RTN_ITM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_SL_RTN_ITM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID
, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT
, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT
FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_RTL_SL_RTN_ITM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) ))
AND (( DDR_B_RTL_SL_RTN_ITM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_SL_RTN_ITM_DAY.DAY_CD
, 'YYYYMMDD' ) >= SKU.EFF_FROM_DT ))
AND (( DDR_B_RTL_SL_RTN_ITM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_RTL_SL_RTN_ITM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) )
WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD )
AND (DDR_R_DAY.CLNDR_DT BETWEEN ADD_MONTHS(SYSDATE
, -12)
AND ADD_MONTHS(SYSDATE
, +12))
AND ( BSNS_UNIT.BSNS_UNIT_TYP != 'DC' ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD ))
AND (( ( SRC.CLNDR_DT BETWEEN DDR_R_BSNS_WK.WK_STRT_DT
AND DDR_R_BSNS_WK.WK_END_DT ) )) )
WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) UNION ALL SELECT SRC.MFG_ORG_CD MFG_ORG_CD
, SRC.RTL_ORG_CD RTL_ORG_CD
, SRC.MFG_SKU_ITEM_NBR LEVEL1
, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP
, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.RTL_BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2
, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID
, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT
, 0 ON_HAND_QTY_ALT
, 0 IN_TRANSIT_QTY_ALT
, 0 FRCST_SLS_QTY_ALT
, 0 SLS_QTY_ALT
, SRC.ORDR_QTY_ALT ORDR_QTY_ALT
, 0 SHIP_QTY_ALT
FROM (SELECT DDR_B_RTL_ORDR_ITEM_DAY.MFG_ORG_CD MFG_ORG_CD
, DDR_B_RTL_ORDR_ITEM_DAY.RTL_ORG_CD RTL_ORG_CD
, BSNS_UNIT.BSNS_UNIT_CD RTL_BSNS_UNIT_CD
, DDR_B_RTL_ORDR_ITEM_DAY.ORDR_QTY_PRMRY ORDR_QTY_ALT
, DDR_R_DAY.CLNDR_DT CLNDR_DT
, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, BSNS_UNIT.DFLT_WHSE DFLT_WHSE
FROM DDR_B_RTL_ORDR_ITEM_DAY DDR_B_RTL_ORDR_ITEM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD
, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE
, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID
, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT
, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT
FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_RTL_ORDR_ITEM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID ))
AND (( DDR_B_RTL_ORDR_ITEM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_ORDR_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT ))
AND (( DDR_B_RTL_ORDR_ITEM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_ORDR_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID
, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT
, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT
FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_RTL_ORDR_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) ))
AND (( DDR_B_RTL_ORDR_ITEM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_ORDR_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) >= SKU.EFF_FROM_DT ))
AND (( DDR_B_RTL_ORDR_ITEM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_RTL_ORDR_ITEM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) )
WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD )
AND (DDR_R_DAY.CLNDR_DT BETWEEN ADD_MONTHS(SYSDATE
, -12)
AND ADD_MONTHS(SYSDATE
, +12))
AND ( BSNS_UNIT.BSNS_UNIT_TYP != 'DC' ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD ))
AND (( ( SRC.CLNDR_DT BETWEEN DDR_R_BSNS_WK.WK_STRT_DT
AND DDR_R_BSNS_WK.WK_END_DT ) )) )
WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) UNION ALL SELECT SRC.MFG_ORG_CD MFG_ORG_CD
, SRC.RTL_ORG_CD RTL_ORG_CD
, SRC.MFG_SKU_ITEM_NBR LEVEL1
, SRC.BSNS_UNIT_TYP BSNS_UNIT_TYP
, CASE WHEN SRC.BSNS_UNIT_TYP ='DC' THEN SRC.RTL_BSNS_UNIT_CD ELSE SRC.DFLT_WHSE END LEVEL2
, DDR_R_BSNS_WK.BSNS_WK_ID BSNS_WK_ID
, DDR_R_BSNS_WK.WK_STRT_DT WK_STRT_DT
, 0 ON_HAND_QTY_ALT
, 0 IN_TRANSIT_QTY_ALT
, 0 FRCST_SLS_QTY_ALT
, 0 SLS_QTY_ALT
, 0 ORDR_QTY_ALT
, SRC.SHIP_QTY_ALT SHIP_QTY_ALT
FROM (SELECT DDR_B_RTL_SHIP_ITEM_DAY.MFG_ORG_CD MFG_ORG_CD
, DDR_B_RTL_SHIP_ITEM_DAY.RTL_ORG_CD RTL_ORG_CD
, BSNS_UNIT.BSNS_UNIT_CD RTL_BSNS_UNIT_CD
, DDR_B_RTL_SHIP_ITEM_DAY.SHIP_QTY_PRMRY SHIP_QTY_ALT
, DDR_R_DAY.CLNDR_DT CLNDR_DT
, BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, BSNS_UNIT.DFLT_WHSE DFLT_WHSE
, SKU.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
FROM DDR_B_RTL_SHIP_ITEM_DAY DDR_B_RTL_SHIP_ITEM_DAY LEFT OUTER JOIN (SELECT DDR_R_ORG_BSNS_UNIT.MFG_ORG_CD MFG_ORG_CD
, DDR_R_ORG_BSNS_UNIT.ORG_CD ORG_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_R_ORG_BSNS_UNIT.BSNS_UNIT_TYP BSNS_UNIT_TYP
, DDR_R_ORG_BSNS_UNIT.DFLT_WHSE DFLT_WHSE
, DDR_R_ORG_BSNS_UNIT.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID
, DDR_R_ORG_BSNS_UNIT.EFF_FROM_DT EFF_FROM_DT
, DDR_R_ORG_BSNS_UNIT.EFF_TO_DT EFF_TO_DT
FROM DDR_R_ORG_BSNS_UNIT DDR_R_ORG_BSNS_UNIT ) BSNS_UNIT ON ( (( DDR_B_RTL_SHIP_ITEM_DAY.ORG_BSNS_UNIT_ID = BSNS_UNIT.ORG_BSNS_UNIT_ID ))
AND (( DDR_B_RTL_SHIP_ITEM_DAY.MFG_ORG_CD = BSNS_UNIT.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_SHIP_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) >= BSNS_UNIT.EFF_FROM_DT ))
AND (( DDR_B_RTL_SHIP_ITEM_DAY.RTL_ORG_CD = BSNS_UNIT.ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_SHIP_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( BSNS_UNIT.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) )) ) LEFT OUTER JOIN (SELECT DDR_R_MFG_SKU_ITEM.MFG_ORG_CD MFG_ORG_CD
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, DDR_R_MFG_SKU_ITEM.ORG_CD ORG_CD
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID GLBL_ITEM_ID
, DDR_R_MFG_SKU_ITEM.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP
, DDR_R_MFG_SKU_ITEM.MFG_SKU_ITEM_ID MFG_SKU_ITEM_ID
, DDR_R_MFG_SKU_ITEM.EFF_FROM_DT EFF_FROM_DT
, DDR_R_MFG_SKU_ITEM.EFF_TO_DT EFF_TO_DT
FROM DDR_R_MFG_SKU_ITEM DDR_R_MFG_SKU_ITEM ) SKU ON ( (( TO_DATE ( DDR_B_RTL_SHIP_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) <= NVL ( SKU.EFF_TO_DT
, TO_DATE ( '31-DEC-9999'
, 'DD-MON-YYYY' ) ) ))
AND (( DDR_B_RTL_SHIP_ITEM_DAY.MFG_ORG_CD = SKU.MFG_ORG_CD ))
AND (( TO_DATE ( DDR_B_RTL_SHIP_ITEM_DAY.DAY_CD
, 'YYYYMMDD' ) >= SKU.EFF_FROM_DT ))
AND (( DDR_B_RTL_SHIP_ITEM_DAY.MFG_SKU_ITEM_ID = SKU.MFG_SKU_ITEM_ID )) ) JOIN DDR_R_DAY DDR_R_DAY ON ( ( DDR_B_RTL_SHIP_ITEM_DAY.DAY_CD = DDR_R_DAY.DAY_CD ) )
WHERE ( SKU.MFG_ORG_CD = SKU.ORG_CD )
AND (DDR_R_DAY.CLNDR_DT BETWEEN ADD_MONTHS(SYSDATE
, -12)
AND ADD_MONTHS(SYSDATE
, +12))
AND ( BSNS_UNIT.BSNS_UNIT_TYP = 'DC' ) ) SRC JOIN DDR_R_BSNS_WK DDR_R_BSNS_WK ON ( (( SRC.MFG_ORG_CD = DDR_R_BSNS_WK.MFG_ORG_CD ))
AND (( ( SRC.CLNDR_DT BETWEEN DDR_R_BSNS_WK.WK_STRT_DT
AND DDR_R_BSNS_WK.WK_END_DT ) )) )
WHERE ( DDR_R_BSNS_WK.CLNDR_CD = DDR_R_BSNS_WK.MFG_ORG_CD || '-BSNS' ) ) ) SET_OPERATION ) AGG_INPUT$1 GROUP BY AGG_INPUT$1.MFG_ORG_CD$4
, AGG_INPUT$1.LEVEL2$4
, AGG_INPUT$1.LEVEL1$4
, AGG_INPUT$1.BSNS_UNIT_TYP$3
, AGG_INPUT$1.BSNS_WK_ID$1
, AGG_INPUT$1.WK_STRT_DT$3 ) AGGREGATOR_4 ) AGG_INPUT GROUP BY AGG_INPUT.LEVEL2$2
, AGG_INPUT.LEVEL1$2
, AGG_INPUT.WK_STRT_DT$1 ) AGGREGATOR ) SRC LEFT OUTER JOIN (SELECT DDR_DMNT_ITEM_MAP.MFG_ORG_CD MFG_ORG_CD
, DDR_DMNT_ITEM_MAP.MFG_SKU_ITEM_NBR MFG_SKU_ITEM_NBR
, DDR_DMNT_ITEM_MAP.MFG_ITEM_EXT_ID MFG_ITEM_EXT_ID
FROM DDR_DMNT_ITEM_MAP DDR_DMNT_ITEM_MAP ) DDR_ITEM ON ( (( SRC.MFG_ORG_CD = DDR_ITEM.MFG_ORG_CD ))
AND (( SRC.LEVEL1 = DDR_ITEM.MFG_SKU_ITEM_NBR )) ) LEFT OUTER JOIN (SELECT DDR_DMNT_LOCATION_MAP.MFG_ORG_CD MFG_ORG_CD
, DDR_DMNT_LOCATION_MAP.ORG_CD ORG_CD
, DDR_DMNT_LOCATION_MAP.BSNS_UNIT_CD BSNS_UNIT_CD
, DDR_DMNT_LOCATION_MAP.MFG_LOC_EXT_ID MFG_LOC_EXT_ID
FROM DDR_DMNT_LOCATION_MAP DDR_DMNT_LOCATION_MAP ) DDR_LOC ON ( (( SRC.LEVEL2 = DDR_LOC.BSNS_UNIT_CD ))
AND (( SRC.MFG_ORG_CD = DDR_LOC.MFG_ORG_CD )) )