WIP Repetitive Pick List

SELECT WL.LINE_CODE                       Line
,      WL.DESCRIPTION                     Line_Desc
,      DECODE(:P_SORT_BY,6,WE.WIP_ENTITY_NAME,'X')                 C_ASSY
,      DECODE(:P_SORT_BY,6,WE.DESCRIPTION,'X')         Assembly_Desc
,      DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
                                          Supply_Code
,      ML.MEANING                         Wip_Supply_Type
--,      &P_COMP                            Component
,      MSI2.DESCRIPTION                   Comp_Desc
,      DECODE(:P_SORT_BY,6,WRO.OPERATION_SEQ_NUM,1)  Op_Seq
,      NVL(BD.DEPARTMENT_CODE,' ')        Department
,      NVL(WRO.SUPPLY_SUBINVENTORY,' ')   Supply_Subinv
--,      null                      Supply_Locator
,      NVL(MIL.INVENTORY_LOCATION_ID,-1)  Locator_Id
,      SUM((LEAST(BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS,:C_END_NUM)
          - GREATEST(BCD.NEXT_SEQ_NUM,:C_START_NUM))
          * WRO.QUANTITY_PER_ASSEMBLY * WRS.DAILY_PRODUCTION_RATE )
                                          Quantity_Required
,      MSI2.PRIMARY_UOM_CODE              UOM
,      MOQ2.SUBINVENTORY_CODE             On_Hand_Subinv
,      DECODE(MIL2.INVENTORY_LOCATION_ID,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_det_loc_disp', 'INV', 'MTLL', 101, MIL2.ORGANIZATION_ID, MIL2.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'))
                                          On_Hand_Locator
,      DECODE(MOQ2.SUBINVENTORY_CODE,NVL(WRO.SUPPLY_SUBINVENTORY,'-1'),
       DECODE(NVL(MOQ2.LOCATOR_ID,-1),NVL(WRO.SUPPLY_LOCATOR_ID,-1),1,0),0)                                           Supply_Flag
,      NVL(MOQ2.QUANTITY,0)         Quantity_On_Hand,
	WIP_WIPREPCK_XMLP_PKG.order_func(DECODE ( :P_SORT_BY , 6 , WRO.OPERATION_SEQ_NUM , 1 ), NVL ( WRO.SUPPLY_SUBINVENTORY , ' ' ), fnd_flex_xml_publisher_apis.process_kff_combination_1('c_component_disp', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'), NVL ( BD.DEPARTMENT_CODE , ' ' ), fnd_flex_xml_publisher_apis.process_kff_combination_1('c_locator_disp', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'), fnd_flex_xml_publisher_apis.process_kff_combination_1('c_comp_sort', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'), fnd_flex_xml_publisher_apis.process_kff_combination_1('c_loc_sort', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'PADDED_VALUE')) C_Order,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_component_disp', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_Component_Disp,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_locator_disp', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_Locator_Disp,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_comp_sort', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_Comp_Sort,
	WIP_WIPREPCK_XMLP_PKG.c_loc_sortformula(NVL ( MIL.INVENTORY_LOCATION_ID , - 1 ), fnd_flex_xml_publisher_apis.process_kff_combination_1('c_loc_sort', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'PADDED_VALUE')) C_Loc_Sort
FROM   MFG_LOOKUPS ML
,      MTL_SYSTEM_ITEMS MSI2
,      MTL_ITEM_LOCATIONS MIL
,      MTL_ITEM_LOCATIONS MIL2
,      MTL_SECONDARY_INVENTORIES MSINV
,      BOM_DEPARTMENTS BD
,      BOM_CALENDAR_DATES BCD
,      MTL_ITEM_QUANTITIES_VIEW MOQ2
,      WIP_REQUIREMENT_OPERATIONS WRO
,      WIP_REPETITIVE_SCHEDULES WRS
,      WIP_LINES WL
,      WIP_ENTITIES WE
WHERE  WL.ORGANIZATION_ID = :P_ORGANIZATION_ID
&C_Limit_Lines
AND    WRS.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WRS.LINE_ID = WL.LINE_ID
AND    WRS.STATUS_TYPE IN &C_Limit_Status
AND    WE.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
AND    WE.ORGANIZATION_ID = :P_ORGANIZATION_ID
&C_Limit_Assemblies
AND    WRO.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
AND    WRO.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WRO.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID
AND    WRO.WIP_SUPPLY_TYPE <> 6
AND    WRO.REQUIRED_QUANTITY != 0
&C_Limit_Supply_Type
&C_Limit_Subinv
AND    ML.LOOKUP_TYPE = 'WIP_SUPPLY_PICK'
AND    ML.LOOKUP_CODE = DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
AND    ML.ENABLED_FLAG = 'Y'
AND    BCD.CALENDAR_CODE = :C_CALENDAR_CODE
AND    BCD.EXCEPTION_SET_ID = :C_EXCEPTION_SET
AND    BCD.CALENDAR_DATE = TRUNC(WRO.DATE_REQUIRED)
AND    (BCD.NEXT_SEQ_NUM < :C_END_NUM
    AND  (BCD.NEXT_SEQ_NUM + WRS.PROCESSING_WORK_DAYS) > :C_START_NUM)
AND    BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID
AND    BD.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    MIL.INVENTORY_LOCATION_ID(+) = WRO.SUPPLY_LOCATOR_ID
AND    MIL.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    MSI2.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND    MSI2.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND    MOQ2.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    MOQ2.INVENTORY_ITEM_ID(+) = WRO.INVENTORY_ITEM_ID
AND    MSINV.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    MSINV.SECONDARY_INVENTORY_NAME(+) = MOQ2.SUBINVENTORY_CODE
AND    (MSINV.DISABLE_DATE IS NULL OR
             MSINV.DISABLE_DATE > TRUNC(SYSDATE))
AND    MIL2.INVENTORY_LOCATION_ID(+) = MOQ2.LOCATOR_ID
AND    MIL2.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
GROUP BY WL.LINE_CODE
,      WL.DESCRIPTION
,      DECODE(:P_SORT_BY,6,WE.WIP_ENTITY_NAME,'X')
,      DECODE(:P_SORT_BY,6,WE.DESCRIPTION,'X')
,      DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
,      ML.MEANING
--,      &P_COMP
,      MSI2.DESCRIPTION
,      DECODE(:P_SORT_BY,6,WRO.OPERATION_SEQ_NUM,1)
,      NVL(BD.DEPARTMENT_CODE,' ')
,      NVL(WRO.SUPPLY_SUBINVENTORY,' ')
--,      &P_SUPPLY_LOC
,      NVL(MIL.INVENTORY_LOCATION_ID,-1)
,      MSI2.PRIMARY_UOM_CODE
,      MOQ2.SUBINVENTORY_CODE
,     MOQ2.REVISION
,      DECODE(MIL2.INVENTORY_LOCATION_ID,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_det_loc_disp', 'INV', 'MTLL', 101, MIL2.ORGANIZATION_ID, MIL2.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'))
,      DECODE(MOQ2.SUBINVENTORY_CODE,NVL(WRO.SUPPLY_SUBINVENTORY,'-1'),
       DECODE(NVL(MOQ2.LOCATOR_ID,-1),NVL(WRO.SUPPLY_LOCATOR_ID,-1),1,0),0)
,      NVL(MOQ2.QUANTITY,0),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_component_disp', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_locator_disp', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_comp_sort', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_loc_sort', 'INV', 'MTLL', 101, MIL.ORGANIZATION_ID, MIL.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'PADDED_VALUE')
ORDER BY 1 ASC,2 ASC,3 ASC,5 ASC,19,14 ASC,15 ASC