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 = :ORG_ID

     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 BYwe.wip_entity_name