WIP Outside Processing

SELECT
      WE.WIP_ENTITY_NAME Job_Name,
       DJ.DESCRIPTION Job_Description,
       -1 WIP_Rep_Sched_ID,
       DECODE(WE.PRIMARY_ITEM_ID,NULL,NULL,
         &P_ASSEMBLY) C_Assy_Data,
       SI1.DESCRIPTION Assy_Description,
       Null Line,
       Null Line_Desc,
        LU1.MEANING  Status,
       SI1.PRIMARY_UOM_CODE UOM,
       TO_CHAR(DJ.SCHEDULED_START_DATE,'DD-MON-YY') || TO_CHAR(DJ.SCHEDULED_START_DATE,' HH24:MI') Sched_Start_Date,
       Null FUC_Date,
       Null LUS_Date,       
       TO_CHAR(DJ.SCHEDULED_COMPLETION_DATE,'DD-MON-YY') || TO_CHAR(DJ.SCHEDULED_COMPLETION_DATE,' HH24:MI') Sched_Completion_Date,
       0 Rate,
       0 Days,
       LU2.MEANING Job_Type,
       DJ.QUANTITY_COMPLETED Quantity_Completed,
       DJ.START_QUANTITY Sched_Quantity,
       POD.WIP_OPERATION_SEQ_NUM WIP_Operation_Seq_Num,
       POD.WIP_RESOURCE_SEQ_NUM WIP_Resource_Seq_Num,
       POR.RELEASE_NUM Release_Num,
       BR.RESOURCE_CODE Resource_Code,
       nvl(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) Due_Date,
       POLL.SHIPMENT_NUM PO_Shipment,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('c_po_assy', 'INV', 'MSTK', 101, SI2.ORGANIZATION_ID, SI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') Item,
       POL.LINE_NUM PO_line,
       UOM.uom_code Unit_of_Meas,
       HP.PARTY_NAME Vendor_Name,
       decode(nvl(POD.PO_RELEASE_ID,-1),-1,POH.AGENT_ID,POR.AGENT_ID)  Buyer_id,
       MEV.FULL_NAME Buyer,
       POH.SEGMENT1 PO_Number,
       WE.ENTITY_TYPE   Entity_type,
       POD.QUANTITY_ORDERED qty_ordered,
       POD.QUANTITY_DELIVERED qty_delivered,
       POD.rowid unique_rowid, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assembly', 'INV', 'MSTK', 101, SI1.ORGANIZATION_ID, SI1.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_Assembly
from   MTL_SYSTEM_ITEMS    SI2,
      MFG_LOOKUPS                    LU1,
      MFG_LOOKUPS                   LU2,
      PO_HEADERS_ALL		POH,
      MTL_UNITS_OF_MEASURE UOM,
      PO_LINES_ALL		POL,
      PO_LINE_LOCATIONS_ALL	POLL,
      PO_DISTRIBUTIONS_ALL	POD,
      AP_SUPPLIERS		POV,
      HZ_PARTIES 		HP,
      PO_RELEASES_ALL                     POR,
      MTL_EMPLOYEES_VIEW    MEV,
      BOM_RESOURCES             BR,
      MTL_SYSTEM_ITEMS	  SI1,
      HR_ORGANIZATION_INFORMATION	OOG,
      WIP_ENTITIES                       WE,
      WIP_DISCRETE_JOBS        DJ
WHERE  DJ.ORGANIZATION_ID = :P_ORG_ID
AND    OOG.ORGANIZATION_ID = :P_ORG_ID
&C_DJ_Line_Limiter
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    WE.ORGANIZATION_ID = :P_ORG_ID
AND    WE.WIP_ENTITY_ID = POD.WIP_ENTITY_ID
AND    WE.ENTITY_TYPE != 2
&C_Job_Limiter
AND    SI1.ORGANIZATION_ID &P_OUTER = :P_ORG_ID
AND    SI1.INVENTORY_ITEM_ID &P_OUTER = DJ.PRIMARY_ITEM_ID
And    &P_ITEM_WHERE
AND    DJ.STATUS_TYPE = NVL(:P_STATUS,DJ.STATUS_TYPE) 
AND    LU1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND    LU1.LOOKUP_CODE = DJ.STATUS_TYPE
AND    LU2.LOOKUP_TYPE = 'WIP_DISCRETE_JOB'
AND    LU2.LOOKUP_CODE = DJ.JOB_TYPE
AND    POD.DESTINATION_ORGANIZATION_ID = :P_ORG_ID
AND    NVL(POD.ORG_ID, NVL(OOG.ORG_INFORMATION3, -1)) = NVL(OOG.ORG_INFORMATION3, -1)
AND    OOG.ORG_INFORMATION_CONTEXT = 'Accounting Information'
AND    POD.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND    POD.WIP_ENTITY_ID is not null	
AND    POH.PO_HEADER_ID = POD.PO_HEADER_ID 
AND    POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND    POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND    POL.PO_LINE_ID = POLL.PO_LINE_ID
AND    POH.PO_HEADER_ID = POLL.PO_HEADER_ID
AND    decode(nvl(POD.PO_RELEASE_ID,-1),-1,POH.AGENT_ID,POR.AGENT_ID) = MEV.EMPLOYEE_ID
AND    MEV.ORGANIZATION_ID = :P_ORG_ID
AND    POR.PO_RELEASE_ID (+) = POD.PO_RELEASE_ID 
AND    POR.PO_HEADER_ID (+) = POD.PO_HEADER_ID 
AND    POV.VENDOR_ID (+) = POH.VENDOR_ID
AND    POV.PARTY_ID = HP.PARTY_ID (+)
AND    BR.RESOURCE_ID (+) = POD.BOM_RESOURCE_ID
AND    SI2.INVENTORY_ITEM_ID = POL.ITEM_ID
AND    SI2.ORGANIZATION_ID = :P_ORG_ID
AND    UOM.UNIT_OF_MEASURE = POL.UNIT_MEAS_LOOKUP_CODE
&C_OPEN_ONLY
UNION
SELECT WE.WIP_ENTITY_NAME Job_Name,
       WE.DESCRIPTION Job_Description,
       WRS.REPETITIVE_SCHEDULE_ID WIP_Rep_Sched_ID,
       NULL C_Assy_Data,
       Null Assy_Description,
       WL.LINE_CODE Line,
       WL.DESCRIPTION Line_Desc,      
       LU1.MEANING Status,
       SI1.PRIMARY_UOM_CODE UOM,
       TO_CHAR(WRS.FIRST_UNIT_START_DATE) || TO_CHAR(WRS.FIRST_UNIT_START_DATE,' HH24:MI') Sched_Start_Date,
       TO_CHAR(WRS.FIRST_UNIT_COMPLETION_DATE) || TO_CHAR(WRS.FIRST_UNIT_COMPLETION_DATE,' HH24:MI') FUC_Date,
       TO_CHAR(WRS.LAST_UNIT_START_DATE) || TO_CHAR(WRS.LAST_UNIT_START_DATE,' HH24:MI') LUS_Date,
       TO_CHAR(WRS.LAST_UNIT_COMPLETION_DATE) || TO_CHAR(WRS.LAST_UNIT_COMPLETION_DATE,' HH24:MI') Sched_Completion_Date,
       WRS.DAILY_PRODUCTION_RATE Rate,
       WRS.PROCESSING_WORK_DAYS Days,
       Null Job_Type,
       WRS.QUANTITY_COMPLETED Quantity_Completed,
       (WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS) Sched_Qty,
       POD.WIP_OPERATION_SEQ_NUM WIP_Operation_Seq_Num,
       POD.WIP_RESOURCE_SEQ_NUM WIP_Resource_Seq_Num,
       POR.RELEASE_NUM Release_Num,
       BR.RESOURCE_CODE Resource_Code, 
       nvl(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) Due_Date,
       POLL.SHIPMENT_NUM PO_Shipment,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('c_po_assy', 'INV', 'MSTK', 101, SI2.ORGANIZATION_ID, SI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') Item,
       POL.LINE_NUM PO_line,
       UOM.UOM_CODE Unit_of_Meas,
       HP.PARTY_NAME Vendor_Name,
       decode(nvl(POD.PO_RELEASE_ID,-1),-1,POR.AGENT_ID)  Buyer_id,
       MEV.FULL_NAME Buyer,
       POH.SEGMENT1 PO_Number,
       WE.ENTITY_TYPE   Entity_type,
       POD.QUANTITY_ORDERED qty_ordered,
       POD.QUANTITY_DELIVERED qty_delivered,
       POD.rowid unique_rowid, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_assembly', 'INV', 'MSTK', 101, SI1.ORGANIZATION_ID, SI1.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_Assembly
FROM  MTL_SYSTEM_ITEMS   SI2,
     WIP_REPETITIVE_SCHEDULES WRS,
     WIP_REPETITIVE_ITEMS WRI,
     MFG_LOOKUPS  LU1,
     WIP_LINES          WL,
     PO_HEADERS_ALL         POH,  
     MTL_UNITS_OF_MEASURE UOM,
     PO_LINES_ALL           POL,   
     PO_LINE_LOCATIONS_ALL  POLL,
     PO_DISTRIBUTIONS_ALL   POD,
     AP_SUPPLIERS	        POV,
     HZ_PARTIES HP,
     PO_RELEASES_ALL                     POR,
     MTL_EMPLOYEES_VIEW       MEV,
     BOM_RESOURCES      BR,
     MTL_SYSTEM_ITEMS   SI1,
     HR_ORGANIZATION_INFORMATION	OOG,
     WIP_ENTITIES WE
WHERE
     WE.ORGANIZATION_ID = :P_ORG_ID
AND  WE.ENTITY_TYPE = 2
AND  WE.WIP_ENTITY_ID = POD.WIP_ENTITY_ID
&C_Job_Limiter
AND  SI1.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND  SI1.ORGANIZATION_ID = :P_ORG_ID
AND  &P_ITEM_WHERE
AND  WRS.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND  WRS.ORGANIZATION_ID = :P_ORG_ID
AND  WRI.PRIMARY_ITEM_ID = SI1.INVENTORY_ITEM_ID
AND  WRI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND  LU1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND  LU1.LOOKUP_CODE = WRS.STATUS_TYPE
AND  WL.LINE_ID = WRS.LINE_ID
AND  WL.ORGANIZATION_ID = :P_ORG_ID
&C_Line_Limiter
AND  WRS.STATUS_TYPE = NVL(:P_STATUS,WRS.STATUS_TYPE)
AND  POD.WIP_ENTITY_ID is not null
AND  POD.DESTINATION_ORGANIZATION_ID = :P_ORG_ID
AND  OOG.ORGANIZATION_ID = :P_ORG_ID
AND  NVL(POD.ORG_ID, NVL(OOG.ORG_INFORMATION3, -1)) = NVL(OOG.ORG_INFORMATION3, -1)
AND  OOG.ORG_INFORMATION_CONTEXT = 'Accounting Information'
AND  POD.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND  POD.WIP_REPETITIVE_SCHEDULE_ID =  WRS.REPETITIVE_SCHEDULE_ID
AND  POD.DESTINATION_TYPE_CODE = 'SHOP FLOOR'	
AND  POH.PO_HEADER_ID = POD.PO_HEADER_ID
AND  POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND  POLL.PO_HEADER_ID = POD.PO_HEADER_ID
AND  POL.PO_LINE_ID = POLL.PO_LINE_ID
AND  POL.PO_HEADER_ID = POD.PO_HEADER_ID
AND  decode(nvl(POD.PO_RELEASE_ID,-1),-1,POH.AGENT_ID,POR.AGENT_ID) = MEV.EMPLOYEE_ID
AND  MEV.ORGANIZATION_ID = :P_ORG_ID
AND   POR.PO_RELEASE_ID (+) = POD.PO_RELEASE_ID 
AND   POR.PO_HEADER_ID (+) = POD.PO_HEADER_ID 
AND  POV.VENDOR_ID (+) = POH.VENDOR_ID
AND  POV.PARTY_ID = HP.PARTY_ID (+)
AND  BR.RESOURCE_ID (+) = POD.BOM_RESOURCE_ID
AND  SI2.INVENTORY_ITEM_ID = POL.ITEM_ID
AND  SI2.ORGANIZATION_ID = POD.DESTINATION_ORGANIZATION_ID
AND  UOM.UNIT_OF_MEASURE = POL.UNIT_MEAS_LOOKUP_CODE
&C_OPEN_ONLY
ORDER BY 1 ASC,6 ASC,10 ASC,3 ASC,19 ASC,20 
  ASC,22 ASC,31 ASC,21 ASC,30 ASC,29 ASC,28 ASC,26 ASC,C_Assembly ASC,27 ASC,24 ASC,
  23 ASC , 1 , 6 , 10 , 3 , 19 , 20 , 28 , 25 , 23