Showing posts with label EBTAX. Show all posts

EBTax: Query to list TAX_RATE_CODE based on Operating Unit in R12

In Oracle R12, we have E-Business Tax module to maintain the Tax Details.

Every TAX_RATE_CODE is attached to a TAX_REGIME and in-turn a TAX_REGIME is tagged to one or more Operating Units.

Below query is used to list down the TAX_RATE_CODES attached to a Operating Unit.
SELECT hou.organization_id,
       hou.set_of_books_id,
       hou.default_legal_context_id,
       hou.short_code,
       hou.NAME,
       zru.first_pty_org_id party_tax_profile_id,
       zru.tax_regime_id,
       zru.tax_regime_code,
       zxr.tax,
       zxr.tax_status_code,
       zxr.tax_rate_code,
       zxr.tax_jurisdiction_code,
       zxr.rate_type_code,
       zxr.recovery_type_code,
       zxr.percentage_rate,
       zxr.tax_rate_id,
       zxr.effective_from,
       zxr.effective_to,
       zxr.active_flag,
       zxr.attribute3,
       zxr.offset_tax,
       zxr.offset_status_code,
       zxr.offset_tax_rate_code
FROM zx_party_tax_profile      ptp,
       zx_subscription_details   zsd,
       hr_operating_units        hou,
       zx_regimes_usages         zru,
       zx_rates_vl               zxr
WHERE zxr.tax_regime_code         = zru.tax_regime_code
   AND ptp.party_type_code         = 'OU'
   AND ptp.party_id                = hou.organization_id
   AND zru.first_pty_org_id        = ptp.party_tax_profile_id
   AND zru.first_pty_org_id        = zsd.first_pty_org_id
   AND zsd.tax_regime_code         = ZRU.TAX_REGIME_CODE
   AND zsd.parent_first_pty_org_id = -99
   AND SYSDATE BETWEEN zsd.effective_from AND NVL(zsd.effective_to,SYSDATE);

EBTax : AutoInvoice and Tax Calculation

AutoInvoice is used to generate invoices from a wide variety of sources. Oracle Applications such as OKS (Contracts), Projects, Order Management and AGIS (Advanced Global Intercompany) are just a few sources that use AutoInvoice to generate invoices.

Taxes can be generated in one of two ways.

Transactions can be created with appropriate attributes defined such as bill_to, ship to, warehouse_id and AutoInvoice can call E-Business Tax to calculate taxes using your tax rules.
Transactions can be passed into the interface tables with a pre-calculated tax line.

Oracle Applications that calculate tax typically generate an estimated tax in their respective user interfaces (such as Order Management) but do not pass this tax line to AR.

Taxes are recalculated at the time of invoicing as this is the point where the tax liability is incurred.

If AutoInvoice Ends In Error:

All AutoInvoice Tax errors in R12 are prefaced with the text below.
The error messages are what you find in the tax debug log when captured as previously instructed.

raaebt()+ Current system time is 08-SEP-2008 12:57:54
ERROR: from eBusiness Tax - return code = 2 Current system time is 08-SEP-2008 12:57:59
Please review the plsql debug log for additional details. Current system time is 08-SEP-2008 12:57:59
Error calling raaebt()
Error calling raapic()
raarrt2()+ Current system time is 08-SEP-2008 12:57:59
Commit after cleanup - Current system time is 08-SEP-2008 12:58:00
...
arp_etax_util.calculate_tax_int()+
ZX_API_PUB.import_document_with_tax returns failure


Error Message	Cause	Action
The system cannot determine the default tax rate for tax and tax status . Please contact your tax manager either to specify a default tax rate code for this tax status and date <Date> or to define appropriate rate determination rules.	Numerous	Refer to Note 1086129.1 for fixes for this error.

If AutoInvoice Ends with a Message in the Exception Report:


Exception Message	Cause	Action	Reference
Invalid tax exempt flag (TAX_EXEMPT_FLAG)	In release 12.0, when interfacing an order or RMA from OM to AR with the tax handling set to O for Override, the record is being rejected by AutoInvoice import with the message in the log file shown below: Invalid tax exempt flag (TAX_EXEMPT_FLAG). If the system option Allow Exemptions = No then valid values are S or R, otherwise the valid values are S, R or E (O). This issue was caused by OM allowing the tax handling to be set to Override. The value O was obsoleted in Release 12.	Refer to Note 788047.1 to remove the Override option from the OM list of values. Alternately, you can use these steps: Login as Application Developer (N) Application > Lookup > Application Object Library > ZX_EXEMPTION_CONTROL Delete the value named Override Save As part of the fix for 8626124 this option was officially removed. Any patch containing the fixed file OEXOEORD.fmb 120.177.12000000.67 or higher will also contain this fix and will eliminate the need for the above mentioned manual steps.	Note 1086586.1 contains a full listing of all known causes for this exception
This Tax Rate is Not Active This Tax Rate is Not Active on the Transaction Date	Bug 7504455	Fixed File/Recommended Patch R12 120.73.12000000.16 /Patch 9151034 R12.1 120.80.12010000.14 Baseline 12.1 code. Also, Oracle E-Business Tax - Tax Configuration - Tax Rate - Check the Effective From and Effective To dates and ensure that the provided Tax Rate is active on the transaction date.	Note 745968.1

EBTAX : E-Business Tax Components

Oracle E-Business Tax provides a single point solution for managing your transaction-based tax requirements.

E-Business Tax uniformly delivers tax services to all E-Business Suite business flows through one application interface.

Here are Oracle E-Business Tax Key Components

Component	Function
Tax Content Services	This component addresses the creation, maintenance, and management of tax content.
Tax Content Repository	This component contains master and reference setup data. Data created via Tax Content Services is stored in the Tax Content Repository.
Tax Service Request Manager	This component manages the access to all tax data and services, including: Integration with E-Business Tax services. Integration with tax service providers. Standard interface for E-Business Suite applications to add tax services to their business process flows.
Tax Determination Services	This component consists of two subcomponents: Tax Determination Management - Manages the requirements and processes around automatic tax calculation based on transaction details and tax setup information. Tax Rules Management - Manages the setup and definition of tax rules.
Tax Record Repository	This component contains the key attributes necessary to record a tax event. A single tax transaction can include over 600 key attributes.
Tax Administration Services	This component manages the accounting for all tax transactions.
Tax Reporting Ledger	This component manages access to the Tax Content and Tax Record Repositories for tax reporting purposes.

EBTAX : QUERY FOR SETUP TABLES

If you need to troubleshoot ebTax setup , you should use the Following query . These are retrived from the main E-Business tax tables that will contain the setup information that will help support in troubleshooting E-Business tax information.

In order to get access you need to provide the tax regime code when prompted.

a. Tax Regimes: ZX_REGIMES_B

SELECT *

FROM zx_regimes_b

WHERE tax_regime_code = '&tax_regime_code';

b. Taxes: ZX_TAXES_B

SELECT *

FROM zx_taxes_b

WHERE DECODE('&tax_name',null,'xxx',tax) = nvl('&tax_name','xxx')