Oracle R12 Upgrade and
E-Business Tax
Implementation Resources
About AuthorOracle Applications (ERP) Financials Functional Consultant with over 14 years of Experience
I can be easily reached at Email - [email protected]
Oracle R12 E-Business Tax Experience:
Countries Implemented Multi Country / currency
-International Rollouts
United States US Sales and Use Tax Canada GST PST & HST
EU VAT - UK and Other countries
Latin Tax Engine - Mexico, Brazil, Argentina Expertise on P2P and O2C Side – Evaluations / proposals/ solution Design / implementing complex solutions etc Navigation
Oracle R12 E-Business Tax (EBTax) Implementation Resources
Analyzing Oracle R12 EBTax Requirements
Comparision - Vertex, Taxware and Sabrix for Oracle EBTax
CV040 - EBTax Supplier Conversion Components for P2P Taxes
EBTax Autoinvoice Errors
EBTax Third Party Tax Rate File Upload Solution Design & Process Overview
Latin Tax Engine - Oracle R12 EBTax Mexico VAT Regime to Rate Setup (Oracle Ebiz / E-Business Tax Configuration) Oracle E-Business Tax - Implementation Checklist Oracle E-Business Tax - Importing AP Invoices - Payables Open Invoice Interface
Oracle E-Business Tax - Tax Classification Codes in R12 Oracle E-Business Tax Implementation Scenarios for United States
Oracle E-Business Tax Integration with Taxware
Oracle EBTax - Business Processes in Vertex and Taxware Oracle EBTax BR100 Setup Document
Oracle EBTax Canadian VAT Setup Examples Oracle EBTax Configuration Migration Path Oracle EBTax Files and Documents
Oracle EBTax Sample EU VAT Setups
Oracle EBTax US Sales and Use Tax Setup Examples Oracle R12 E-Business / EBTax Regime to Rate - Inclusive Taxes for Australia VAT
Oracle R12 E-Business / EBTax UK VAT Regime to Rate Setup
Oracle R12 E-Business Tax (EBTax): Considering an Upgrade or New Implementation?
Oracle R12 EBTax SQL
Queries for Functional
Implementers for
Troubleshooting
Contents
1 EBTAX SETUP TABLES 2 EBTAX TRANSACTION TABLES
3 SQL FOR PARTY FISCAL CLASSIFICATION CODE
--4 -- BELOW QUERY RETRIEVES CUSTOMER ADDRESSES THAT DOESNT HAVE ANY G 5 -- BELOW SQL QUERY RETRIEVES LIST OF JURISDICTIONS' FOR WHICH TAX RATE 6 -- BELOW QUERY RETRIEVES LIST OF GEOGRAPHY'S WITHOUT JURISDICTIONS--7 --SUPPLIER TAX REGISTRATION
CREATION--8 --EXCLUDE FREIGHT FROM
DISCOUNT--=========================================================
--EBTAX
SETUP
TABLES
--Following are the main E-Business tax tables that will contain the setup information that will help support in troubleshooting
Oracle R12 E-Business Tax / EBTax Technical Information Oracle R12 E-Business Tax Implementation Presentations
Troubleshoot - Form to Identify Unexpected EB-Tax Errors
Oracle R12 E-Business Tax Integration with Oracle Payables - Presentations
Oracle R12 E-Business Tax UK VAT Regime to Rate Setups Oracle R12 EBTax / E-Business Tax DE VAT / Germany Regime to Rate Setup
Oracle R12 EBTax Implementation Seed Data for EU Non-EU and other Country specific Scenarios Oracle R12 EBTax profile options
Oracle R12 EBTax SQL Queries for Functional Implementers for Troubleshooting
Oracle R12 EBTax UK VAT Setup Examples Oracle R12 Enhancements Cheat Sheet Oracle R12 Financials PreUpgrade Setup Tips Oracle R12 Latin Tax Engine Overview
Oracle R12 Upgrade Enhancements - Financial Modules R12 EB Tax Accounting - Setting Up Tax Accounts R12 EBTax Upgrade Considerations
TCA Architechture in Oracle R12
Troubleshooting why eBTax does Not Calculate in Oracle E-Business Suite Sitemap 0 E-Business tax information. 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 To get a dump of the eTax setups, you can use the following set of queries. Please provide the tax regime code when prompted. If the issue is limited to a tax
then provide the tax name when prompted else please leave it blank. 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'; =========================================
--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
=========================================================
--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; =========================================================