Wednesday, December 15, 2021

Query to list Jurisdiction's for which Tax Rates Has been defined in Oracle APPS

SELECT tax
      ,tax_jurisdiction_code
  ,geography_element2_code state_code
  ,geography_element3_code county_code
  ,geography_element4_code city_code
  FROM zx_jurisdictions_b tax_juridiction
      ,hz_geographies geography
 WHERE tax_juridiction.tax_regime_code = '<<TAX_REGIME_CODE>>'
   AND SYSDATE BETWEEN tax_juridiction.effective_from AND NVL(tax_juridiction.effective_to,'31-DEC-4999')
   AND tax_juridiction.zone_geography_id=geography.geography_id
   AND tax_juridiction.tax = geography.geography_type
   AND SYSDATE BETWEEN geography.start_date AND geography.end_date
   AND NOT EXISTS (SELECT 1 
                     FROM zx_rates_b zr
                    WHERE zr.tax_regime_code='<<TAX_REGIME_CODE>>'
                      AND zr.tax_jurisdiction_code = tax_juridiction.tax_jurisdiction_code
  )
ORDER BY tax
        ,tax_jurisdiction_code
,geography_element2_code 
,geography_element3_code
,geography_element4_code

2 comments:

Query to get Parent and Child Accounts in Oracle APPS R12

SELECT ffv1.flex_value parent_account       ,ffvt1.description parent_account_desc   ,ffv2.flex_value child_account   ,ffvt2.description...