WIP Discrete Job Pick List

select rq.Job,
       rq.Job_Desc,
	   rq.Status,
	   rq.C_ASSY,
	   rq.Assembly_ID,
	   rq.Assembly_Desc,
       rq.Planner,
	   rq.Scheduled_Start,
	   rq.Class,
	   rq.Class_Desc,
	   rq.Job_Start_Qty,
	   rq.Scheduled_Complete,
       rq.Supply_Code,
	   rq.Wip_Supply_Type,
	   rq.Date_Required,
	   rq.Component,
	   rq.Comp_Desc,
	   rq.Op_Seq,
	   rq.Supply_Subinv,
	   rq.Supply_Locator,
	   rq.Locator_Id,
	   rq.Quantity_Open,
	   rq.UOM,
	   rq.On_Hand_Subinv,
	   rq.On_Hand_Locator,
	   rq.Supply_Flag,
	   rq.Quantity_On_Hand,
	   rq.Build_Sequence,
	   rq.Schedule_Group_Name,
	   rq.Item_Seq,
	   rq.C_ASSY_DISP,
	   rq.C_Order,
	   rq.C_Comp_Sort,
	   rq.C_Loc_Sort,
	   rq.C_Component_Disp,
	   rq.C_Locator_Disp,
	   rq.C_Det_Loc_Disp,
	   rq.Scheduled_Start1,
	   rq.Scheduled_complete1,
	   rq.DATE_REQUIRED1
FROM		  
(SELECT DECODE(:P_SORT_BY, 9, 'X',WE.WIP_ENTITY_NAME)    Job
,      DECODE(:P_SORT_BY, 9, 'X',WE.DESCRIPTION )       Job_Desc
,      DECODE(:P_SORT_BY, 9, 'X',ML1.MEANING )          Status
,      DECODE(:P_SORT_BY, 9, 'X',null )                      C_ASSY
,      DECODE(:P_SORT_BY, 9, -1,NVL(WDJ.PRIMARY_ITEM_ID, -1)) Assembly_ID
,      DECODE(:P_SORT_BY, 9, 'X',MSITL.DESCRIPTION )            Assembly_Desc
,      DECODE(:P_SORT_BY, 9, 'X',MSI.PLANNER_CODE)            Planner
,      DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_START_DATE) Scheduled_Start
,      DECODE(:P_SORT_BY, 9, 'X',WDJ.CLASS_CODE )             Class
,      DECODE(:P_SORT_BY, 9, 'X',WAC.DESCRIPTION)             Class_Desc
,      DECODE(:P_SORT_BY, 9, 0,WDJ.START_QUANTITY)            Job_Start_Qty
,      DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_COMPLETION_DATE)  Scheduled_Complete
,      DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
                                          Supply_Code
,      ML2.MEANING                        Wip_Supply_Type
,      DECODE(:P_SORT_BY, 9, SYSDATE,WRO.DATE_REQUIRED  )                Date_Required
,      null                            Component
,      MSI2.DESCRIPTION                   Comp_Desc
,      DECODE(:P_SORT_BY, 9, 0,WRO.OPERATION_SEQ_NUM  )           Op_Seq
,      WRO.SUPPLY_SUBINVENTORY            Supply_Subinv
,      null                      Supply_Locator
,      NVL(MIL.INVENTORY_LOCATION_ID,-1)  Locator_Id
,      SUM(WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED)       Quantity_Open
,      MSI2.PRIMARY_UOM_CODE              UOM
,      MOQ2.SUBINVENTORY_CODE             On_Hand_Subinv
,      DECODE(MIL2.INVENTORY_LOCATION_ID,NULL,NULL,&P_DETAIL_LOC)                      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
,      DECODE(:P_SORT_BY, 9,0, WDJ.BUILD_SEQUENCE)              Build_Sequence
,      DECODE(:P_SORT_BY, 9, 'X',SG.SCHEDULE_GROUP_NAME)   Schedule_Group_Name
,      DECODE(:P_SORT_BY, 9, 0, BIC.ITEM_NUM)           Item_Seq,
	decode(WDJ.PRIMARY_ITEM_ID,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assy_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_ASSY_DISP,
	WIP_WIPDJPCK_XMLP_PKG.order_func(( MSI2.SEGMENT1 || '\n' || MSI2.SEGMENT2 || '\n' || MSI2.SEGMENT3 || '\n' || MSI2.SEGMENT4 || '\n' || MSI2.SEGMENT5 || '\n' || MSI2.SEGMENT6 || '\n' || MSI2.SEGMENT7 || '\n' || MSI2.SEGMENT8 || '\n' || MSI2.SEGMENT9 || '\n' || MSI2.SEGMENT10 || '\n' || MSI2.SEGMENT11 || '\n' || MSI2.SEGMENT12 || '\n' || MSI2.SEGMENT13 || '\n' || MSI2.SEGMENT14 || '\n' || MSI2.SEGMENT15 || '\n' || MSI2.SEGMENT16 || '\n' || MSI2.SEGMENT17 || '\n' || MSI2.SEGMENT18 || '\n' || MSI2.SEGMENT19 || '\n' || MSI2.SEGMENT20 ), ( MIL.SEGMENT1 || '\n' || MIL.SEGMENT2 || '\n' || MIL.SEGMENT3 || '\n' || MIL.SEGMENT4 || '\n' || MIL.SEGMENT5 || '\n' || MIL.SEGMENT6 || '\n' || MIL.SEGMENT7 || '\n' || MIL.SEGMENT8 || '\n' || MIL.SEGMENT9 || '\n' || MIL.SEGMENT10 || '\n' || MIL.SEGMENT11 || '\n' || MIL.SEGMENT12 || '\n' || MIL.SEGMENT13 || '\n' || MIL.SEGMENT14 || '\n' || MIL.SEGMENT15 || '\n' || MIL.SEGMENT16 || '\n' || MIL.SEGMENT17 || '\n' || MIL.SEGMENT18 || '\n' || MIL.SEGMENT19 || '\n' || MIL.SEGMENT20 ), :C_Component_Disp, :C_Locator_Disp, :C_Comp_Sort, :C_Loc_Sort, WRO.SUPPLY_SUBINVENTORY, DECODE ( :P_SORT_BY , 9 , 0 , WRO.OPERATION_SEQ_NUM ), DECODE ( :P_SORT_BY , 9 , SYSDATE , WRO.DATE_REQUIRED ), DECODE ( :P_SORT_BY , 9 , 0 , BIC.ITEM_NUM )) C_Order,
	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_WIPDJPCK_XMLP_PKG.c_loc_sortformula(:C_Locator_Disp, NVL ( MIL.INVENTORY_LOCATION_ID , - 1 ), :C_Loc_Sort) C_Loc_Sort,
	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_det_loc_disp', 'INV', 'MTLL', 101, MIL2.ORGANIZATION_ID, MIL2.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_Det_Loc_Disp,
	DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_START_DATE,'DD-MON-YY')) Scheduled_Start1
	,DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_COMPLETION_DATE,'DD-MON-YY')) Scheduled_complete1
	,      DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WRO.DATE_REQUIRED,'DD-MON-YY')) DATE_REQUIRED1
FROM   MFG_LOOKUPS ML1
,      MFG_LOOKUPS ML2
,      MTL_ITEM_LOCATIONS MIL
,      MTL_ITEM_LOCATIONS MIL2
,      MTL_SECONDARY_INVENTORIES MSINV
,      MTL_ITEM_QUANTITIES_VIEW MOQ2
,      WIP_ENTITIES WE
,      MTL_SYSTEM_ITEMS_B MSI
,      MTL_SYSTEM_ITEMS_TL MSITL
,      WIP_ACCOUNTING_CLASSES WAC
,      MTL_SYSTEM_ITEMS_VL MSI2
,      WIP_REQUIREMENT_OPERATIONS WRO
,      WIP_SCHEDULE_GROUPS SG
,      WIP_DISCRETE_JOBS WDJ
,      BOM_INVENTORY_COMPONENTS BIC
WHERE  WDJ.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WDJ.STATUS_TYPE IN &C_Limit_Status
AND    SG.SCHEDULE_GROUP_ID &P_SG_OUTER = WDJ.SCHEDULE_GROUP_ID
AND    SG.ORGANIZATION_ID &P_SG_OUTER = WDJ.ORGANIZATION_ID
AND    ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND    ML1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND    ML1.ENABLED_FLAG = 'Y'
AND    MSI.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND    MSI.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    MSITL.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND    MSITL.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    MSITL.LANGUAGE(+) = USERENV('LANG')
AND    WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND    WE.ENTITY_TYPE IN (1,6)
AND    WE.ORGANIZATION_ID = :P_ORGANIZATION_ID
&C_Limit_Jobs
&C_Limit_Dates
&C_Schedule_Group
AND    WAC.CLASS_CODE = WDJ.CLASS_CODE
AND    WAC.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND    WRO.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WRO.WIP_SUPPLY_TYPE <> 6
AND    (      ( WRO.REQUIRED_QUANTITY > 0
                     AND    WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED > 0 )
              OR ( WRO.REQUIRED_QUANTITY < 0
                      AND   WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED < 0 )  )
&C_Limit_Supply_Type
&C_Limit_Subinv
AND    ML2.LOOKUP_TYPE = 'WIP_SUPPLY_PICK'
AND    ML2.LOOKUP_CODE =          DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
AND    ML2.ENABLED_FLAG = 'Y'
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
AND    MIL2.INVENTORY_LOCATION_ID(+) = MOQ2.LOCATOR_ID
AND    MIL2.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    BIC.COMPONENT_SEQUENCE_ID(+) = WRO.COMPONENT_SEQUENCE_ID
AND    NOT(NVL(MSI2.RECEIVING_ROUTING_ID,-1) = 3
           AND
           MSI2.STOCK_ENABLED_FLAG = 'N'
		   AND
		   WE.ENTITY_TYPE <> 1 )
GROUP BY DECODE(:P_SORT_BY, 9, 'X',WE.WIP_ENTITY_NAME)
,      DECODE(:P_SORT_BY, 9, 'X',WE.DESCRIPTION )
,      DECODE(:P_SORT_BY, 9, 'X',ML1.MEANING )
,      DECODE(:P_SORT_BY, 9, 'X',null)
,      DECODE(:P_SORT_BY, 9, -1,NVL(WDJ.PRIMARY_ITEM_ID, -1))
,      DECODE(:P_SORT_BY, 9, 'X',MSITL.DESCRIPTION )
,      DECODE(:P_SORT_BY, 9, 'X',MSI.PLANNER_CODE)
,      DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_START_DATE)
,      DECODE(:P_SORT_BY, 9, 'X',WDJ.CLASS_CODE )
,      DECODE(:P_SORT_BY, 9, 'X',WAC.DESCRIPTION)
,      DECODE(:P_SORT_BY, 9, 0,WDJ.START_QUANTITY)
,      DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_COMPLETION_DATE)
,      DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
,      ML2.MEANING
,      DECODE(:P_SORT_BY, 9, SYSDATE,WRO.DATE_REQUIRED  )
,      null
,      MSI2.DESCRIPTION
,      DECODE(:P_SORT_BY,9,0,WRO.OPERATION_SEQ_NUM  )
,      DECODE(:P_SORT_BY,9,0,BIC.ITEM_NUM)
,      WRO.SUPPLY_SUBINVENTORY
,      null
,      NVL(MIL.INVENTORY_LOCATION_ID,-1)
,      MSI2.PRIMARY_UOM_CODE
,      MOQ2.SUBINVENTORY_CODE
,      DECODE(MIL2.INVENTORY_LOCATION_ID,NULL,NULL,&P_DETAIL_LOC)
,      MOQ2.REVISION
,      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)
,      DECODE(:P_SORT_BY, 9,0, WDJ.BUILD_SEQUENCE)
,      DECODE(:P_SORT_BY, 9, 'X',SG.SCHEDULE_GROUP_NAME),
decode(WDJ.PRIMARY_ITEM_ID,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assy_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_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_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_det_loc_disp', 'INV', 'MTLL', 101, MIL2.ORGANIZATION_ID, MIL2.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'),
( MSI2.SEGMENT1 || '\n' || MSI2.SEGMENT2 || '\n' || MSI2.SEGMENT3 || '\n' || MSI2.SEGMENT4 || '\n' || MSI2.SEGMENT5 || '\n' || MSI2.SEGMENT6 || '\n' || MSI2.SEGMENT7 || '\n' || MSI2.SEGMENT8 || '\n' || MSI2.SEGMENT9 || '\n' || MSI2.SEGMENT10 || '\n' || MSI2.SEGMENT11 || '\n' || MSI2.SEGMENT12 || '\n' || MSI2.SEGMENT13 || '\n' || MSI2.SEGMENT14 || '\n' || MSI2.SEGMENT15 || '\n' || MSI2.SEGMENT16 || '\n' || MSI2.SEGMENT17 || '\n' || MSI2.SEGMENT18 || '\n' || MSI2.SEGMENT19 || '\n' || MSI2.SEGMENT20 ), ( MIL.SEGMENT1 || '\n' || MIL.SEGMENT2 || '\n' || MIL.SEGMENT3 || '\n' || MIL.SEGMENT4 || '\n' || MIL.SEGMENT5 || '\n' || MIL.SEGMENT6 || '\n' || MIL.SEGMENT7 || '\n' || MIL.SEGMENT8 || '\n' || MIL.SEGMENT9 || '\n' || MIL.SEGMENT10 || '\n' || MIL.SEGMENT11 || '\n' || MIL.SEGMENT12 || '\n' || MIL.SEGMENT13 || '\n' || MIL.SEGMENT14 || '\n' || MIL.SEGMENT15 || '\n' || MIL.SEGMENT16 || '\n' || MIL.SEGMENT17 || '\n' || MIL.SEGMENT18 || '\n' || MIL.SEGMENT19 || '\n' || MIL.SEGMENT20 ),
DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_START_DATE,'DD-MON-YY'))
,DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_COMPLETION_DATE,'DD-MON-YY'))
,DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WRO.DATE_REQUIRED,'DD-MON-YY'))
UNION
SELECT DECODE(:P_SORT_BY, 9, 'X',WE.WIP_ENTITY_NAME)    Job
,      DECODE(:P_SORT_BY, 9, 'X',WE.DESCRIPTION )       Job_Desc
,      DECODE(:P_SORT_BY, 9, 'X',ML1.MEANING )          Status
,      DECODE(:P_SORT_BY, 9, 'X',null )                      C_ASSY
,      DECODE(:P_SORT_BY, 9, -1,NVL(WDJ.PRIMARY_ITEM_ID, -1)) Assembly_ID
,      DECODE(:P_SORT_BY, 9, 'X',MSITL.DESCRIPTION )            Assembly_Desc
,      DECODE(:P_SORT_BY, 9, 'X',MSI.PLANNER_CODE)            Planner
,      DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_START_DATE) Scheduled_Start
,      DECODE(:P_SORT_BY, 9, 'X',WDJ.CLASS_CODE )             Class
,      DECODE(:P_SORT_BY, 9, 'X',WAC.DESCRIPTION)             Class_Desc
,      DECODE(:P_SORT_BY, 9, 0,WDJ.START_QUANTITY)            Job_Start_Qty
,      DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_COMPLETION_DATE)  Scheduled_Complete
,      DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
                                          Supply_Code
,      ML2.MEANING                        Wip_Supply_Type
,      DECODE(:P_SORT_BY, 9, SYSDATE,WRO.DATE_REQUIRED  )                Date_Required
,      null                            Component
,      MSI2.DESCRIPTION                   Comp_Desc
,      DECODE(:P_SORT_BY, 9, 0,WRO.OPERATION_SEQ_NUM  )           Op_Seq
,      WRO.SUPPLY_SUBINVENTORY            Supply_Subinv
,      null                      Supply_Locator
,      NVL(MIL.INVENTORY_LOCATION_ID,-1)  Locator_Id
,      SUM(WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED)       Quantity_Open
,      MSI2.PRIMARY_UOM_CODE              UOM
,      MOQ2.SUBINVENTORY_CODE             On_Hand_Subinv
,      DECODE(MIL2.INVENTORY_LOCATION_ID,NULL,NULL,&P_DETAIL_LOC)                      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
,      DECODE(:P_SORT_BY, 9,0, WDJ.BUILD_SEQUENCE)              Build_Sequence
,      DECODE(:P_SORT_BY, 9, 'X',SG.SCHEDULE_GROUP_NAME)   Schedule_Group_Name
,      DECODE(:P_SORT_BY, 9, 0, BIC.ITEM_NUM)           Item_Seq,
	decode(WDJ.PRIMARY_ITEM_ID,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assy_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_ASSY_DISP,
	WIP_WIPDJPCK_XMLP_PKG.order_func(( MSI2.SEGMENT1 || '\n' || MSI2.SEGMENT2 || '\n' || MSI2.SEGMENT3 || '\n' || MSI2.SEGMENT4 || '\n' || MSI2.SEGMENT5 || '\n' || MSI2.SEGMENT6 || '\n' || MSI2.SEGMENT7 || '\n' || MSI2.SEGMENT8 || '\n' || MSI2.SEGMENT9 || '\n' || MSI2.SEGMENT10 || '\n' || MSI2.SEGMENT11 || '\n' || MSI2.SEGMENT12 || '\n' || MSI2.SEGMENT13 || '\n' || MSI2.SEGMENT14 || '\n' || MSI2.SEGMENT15 || '\n' || MSI2.SEGMENT16 || '\n' || MSI2.SEGMENT17 || '\n' || MSI2.SEGMENT18 || '\n' || MSI2.SEGMENT19 || '\n' || MSI2.SEGMENT20 ), ( MIL.SEGMENT1 || '\n' || MIL.SEGMENT2 || '\n' || MIL.SEGMENT3 || '\n' || MIL.SEGMENT4 || '\n' || MIL.SEGMENT5 || '\n' || MIL.SEGMENT6 || '\n' || MIL.SEGMENT7 || '\n' || MIL.SEGMENT8 || '\n' || MIL.SEGMENT9 || '\n' || MIL.SEGMENT10 || '\n' || MIL.SEGMENT11 || '\n' || MIL.SEGMENT12 || '\n' || MIL.SEGMENT13 || '\n' || MIL.SEGMENT14 || '\n' || MIL.SEGMENT15 || '\n' || MIL.SEGMENT16 || '\n' || MIL.SEGMENT17 || '\n' || MIL.SEGMENT18 || '\n' || MIL.SEGMENT19 || '\n' || MIL.SEGMENT20 ), :C_Component_Disp, :C_Locator_Disp, :C_Comp_Sort, :C_Loc_Sort, WRO.SUPPLY_SUBINVENTORY, DECODE ( :P_SORT_BY , 9 , 0 , WRO.OPERATION_SEQ_NUM ), DECODE ( :P_SORT_BY , 9 , SYSDATE , WRO.DATE_REQUIRED ), DECODE ( :P_SORT_BY , 9 , 0 , BIC.ITEM_NUM )) C_Order,
	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_WIPDJPCK_XMLP_PKG.c_loc_sortformula(:C_Locator_Disp, NVL ( MIL.INVENTORY_LOCATION_ID , - 1 ), :C_Loc_Sort) C_Loc_Sort,
	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_det_loc_disp', 'INV', 'MTLL', 101, MIL2.ORGANIZATION_ID, MIL2.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') C_Det_Loc_Disp,
	DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_START_DATE,'DD-MON-YY')) Scheduled_Start1
	,DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_COMPLETION_DATE,'DD-MON-YY')) Scheduled_complete1
	,      DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WRO.DATE_REQUIRED,'DD-MON-YY')) DATE_REQUIRED1
FROM   MFG_LOOKUPS ML1
,      MFG_LOOKUPS ML2
,      MTL_ITEM_LOCATIONS MIL
,      MTL_ITEM_LOCATIONS MIL2
,      MTL_SECONDARY_INVENTORIES MSINV
,      MTL_ITEM_QUANTITIES_VIEW MOQ2
,      WIP_ENTITIES WE
,      MTL_SYSTEM_ITEMS_B MSI
,      MTL_SYSTEM_ITEMS_TL MSITL
,      WIP_ACCOUNTING_CLASSES WAC
,      MTL_SYSTEM_ITEMS_VL MSI2
,      WIP_REQUIREMENT_OPERATIONS WRO
,      WIP_SCHEDULE_GROUPS SG
,      WIP_DISCRETE_JOBS WDJ
,      BOM_INVENTORY_COMPONENTS BIC
WHERE  WDJ.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WDJ.STATUS_TYPE IN &C_Limit_Status
AND    SG.SCHEDULE_GROUP_ID &P_SG_OUTER = WDJ.SCHEDULE_GROUP_ID
AND    SG.ORGANIZATION_ID &P_SG_OUTER = WDJ.ORGANIZATION_ID
AND    ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND    ML1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND    ML1.ENABLED_FLAG = 'Y'
AND    MSI.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND    MSI.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    MSITL.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND    MSITL.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    MSITL.LANGUAGE(+) = USERENV('LANG')
AND    WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND    WE.ENTITY_TYPE IN (1,6)
AND    WE.ORGANIZATION_ID = :P_ORGANIZATION_ID
&C_Limit_Jobs
&C_Limit_Dates
&C_Schedule_Group
AND    WAC.CLASS_CODE = WDJ.CLASS_CODE
AND    WAC.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND    WRO.ORGANIZATION_ID = :P_ORGANIZATION_ID
AND    WRO.WIP_SUPPLY_TYPE <> 6
AND    (      ( WRO.REQUIRED_QUANTITY > 0
                     AND    WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED > 0 )
              OR ( WRO.REQUIRED_QUANTITY < 0
                      AND   WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED < 0 )  )
&C_Limit_Supply_Type
&C_Limit_Subinv
AND    ML2.LOOKUP_TYPE = 'WIP_SUPPLY_PICK'
AND    ML2.LOOKUP_CODE =          DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
AND    ML2.ENABLED_FLAG = 'Y'
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(+) > TRUNC(SYSDATE)
AND    MIL2.INVENTORY_LOCATION_ID(+) = MOQ2.LOCATOR_ID
AND    MIL2.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
AND    BIC.COMPONENT_SEQUENCE_ID(+) = WRO.COMPONENT_SEQUENCE_ID
AND    NOT(NVL(MSI2.RECEIVING_ROUTING_ID,-1) = 3
           AND
           MSI2.STOCK_ENABLED_FLAG = 'N'
		   AND 
		   WE.ENTITY_TYPE <> 1 )
GROUP BY DECODE(:P_SORT_BY, 9, 'X',WE.WIP_ENTITY_NAME)
,      DECODE(:P_SORT_BY, 9, 'X',WE.DESCRIPTION )
,      DECODE(:P_SORT_BY, 9, 'X',ML1.MEANING )
,      DECODE(:P_SORT_BY, 9, 'X',null)
,      DECODE(:P_SORT_BY, 9, -1,NVL(WDJ.PRIMARY_ITEM_ID, -1))
,      DECODE(:P_SORT_BY, 9, 'X',MSITL.DESCRIPTION )
,      DECODE(:P_SORT_BY, 9, 'X',MSI.PLANNER_CODE)
,      DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_START_DATE)
,      DECODE(:P_SORT_BY, 9, 'X',WDJ.CLASS_CODE )
,      DECODE(:P_SORT_BY, 9, 'X',WAC.DESCRIPTION)
,      DECODE(:P_SORT_BY, 9, 0,WDJ.START_QUANTITY)
,      DECODE(:P_SORT_BY, 9, SYSDATE,WDJ.SCHEDULED_COMPLETION_DATE)
,      DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
,      ML2.MEANING
,      DECODE(:P_SORT_BY, 9, SYSDATE,WRO.DATE_REQUIRED  )
,      null
,      MSI2.DESCRIPTION
,      DECODE(:P_SORT_BY,9,0,WRO.OPERATION_SEQ_NUM  )
,      DECODE(:P_SORT_BY,9,0,BIC.ITEM_NUM)
,      WRO.SUPPLY_SUBINVENTORY
,      null
,      NVL(MIL.INVENTORY_LOCATION_ID,-1)
,      MSI2.PRIMARY_UOM_CODE
,      MOQ2.SUBINVENTORY_CODE
,      DECODE(MIL2.INVENTORY_LOCATION_ID,NULL,NULL,&P_DETAIL_LOC)
,      MOQ2.REVISION
,      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)
,      DECODE(:P_SORT_BY, 9,0, WDJ.BUILD_SEQUENCE)
,      DECODE(:P_SORT_BY, 9, 'X',SG.SCHEDULE_GROUP_NAME),
decode(WDJ.PRIMARY_ITEM_ID,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assy_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_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_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_det_loc_disp', 'INV', 'MTLL', 101, MIL2.ORGANIZATION_ID, MIL2.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE'),
( MSI2.SEGMENT1 || '\n' || MSI2.SEGMENT2 || '\n' || MSI2.SEGMENT3 || '\n' || MSI2.SEGMENT4 || '\n' || MSI2.SEGMENT5 || '\n' || MSI2.SEGMENT6 || '\n' || MSI2.SEGMENT7 || '\n' || MSI2.SEGMENT8 || '\n' || MSI2.SEGMENT9 || '\n' || MSI2.SEGMENT10 || '\n' || MSI2.SEGMENT11 || '\n' || MSI2.SEGMENT12 || '\n' || MSI2.SEGMENT13 || '\n' || MSI2.SEGMENT14 || '\n' || MSI2.SEGMENT15 || '\n' || MSI2.SEGMENT16 || '\n' || MSI2.SEGMENT17 || '\n' || MSI2.SEGMENT18 || '\n' || MSI2.SEGMENT19 || '\n' || MSI2.SEGMENT20 ), ( MIL.SEGMENT1 || '\n' || MIL.SEGMENT2 || '\n' || MIL.SEGMENT3 || '\n' || MIL.SEGMENT4 || '\n' || MIL.SEGMENT5 || '\n' || MIL.SEGMENT6 || '\n' || MIL.SEGMENT7 || '\n' || MIL.SEGMENT8 || '\n' || MIL.SEGMENT9 || '\n' || MIL.SEGMENT10 || '\n' || MIL.SEGMENT11 || '\n' || MIL.SEGMENT12 || '\n' || MIL.SEGMENT13 || '\n' || MIL.SEGMENT14 || '\n' || MIL.SEGMENT15 || '\n' || MIL.SEGMENT16 || '\n' || MIL.SEGMENT17 || '\n' || MIL.SEGMENT18 || '\n' || MIL.SEGMENT19 || '\n' || MIL.SEGMENT20 ),
DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_START_DATE,'DD-MON-YY'))
,DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WDJ.SCHEDULED_COMPLETION_DATE,'DD-MON-YY'))
,DECODE(:P_SORT_BY, 9,to_char(SYSDATE,'DD-MON-YY'),to_char(WRO.DATE_REQUIRED,'DD-MON-YY'))) rq
ORDER BY JOB ASC,C_ASSY_DISP ASC,Scheduled_Start ASC,Planner ASC,Class ASC,Build_Sequence ASC,Schedule_Group_Name ASC,Supply_Code ASC,C_COMPONENT_DISP ASC,On_Hand_Subinv ASC,C_Det_Loc_Disp ASC,SUPPLY_FLAG ASC