Query to get Customer Name, Number and Address

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