EBS Query : Customer: Query to get Customer Account Level and Site Level Collector

 Query to get Customer Account Level and Site Level Collector

select  

       (select name from hr_operating_units where organization_id = hcas.org_id )  Operating_unit   ,

       hca.account_number customer_number, 
       hca.account_name customer_name,
       hcsu.location site_number,
      (select name from ar_collectors where collector_id = hcp.collector_id) account_level_collector ,

      (select name from ar_collectors where collector_id = hcp_site.collector_id) site_level_collector ,

       hl.country, 
       hl.address1, 
       hl.address2,

       hl.city ,
       hl.postal_code ,

       hl.state ,

       (select territory_short_name  from FND_TERRITORIES_tl where territory_code =  hl.country  and language = 'US' ) country 

       --hcsu.site_use_code ,     
        --  , hca.cust_account_id , hcsu.site_use_id, hcp.status, hcp_site.status
  from hz_cust_accounts_all hca, 

       hz_cust_acct_sites_all hcas, 

       hz_cust_site_uses_all hcsu, 
       hz_party_sites hps, 
       hz_locations hl, 
       hz_customer_profiles hcp, 
       hz_customer_profiles hcp_site

where hca.cust_account_id = hcas.cust_account_id

   and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
   and hcas.party_site_id = hps.party_site_id
   and hps.location_id = hl.location_id
   and hca.cust_account_id = hcp.cust_account_id
   and hcp.site_use_id is null 
   and hca.cust_account_id = hcp_site.cust_account_id
   and hcsu.site_use_id = nvl(hcp_site.site_use_id,0)
   and hca.status = 'A'
   and hcas.status = 'A'

   and hcsu.status = 'A'

   and hcsu.site_use_code = 'BILL_TO'
   and hcas.org_id in ('971')
   --and hca.account_number = '33333333'
order by hca.account_number ,
         hcsu.location