Oracle WIP Job Status and OSP Receipts in Discrete MFG Industries

SELECT  

  we.wip_entity_name "Job No.",

  DECODE (wdj.status_type,

         1, 'Unreleased',

         3, 'Released',

         4, 'Complete',

         5, 'Complete NoCharge',

         6, 'On Hold',

         7, 'Cancelled',

         8, 'Pend Bill Load',

         9, 'Failed Bill Load',

         10, 'Pend Rtg Load',

         11, 'Failed Rtg Load',

         12, 'Closed',

         13, 'Pending- Mass Loaded',

         14, 'Pending Close',

         15, 'Failed Close',

         wdj.status_type

       ) "Status Type",

  rsh.receipt_num "OSP RECEIPT No.", wdj.status_type "Status Code",

  msi.segment1 " OSP Item Code",

  msi.description "OSP Item Description",

  djv.PRIMARY_ITEM_ID "Assy Item ID",

  (Select msi.segment1 from mtl_system_items msi where djv.PRIMARY_ITEM_ID=msi.inventory_item_id and msi.organization_id = 2499 ) "Assy.Item Code",

  djv.DESCRIPTION "Assy. Item Description",

  plla.quantity_received,

  wdj.start_quantity "Job Quantity",

  wdj.quantity_completed "Quantity Completed",

  (wdj.start_quantity - wdj.quantity_completed) "QUANTITY REMAINING"

FROM

  wip_entities we,

  wip_discrete_jobs wdj,

  WIP_DISCRETE_JOBS_V djv,

  rcv_shipment_headers rsh,

  rcv_transactions rt,

  po_lines_all pla,                          

  mtl_system_items msi,                      

  po_line_locations_all plla            

WHERE rt.wip_entity_id = we.wip_entity_id

  AND rt.shipment_header_id = rsh.shipment_header_id

  AND rt.wip_entity_id IS NOT NULL

  AND rsh.creation_date > '01-Feb-2010'

  AND rt.destination_type_code = 'SHOP FLOOR'

  AND rt.organization_id = 2499

  AND rt.po_line_id = pla.po_line_id

  AND pla.ITEM_ID= msi.inventory_item_id

  AND msi.organization_id = rt.organization_id

  AND rt.po_line_location_id = plla.line_location_id

  AND rt.wip_entity_id = wdj.wip_entity_id

  AND rt.organization_id = wdj.organization_id

  and djv.WIP_ENTITY_ID=rt.wip_entity_id

ORDER BY we.wip_entity_name