Customers Query

Below is the Query to get the list of Customers, Accounts, Locations and Sites in Oracle application.

SELECT DISTINCT
hp.party_name "Customer Name",
hca.account_number,
hca.status,
hou.name "Operating Unit",
hps.party_site_number,
hps.party_site_name,
hcsu.location,
hcsu.site_use_code,
hcsu.status loc_stat,
hcsu.site_use_id,
hcpc.name profile_name,
hl.address1,
hl.address2,
hl.address3,
hl.city,
hl.state,
hl.postal_code,
hps.identifying_address_flag
FROM
apps.hz_parties hp,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcas,
apps.hz_cust_site_uses_all hcsu,
apps.hz_customer_profiles hcp,
apps.hz_cust_profile_classes hcpc,
apps.hr_operating_units hou
WHERE hp.party_id = hca.party_id
AND hp.party_id = hcp.party_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_id
AND hps.location_id = hl.location_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hca.cust_account_id = hcp.cust_account_id
AND hcp.profile_class_id = hcpc.profile_class_id
AND hcsu.org_id = hou.organization_id;