SQL query to get list of Jurisdictions for which Tax Rates has been defined in Oracle APPS R12

SELECT zj.tax

      ,zj.tax_jurisdiction_code

  ,hzg.geography_element2_code state_code

  ,hzg.geography_element3_code county_code

  ,hzg.geography_element4_code city_code

  FROM zx_jurisdictions_b zj

      ,hz_geographies hzg

 WHERE zj.tax_regime_codE='XX_SALE_TAX'

   AND SYSDATE BETWEEN zj.effective_from AND NVL(zj.effective_to,'31-DEC-4999')

   AND SYSDATE BETWEEN hzg.start_date AND hzg.end_date

   AND zj.zone_geography_id=hzg.geography_id

   AND zj.tax=hzg.geography_type

   AND NOT EXISTS (SELECT 1 

                     FROM zx_rates_b zr

                    WHERE zr.tax_regime_code='XX_SALE_TAX'

                      AND zr.tax_jurisdiction_code=zj.tax_jurisdiction_code

   )

ORDER BY zj.tax

        ,zj.tax_jurisdiction_code

    ,hzg.geography_element2_code

,hzg.geography_element3_code

,hzg.geography_element4_code

Query to get Customer Address that doesn't have Geography Reference in Oracle APPS R12

SELECT hca.account_number

      ,hca.account_name

      ,hcs_ship.site_use_code

      ,hl_ship.address1

      ,hl_ship.state

      ,hl_ship.county

      ,hl_ship.city

      ,hl_ship.postal_code

  FROM hz_cust_site_uses_all hcs_ship

      ,hz_cust_acct_sites_all hca_ship

      ,hz_cust_accounts hca

      ,hz_party_sites hps_ship

      ,hz_locations hl_ship

 WHERE hca.cust_account_id=hca_ship.cust_account_id(+)

   AND hcs_ship.cust_acct_site_id(+) = hca_ship.cust_acct_site_id

   AND hca_ship.party_site_id = hps_ship.party_site_id

   AND hps_ship.location_id = hl_ship.location_id

   AND hca.status='A'

   AND hcs_ship.status='A'

   AND hca_ship.status='A'

   AND hl_ship.country='US'

   --AND hca.account_number='1234567890'

   AND NOT EXISTS (SELECT 1 

                     FROM hz_geographies hg

                    WHERE hg.geography_element2_code=hl_ship.state

                      AND UPPER(hl_ship.county)=UPPER(hg.geography_element3_code)

                      AND UPPER(hl_ship.city)=UPPER(hg.geography_element4_code)

                      AND SYSDATE BETWEEN hg.start_date AND hg.end_date

  )

Query to list 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

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

EB-Tax Setup Tables in Oracle APPS

Tax Setup	Table Name
Tax Regimes	ZX_REGIMES_B
Taxes	ZX_TAXES_B
Tax Status	ZX_STATUS_B
Tax Rates	ZX_RATES_B
Tax Jurisdictions	ZX_JURISDICTIONS_B
Tax Rules	ZX_RULES_B

 

EB-Tax Tables in Oracle APPS

TABLE_NAME

ZX_ACCOUNTS

ZX_ACCOUNT_RATES

ZX_ACCT_TX_CLS_DEFS_ALL

ZX_API_CODE_COMBINATIONS

ZX_API_OWNER_STATUSES

ZX_API_REGISTRATIONS

ZX_COMPOUND_ERRORS

ZX_COMPOUND_ERRORS_T

ZX_CONDITIONS

ZX_CONDITION_GROUPS_B

ZX_CONDITION_GROUPS_TL

ZX_CONTENT_CHOICES_TMP

ZX_CONTENT_SOURCES

ZX_DATA_UPLOAD_INTERFACE

ZX_DETAIL_TAX_LINES_GT

ZX_DETERMINING_FACTORS_B

ZX_DET_FACTORS_TL

ZX_DET_FACTOR_TEMPL_B

ZX_DET_FACTOR_TEMPL_DTL

ZX_DET_FACTOR_TEMPL_TL

ZX_ERRORS_GT

ZX_EVENT_CLASSES_B

ZX_EVENT_CLASSES_TL

ZX_EVENT_CLASS_PARAMS

ZX_EVNT_CLS_MAPPINGS

ZX_EVNT_CLS_OPTIONS

ZX_EVNT_CLS_TYPS

ZX_EVNT_TYP_MAPPINGS

ZX_EXCEPTIONS

ZX_EXEMPTIONS

ZX_FC_CODES_B

ZX_FC_CODES_CATEG_ASSOC

ZX_FC_CODES_DENORM_B

ZX_FC_CODES_TL

ZX_FC_COUNTRY_DEFAULTS

ZX_FC_TYPES_B

ZX_FC_TYPES_REG_ASSOC

ZX_FC_TYPES_TL

ZX_FORMULA_B

ZX_FORMULA_DETAILS

ZX_FORMULA_TL

ZX_ID_TCC_MAPPING_ALL

ZX_IMPORT_TAX_LINES_GT

ZX_ITM_DISTRIBUTIONS_GT

ZX_JURISDICTIONS_B

ZX_JURISDICTIONS_GT

ZX_JURISDICTIONS_TL

ZX_LINES

ZX_LINES_DET_FACTORS

ZX_LINES_SUMMARY

ZX_PARAMETERS_B

ZX_PARAMETERS_TL

ZX_PARAM_DETAILS

ZX_PARTY_TAX_PROFILE

ZX_PARTY_TYPES

ZX_PO_REC_DIST

ZX_PROCESS_RESULTS

ZX_PRODUCT_OPTIONS_ALL

ZX_PRVDR_HDR_EXTNS_GT

ZX_PRVDR_LINE_EXTNS_GT

ZX_PTNR_LOCATION_INFO_GT

ZX_PTNR_NEG_LINE_GT

ZX_PTNR_NEG_TAX_LINE_GT

ZX_PURGE_TRANSACTIONS_GT

ZX_RATES_B

ZX_RATES_TL

ZX_REGIMES_USAGES

ZX_REGIME_RELATIONS

ZX_REGISTRATIONS

ZX_REPORTING_CODES_B

ZX_REPORTING_CODES_TL

ZX_REPORTING_TYPES_B

ZX_REPORTING_TYPES_TL

ZX_REPORT_CODES_ASSOC

ZX_REPORT_TYPES_USAGES

ZX_REP_ACTG_EXT_T

ZX_REP_CONTEXT_T

ZX_REP_MATRIX_EXT_T

ZX_REP_TRX_DETAIL_T

ZX_REP_TRX_JX_EXT_T

ZX_REVERSE_DIST_GT

ZX_REVERSE_TRX_LINES_GT

ZX_RULES_B

ZX_RULES_TL

ZX_SERVICE_TYPES

ZX_SIM_CONDITIONS

ZX_SIM_PROCESS_RESULTS

ZX_SIM_PURGE

ZX_SIM_RULES_B

ZX_SIM_RULES_TL

ZX_SIM_RULE_CONDITIONS

ZX_SIM_TRX_DISTS

ZX_SRVC_SBSCRPTN_EXCLS

ZX_SRVC_SUBSCRIPTIONS

ZX_SRVC_TYP_PARAMS

ZX_STATUS_B

ZX_STATUS_TL

ZX_SUBSCRIPTION_DETAILS

ZX_SUBSCRIPTION_OPTIONS

ZX_TAXES_B

ZX_TAXES_TL

ZX_TAX_PRIORITIES_T

ZX_TAX_RELATIONS_T

ZX_TRANSACTION

ZX_TRANSACTION_LINES

ZX_TRANSACTION_LINES_GT

ZX_TRX_HEADERS_GT

ZX_TRX_LINE_APP_REGIMES

ZX_TRX_PRE_PROC_OPTIONS_GT

ZX_TRX_TAX_LINK_GT

ZX_UPDATE_CRITERIA_RESULTS

ZX_VALIDATION_ERRORS_GT

Query to get E-Biz Tax Details in Oracle Apps

 SELECT xe.name entity_name

      ,ledger.name ledger_name

      ,hou.name operating_unit

      ,zxr.tax_regime_code tax_regime_code

      ,zxr.tax tax_code

      ,zxr.inclusive_tax_flag

      ,zxr.tax_status_code tax_status_code

      ,zxr.tax_rate_code tax_rate_code

      ,zxr.tax_jurisdiction_code

      ,zxr.rate_type_code

      ,zxr.percentage_rate

      ,zxr.effective_from rate_effective_from

      ,zxr.effective_to rate_effective_to

      ,acct.tax_account_ccid

      ,gcc.concatenated_segments tax_account

  FROM zx_rates_vl zxr

      ,zx_accounts acct

      ,hr_operating_units hou

      ,gl_ledgers ledger

      ,gl_code_combinations_kfv gcc

      ,xle_entity_profiles xe

 WHERE     1 = 1

       AND acct.tax_account_entity_code = 'RATES'

       AND zxr.active_flag = 'Y'

       AND TRUNC (SYSDATE) BETWEEN TRUNC (zxr.effective_from)

                               AND NVL (TRUNC (zxr.effective_to),

                                        TRUNC (SYSDATE) + 1)

       AND ledger.ledger_id = hou.set_of_books_id

       AND gcc.code_combination_id = acct.tax_account_ccid

       AND hou.organization_id = acct.internal_organization_id

       AND acct.tax_account_entity_id = zxr.tax_rate_id

       AND hou.default_legal_context_id = xe.legal_entity_id

--AND zxr.tax_regime_code = 'XX'

--AND zxr.tax_rate_code = 'XX'

--AND xe.legal_entity_identifier = 'XX12345'

How to Turn off Tax Calculation for an AR Transaction Type

• Do the following to stop the tax calculation for specific transaction type.
• Go to Tax Manager >> Advanced Setup Options >> Tax Determining Factor Sets Click on Create >> Enter name
• Determining Factor Class >> select Transaction Generic Classification
• Determining Factor Name >> Select Transaction Type
• Click Apply.
• Go to Tax Condition Sets and click on create
• Give a name and select the factor set you have just defined
• Click next
• Select the relevant transaction type in the Value From field and apply.
• This completes the factor and condition sets setup.
• Go to Tax Configuration >> Tax Rules Page
• Give Operating Unit as the Configuration Owner and query the regime.
• For the Rule Type "Determine Applicability", click on the "Guided Rule Entry" Button.
• Give a rule name, start date and click next.
• Select the condition set and factor set you have defined and select Result as 'Not Applicable'.
• Then give rule Order and enable the rule and click finish.
• This rule will imply the following
• If Transaction Type = '.......', then the tax is not applicable;
• So if you use this transaction type, the tax should not get calculated.