o2c Queries and Tables , Order management important queries

Following  SQL queries can be very handy if you are performing any transactions related to Order to cash cycle in Oracle applications. 

For Getting the Order header info:
Select * from oe_order_headers_all ooh
where order_number= :p_order_number

For Getting the Operating unit info
select * from hr_operating_units
where organization_id=oe_order_headers_all.org_id

For Getting the Order type info
select * from apps.oe_transaction_types_tl
where transaction_type_id=oe_order_headers_all.order_type_id

For Getting the Price list info
select * from apps.qp_list_headers_tl
where list_header_id=oe_order_headers_all.price_list_id

select * from apps.qp_list_lines
where list_header_id=oe_order_headers_all.price_list_id

For Getting the customer info
select * from hz_cust_accounts hca
where cust_account_id=oe_order_headers_all.sold_to_org_id

select * from apps.hz_parties
where party_id=hz_cust_accounts.party_id

For Getting the Ship to location info
select * from hz_cust_site_uses_all
where site_use_id=oe_order_headers_all.ship_to_org_id

select * from apps.hz_cust_acct_sites_all
where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id

select * from hz_party_sites
where party_site_id=hz_cust_acct_sites_all.party_site_id

For Getting the Bill to location
select * from hz_cust_site_uses_all
where site_use_id=oe_order_headers_all.invoice_to_org_id
         
select * from hz_cust_acct_sites_all
where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id
         
select * from hz_party_sites
where party_site_id=hz_cust_acct_sites_all.party_site_id

For Getting the Actual address
select * from hz_locations 
where location_id=hz_party_sites.location_id

For Getting the Sales rep id
select name from apps.ra_salesreps_all salerep  where
salesrep_id = oe_order_headers_all.salesrep_id  and rownum =1

For Getting the Payment terms details
select name from apps.ra_terms_tl
where term_id =oe_order_headers_all.payment_term_id
and language = 'US'

For Getting the Order source details
select name from apps.oe_order_sources
where order_source_id= oe_order_headers_all.order_source_id
and enabled_flag= 'Y'

For Getting the Order Source Reference details
select orig_sys_document_ref from oe_order_headers_all ooh
where order_number='&oracle order number'

For Getting the FOB Point Code
select lookup_code from ar_lookups
where lookup_type = 'FOB' and enabled_flag = 'Y'
and upper(meaning) = upper(oe_order_headers_all.fob_point_code)

For Getting the Freight terms details
select lookup_code from apps.oe_lookups
where upper (lookup_type) = 'FREIGHT_TERMS'  and enabled_flag = 'Y'
and upper (lookup_code) = upper (oe_order_headers_all.freight_terms_code)

For performing the sales channel code validation
select lookup_code from apps.oe_lookups
where lookup_type = 'SALES_CHANNEL' and enabled_flag = 'Y'
upper(lookup_code) = upper(oe_order_headers_all.sales_channel_code)

For Getting the Ship method
select ship_method_code from wsh.wsh_carrier_services
where ship_method_code = oe_order_headers_all.shipping_method_code

For Getting the Warehouse Info
select * from org_organization_definitions
where organization_id = oe_order_headers_all.ship_from_org_id

For Getting the Sales order Lines Details
select * from apps.oe_order_lines_all
where header_id=oe_order_headers_all.header_id

For Getting the Transactional currency code
select ota.price_list_id, qhb.currency_code
from ont.oe_transaction_types_all ota, qp.qp_list_headers_b qhb
where ota.transaction_type_id = oe_order_headers_all.order_type_id
and ota.price_list_id = qhb.list_header_id(+)
and NVL(qhb.list_type_code, 'PRL') = 'PRL'
and qhb.currency_code =oe_order_headers_all.transactional_curr_code

For Getting the Item info
select * from apps.mtl_system_items_b
where segment1 like oe_order_lines_all.ordered_item
and organization_id=oe_order_lines_all.ship_from_org_id

For Getting the UOM details
select uom_code from inv.mtl_units_of_measure_tl
where upper(uom_code)= upper(oe_order_lines_all.order_quantity_uom)
and language= 'US' and nvl(disable_date, (sysdate + 1)) > sysdate

For performing the Item type code validation
select lookup_code from apps.oe_lookups
where upper(lookup_type) = 'ITEM_TYPE'
and enabled_flag = 'Y'
and upper(lookup_code)= oe_order_lines_all.item_type_code

For Getting the On hand quantities details
select * from apps.mtl_onhand_quantities
where inventory_item_id=oe_order_lines_all.inventory_item_id
and organization_id=oe_order_lines_all.ship_from_org_id

For Getting the Shipping details
select * from wsh_delivery_details
where source_header_id=oe_order_headers_all.header_id

select * from wsh_delivery_assignments
where delivery_detail_id=wsh_delivery_details.delivery_detail_id

select * from wsh_new_deliveries
where delivery_id=wsh_delivery_assignments.delivery_id

select * from wsh_delivery_legs
where delivery_id=wsh_new_deliveries.delivery_id

select * from wsh_trip_stops wts
where stop_id=wsh_delivery_legs.pick_up_stop_id

select * from wsh_trips wt
where trip_id=wsh_trip_stops.trip_id

select * from org_organization_definitions
where organization_id = wsh_new_deliveries.organization_id

For Getting the Material transactions details
select * from mtl_material_transactions
where inventory_item_id=oe_order_lines_all.inventory_item_id
and organization_id=oe_order_lines_all.ship_from_org_id

select * from mtl_transaction_types
where transaction_type_id = mmt.transaction_type_id

select * from apps.mtl_txn_source_types
where transaction_source_type_id= mmt.transaction_source_type_id
mmt = mtl_material_transactions


Join between OMWSHAR Tables SELECT ooh.order_number
              ,ool.line_id
              ,ool.ordered_quantity
              ,ool.shipped_quantity
              ,ool.invoiced_quantity
              ,wdd.delivery_detail_id
              ,wnd.delivery_id
              ,rctl.interface_line_attribute1
              ,rctl.interface_line_attribute3
              ,rctl.interface_line_attribute6
              ,rct.org_id
              ,rct.creation_date
              ,trx_number
              ,rctl.quantity_ordered
              ,rct.interface_header_context
  FROM oe_order_headers_all ooh
             ,oe_order_lines_all ool
             ,wsh_delivery_details wdd
             ,wsh_new_deliveries wnd
             ,wsh_delivery_assignments wda
             ,ra_customer_trx_all rct
             ,ra_customer_trx_lines_all rctl
 WHERE ooh.header_Id=ool.header_id
      AND wdd.source_header_id=ooh.header_id
      AND wdd.delivery_detail_Id=wda.delivery_detail_id
      AND wda.delivery_id=wnd.delivery_id
      AND rctl.interface_line_attribute1=to_char(ooh.order_number)
      AND rctl.interface_line_attribute6=to_char(ool.line_id)
      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)
      AND rctl.customer_trx_id=rct.customer_trx_id
      AND rct.interface_header_context='ORDER ENTRY'


Purchase release concurrent program will transfer the details from OM to PO requisitions interface. The following query will verify the same:

SELECT interface_source_code,
           interface_source_line_id,
           quantity,
           destination_type_code,
           transaction_id,
           process_flag,
           request_id,
           TRUNC (creation_date)
  FROM po_requisitions_interface_all
 WHERE interface_source_code = 'ORDER ENTRY'
   AND interface_source_line_id IN (SELECT drop_ship_source_id
                                      FROM oe_drop_ship_sources
                                     WHERE header_id = &order_hdr_id
                                       AND line_id = &order_line_id);

The following sql is used to review the requisition, sales order, and receipt number. It shows the joins between various tables in Internal Sales order (ISO)

SELECT porh.segment1,
           porl.line_num,
           pord.distribution_num,
           ooh.order_number
           sales_order,
           ool.line_number so_line_num,
           rsh.receipt_num,
           rcv.transaction_type
  FROM oe_order_headers_all ooh,
          po_requisition_headers_all porh,
          po_requisition_lines_all porl,
          po_req_distributions_all pord,
          oe_order_lines_all ool,
          po_system_parameters_all posp,
          rcv_shipment_headers rsh,
          rcv_transactions rcv
 WHERE ooh.order_source_id = posp.order_source_id
   AND porh.org_id = posp.org_id
   AND porh.requisition_header_id = ool.source_document_id
   AND porl.requisition_line_id = ool.source_document_line_id
   AND porh.requisition_header_id = porl.requisition_header_id
   AND porl.requisition_line_id = pord.requisition_line_id
   AND porl.requisition_line_id = rcv.requisition_line_id
   AND pord.distribution_id = rcv.req_distribution_id
   AND rcv.shipment_header_id = rsh.shipment_header_id