WIP Repetitive Routing Sheet

SELECT  E.ORGANIZATION_ID ORGANIZATION_ID10,
             E.WIP_ENTITY_ID WIP_ENTITY_ID10,
             RS.REPETITIVE_SCHEDULE_ID REPETITIVE_SCHEDULE_ID10,
             E.WIP_ENTITY_NAME Assembly,
             E.DESCRIPTION,
             M.PRIMARY_UOM_CODE UOM,
             RS.FIRST_UNIT_START_DATE First_Unit_Start,
             TO_CHAR(RS.FIRST_UNIT_START_DATE,'HH24:MI') FUS_TIME,
             L.LINE_CODE Line,
             L.DESCRIPTION LINE_DESC,
             RS.DAILY_PRODUCTION_RATE Daily_Rate,
             RS.FIRST_UNIT_COMPLETION_DATE First_Unit_Comp,
             TO_CHAR(RS.FIRST_UNIT_COMPLETION_DATE,'HH24:MI') FUC_TIME,
             LU.MEANING Status,
             RS.PROCESSING_WORK_DAYS Process_Days,
             RS.LAST_UNIT_START_DATE Last_Unit_Start,
             TO_CHAR(RS.LAST_UNIT_START_DATE,'HH24:MI') LUS_TIME,
             RS.BOM_REVISION BOM_Revision,
             RS.BOM_REVISION_DATE,
             RI.COMPLETION_SUBINVENTORY Completion_Sub,
            null CLOCFLEX,
             RI.COMPLETION_LOCATOR_ID Compl_Loc_Id,
             TO_CHAR(RS.BOM_REVISION_DATE,'HH24:MI') BOM_TIME,
             (RS.PROCESSING_WORK_DAYS * RS.DAILY_PRODUCTION_RATE) Total_Units,
             RS.LAST_UNIT_COMPLETION_DATE Last_Unit_Comp,
             TO_CHAR(RS.LAST_UNIT_COMPLETION_DATE,'HH24:MI') LUC_TIME,
             RS.ROUTING_REVISION Rtg_Revision,
             RS.ROUTING_REVISION_DATE,
             TO_CHAR(RS.ROUTING_REVISION_DATE,'HH24:MI') RTG_TIME,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_clocator', 'INV', 'MTLL', 101, X.ORGANIZATION_ID, X.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_CLOCATOR
            FROM WIP_ENTITIES E,
             WIP_REPETITIVE_SCHEDULES RS,
             WIP_REPETITIVE_ITEMS RI,
             WIP_LINES L,
             MTL_SYSTEM_ITEMS M,
            MTL_ITEM_LOCATIONS X,
             MFG_LOOKUPS LU
            WHERE E.ORGANIZATION_ID = :P_ORGANIZATION_ID
             AND RS.ORGANIZATION_ID = :P_ORGANIZATION_ID
             AND RI.ORGANIZATION_ID = :P_ORGANIZATION_ID
             AND M.ORGANIZATION_ID = E.ORGANIZATION_ID
             AND L.ORGANIZATION_ID = :P_ORGANIZATION_ID &ASSEMBLY_SPEC &LINE_SPEC
             AND X.ORGANIZATION_ID (+) = RI.ORGANIZATION_ID
             AND X.INVENTORY_LOCATION_ID (+) = RI.COMPLETION_LOCATOR_ID
             AND M.INVENTORY_ITEM_ID = E.PRIMARY_ITEM_ID
             AND RS.WIP_ENTITY_ID = E.WIP_ENTITY_ID
             AND RI.WIP_ENTITY_ID = E.WIP_ENTITY_ID
             AND L.LINE_ID = RI.LINE_ID
             AND RS.LINE_ID = RI.LINE_ID
             AND LU.LOOKUP_TYPE = 'WIP_JOB_STATUS'
             AND LU.LOOKUP_CODE = RS.STATUS_TYPE &DATE_RANGE
             AND E.ENTITY_TYPE = 2
             AND RS.STATUS_TYPE IN (1,3,4,6)
            ORDER BY E.WIP_ENTITY_NAME, RS.FIRST_UNIT_START_DATE