Query to get Customer Name, Number and Address

Below query can be handy to get customer related information.

The query will list Party Name, Number, Customer Number and their Bill To and Ship Addresses.

                     SELECT hp.party_name

     , hp.party_number

     , hca.account_number

     , hca.cust_account_id

     , hp.party_id

     , hps.party_site_id

     , hps.location_id

     , hl.address1

     , hl.address2

     , hl.address3

     , hl.city

     , hl.state

     , hl.country

     , hl.postal_code

     , hcsu.site_use_code

     , hcsu.site_use_id

     , hcsa.bill_to_flag

FROM   hz_parties hp

     , hz_party_sites hps

     , hz_locations hl

     , hz_cust_accounts_all hca

     , hz_cust_acct_sites_all hcsa

     , hz_cust_site_uses_all hcsu

WHERE  hp.party_id = hps.party_id

AND    hps.location_id = hl.location_id

AND    hp.party_id = hca.party_id

AND    hcsa.party_site_id = hps.party_site_id

AND    hcsu.cust_acct_site_id = hcsa.cust_acct_site_id

AND    hca.cust_account_id = hcsa.cust_account_id

AND    hca.account_number = :customer_number