SQL Query To Link Transfer Order, Pick Slip Number, and Supply Orchestration Number (Metalink Doc ID 2309017.1)

 

SELECT
  a.pick_slip_number ,
  b.source_header_number transfer_order_number,
  c.supply_order_number ,
  c.supply_order_reference_number
FROM
  INV_PICK_SLIP_NUMBERS a ,
  WSH_DELIVERY_DETAILS b ,
  DOS_SUPPLY_HEADERS c ,
  INV_TRANSFER_ORDER_HEADERS d,
  INV_TRANSFER_ORDER_LINES e
WHERE 1 =1
  AND a.PICK_SLIP_NUMBER = 3001
  AND a.pick_slip_batch_id = b.batch_id
  AND b.source_header_number = d.header_number
  AND d.header_id = e.header_id
  AND e.source_header_id = c.header_id

 

------------

 

select wdd.batch_id, hdr.header_id hdr_header_id, lines.*
  from inv_transfer_order_headers hdr,
       inv_Transfer_order_lines lines,
       dos_supply_headers sup_hdr,
       wsh_Delivery_details wdd
    --   ,inv_pick_slip_numbers pickslips
 where 1=1
   and hdr.header_number= '778106'
   and lines.header_id = hdr.header_id
   and sup_hdr.header_id (+) = hdr.header_id
   and wdd.source_header_number = hdr.header_number
   and wdd.source_line_number = lines.line_number
  -- and nvl(pickslips.pick_slip_batch_id, 'x') = nvl(wdd.batch_id, 'x')
 order by hdr.creation_Date desc;