Query to list Active Customers and their Sites Information

Shailender Thallam

9 years ago

Here is the query to list Active Customers and their Sites Information

/**********************************************************
 *PURPOSE: To list customers and their sites information  *
 *AUTHOR: Shailender Thallam                              *
 **********************************************************/
 SELECT
  ----------------------
  --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
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
ORDER BY to_number(hp.party_number),
  hp.party_name,
  hca.account_number;

For Further Reading