SQL

No Ship to on intercompany invoices – solution

This posting is a direct copy from the Oracle website based on note 1048431.6

For years we have been using a work around but Oracle have now provided the following solution to help with the missing ship-to information badly needed to correctly determine the tax.

Reference INCIAR – Ship-To/Shipto Address Does Not Appear On Intercompany AR Invoices (INCIAR) (Doc ID 1048431.6)

GOAL

The “ship to” address is not appearing on intercompany AR invoices.

The “ship to” address for Intercompany AR Invoices is not populated after the AR Autoinvoice process is completed.

This document includes all version of Oracle E-Business Suite 11i and Release 12.

SOLUTION

Continue Reading

Oracle R12 – US Geography data clean up

This is not an approved Oracle approach but if you need to clean up bad data from an 11i upgrade where you have all sorts of States, Counties or Cities then the flowing SQL could help you.

 

DELETE

hz_relationships

where subject_id IN (

select geography_id

from hz_geographies where created_by_module = ‘EBTAX_MIGRATION’

)

and subject_table_name = ‘HZ_GEOGRAPHIES’

;

 

DELETE

hz_relationships

where object_id IN (

select geography_id

from hz_geographies where created_by_module = ‘EBTAX_MIGRATION’

)

and object_table_name = ‘HZ_GEOGRAPHIES’

;

 

The last step must be the delete of the HZ_GEOGRAPHIES as it is used in the queries above

 

DELETE hz_geographies where created_by_module = ‘EBTAX_MIGRATION’

;

Oracle R12 US Geographies data cleanse before new load

If you have upgraded from 11i to R12 and have the misfortune to have brought across all the bad geographies for states through to Zip Code then the following SQL will allow you to remove this bad data to clear the way for a 3rd party data file such as one from TTR

 

 

DELETE

hz_relationships

where object_id IN (

select geography_id

from hz_geographies where created_by_module = ‘EBTAX_MIGRATION’)

and object_table_name = ‘HZ_GEOGRAPHIES’;

 

The last step must be the delete of the HZ_GEOGRAPHIES as it is used in the queries above

 

DELETE hz_geographies where created_by_module = ‘EBTAX_MIGRATION’;

Oracle R12 eBTax adding extra values to belgium or portugal allocation tax boxes – JGZZ_VAT_REPORT_BOXES

Ever need to add to the lsit of values for your ‘Tax Box’ number when setting up allocations? You cannot do it from the tax manager lookups, it must be done through the belgium regional localizations!

First you need to assign the belgium AP localizations responsibility to yourself

go to the ‘Define Lookups’

search for ‘JGZZ_VAT_REPORT_BOXES’

Add your own values

Now you can select the value

JGZZ_VAT_REPORT_BOXES

Oracle R12: How to find a file version


If you want to see a file version but dont want to bother your DBA, the request is visible from a Purcahsing Super user by default;
Run the Diagnostics App Check for e-business Tax
diagnostics apps check
 then in the output file you can see all the file versions for the ZX files

Oracle eBTax: How to find the Tax Reporting Codes linked to your transactions

Tax reporting codes – how to find them?

Oracle provide tax reporting type codes that you can assign to the tax rate, the status, the process result, pretty much anywhere but once the tax has been calculated, how on earth do we find out what tax reporting codes have been assigned or not?

Below is an example of a Tax Reporting Type that we have created with the codes.

Navigation: Tax Manager>Defaults and Controls>Tax Reporting Types

eBusiness Tax Fusion Tax

Below are 2 scripts, the first one is to find the tax reporting type code assigned tot he process result (when a tax rule has selected a tax status) and the second one that is linked to the tax rate itself.

If you’re creating a custom process or using this information in a report then i would suggest that you check the process result first before checking the value linked to the tax rate.

Apologies for the poor SQL but I am more of a functional consultant rather than technical.

These scripts will list all the tax reporting codes linked to the transaction.

Process Result

select  zxli.trx_id, rcvl.reporting_code_name, repco.reporting_code_char_value, reptl.reporting_code_name
from apps.zx_reporting_codes_vl rcvl, apps.zx_report_codes_assoc reas, apps.zx_lines zxli, apps.zx_reporting_codes_b repco, APPS.ZX_REPORTING_CODES_tl reptl
where rcvl.reporting_code_id = reas.reporting_code_id
and zxli.status_result_id = reas.entity_id
–and reas.reporting_type_id = ‘&Tax_reporting_Type_ID’
and repco.reporting_code_id = reptl.reporting_code_id
and repco.reporting_code_id = reas.reporting_code_id
and zxli.trx_id = ‘&Transaction_ID’

Tax Rate

select  zxli.trx_id, rcvl.reporting_code_name, repco.reporting_code_char_value, reptl.reporting_code_name
from apps.zx_reporting_codes_vl rcvl, apps.zx_report_codes_assoc reas, apps.zx_lines zxli, apps.zx_reporting_codes_b repco, APPS.ZX_REPORTING_CODES_tl reptl
where rcvl.reporting_code_id = reas.reporting_code_id
and zxli.tax_rate_id = reas.entity_id
–and reas.reporting_type_id = ‘&Tax_reporting_Type_ID’
and repco.reporting_code_id = reptl.reporting_code_id
and repco.reporting_code_id = reas.reporting_code_id
and zxli.trx_id = ‘&Transaction_ID’

Oracle R12 eBusiness Tax – JG_ZZ Table list

List of the JG_ZZ% tables

Used in Oracle eBTax Tax reporting etc.

AP
JG_ZZ_AP_BALANCES
JG_ZZ_AP_IR_REP_ITF
JG_ZZ_AP_TMP_ACCOUNT
JG_ZZ_AP_TMP_BAL
JG_ZZ_AP_TMP_DETAIL
JG_ZZ_INVOICE_INFO
JG_ZZ_JGZZVSLP_MESSAGES
JG_ZZ_PAY_FORMAT_INFO
JG_ZZ_PAY_SCHED_INFO
JG_ZZ_SYS_FORMATS_ALL_B
JG_ZZ_SYS_FORMATS_ALL_TL
JG_ZZ_VEND_SITE_INFO
AR
JG_ZZ_AR_BALANCES
JG_ZZ_AR_TMP_ACCOUNT
JG_ZZ_AR_TMP_BAL
JG_ZZ_AR_TMP_DETAIL
JG_ZZ_II_INT_RATES_ALL
JG_ZZ_INTEREST_BATCHES_ALL
JG_ZZ_INTEREST_INVOICES_ALL
JG_ZZ_INT_INV_LINES_ALL
JG_ZZ_JGZZCSLP_MESSAGES
GL
JG_ZZ_TA_ACCOUNT_RANGES
JG_ZZ_TA_ALLOCATED_LINES
JG_ZZ_TA_CC_RANGES
JG_ZZ_TA_RULE_LINES
JG_ZZ_TA_RULE_SETS
JG
JG_ZZ_AP_WHT_ITF
JG_ZZ_AR_SRC_TRX_TY_ALL
JG_ZZ_ENTITY_ASSOC
JG_ZZ_FA_REG_ITF
JG_ZZ_GL_ACCT_HIER_GT
JG_ZZ_GL_RECON_TRX
JG_ZZ_GL_UNREC_TRX
JG_ZZ_TA_ACCOUNT_RANGES
JG_ZZ_TA_ALLOCATED_LINES
JG_ZZ_TA_CC_RANGES
JG_ZZ_TA_RULE_LINES
JG_ZZ_TA_RULE_SETS
JG_ZZ_TMP_RPT_HEADERS
JG_ZZ_VAT_ALLOC_RULES
JG_ZZ_VAT_BOX_ALLOCS
JG_ZZ_VAT_BOX_ERRORS
JG_ZZ_VAT_DOC_SEQUENCES
JG_ZZ_VAT_FINAL_REPORTS
JG_ZZ_VAT_REGISTERS_B
JG_ZZ_VAT_REGISTERS_TL
JG_ZZ_VAT_REP_ENTITIES
JG_ZZ_VAT_REP_STATUS
JG_ZZ_VAT_TRX_DETAILS
JG_ZZ_VAT_TRX_GT
JG_ZZ_VAT_TRX_UPG_STG
JG_ZZ_VAT_UPG_STG

Oracle R12 e-Business Tax Tables (ZX schema)

Below is the list of all the tables in the ZX Schema;

SchemaZX

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_DISTCCID_DET_FACTS_GT

ZX_DISTRIBUTION_LINES_GT

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_MRC_GT

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_RECOVERY_TYPES_B

ZX_RECOVERY_TYPES_TL

ZX_REC_NREC_DIST

ZX_REC_NREC_DIST_GT

ZX_REGIMES_B

ZX_REGIMES_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_REV_TRX_HEADERS_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_SUMMARY_TAX_LINES_GT

ZX_TAXES_B

ZX_TAXES_TL

ZX_TAX_DIST_ID_GT

ZX_TAX_PRIORITIES_T

ZX_TAX_RELATIONS_T

ZX_TEST_API_GT

ZX_TRANSACTION

ZX_TRANSACTIONS_GT

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_VALDN_STATUSES_GT

ZX_VALIDATION_ERRORS_GT

Oracle eBTax Taz Zone and Geographies tables

Taken from the Oracle eTRM site

 

Oracle eBTax R12 SQL scripts

Oracle R12 eBTax SQL Queries

Following are some SQL queries to tun to pull Oracle eBTax (Oracle eBusiness Tax) information directly from the tables.

a. Tax Regimes: ZX_REGIMES_B
b. Taxes: ZX_TAXES_B
c. Tax Status: ZX_STATUS_B
d. Tax Rates: ZX_RATES_B
e. Tax Jurisdictions: ZX_JURISDICTIONS_B
f. Tax Rules: ZX_RULES_B

You will most likely need to refine your extracts based on the data you have, whetehr you have migrated data or multiple countries etc.

SELECT *
FROM zx_regimes_b
WHERE tax_regime_code = ‘&tax_regime_code’;
SELECT *
FROM zx_taxes_b
WHERE DECODE(‘&tax_name’,null,’xxx’,tax) = nvl(‘&tax_name’,’xxx’)
AND tax_regime_code = ‘&tax_regime_code’;
SELECT *
FROM zx_status_b
WHERE tax = ‘&tax_name’
AND tax_regime_code = ‘&tax_regime_code’;
SELECT *
FROM zx_rates_b
WHERE tax = ‘&tax_name’
AND tax_regime_code = ‘&tax_regime_code’;
SELECT *
FROM zx_jurisdictions_b
WHERE DECODE(‘&tax_name’,null,’xxx’,tax) = nvl(‘&tax_name’,’xxx’)
AND tax_regime_code = ‘&tax_regime_code’;
SELECT *
FROM zx_rules_b
WHERE tax = ‘&tax_name’
AND tax_regime_code = ‘&tax_regime_code’;
===================================================

— TAX DETERMINING FACTORS–

 Select
dftt.DET_FACTOR_TEMPL_NAME,
dft.DETERMINING_FACTOR_CLASS_CODE,
dft.DETERMINING_FACTOR_CQ_CODE,
dft.DETERMINING_FACTOR_CODE,
dft.REQUIRED_FLAG–,
from zx_det_factor_templ_dtl dft, zx_det_factor_templ_tl dftt
WHERE dft.DET_FACTOR_TEMPL_ID = dftt.DET_FACTOR_TEMPL_ID
===================================================

— TAX CONDITIONS —

Select
zxc.CONDITION_GROUP_CODE,
zxcg.DET_FACTOR_TEMPL_CODE,
zxc.DETERMINING_FACTOR_CLASS_CODE,
zxc.DETERMINING_FACTOR_CODE,
zxc.DETERMINING_FACTOR_CQ_CODE,
zxc.OPERATOR_CODE,
zxc.value_low
from zx_conditions zxc, zx_condition_groups_b zxcg
where OPERATOR_CODE <> ‘Y’
and zxc.CONDITION_GROUP_CODE = zxcg.CONDITION_GROUP_CODE
AND ZXC.IGNORE_FLAG = ‘N’
order by zxcg.det_factor_templ_code, zxc.CONDITION_GROUP_CODE, zxc.condition_group_code, zxc.determining_factor_class_code
===================================================

— EBTAX TRANSACTION TABLES —

Following are the main E-Business tax tables that will contain the transaction information that will have the tax details after tax is calculated.
a. ZX_LINES: This table will have the tax lines for associated with PO/Release schedules.
TRX_ID: Transaction ID. This is linked to the
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction Line ID. This is linked to the
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
b. ZX_REC_NREC_DIST: This table will have the tax distributions for associated with PO/Release distributions.
TRX_ID: Transaction ID. This is linked to the
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction Line ID. This is linked to the
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID
TRX_LINE_DIST_ID: Transaction Line Distribution ID. This is linked to the
PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
RECOVERABLE_FLAG: Recoverable Flag. If the distribution is recoverable then the flag will be set to Y and there will be values in the RECOVERY_TYPE_CODE and RECOVERY_RATE_CODE.
c. PO_REQ_DISTRIBUTIONS_ALL: This table will have the tax distributions for associated with Requisition distribution.
RECOVERABLE_TAX: Recoverable tax amount
NONRECOVERABLE_TAX: Non Recoverable tax amount
d. ZX_LINES_DET_FACTORS: This table holds all the information of the tax line transaction for both the requisitions as well as the purchase orders/releases.
TRX_ID: Transaction ID. This is linked to the
PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID /
PO_HEADERS_ALL.PO_HEADER_ID
TRX_LINE_ID: Transaction Line ID. This is linked to the
PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID /
PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID

=============================================================

— SQL FOR PARTY FISCAL CLASSIFICATION CODE —

SELECT HPP.PARTY_NAME,HP.PARTY_SITE_NAME ,HCA.*
    FROM ZX_PARTY_TAX_PROFILE ZP
        ,HZ_CODE_ASSIGNMENTS HCA
        ,HZ_PARTY_SITES HP
        ,HZ_PARTIES HPP
    WHERE ZP.PARTY_TAX_PROFILE_ID = HCA.OWNER_TABLE_ID
    –AND ZP.PARTY_ID = :PARTY_ID
    AND HCA.OWNER_TABLE_NAME = ‘ZX_PARTY_TAX_PROFILE’
    AND HP.PARTY_SITE_ID = ZP.PARTY_ID
    AND HPP.PARTY_ID= HP.PARTY_ID
    AND HCA.CLASS_CODE IS NOT NULL
    ORDER BY ZP.LAST_UPDATE_DATE DESC
   
   
SELECT   HP.PARTY_ID, HP.PARTY_NAME, HPS.PARTY_SITE_ID, HPS.PARTY_SITE_NAME, ZP.PARTY_TAX_PROFILE_ID
FROM     ZX_PARTY_TAX_PROFILE ZP,
         HZ_PARTY_SITES HPS,
         HZ_PARTIES HP,
         HZ_CUST_ACCOUNTS_ALL CA
   WHERE HP.PARTY_ID = HPS.PARTY_ID
     AND HP.PARTY_ID = CA.PARTY_ID
     AND HPS.PARTY_SITE_ID = ZP.PARTY_ID
     AND CA.CUSTOMER_CLASS_CODE = ‘WEB CUSTOMER’
     AND UPPER(HP.PARTY_NAME) LIKE ‘CAROLE%FINCK%’
     AND EXISTS (
            SELECT 1
              FROM HZ_CODE_ASSIGNMENTS HCA
             WHERE HCA.OWNER_TABLE_ID = ZP.PARTY_TAX_PROFILE_ID
               AND HCA.OWNER_TABLE_NAME = ‘ZX_PARTY_TAX_PROFILE’
               AND HCA.CLASS_CODE IS NOT NULL)
ORDER BY ZP.LAST_UPDATE_DATE DESC;
=================================================================

— BELOW QUERY  RETRIEVES CUSTOMER ADDRESSES THAT DOESNT HAVE ANY GEOGRAPHY REFERENCE —

SELECT    HCA.ACCOUNT_NUMBER
              ,HCA.ACCOUNT_NAME
              ,HCS_SHIP.SITE_USE_CODE
              ,HL_SHIP.ADDRESS1 ADDRESS
              ,HL_SHIP.STATE STATE
              ,HL_SHIP.COUNTY COUNTY
              ,HL_SHIP.CITY 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.ACCOUNT_NUMBER=’10001′
           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 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)

=================================================================

— BELOW SQL QUERY RETRIEVES LIST OF JURISDICTIONS’ FOR WHICH TAX RATES HAS BEEN DEFINED —

SELECT  TAX,
        TAX_JURISDICTION_CODE,
        GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
        GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
        GEOGRAPHY_ELEMENT4_CODE CITY_CODE
FROM ZX_JURISDICTIONS_B ZJ,
     HZ_GEOGRAPHIES HG
WHERE
    ZJ.TAX_REGIME_CODE=’US_SALE_AND_USE_TAX’
    AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,’31-DEC-4999′)
    AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
    AND ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
    AND ZJ.TAX=HG.GEOGRAPHY_TYPE
    AND NOT EXISTS (SELECT 1 FROM ZX_RATES_B ZR
                    WHERE
                        ZR.TAX_REGIME_CODE=’US_SALE_AND_USE_TAX’
                        AND ZR.TAX_JURISDICTION_CODE=ZJ.TAX_JURISDICTION_CODE)
ORDER BY TAX,
        TAX_JURISDICTION_CODE,
        GEOGRAPHY_ELEMENT2_CODE ,
        GEOGRAPHY_ELEMENT3_CODE,
        GEOGRAPHY_ELEMENT4_CODE
========================================================================

— BELOW QUERY RETRIEVES LIST OF GEOGRAPHY’S WITHOUT JURISDICTIONS–

SELECT * FROM
(SELECT  GEOGRAPHY_TYPE,
        GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
        GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
        GEOGRAPHY_ELEMENT4_CODE CITY_CODE
    FROM
        HZ_GEOGRAPHIES HG
    WHERE HG.GEOGRAPHY_TYPE=’STATE’
        AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
        AND GEOGRAPHY_ELEMENT1_CODE=’US’
        AND NOT EXISTS (SELECT 1 FROM ZX_JURISDICTIONS_B ZJ
                        WHERE ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
                              AND ZJ.TAX_REGIME_CODE=’US_SALE_AND_USE_TAX’
                              AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,’31-DEC-4999′)
                              AND ZJ.TAX=HG.GEOGRAPHY_TYPE)
UNION
SELECT  GEOGRAPHY_TYPE,
        GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
        GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
        GEOGRAPHY_ELEMENT4_CODE CITY_CODE
    FROM
        HZ_GEOGRAPHIES HG
    WHERE HG.GEOGRAPHY_TYPE=’COUNTY’
        AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
        AND GEOGRAPHY_ELEMENT1_CODE=’US’
        AND NOT EXISTS (SELECT 1 FROM ZX_JURISDICTIONS_B ZJ
                        WHERE ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
                                AND ZJ.TAX_REGIME_CODE=’US_SALE_AND_USE_TAX’
                              AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,’31-DEC-4999′)
                              AND ZJ.TAX=HG.GEOGRAPHY_TYPE)
UNION
SELECT  GEOGRAPHY_TYPE,
        GEOGRAPHY_ELEMENT2_CODE STATE_CODE,
        GEOGRAPHY_ELEMENT3_CODE COUNTY_CODE,
        GEOGRAPHY_ELEMENT4_CODE CITY_CODE
    FROM
        HZ_GEOGRAPHIES HG
    WHERE HG.GEOGRAPHY_TYPE=’CITY’
        AND SYSDATE BETWEEN HG.START_DATE AND HG.END_DATE
        AND GEOGRAPHY_ELEMENT1_CODE=’US’
        AND NOT EXISTS (SELECT 1 FROM ZX_JURISDICTIONS_B ZJ
                        WHERE ZJ.ZONE_GEOGRAPHY_ID=HG.GEOGRAPHY_ID
                              AND ZJ.TAX_REGIME_CODE=’_US_SALE_AND_USE_TAX’
                              AND SYSDATE BETWEEN ZJ.EFFECTIVE_FROM AND NVL(ZJ.EFFECTIVE_TO,’31-DEC-4999′)
                              AND ZJ.TAX=HG.GEOGRAPHY_TYPE))
ORDER BY GEOGRAPHY_TYPE,STATE_CODE,
        COUNTY_CODE,
        CITY_CODE
===================================================================

— TAX RULES AND CONDITIONS —

 
SELECT tax_regime_code
        ,tax
        ,DECODE(
           rul.service_type_code
          ,’DET_TAX_STATUS’
          ,’Determine Tax Status’
          ,’DET_RECOVERY_RATE’
          ,’Determine Tax Rate’
          ,’DET_APPLICABLE_TAXES’
          ,’Determine Applicability’
          ,’DET_PLACE_OF_SUPPLY’
          ,’Determine Place of supply’
          ,’DET_TAX_RATE’
          ,’Determine Tax Rate’
         )
           rule
        ,rul.priority
        ,det_factor_templ_code factor_set
        ,res.priority
        ,condition_group_code
        ,alphanumeric_result
        ,NVL(ou.name, ‘Global Configuration Owner’) owner
    FROM zx.zx_rules_b rul
        ,zx.zx_process_results res
        ,zx.zx_party_tax_profile pp
        ,hr_operating_units ou
   WHERE     rul.tax_rule_id = res.tax_rule_id
         AND rul.content_owner_id = pp.party_tax_profile_id
         AND pp.party_id = ou.organization_id(+)
ORDER BY rul.tax_regime_code
        ,rul.tax
        ,rul.service_type_code
        ,rul.priority
        ,res.priority
===================================================================

–SUPPLIER TAX REGISTRATION CREATION–

Use the below script to create Tax Registrations for suppliers – if you have defined any tax rule based on Tax Registrations

DECLARE X_RETURN_STATUS VARCHAR2(1);
BEGIN

     ZX_REGISTRATIONS_PKG.INSERT_ROW (       P_REQUEST_ID => NULL
                                            ,P_ATTRIBUTE1 => NULL
                                            ,P_ATTRIBUTE2 => NULL
                                            ,P_ATTRIBUTE3 => NULL
                                            ,P_ATTRIBUTE4 => NULL
                                            ,P_ATTRIBUTE5 => NULL
                                            ,P_ATTRIBUTE6 => NULL
                                            ,P_VALIDATION_RULE => NULL
                                            ,P_ROUNDING_RULE_CODE => ‘UP’
                                            ,P_TAX_JURISDICTION_CODE => NULL
                                            ,P_SELF_ASSESS_FLAG => ‘Y’
                                            ,P_REGISTRATION_STATUS_CODE => ‘REGISTERED’
                                            ,P_REGISTRATION_SOURCE_CODE => ‘IMPLICIT’
                                            ,P_REGISTRATION_REASON_CODE => NULL
                                            ,P_TAX => NULL
                                            ,P_TAX_REGIME_CODE => ‘DAR’
                                            ,P_INCLUSIVE_TAX_FLAG => ‘N’
                                            ,P_EFFECTIVE_FROM => TO_DATE(’01-DEC-2007′,’DD-MON-YYYY’)
                                            ,P_EFFECTIVE_TO => NULL
                                            ,P_REP_PARTY_TAX_NAME => NULL
                                            ,P_DEFAULT_REGISTRATION_FLAG => ‘N’
                                            ,P_BANK_ACCOUNT_NUM => NULL
                                            ,P_RECORD_TYPE_CODE => NULL
                                            ,P_LEGAL_LOCATION_ID => NULL
                                            ,P_TAX_AUTHORITY_ID => NULL
                                            ,P_REP_TAX_AUTHORITY_ID => NULL
                                            ,P_COLL_TAX_AUTHORITY_ID => NULL
                                            ,P_REGISTRATION_TYPE_CODE => NULL
                                            ,P_REGISTRATION_NUMBER => NULL
                                            ,P_PARTY_TAX_PROFILE_ID => 812988
                                            ,P_LEGAL_REGISTRATION_ID => NULL
                                            ,P_BANK_ID => NULL
                                            ,P_BANK_BRANCH_ID => NULL
                                            ,P_ACCOUNT_SITE_ID => NULL
                                            ,P_ATTRIBUTE14 => NULL
                                            ,P_ATTRIBUTE15 => NULL
                                            ,P_ATTRIBUTE_CATEGORY => NULL
                                            ,P_PROGRAM_LOGIN_ID => NULL
                                            ,P_ACCOUNT_ID => NULL
                                            ,P_TAX_CLASSIFICATION_CODE => NULL
                                            ,P_ATTRIBUTE7 => NULL
                                            ,P_ATTRIBUTE8 => NULL
                                            ,P_ATTRIBUTE9 => NULL
                                            ,P_ATTRIBUTE10 => NULL
                                            ,P_ATTRIBUTE11 => NULL
                                            ,P_ATTRIBUTE12 => NULL
                                            ,P_ATTRIBUTE13 => NULL
                                            ,X_RETURN_STATUS => X_RETURN_STATUS
);

DBMS_OUTPUT.PUT_LINE(‘RETURN STATUS :’ ||X_RETURN_STATUS);
COMMIT;
END;
================================================================

–EXCLUDE FREIGHT FROM DISCOUNT–

SELECT  APS.VENDOR_NAME,
             APS.EXCLUDE_FREIGHT_FROM_DISCOUNT VEND_EXCD,
            APSS.VENDOR_SITE_CODE,
            APSS.EXCLUDE_FREIGHT_FROM_DISCOUNT SITE_EXCD
 FROM   APPS.AP_SUPPLIERS APS,
            APPS.AP_SUPPLIER_SITES_ALL APSS
 WHERE    APS.VENDOR_ID = APSS.VENDOR_ID
        AND APS.VENDOR_ID NOT IN (1, 2, 3)
        AND APSS.EXCLUDE_FREIGHT_FROM_DISCOUNT IS NULL
        AND APS.EXCLUDE_FREIGHT_FROM_DISCOUNT  IS NULL
=================================================================

–Tax rates and the accounts associated to them–

 
SELECT rates.tax_regime_code regime
      ,rates.tax tax
      ,rates.tax_status_code status
      ,rates.tax_rate_code tax_rate
      ,rates.percentage_rate rate
      ,rates.default_rec_rate_code rec_rate
      ,rates.offset_tax_rate_code offset_rate
      ,ou.name org
      ,rate_acc.concatenated_segments ar_acc
      ,rec_acc.concatenated_segments ap_acc
  FROM zx.zx_rates_b rates
      ,zx.zx_taxes_b tax
      ,zx.zx_accounts rate_zx_acc
      ,gl_code_combinations_kfv rate_acc
      ,zx.zx_rates_b rec
      ,zx.zx_accounts rec_zx_acc
      ,gl_code_combinations_kfv rec_acc
      ,hr_operating_units ou
 WHERE     1 = 1
       AND rates.tax = tax.tax
       AND rates.default_rec_rate_code = rec.tax_rate_code
       AND rates.rate_type_code = ‘PERCENTAGE’
       AND tax.tax_type_code <> ‘OFFSET’
       AND(rates.effective_to IS NULL
           OR rates.effective_to >= TRUNC(SYSDATE))
       AND rates.active_flag = ‘Y’
       AND rate_zx_acc.tax_account_entity_code(+) = ‘RATES’
       AND rate_zx_acc.tax_account_entity_id(+) = rates.tax_rate_id
       AND rate_zx_acc.tax_account_ccid = rate_acc.code_combination_id(+)
       AND rec_zx_acc.tax_account_entity_code = ‘RATES’
       AND rec_zx_acc.tax_account_entity_id = rec.tax_rate_id
       AND rec_zx_acc.tax_account_ccid = rec_acc.code_combination_id
       AND rate_zx_acc.internal_organization_id = ou.organization_id (+)
       AND rec_zx_acc.internal_organization_id = NVL(rate_zx_acc.internal_organization_id, rec_zx_acc.internal_organization_id)
       AND rates.tax <> ‘DUMMY TAX’
       UNION
SELECT rates.tax_regime_code regime
      ,rates.tax tax
      ,rates.tax_status_code status
      ,rates.tax_rate_code tax_rate
      ,rates.percentage_rate rate
      ,rates.default_rec_rate_code rec_rate
      ,rates.offset_tax_rate_code offset_rate
      ,ou.name org
      ,rate_acc.concatenated_segments ar_acc
      ,NULL ap_acc
  FROM zx.zx_rates_b rates
      ,zx.zx_taxes_b tax
      ,zx.zx_accounts rate_zx_acc
      ,gl_code_combinations_kfv rate_acc
      ,hr_operating_units ou
 WHERE     1 = 1
       AND rates.tax = tax.tax
       AND rates.rate_type_code = ‘PERCENTAGE’
       AND tax.tax_type_code <> ‘OFFSET’
       AND(rates.effective_to IS NULL
           OR rates.effective_to >= TRUNC(SYSDATE))
       AND rates.active_flag = ‘Y’
       AND rate_zx_acc.tax_account_entity_code(+) = ‘RATES’
       AND rate_zx_acc.tax_account_entity_id(+) = rates.tax_rate_id
       AND rate_zx_acc.tax_account_ccid = rate_acc.code_combination_id(+)
       AND rate_zx_acc.internal_organization_id = ou.organization_id (+)
       AND rates.default_rec_rate_code IS NULL
       AND rates.tax <> ‘DUMMY TAX’
     ORDER BY regime
        ,tax
        ,status
        ,tax_rate

zx table names

For a list of all the zx (tax) tables, go to:-

http://etrm.oracle.com/pls/etrm/etrm_pnav.ls_object?c_name=ZX_*&c_owner=ZX&c_type=SEQUENCE&c_status=*