SELECT ooha.order_number
, ooha.org_id
, ooha.sold_to_org_id bill_cust_account_id
, ooha.ship_to_org_id ship_to_site_use_id
, ooha.invoice_to_org_id bill_to_site_use_id
, hp.party_name "Customer Name"
, hca.account_name
, hcasab.orig_system_reference bill_to_orig_ref
, hpb.status bill_to_status
, bill_loc.address1
|| ', '
|| bill_loc.address2
|| ', '
|| bill_loc.address3
|| ', '
|| bill_loc.city
|| ', '
|| bill_loc.postal_code
|| ', '
|| bill_loc.country
bill_to_address
, hcasas.orig_system_reference ship_to_orig_ref
, hps.status ship_to_status
, ship_loc.address1
|| ', '
|| ship_loc.address2
|| ', '
|| ship_loc.address3
|| ', '
|| ship_loc.city
|| ', '
|| ship_loc.postal_code
|| ', '
|| ship_loc.country
ship_to_address
FROM apps.oe_order_headers_all ooha
, apps.hz_parties hp
, apps.hz_cust_accounts hca
, apps.hz_cust_acct_sites_all hcasab
, apps.hz_cust_acct_sites_all hcasas
, apps.hz_cust_site_uses_all hzsuab
, apps.hz_cust_site_uses_all hzsuas
, apps.hz_party_sites hps
, apps.hz_party_sites hpb
, apps.hz_locations bill_loc
, apps.hz_locations ship_loc
WHERE 1 = 1
AND hp.party_id = hca.party_id
AND hca.cust_account_id = ooha.sold_to_org_id
AND hcasab.cust_account_id = hca.cust_account_id
AND hcasas.cust_account_id = hca.cust_account_id
AND hpb.location_id = bill_loc.location_id
AND hps.location_id = ship_loc.location_id
AND hcasab.party_site_id = hpb.party_site_id
AND hcasas.party_site_id = hps.party_site_id
AND hcasab.cust_acct_site_id = hzsuab.cust_acct_site_id
AND hcasas.cust_acct_site_id = hzsuas.cust_acct_site_id
AND ooha.ship_to_org_id = hzsuas.site_use_id
AND ooha.invoice_to_org_id = hzsuab.site_use_id
AND ooha.order_number = '&Enter_Order_Number';
No comments:
Post a Comment