WIP Discrete Job Routing Sheet

SELECT E.WIP_ENTITY_NAME Job,
             E.ORGANIZATION_ID C_ORGANIZATION_ID,
             E.WIP_ENTITY_ID WIP_ENTITY_ID10,
             D.DESCRIPTION DESCRIPTION,
             M.PRIMARY_UOM_CODE UOM,
             D.START_QUANTITY Start_Quantity,
            D.JOB_TYPE Job_Type,
             DECODE(M.INVENTORY_ITEM_ID, NULL, NULL,null) FLEXDATA,
             MSITL.DESCRIPTION Item_Desc,
             D.COMPLETION_SUBINVENTORY Subinventory,
            null CLOCFLEX,
            D.COMPLETION_LOCATOR_ID Compl_Loc_Id,
             D.BOM_REVISION BOM_Revision,
             D.BOM_REVISION_DATE BOM_REVISION_DATE,
             SUBSTR(TO_CHAR(D.BOM_REVISION_DATE,'HH24:MI'),1,5) BOM_TIME,
             D.SCHEDULED_START_DATE Scheduled_Start,
             SUBSTR(TO_CHAR(D.SCHEDULED_START_DATE,'HH24:MI'),1,5) START_TIME,
             LU.MEANING Status,
             D.ROUTING_REVISION Routing_Revision,
             D.ROUTING_REVISION_DATE ROUTING_REVISION_DATE,
             SUBSTR(TO_CHAR(D.ROUTING_REVISION_DATE,'HH24:MI'),1,5) ROUTING_TIME,
             D.SCHEDULED_COMPLETION_DATE Scheduled_Complete,
             DECODE(D.BOM_REFERENCE_ID,NULL,NULL,
                    null) C_Bill_Ref_Data,
                   SI2.DESCRIPTION Bill_Ref_Description,
             DECODE(D.ROUTING_REFERENCE_ID,NULL,NULL,
                    null) C_Routing_Ref_Data,
                   SI3.DESCRIPTION Rout_Ref_Description, SUBSTR(TO_CHAR(D.SCHEDULED_COMPLETION_DATE,'HH24:MI'),1,5) COMPLETION_TIME,
            SG.SCHEDULE_GROUP_NAME,
            D.BUILD_SEQUENCE,
            WL.LINE_CODE,
                DECODE(M.INVENTORY_ITEM_ID, NULL, NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('flexfield', 'INV', 'MSTK', 101, M.ORGANIZATION_ID, M.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) FLEXFIELD,
                DECODE(D.BOM_REFERENCE_ID,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_bill_reference', 'INV', 'MSTK', 101, SI2.ORGANIZATION_ID, SI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_Bill_Reference,
                DECODE(D.ROUTING_REFERENCE_ID,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_routing_reference', 'INV', 'MSTK', 101, SI3.ORGANIZATION_ID, SI3.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_Routing_Reference,
                DECODE(D.COMPLETION_LOCATOR_ID, NULL, NULL, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_clocator', 'INV', 'MTLL', 101, L.ORGANIZATION_ID, L.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE')) C_CLOCATOR
            FROM MTL_SYSTEM_ITEMS_B M,
             MTL_SYSTEM_ITEMS_TL MSITL,
            MTL_SYSTEM_ITEMS SI2,
            MTL_SYSTEM_ITEMS SI3,
             MFG_LOOKUPS LU,
            MTL_ITEM_LOCATIONS L,
            WIP_LINES WL,
            WIP_SCHEDULE_GROUPS SG,
             WIP_ENTITIES E,
             WIP_DISCRETE_JOBS D
            WHERE E.ORGANIZATION_ID = :P_ORGANIZATION_ID &NAME_RANGE &DATE_RANGE
            &C_SCHEDULE_GROUP
            AND SG.SCHEDULE_GROUP_ID &P_SG_OUTER = D.SCHEDULE_GROUP_ID
            AND SG.ORGANIZATION_ID &P_SG_OUTER = D.ORGANIZATION_ID
            AND WL.LINE_ID (+) = D.LINE_ID
            AND WL.ORGANIZATION_ID (+) = D.ORGANIZATION_ID
             AND L.INVENTORY_LOCATION_ID(+) = NVL(D.COMPLETION_LOCATOR_ID,'-1')
            AND L.ORGANIZATION_ID (+) = D.ORGANIZATION_ID
             AND D.WIP_ENTITY_ID = E.WIP_ENTITY_ID
             AND M.INVENTORY_ITEM_ID(+) = E.PRIMARY_ITEM_ID
             AND    MSITL.INVENTORY_ITEM_ID(+) = E.PRIMARY_ITEM_ID
             AND    MSITL.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
             AND    MSITL.LANGUAGE(+) = USERENV('LANG')
             AND LU.LOOKUP_TYPE = 'WIP_JOB_STATUS'
             AND LU.LOOKUP_CODE = D.STATUS_TYPE
             AND D.STATUS_TYPE IN (1,3,4,6)
             AND E.ENTITY_TYPE in (1,5)
             AND D.ORGANIZATION_ID = :P_ORGANIZATION_ID
             AND M.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
            AND    SI2.ORGANIZATION_ID (+) = :P_ORGANIZATION_ID
            AND    SI3.ORGANIZATION_ID (+) = :P_ORGANIZATION_ID
            AND    SI2.INVENTORY_ITEM_ID (+) = D.BOM_REFERENCE_ID
            AND    SI3.INVENTORY_ITEM_ID (+) = D.ROUTING_REFERENCE_ID
            ORDER BY &C_ORDER_BY