Tuesday, September 9, 2014

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


1 comment:

Shobhit Sharma said...

HI please enable copying.if possible...

Post a Comment

Best Blogger TipsGet Flower Effect