SELECT SUP_BATCH.FROM_PERIOD,
SUP_BATCH.TO_PERIOD,
SUP_BATCH.REPORT_DATE,
SUP_BATCH.ORGANIZATION_ID,
SUP_BATCH.ORGANIZATION_CODE,
SUP_BATCH.ORGANIZATION_NAME,
SUP_BATCH.INVENTORY_ITEM_ID,
SUP_BATCH.ITEM_SEGMENTS,
SUP_BATCH.DESCRIPTION,
SUP_BATCH.UOM_CODE,
SUP_BATCH.SR_INSTANCE_ID,
SUP_BATCH.COMPILE_DESIGNATOR,
SUP_BATCH.ITEM_TYPE,
SUP_BATCH.PACK_SIZE,
SUP_BATCH.BATCH_SIZE,
SUP_BATCH.NO_OF_BATCHES_JUL_19,
SUP_BATCH.NO_OF_BATCHES_AUG_19,
SUP_BATCH.NO_OF_BATCHES_SEP_19,
SUP_BATCH.NO_OF_BATCHES_OCT_19,
SUP_BATCH.NO_OF_BATCHES_NOV_19,
SUP_BATCH.NO_OF_BATCHES_DEC_19,
SUP_BATCH.NO_OF_BATCHES_JAN_20,
SUP_BATCH.NO_OF_BATCHES_FEB_20,
SUP_BATCH.NO_OF_BATCHES_MAR_20,
SUP_BATCH.NO_OF_BATCHES_APR_20,
SUP_BATCH.NO_OF_BATCHES_MAY_20,
SUP_BATCH.NO_OF_BATCHES_JUN_20,
-- DEMANDS_FORCAST.ORGANIZATION_CODE,
-- DEMANDS_FORCAST.ITEM_SEGMENTS,
-- DEMANDS_FORCAST.DESCRIPTION,
-- DEMANDS_FORCAST.ORGANIZATION_ID,
-- DEMANDS_FORCAST.SR_INSTANCE_ID,
-- DEMANDS_FORCAST.COMPILE_DESIGNATOR,
-- DEMANDS_FORCAST.INVENTORY_ITEM_ID,
DEMANDS_FORCAST.FORCAST_QTY_JUL_19,
DEMANDS_FORCAST.FORCAST_QTY_AUG_19,
DEMANDS_FORCAST.FORCAST_QTY_SEP_19,
DEMANDS_FORCAST.FORCAST_QTY_OCT_19,
DEMANDS_FORCAST.FORCAST_QTY_NOV_19,
DEMANDS_FORCAST.FORCAST_QTY_DEC_19,
DEMANDS_FORCAST.FORCAST_QTY_JAN_20,
DEMANDS_FORCAST.FORCAST_QTY_FEB_20,
DEMANDS_FORCAST.FORCAST_QTY_MAR_20,
DEMANDS_FORCAST.FORCAST_QTY_APR_20,
DEMANDS_FORCAST.FORCAST_QTY_MAY_20,
DEMANDS_FORCAST.FORCAST_QTY_JUN_20,
( NVL(SUP_BATCH.NO_OF_BATCHES_JUL_19,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_AUG_19,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_SEP_19,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_OCT_19,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_NOV_19,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_DEC_19,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_JAN_20,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_FEB_20,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_MAR_20,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_APR_20,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_MAY_20,0) +
NVL(SUP_BATCH.NO_OF_BATCHES_JUN_20,0)) TOTAL_NO_OF_BATCHES,
(NVL(DEMANDS_FORCAST.FORCAST_QTY_JUL_19,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_AUG_19,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_SEP_19,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_OCT_19,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_NOV_19,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_DEC_19,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_JAN_20,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_FEB_20,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_MAR_20,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_APR_20,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_MAY_20,0) +
NVL(DEMANDS_FORCAST.FORCAST_QTY_JUN_20,0)) TOTAL_FORCAST_QTY
FROM
(
---- ############## FOR SUPPLIER - NO OF BATCHES ################ ----
SELECT TO_CHAR(GET_TAX_YEAR_START_FUNC(:P_RUN_DATE),'MM/YY') FROM_PERIOD,
TO_CHAR(GET_TAX_YEAR_END_FUNC (:P_RUN_DATE),'MM/YY') TO_PERIOD,
:P_RUN_DATE REPORT_DATE,
SUPPLIES_NO_OF_BATCH.ORGANIZATION_ID,
SUPPLIES_NO_OF_BATCH.ORGANIZATION_CODE,
SUPPLIES_NO_OF_BATCH.ORGANIZATION_NAME,
SUPPLIES_NO_OF_BATCH.INVENTORY_ITEM_ID,
SUPPLIES_NO_OF_BATCH.ITEM_SEGMENTS,
SUPPLIES_NO_OF_BATCH.DESCRIPTION,
SUPPLIES_NO_OF_BATCH.UOM_CODE,
SUPPLIES_NO_OF_BATCH.SR_INSTANCE_ID,
SUPPLIES_NO_OF_BATCH.COMPILE_DESIGNATOR,
SUPPLIES_NO_OF_BATCH.ITEM_TYPE,
SUPPLIES_NO_OF_BATCH.PACK_SIZE,
SUPPLIES_NO_OF_BATCH.BATCH_SIZE,
SUM(NO_OF_BATCHES_JUL_19) NO_OF_BATCHES_JUL_19,
SUM(NO_OF_BATCHES_AUG_19) NO_OF_BATCHES_AUG_19,
SUM(NO_OF_BATCHES_SEP_19) NO_OF_BATCHES_SEP_19,
SUM(NO_OF_BATCHES_OCT_19) NO_OF_BATCHES_OCT_19,
SUM(NO_OF_BATCHES_NOV_19) NO_OF_BATCHES_NOV_19,
SUM(NO_OF_BATCHES_DEC_19) NO_OF_BATCHES_DEC_19,
SUM(NO_OF_BATCHES_JAN_20) NO_OF_BATCHES_JAN_20,
SUM(NO_OF_BATCHES_FEB_20) NO_OF_BATCHES_FEB_20,
SUM(NO_OF_BATCHES_MAR_20) NO_OF_BATCHES_MAR_20,
SUM(NO_OF_BATCHES_APR_20) NO_OF_BATCHES_APR_20,
SUM(NO_OF_BATCHES_MAY_20) NO_OF_BATCHES_MAY_20,
SUM(NO_OF_BATCHES_JUN_20) NO_OF_BATCHES_JUN_20
FROM
(
SELECT MPO.ORGANIZATION_CODE,
MPO.ORGANIZATION_NAME,
MOV.ITEM_SEGMENTS,
MOV.DESCRIPTION,
MOV.ORGANIZATION_ID,
MOV.SR_INSTANCE_ID,
MOV.COMPILE_DESIGNATOR,
MOV.INVENTORY_ITEM_ID,
MOV.UOM_CODE,
SUBSTR(MOV.CATEGORY_NAME,INSTR(MOV.CATEGORY_NAME, '.', 1)+1,INSTR(MOV.CATEGORY_NAME, '.', 2,2)-INSTR(MOV.CATEGORY_NAME, '.', 1)-1 ) ITEM_TYPE,
MSI.ATTRIBUTE11 PACK_SIZE,
CASE WHEN
SUBSTR(MOV.CATEGORY_NAME,INSTR(MOV.CATEGORY_NAME, '.', 1)+1,INSTR(MOV.CATEGORY_NAME, '.', 2,2)-INSTR(MOV.CATEGORY_NAME, '.', 1)-1 ) = 'Finished Goods' THEN
MSI.MAXIMUM_ORDER_QUANTITY
ELSE MSI.FIXED_ORDER_QUANTITY
END BATCH_SIZE,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN GET_TAX_YEAR_START_FUNC (:P_RUN_DATE) AND LAST_DAY(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_JUL_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),1) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),1))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_AUG_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),2) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),2))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_SEP_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),3) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),3))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_OCT_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),4) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),4))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_NOV_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),5) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),5))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_DEC_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),6) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),6))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_JAN_20,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),7) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),7))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_FEB_20,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),8) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),8))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_MAR_20,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),9) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),9))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_APR_20,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),10) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),10))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_MAY_20,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_SUPPLIES' AND
TRUNC (MOV.NEW_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),11) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),11))
THEN
COUNT(MOV.ORDER_NUMBER)
ELSE
NULL
END NO_OF_BATCHES_JUN_20
FROM MSC_ORDERS_V MOV,
MSC_DESIGNATORS MD,
MSC_PLAN_ORGS_V MPO,
MTL_SYSTEM_ITEMS_B @D_TO_S MSI
WHERE MOV.PLAN_ID = :P_PLAN_ID
-- AND MOV.QUANTITY <> 0 -- comment on 23-DEC-2019--
AND MOV.SOURCE_TABLE = 'MSC_SUPPLIES'
--- AND MOV.CATEGORY_SET_ID = 8
AND SUBSTR(MOV.CATEGORY_NAME,INSTR(MOV.CATEGORY_NAME, '.', 1)+1,INSTR(MOV.CATEGORY_NAME, '.', 2,2)-INSTR(MOV.CATEGORY_NAME, '.', 1)-1 ) IN ('Finished Goods','Bulk')
AND MOV.ORDER_TYPE <> 60
AND MOV.ORGANIZATION_ID = NVL(:P_ORGANIZATION_ID,MOV.ORGANIZATION_ID)
-- AND MOV.SR_INSTANCE_ID = :P_SR_INSTANCE_ID
AND MOV.PLANNING_MAKE_BUY_CODE = '1'
AND MD.SR_INSTANCE_ID = MOV.SR_INSTANCE_ID
AND MD.ATTRIBUTE_CATEGORY = 'Plan Type'
AND MD.ATTRIBUTE1 = 'YEARLY' --'ROFO'
AND MD.DESIGNATOR = MOV.COMPILE_DESIGNATOR
AND MPO.PLAN_ID = MOV.PLAN_ID
AND MPO.SR_INSTANCE_ID = MOV.SR_INSTANCE_ID
AND MPO.PLANNED_ORGANIZATION = MOV.ORGANIZATION_ID
AND MPO.COMPILE_DESIGNATOR = MOV.COMPILE_DESIGNATOR
AND MSI.SEGMENT1 = MOV.ITEM_SEGMENTS
AND MSI.ORGANIZATION_ID = MOV.ORGANIZATION_ID
AND MOV.ORDER_TYPE = 5 -- Planned order -- only
-- AND MOV.SR_INSTANCE_ID = 2024
AND MOV.NEW_START_DATE BETWEEN GET_TAX_YEAR_START_FUNC(:P_RUN_DATE) AND GET_TAX_YEAR_END_FUNC(:P_RUN_DATE)
AND SUBSTR(MOV.CATEGORY_NAME,INSTR(MOV.CATEGORY_NAME, '.', 1)+1,INSTR(MOV.CATEGORY_NAME, '.', 2,2)-INSTR(MOV.CATEGORY_NAME, '.', 1)-1 ) IN ('Finished Goods','Bulk')
-- AND TRUNC (MOV.NEW_DUE_DATE) BETWEEN GET_TAX_YEAR_START_FUNC (:P_RUN_DATE)
-- AND LAST_DAY(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE))
GROUP BY
MPO.ORGANIZATION_CODE,
MPO.ORGANIZATION_NAME,
MOV.ITEM_SEGMENTS,
MOV.DESCRIPTION,
MOV.ORGANIZATION_ID,
MOV.SR_INSTANCE_ID,
MOV.COMPILE_DESIGNATOR,
MOV.INVENTORY_ITEM_ID,
TRUNC (MOV.NEW_DUE_DATE),
MOV.SOURCE_TABLE,
SUBSTR(MOV.CATEGORY_NAME,INSTR(MOV.CATEGORY_NAME, '.', 1)+1,INSTR(MOV.CATEGORY_NAME, '.', 2,2)-INSTR(MOV.CATEGORY_NAME, '.', 1)-1 ),
MOV.UOM_CODE,
MSI.ATTRIBUTE11,
MSI.FIXED_ORDER_QUANTITY,
MSI.MAXIMUM_ORDER_QUANTITY
) SUPPLIES_NO_OF_BATCH
GROUP BY
SUPPLIES_NO_OF_BATCH.ORGANIZATION_CODE,
SUPPLIES_NO_OF_BATCH.ORGANIZATION_NAME,
SUPPLIES_NO_OF_BATCH.ITEM_SEGMENTS,
SUPPLIES_NO_OF_BATCH.DESCRIPTION,
SUPPLIES_NO_OF_BATCH.ORGANIZATION_ID,
SUPPLIES_NO_OF_BATCH.SR_INSTANCE_ID,
SUPPLIES_NO_OF_BATCH.COMPILE_DESIGNATOR,
SUPPLIES_NO_OF_BATCH.INVENTORY_ITEM_ID,
SUPPLIES_NO_OF_BATCH.ITEM_TYPE,
SUPPLIES_NO_OF_BATCH.UOM_CODE,
SUPPLIES_NO_OF_BATCH.PACK_SIZE,
SUPPLIES_NO_OF_BATCH.BATCH_SIZE
)SUP_BATCH,
( ---- ############## FOR DEMANDS - FORCAST_QTY ################ ----
SELECT DEMANDS_FORCAST_QTY.ORGANIZATION_CODE,
DEMANDS_FORCAST_QTY.ITEM_SEGMENTS,
DEMANDS_FORCAST_QTY.DESCRIPTION,
DEMANDS_FORCAST_QTY.ORGANIZATION_ID,
DEMANDS_FORCAST_QTY.SR_INSTANCE_ID,
DEMANDS_FORCAST_QTY.COMPILE_DESIGNATOR,
DEMANDS_FORCAST_QTY.INVENTORY_ITEM_ID,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_JUL_19) FORCAST_QTY_JUL_19,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_AUG_19) FORCAST_QTY_AUG_19,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_SEP_19) FORCAST_QTY_SEP_19,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_OCT_19) FORCAST_QTY_OCT_19,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_NOV_19) FORCAST_QTY_NOV_19,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_DEC_19) FORCAST_QTY_DEC_19,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_JAN_20) FORCAST_QTY_JAN_20,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_FEB_20) FORCAST_QTY_FEB_20,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_MAR_20) FORCAST_QTY_MAR_20,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_APR_20) FORCAST_QTY_APR_20,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_MAY_20) FORCAST_QTY_MAY_20,
SUM(DEMANDS_FORCAST_QTY.FORCAST_QTY_JUN_20) FORCAST_QTY_JUN_20
FROM
(
SELECT MOV.ORGANIZATION_CODE,
MOV.ITEM_SEGMENTS,
ABS(MOV.QUANTITY_RATE) QUANTITY_RATE,
MOV.DESCRIPTION,
MOV.ORGANIZATION_ID,
MOV.SR_INSTANCE_ID,
MOV.COMPILE_DESIGNATOR,
MOV.INVENTORY_ITEM_ID,
MOV.SOURCE_TABLE,
--TRUNC (MOV.OLD_DUE_DATE) DEMANDS_OLD_DUE_DATE,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN GET_TAX_YEAR_START_FUNC (:P_RUN_DATE) AND LAST_DAY(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_JUL_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),1) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),1))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_AUG_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),2) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),2))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_SEP_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),3) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),3))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_OCT_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),4) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),4))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_NOV_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),5) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),5))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_DEC_19,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),6) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),6))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_JAN_20,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),7) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),7))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_FEB_20,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),8) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),8))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_MAR_20,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),9) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),9))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_APR_20,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),10) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),10))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_MAY_20,
CASE
WHEN MOV.SOURCE_TABLE = 'MSC_DEMANDS' AND
TRUNC (MOV.OLD_DUE_DATE) BETWEEN ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),11) AND LAST_DAY(ADD_MONTHS(GET_TAX_YEAR_START_FUNC (:P_RUN_DATE),11))
THEN
ABS(MOV.QUANTITY_RATE)
ELSE
NULL
END FORCAST_QTY_JUN_20
FROM MSC_ORDERS_V MOV
WHERE MOV.PLAN_ID = :P_PLAN_ID
-- AND MOV.QUANTITY <> 0 -- Comment on 23-DEC-2019 --
AND MOV.SOURCE_TABLE = 'MSC_DEMANDS'
-- AND MOV.CATEGORY_SET_ID = 8
AND SUBSTR(MOV.CATEGORY_NAME,INSTR(MOV.CATEGORY_NAME, '.', 1)+1,INSTR(MOV.CATEGORY_NAME, '.', 2,2)-INSTR(MOV.CATEGORY_NAME, '.', 1)-1 ) IN ('Finished Goods')
AND MOV.ORDER_TYPE <> 60
-- AND MOV.ORDER_TYPE = 5 -- Planned order -- only -- comment becouse FORCAST_QTY is not shown ---
AND MOV.ORGANIZATION_ID = NVL(:P_ORGANIZATION_ID,MOV.ORGANIZATION_ID)
-- AND MOV.SR_INSTANCE_ID =2024 --:P_SR_INSTANCE_ID
AND MOV.PLANNING_MAKE_BUY_CODE = '1'
-- AND TRUNC (MOV.OLD_DUE_DATE) BETWEEN GET_TAX_YEAR_START_FUNC (:P_FROM_DATE)
-- AND LAST_DAY(GET_TAX_YEAR_START_FUNC (:P_FROM_DATE))
GROUP BY
MOV.ORGANIZATION_CODE,
MOV.ITEM_SEGMENTS,
ABS(MOV.QUANTITY_RATE),
MOV.DESCRIPTION,
MOV.ORGANIZATION_ID,
MOV.SR_INSTANCE_ID,
MOV.COMPILE_DESIGNATOR,
MOV.INVENTORY_ITEM_ID,
TRUNC (MOV.OLD_DUE_DATE),
MOV.SOURCE_TABLE
) DEMANDS_FORCAST_QTY
GROUP BY
DEMANDS_FORCAST_QTY.ORGANIZATION_CODE,
DEMANDS_FORCAST_QTY.ITEM_SEGMENTS,
DEMANDS_FORCAST_QTY.DESCRIPTION,
DEMANDS_FORCAST_QTY.ORGANIZATION_ID,
DEMANDS_FORCAST_QTY.SR_INSTANCE_ID,
DEMANDS_FORCAST_QTY.COMPILE_DESIGNATOR,
DEMANDS_FORCAST_QTY.INVENTORY_ITEM_ID
)DEMANDS_FORCAST
WHERE 1=1
AND SUP_BATCH.ORGANIZATION_ID = DEMANDS_FORCAST.ORGANIZATION_ID (+)
AND SUP_BATCH.INVENTORY_ITEM_ID = DEMANDS_FORCAST.INVENTORY_ITEM_ID (+)
AND SUP_BATCH.SR_INSTANCE_ID = DEMANDS_FORCAST.SR_INSTANCE_ID (+)
ORDER BY SUP_BATCH.ORGANIZATION_ID, SUP_BATCH.ITEM_TYPE DESC
No comments:
Post a Comment