SELECT MAIN_Q.ITEM_CODE,
MAIN_Q.ITEM_DESC,
MAIN_Q.UOM,
MAIN_Q.PACK_SIZE,
MAIN_Q.BATCH_SIZE,
ROUND(((MAIN_Q.PRORATED_ROFO_QTY_MONTH /MAIN_Q.TOTAL_DAYS_IN_MONTH)*NO_OF_CURRENT_MONTH_DAY),4) PRORATED_ROFO_QTY_MONTH,
MAIN_Q.QTY_DISPATCHED_MONTH,
ROUND(((MAIN_Q.QTY_DISPATCHED_MONTH/MAIN_Q.PRORATED_ROFO_QTY_MONTH)*100),2) PERCENT_DISPATCHED_ACHIEVED,
ROUND((100-((MAIN_Q.QTY_DISPATCHED_MONTH/MAIN_Q.PRORATED_ROFO_QTY_MONTH)*100)),2) PERCENT_DISPAT_TO_BE_ACHIEVED,
TO_CHAR((TO_DATE('01'||'-'||:P_PERIOD)),'DD-MON-YYYY') FROM_DATE,
TO_CHAR(LAST_DAY(TO_DATE(TO_CHAR(TO_DATE(SYSDATE),'DD') ||'-'||:P_PERIOD)),'DD-MON-YYYY') TODATE,
-- TO_CHAR(GET_TAX_YEAR_START_FUNC(TO_DATE('01'||'-'||:P_PERIOD)),'DD-MON-YYYY') FROM_DATE,
-- TO_CHAR(TO_DATE(TO_CHAR(TO_DATE(SYSDATE),'DD') ||'-'||:P_PERIOD),'DD-MON-YYYY') TODATE,
MAIN_Q.ORGANIZATION_CODE,
MAIN_Q.ORGANIZATION_NAME,
MAIN_Q.TOTAL_DAYS_IN_MONTH,
MAIN_Q.NO_OF_CURRENT_MONTH_DAY,
MAIN_Q.ON_HAND_INV_BALANCE
FROM (SELECT MSI1.INVENTORY_ITEM_ID ,
MSI1.ORGANIZATION_ID,
MSI1.SEGMENT1 ITEM_CODE,
MSI1.DESCRIPTION ITEM_DESC,
MSI1.PRIMARY_UNIT_OF_MEASURE UOM,
MSI1.ATTRIBUTE11 PACK_SIZE,
MSI1.FIXED_ORDER_QUANTITY BATCH_SIZE,
SUM(CURRENT_FORECAST_QUANTITY) PRORATED_ROFO_QTY_MONTH,
--CURRENT_FORECAST_QUANTITY,
OOD.ORGANIZATION_CODE,
OOD.ORGANIZATION_NAME,
TO_NUMBER(TO_CHAR(LAST_DAY('01'||'-'||:P_PERIOD),'DD')) TOTAL_DAYS_IN_MONTH,
TO_NUMBER(TO_CHAR(TO_DATE(SYSDATE),'DD')) NO_OF_CURRENT_MONTH_DAY,
NVL((SELECT SUM(MMT1.PRIMARY_QUANTITY)
FROM MTL_MATERIAL_TRANSACTIONS MMT1,
MTL_TRANSACTION_TYPES MTT1
WHERE 1=1
AND MMT1.TRANSACTION_TYPE_ID = MTT1.TRANSACTION_TYPE_ID
AND MTT1.TRANSACTION_TYPE_NAME = 'Subinventory Transfer'
AND MMT1.TRANSACTION_QUANTITY >0
AND MMT1.ORGANIZATION_ID = NVL(:P_ORGANIZATION_ID,MMT1.ORGANIZATION_ID)
-- AND TO_DATE(TRUNC(MMT1.TRANSACTION_DATE)) BETWEEN GET_TAX_YEAR_START_FUNC(TO_DATE('01'||'-'||:P_PERIOD)) AND LAST_DAY(TO_DATE(TO_CHAR(TO_DATE(SYSDATE),'DD') ||'-'||:P_PERIOD)) --TO_DATE(TO_CHAR(TO_DATE(SYSDATE),'DD') ||'-'||:P_PERIOD)---- comment on 04DEC19 --
AND TO_DATE(TRUNC(MMT1.TRANSACTION_DATE)) BETWEEN TO_DATE('01'||'-'||:P_PERIOD) AND LAST_DAY(TO_DATE(TO_CHAR(TO_DATE(SYSDATE),'DD') ||'-'||:P_PERIOD)) --TO_DATE(TO_CHAR(TO_DATE(SYSDATE),'DD') ||'-'||:P_PERIOD)---- comment on 04DEC19 --
AND MMT1.INVENTORY_ITEM_ID =MSI1.INVENTORY_ITEM_ID
),0) QTY_DISPATCHED_MONTH,
XXBEX_OH_QTY_ROFO_FUN(MSI1.INVENTORY_ITEM_ID ,MSI1.ORGANIZATION_ID )ON_HAND_INV_BALANCE
FROM MRP_FORECAST_DESIGNATORS MFD,
MRP_FORECAST_ITEMS MFI,
MTL_SYSTEM_ITEMS_B MSI1,
MRP_FORECAST_DATES MFDT,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE 1=1
AND MFD.ATTRIBUTE_CATEGORY = 'Forecast Type'
AND MFD.ATTRIBUTE1 = 'ROFO'
AND MFD.FORECAST_DESIGNATOR = MFI.FORECAST_DESIGNATOR
AND MFD.ORGANIZATION_ID = MFI.ORGANIZATION_ID
AND MFI.INVENTORY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
AND MFI.ORGANIZATION_ID = MSI1.ORGANIZATION_ID
AND MFI.FORECAST_DESIGNATOR = MFDT.FORECAST_DESIGNATOR
AND MFI.INVENTORY_ITEM_ID = MFDT.INVENTORY_ITEM_ID
AND MFI.ORGANIZATION_ID = MFDT.ORGANIZATION_ID
AND MSI1.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND MSI1.ORGANIZATION_ID = NVL(:P_ORGANIZATION_ID,MSI1.ORGANIZATION_ID)
-- AND TO_DATE(MFDT.FORECAST_DATE) BETWEEN TO_DATE('01'||'-'||:P_PERIOD) AND LAST_DAY(TO_DATE(TO_CHAR(TO_DATE(SYSDATE),'DD') ||'-'||:P_PERIOD))-- TO_DATE(TO_CHAR(TO_DATE(SYSDATE),'DD') ||'-'||:P_PERIOD) -- comment on 04DEC19
AND TO_DATE(MFDT.FORECAST_DATE) BETWEEN TO_DATE('01'||'-'||:P_PERIOD) AND LAST_DAY(TO_DATE(TO_CHAR(TO_DATE(SYSDATE),'DD') ||'-'||:P_PERIOD))-- TO_DATE(TO_CHAR(TO_DATE(SYSDATE),'DD') ||'-'||:P_PERIOD) -- comment on 04DEC19
GROUP BY
MSI1.INVENTORY_ITEM_ID ,
MSI1.ORGANIZATION_ID,
MSI1.SEGMENT1,
MSI1.DESCRIPTION,
MSI1.PRIMARY_UNIT_OF_MEASURE,
MSI1.ATTRIBUTE11,
MSI1.FIXED_ORDER_QUANTITY,
OOD.ORGANIZATION_CODE,
OOD.ORGANIZATION_NAME
)MAIN_Q
