Customer Tax Registration - EBS Query

             SELECT DISTINCT 
hp.party_type,
hp.party_name,
hca.account_name,
hp.party_number,
hps.party_site_number,
hca.account_number,
hcsu.location,
hcsu.site_use_code,
zr.registration_id,
            zr.registration_number,
            zr.registration_status_code,
            zr.tax_regime_code,
            zr.tax,
            zr.attribute1           country_cod,
            zr.party_tax_profile_id,
hca.cust_account_id,
hp.party_id,
hps.party_site_id,
hcsu.site_use_id,
hps.location_id,
hcsa.cust_acct_site_id,
hl.address1,
hl.city,
hl.state,
hl.country,
hl.postal_code,
hcsa.bill_to_flag,
hcsu.org_id,
hcsu.status site_use_status,
hcsa.status acct_site_status,
hps.status party_site_status,
hca.creation_date    account_creation,
hcsa.creation_date    acct_site_creation,
hcsu.creation_date    site_use_creation
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,
zx.zx_party_tax_profile tax_prof,
zx_registrations     zr
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 tax_prof.party_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 tax_prof.party_type_code = 'THIRD_PARTY_SITE'
AND zr.party_tax_profile_id = tax_prof.party_tax_profile_id
AND hp.status = 'A'
AND hca.status = 'A'
--AND hcsa.status = 'A'
--AND hcsu.status = 'A'
          AND location IN('47840945','47840946','142842','142843')--'52042893','50354303','50343674','51957873')
--and hp.party_id=9860;
--and party_number='391490'; 
-- AND hca.account_number='435080'--'24830389';  
ORDER BY 3, 8 DESC;

SELECT zr.creation_date,
      -- zr.created_by,
       hp.party_number,
       hp.party_name,
       hca.account_number,
       hca.account_name,
       hps.party_site_number,
    --   zr.registration_id,
       zr.registration_number,
       zr.registration_status_code,
       zr.tax_regime_code,
       zr.tax,
       (select distinct location FROM 
            hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all  hcsu
WHERE hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.site_use_code='SHIP_TO') ship_to_location ,
       (select distinct location FROM 
            hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all  hcsu
WHERE hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.site_use_code='BILL_TO') bill_to_location,
   zr.attribute1     country_cod,
       zr.party_tax_profile_id,
       zptp.party_id          
  FROM zx_party_tax_profile  zptp,
       zx_registrations      zr,
       hz_party_sites        hps,
       hz_parties            hp,
       hz_cust_accounts      hca
 WHERE     zr.party_tax_profile_id = zptp.party_tax_profile_id
       AND hp.party_id = hps.party_id
       AND hp.party_id = hca.party_id
       AND hps.party_site_id = zptp.party_id
       AND zptp.party_type_code = 'THIRD_PARTY_SITE'
       AND hca.account_number = '50354303';