Monday, July 4, 2016

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect