September 7, 2014

Oracle Order Management: Drop Ship Tables and SQL Query

bishnupc / /
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';
Copyright © . Oracle Apps (SCM) . All Rights Reserved
Oracle Apps (SCM) is an online knowledge sharing blog which index online free tutorials, blogs and other sources, to get easier and accessible manner. The blog has been created keeping only one intention of sharing knowledge and for learning purpose. All trademarks, trade names, service marks, copyrighted work, logos referenced herein belong to their respective owners/companies. If any of the posting is violating copyright and you want us to remove that content. Please contact Here