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)
Types of animations
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 ...
-
Approval Management Engine FAQ 1. AME – What is it? i. AME is a simple to use Rules Engine for Defining Approval Policy ii. AME is ...
-
Register Custom Tables in Oracle Apps Say you have a custom table called “ERPS_EMPL...
-
OU & ORG ID & ORG CODE & ORG NAME ================================== select OPERATING_UNIT,ORGANIZATION_ID,ORGANIZATION_CODE...
No comments:
Post a Comment