Tuesday 13 March 2012

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 there 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_flagFROM   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

No comments:

Post a Comment