Danish Halim: In ASCP Oracle apps eBS. Yearly Foretasted Manufacturing Requirement Report

Friday, 20 November 2020

In ASCP Oracle apps eBS. Yearly Foretasted Manufacturing Requirement Report

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

Danish Halim

Friday, 20 November 2020

In ASCP Oracle apps eBS. Yearly Foretasted Manufacturing Requirement Report

No comments:

Post a Comment

How to change Employee Number Generation from Manual to Automatic in Oracle HRMS (When attempting to apply for a job in iRecruitment)