Saturday, January 29, 2022

Query to find list all geography's without Jurisdiction's in oracle apps eb-tax



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

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...