SELECT hou.name Organization_name,
----------------------
--Customer
Information
----------------------
hp.party_id,
hp.party_name "CUSTOMER_NAME",
hca.cust_account_id,
hca.account_number,
hcas.org_id,
---------------------------
--Customer
Site Information
---------------------------
hcas.cust_acct_site_id,
hps.party_site_number,
hcsu.site_use_code,
-----------------------
--Customer
Site Address
-----------------------
hl.address1,
hl.address2,
hl.address3,
hl.address4,
hl.city,
hl.postal_code,
hl.state,
hl.province,
hl.county,
hl.country,
hl.address_style
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts_all hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_locations hl,
hr_operating_units hou
WHERE 1 = 1
AND hp.party_id = hca.party_id
AND hca.cust_account_id = hcas.cust_account_id(+)
AND hps.party_site_id(+) = hcas.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.location_id = hl.location_id(+)
AND hp.party_type = 'ORGANIZATION' -- only ORGANIZATION Party types
AND hp.status = 'A' -- only Active Parties/Customers
AND hps.status = 'A'
AND hcas.org_id = &porg_id
AND hcas.org_id = hou.organization_id
AND hcsu.site_use_code = 'BILL_TO'
-- AND
hp.party_name = 'ABU DHABI ISLAMIC BANK'
ORDER BY hp.party_name,
hca.account_number;
