Wednesday, June 5, 2013

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',
                ) "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",
         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 BY we.wip_entity_name

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect