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

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 = '66411';