Useful queries/code/scripts in Oracle Apps Order Management

Following are some of the most frequently used queries in oracle apps order management:

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