Script for Sales 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, hz_parties hp, oe_order_headers_all

where  hp.party_id=hca.party_id

and hca.cust_account_id=oe_order_headers_all.sold_to_org_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