SELECT * FROM ( SELECT geography_type , geography_element2_code state_code , geography_element3_code county_code , geography_element4_code city_code FROM hz_geographies geography WHERE geography.geography_type='STATE' AND sysdate BETWEEN geography.start_date AND geography.end_date AND geography_element1_code='US' AND NOT EXISTS ( SELECT 1 FROM zx_jurisdictions_b tax_juridiction WHERE tax_juridiction.zone_geography_id=geography.geography_id AND 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.tax=geography.geography_type ) UNION SELECT geography_type , geography_element2_code state_code , geography_element3_code county_code , geography_element4_code city_code FROM hz_geographies geography WHERE geography.geography_type='COUNTY' AND sysdate BETWEEN geography.start_date AND geography.end_date AND geography_element1_code='US' AND NOT EXISTS ( SELECT 1 FROM zx_jurisdictions_b tax_juridiction WHERE tax_juridiction.zone_geography_id=geography.geography_id AND 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.tax=geography.geography_type ) UNION SELECT geography_type , geography_element2_code state_code , geography_element3_code county_code , geography_element4_code city_code FROM hz_geographies geography WHERE geography.geography_type='CITY' AND sysdate BETWEEN geography.start_date AND geography.end_date AND geography_element1_code='US' AND NOT EXISTS ( SELECT 1 FROM zx_jurisdictions_b tax_juridiction WHERE tax_juridiction.zone_geography_id = geography.geography_id AND 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.tax=geography.geography_type ) ) ORDER BY geography_type , state_code , county_code , city_code
Saturday, January 29, 2022
Query to find list all geography's without Jurisdiction's in oracle apps eb-tax
Subscribe to:
Post Comments (Atom)
Drilldown from GL to Inventory Transactions, Inv to gl Link Query R12,Inventory to General ledger links, r12 ,Inv to gl drill down
Drilldown from GL to Inventory Transactions, Inv to gl Link Query R12,Inventory to General ledger links, r12 ,Inv to gl drill down Link bet...
-
Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...
-
Query to Get Profile value at any level SELECT distinct pot.user_profile_option_name Profile, DECODE ( a.profile_option_value, ...
-
Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...
No comments:
Post a Comment