Customer Related Information

SELECT ooh.order_number,
hp_bill.party_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,
ooh.transactional_curr_code currency_code,
mp.organization_code,
ooh.fob_point_code,
ooh.freight_terms_code,
ooh.cust_po_number
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 = NVL (:p_header_id, ooh.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