AR Customers Basic - Release 11i

SELECT ca.account_number custno

     , ca.attribute_category

     , DECODE (cs.status,  'I', 'Inactive',  'A', 'Active',  'Other') status

     , pa.party_name name

     , cu.site_use_code site_use

     , hps.party_site_number site_num

     , cu.location site_location

     , hl.address1

     , hl.city

     , hl.postal_code

     , hl.country

  FROM ar.hz_cust_site_uses_all cu

     , ar.hz_cust_accounts ca

     , ar.hz_cust_acct_sites_all cs

     , ar.hz_parties pa

     , ar.hz_party_sites hps

     , ar.hz_locations hl

 WHERE ca.cust_account_id = cs.cust_account_id

   AND cu.cust_acct_site_id = cs.cust_acct_site_id

   AND ca.party_id = pa.party_id

   AND pa.party_id = hps.party_id

   AND cs.party_site_id = hps.party_site_id

   AND hps.location_id = hl.location_id

   AND ca.account_number = 123

   AND cu.site_use_code = 'BILL_TO'

   AND ca.customer_type = 'R'