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

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...