Query to get the corresponding VAT tax from Customers and Customer Site level

SELECT   hp.party_name customer_name, hca.account_number customer_number,

         hca.tax_code cust_tax_code, hcsua.site_use_code,

            hl.address1

         || ','

         || hl.address2

         || ','

         || hl.address3

         || ','

         || hl.address4

         || ','

         || hl.city

         || ','

         || hl.state

         || ','

         || hl.postal_code

         || ','

         || hl.country cust_site_address,

         hcsua.LOCATION, hcsua.tax_code site_level_tax_code,

         hcsua.org_id site_org_id,

         (SELECT NAME

            FROM hr_operating_units

           WHERE organization_id = hcsua.org_id) site_org

    FROM hz_cust_accounts_all hca,

         hz_parties hp,

         hz_party_sites hps,

         hz_cust_site_uses_all hcsua,

         hz_cust_acct_sites_all hcasa,

         hz_locations hl

   WHERE hp.party_id = hca.party_id

     AND hcasa.cust_account_id = hca.cust_account_id

     AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

     AND hp.party_id = hps.party_id

     AND hps.location_id = hl.location_id

     AND hps.party_site_id = hcasa.party_site_id

     AND hcsua.tax_code IN (

                  SELECT tax_code

                    FROM ar_vat_tax_all

                   WHERE tax_type = 'VAT'

                         AND NVL (end_date, SYSDATE) >= SYSDATE)

ORDER BY party_name