Tuesday 17 September 2019

Query to extract the OU wise active customers

SELECT 
hcsa.orig_system_reference "Address Reference"
,hps.party_site_number
,hp.party_id
,hp.party_name "Customer Name"
,hca.account_number "Customer Number"
,hl.location_id
, hl.address1
, hl.address2
, hl.address3
,hl.county
, hl.city
,hl.province
, hl.state
, hl.country
, hl.postal_code
,rt.NAME
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 hcsa
            , apps.hz_cust_site_uses_all hcsu 
            ,apps.ra_terms rt
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    hcsa.org_id= :Org_id
AND    hca.status='A'
AND    hcsu.payment_term_id = rt.term_id(+)
AND    hcsu.site_use_code ='BILL_TO'


2 comments: