In ASCP Oracle apps eBS, Monthly Dispatched Achievement Against ROFO Report

  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