Query - Get Customer,its Bill To Address and Ship To Address for a Sales Order

SELECT 
  ooh.order_number 
, hps_ship.party_site_number site_number,
, hp_bill.party_name customer_name
, hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10)) 
||hl_ship.address2||Decode(hl_ship.address3,NULL,'', chr(10)) 
||hl_ship.address3||Decode(hl_ship.address4,NULL,'', chr(10)) 
||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr( 10)) 
||hl_ship.city ||Decode(hl_ship.state,NULL,'',',') 
||hl_ship.state ||Decode(hl_ship.postal_code,'',',') 
||hl_ship.postal_code ship_to_address 
, hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10)) 
||hl_bill.address2||Decode(hl_bill.address3,NULL,'', chr(10)) 
||hl_bill.address3||Decode(hl_bill.address4,NULL,'', chr(10)) 
||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr( 10)) 
||hl_bill.city ||Decode(hl_bill.state,NULL,'',',') 
||hl_bill.state ||Decode(hl_bill.postal_code,'',',') 
||hl_bill.postal_code bill_to_address 
FROM oe_order_headers_all ooh 
, hz_cust_site_uses_all hcs_ship 
, hz_cust_acct_sites_all hca_ship 
, hz_party_sites hps_ship 
, hz_parties hp_ship 
, hz_locations hl_ship 
, hz_cust_site_uses_all hcs_bill 
, hz_cust_acct_sites_all hca_bill 
, hz_party_sites hps_bill 
, hz_parties hp_bill 
, hz_locations hl_bill 
, mtl_parameters mp 
WHERE 1 = 1 
AND ooh.order_number = :p_order_number 
AND ooh.ship_to_org_id = hcs_ship.site_use_id 
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id 
AND hca_ship.party_site_id = hps_ship.party_site_id 
AND hps_ship.party_id = hp_ship.party_id 
AND hps_ship.location_id = hl_ship.location_id 
AND ooh.invoice_to_org_id = hcs_bill.site_use_id 
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id 
AND hca_bill.party_site_id = hps_bill.party_site_id 
AND hps_bill.party_id = hp_bill.party_id 
AND hps_bill.location_id = hl_bill.location_id 
AND mp.organization_id(+) = ooh.ship_from_org_id 

Posted in: Oracle Apps Query,Oracle Notebook,Order Management