Oracle Order Management: Drop Ship Tables and SQL Query

Below are the basic tables used during Drop Ship flow

apps.oe_order_headers_all 

apps.oe_order_lines_all

apps.oe_drop_ship_sources

apps.po_requisitions_interface_all

apps.po_interface_errors

apps.po_requisition_headers_all

apps.po_headers_all

apps.po_lines_all

apps.po_line_locations_all

apps.po_distibutions_all

apps.po_releases_all

Below is the SQL Statement to find out the Drop Ship SO and corresponding Requistiona and PO details along with their status

/* Formatted on 3/23/2017 11:23:52 AM (QP5 v5.114.809.3010) */
SELECT   ooh.order_number,
         ool.ordered_item,
         ool.ordered_quantity,
         ooh.flow_status_code header_status,
         ool.flow_status_code line_status,
         prha.segment1 requisition,
         poh.segment1 po_number,
         poh.closed_code po_status,
         pll.quantity,
         pll.quantity_received,
         pll.closed_code po_shipment_status
  FROM   apps.oe_order_headers_all ooh,
         apps.oe_order_lines_all ool,
         apps.oe_drop_ship_sources odss,
         apps.po_requisition_headers_all prha,
         apps.po_headers_all poh,
         apps.po_lines_all pol,
         apps.po_line_locations_all pll
 WHERE       ool.header_id = ooh.header_id
         AND odss.header_id = ooh.header_id
         AND odss.line_id = ool.line_id
         AND prha.requisition_header_id = odss.requisition_header_id
         AND poh.po_header_id = odss.po_header_id
         AND pol.po_line_id = odss.po_line_id
         AND pol.po_header_id = poh.po_header_id
         AND pll.po_line_id = pol.po_line_id
         AND ooh.order_number = 'XXXXX';