Oracle Order Management Tables

Oracle Order management is one of the most important part of the oracle EBS. The Order Management Application provides many flexible features enabling you to set up your system to begin processing order information. These orders can be created in Oracle Order Management Suite as well as in some other front-desk application. We can integrate these applications with Oracle Order Management for keeping a controlled track of orders and proper business flow. We can create different types of orders using this application can be for inventory stock items or for services.we know tables of OM.

Here i am publishing Order Management - tables and there links.

Order header info

Select * from oe_order_headers_all ooh

where order_number= :p_order_number

Operating unit info

select * from hr_operating_units

where organization_id=oe_order_headers_all.org_id

Order type info

select * from apps.oe_transaction_types_tl

where transaction_type_id=oe_order_headers_all.order_type_id

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

Find 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

Find 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

Find 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

actual address

select * from hz_locations 

where location_id=hz_party_sites.location_id

Sales rep id

select name from apps.ra_salesreps_all salerep  where

salesrep_id = oe_order_headers_all.salesrep_id  and rownum =1

Payment terms

select name from apps.ra_terms_tl

where term_id =oe_order_headers_all.payment_term_id

and language = 'US'

Order source

select name from apps.oe_order_sources

where order_source_id= oe_order_headers_all.order_source_id

and enabled_flag= 'Y'

Order Source Reference

select orig_sys_document_ref from oe_order_headers_all ooh

where order_number='&oracle order number'

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)

Freight terms

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 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)

Ship method

select ship_method_code from wsh.wsh_carrier_services

where ship_method_code = oe_order_headers_all.shipping_method_code

Warehouse Info

select * from org_organization_definitions

where organization_id = oe_order_headers_all.ship_from_org_id

Sales order Lines Details

select * from apps.oe_order_lines_all

where header_id=oe_order_headers_all.header_id

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

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

UOM

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

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

On hand quantities

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

Shipping

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

Material transactions

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