Search This Blog

Thursday, March 19, 2015

Query for Drop Ship Order

Below query can be helpful to get integrated information for drop ship orders

SELECT h.order_number
     , l.line_number so_line_number
     , ph.segment1 po_number
     , l.ordered_item
     , l.ordered_quantity
     , por.release_num
     , pl.line_num po_line_number
     , ph.authorization_status
     , prh.interface_source_code
     , prh.segment1 requisition_number
     , prl.line_num requisition_line_number
     , ph.closed_date
     , ph.closed_code
     , ods.header_id so_header_id
     , ods.line_id so_line_id
     , prh.requisition_header_id
     , prl.requisition_line_id
     , ph.po_header_id
     , pl.po_line_id
     , pll.line_location_id po_line_location_id
     , por.po_release_id
FROM oe_drop_ship_sources ods
   , oe_order_headers_all h
   , oe_order_lines_all l
   , po_line_locations_all pll
   , po_lines_all pl
   , po_headers_all ph
   , po_requisition_headers_all prh
   , po_requisition_lines_all prl
   , po_releases_all por
WHERE h.header_id = l.header_id
  AND h.header_id = ods.header_id
  AND l.line_id = ods.line_id
  AND por.po_release_id(+) = ods.po_release_id
  AND ods.line_location_id = pll.line_location_id(+)
  AND ods.po_header_id = pl.po_header_id(+)
  AND ods.po_line_id = pl.po_line_id(+)
  AND ph.po_header_id(+) = pl.po_header_id
  AND prl.requisition_header_id(+) = ods.requisition_header_id
  AND prl.requisition_line_id(+) = ods.requisition_line_id
  AND prh.requisition_header_id(+) = prl.requisition_header_id
  AND h.order_number = :sales_order;


Copyright (c) All rights reserved. Presented by Suresh Vaishya