In ASCP Oracle apps eBS. Daily Resource Loading and Production Scheduling Report

 SELECT TO_CHAR(MRR.RESOURCE_DATE,'DD/MM/YY') RESOURCE_DATE,

           TO_CHAR((MRR.RESOURCE_DATE), 'Day') RESOURCE_DAY,

           MPR.ORGANIZATION_ID,

           MPO.ORGANIZATION_CODE,MPO.ORGANIZATION_NAME,

           TO_CHAR(NVL(TO_DATE(P_SYS_DATE,'YYYY/MM/DD HH24:MI:SS'),SYSDATE),'DD-MON-YY')PARA_SYS_DATE,

           MRR.RESOURCE_CODE,

           MRR.RESOURCE_ID,

           MPR.RESOURCE_GROUP_NAME,

           MRR.ASSEMBLY_ITEM_ID,

           MRR.ASSEMBLY_ITEM_NUMBER,

           MRR.ASSEMBLY_ITEM_DESC,

           UPPER(MO.CATEGORY_NAME) DEPT_NAME,

           MO.CATEGORY_SET_ID,

           (SELECT DISTINCT SUBSTR(MOV.CATEGORY_NAME,INSTR(MOV.CATEGORY_NAME, '.', 1)+1,INSTR(MOV.CATEGORY_NAME, '.', 2,2)-INSTR(MOV.CATEGORY_NAME, '.', 1)-1 ) ITEM_TYPE

              FROM MSC_ORDERS_V MOV

             WHERE 1=1

             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.ORGANIZATION_ID      = MPR.ORGANIZATION_ID

             AND MOV.INVENTORY_ITEM_ID    = MRR.ASSEMBLY_ITEM_ID

             AND MOV.CATEGORY_SET_ID      = 8

           ) ITEM_TYPE

      FROM MSC_DESIGNATORS MD,

           MSC_PLAN_ORGS_V MPO,

           MSC_PLANNED_RESOURCES_V MPR,

           MSC_RESOURCE_REQUIREMENTS_V MRR,

           MSC_ORDERS_V MO

     WHERE 1=1

       AND MD.SR_INSTANCE_ID      = MPO.SR_INSTANCE_ID

       AND MD.ATTRIBUTE_CATEGORY  = 'Plan Type'

       AND MD.ATTRIBUTE1          = 'ROFO'

       AND MPO.COMPILE_DESIGNATOR = MD.DESIGNATOR

       AND MPR.PLAN_ID            = MPO.PLAN_ID

       AND MPR.SR_INSTANCE_ID     = MD.SR_INSTANCE_ID

       AND MPO.PLANNED_ORGANIZATION   = MPR.ORGANIZATION_ID

       AND NVL (MPR.RESOURCE_TYPE, 1) <> 100

       AND MPR.ROUTING_SEQUENCE_ID IS NULL

       AND MRR.ORGANIZATION_ID     = MPR.ORGANIZATION_ID

       AND MRR.RESOURCE_ID         = MPR.RESOURCE_ID 

       AND MRR.PLAN_ID             = MPR.PLAN_ID

       AND MRR.SOURCE_TRANSACTION_ID = MO.TRANSACTION_ID

       AND MO.ORGANIZATION_ID      = MPR.ORGANIZATION_ID

       AND MO.SR_INSTANCE_ID       = MPR.SR_INSTANCE_ID

       AND MO.COMPILE_DESIGNATOR   = MPO.COMPILE_DESIGNATOR

       -- AND MO.FIRM_PLANNED_TYPE    = 1 -- comment 0n 17-MAR-2020---

       -------- PARAMETERS --------

       AND MPO.PLAN_ID             = P_PLAN_ID 

       AND MPR.ORGANIZATION_ID     = NVL(P_ORGANIZATION_ID,MPR.ORGANIZATION_ID)    

       AND MRR.RESOURCE_DATE BETWEEN NVL(TO_DATE(P_SYS_DATE,'YYYY/MM/DD HH24:MI:SS'),SYSDATE) AND NVL(TO_DATE(P_SYS_DATE,'YYYY/MM/DD HH24:MI:SS')+30,SYSDATE+30)   

       --AND MRR.RESOURCE_DATE BETWEEN NVL(:P_SYS_DATE,SYSDATE) AND NVL(MRR.RESOURCE_DATE+30,SYSDATE+30)  

       --AND UPPER(MO.CATEGORY_NAME) LIKE '%' || UPPER(P_ITEM_TYPE) || '%'  --LIKE '%Finished Good%'  -- like '%' || :p_name || '%' 

       AND MPR.RESOURCE_GROUP_NAME = NVL(P_RESOURCE_GROUP_NAME,MPR.RESOURCE_GROUP_NAME)

       --AND MRR.ASSEMBLY_ITEM_NUMBER = NVL(:P_ASSEMBLY_ITEM_NUMBER,MRR.ASSEMBLY_ITEM_NUMBER) 

       AND MO.CATEGORY_SET_ID       = 1001 -- 'BEX_PLANNING_CATEGORY' 

       AND UPPER(MO.CATEGORY_NAME)  = UPPER(NVL(P_DEPT_NAME,UPPER(MO.CATEGORY_NAME)))

       --AND MRR.ASSEMBLY_ITEM_NUMBER ='2020000393'

     GROUP BY TO_CHAR(MRR.RESOURCE_DATE,'DD/MM/YY'),

              TO_CHAR((MRR.RESOURCE_DATE), 'Day'),

              MPR.ORGANIZATION_ID,

              MPO.ORGANIZATION_CODE,MPO.ORGANIZATION_NAME,

              MRR.RESOURCE_CODE,

              MRR.RESOURCE_ID,

              MPR.RESOURCE_GROUP_NAME,

              MRR.ASSEMBLY_ITEM_ID,

              MRR.ASSEMBLY_ITEM_NUMBER,

              MRR.ASSEMBLY_ITEM_DESC,

              UPPER(MO.CATEGORY_NAME),

              MO.CATEGORY_SET_ID;