Adding Real Time Analytics to your Tax Department’s Indirect Tax Toolkit

eBiz Answers have recently added Real Time Tax Analytics to their indirect tax toolkit,  the application has designed specifically with the tax department in mind.   Real Time analytics is a user friendly web application which will inform the tax department of a transaction with a potential tax issue within seconds of it being entered.   This allows the tax department to identify and correct issues at source and empowering the tax department to be involved in the end to end process.

The ever increasing use of technology by Tax Authorities for tax compliance is directly impacting the inner workings of the tax department.     The VAT landscape is changing rapidly across the globe as Tax Authorities introduce technology to crackdown on tax avoidance and ensure corporate tax transparency.    From South American to Europe,  tax authorities are passing regulation to ensure companies provide accounting data in a standardised format, direct from their ERP applications on a monthly basis.  The tax authorities can then check the file, validate the data and ensure compliance.   Poland has introduced electronic filing with SAF-T files from the 1st July to join Portugal, Luxembourg, Austria and Lithuania who are already using the SAF-T filing format.

Continue Reading

Oracle ebusiness tax – when to use a tax jurisdiction

Linking a tax jurisdiction to a tax rate.

Whether its R12 eTax, eBTax or Oracle Cloud (Fusion) Tax, with Oracle eBusiness Suite, every Tax created under a Tax Regime needs a ‘Tax Jurisdiction’ if it wants to be enabled for transactions. Under the Oracle tax engine, a tax jurisdiction only applies to certain types of taxes.

Continue Reading

R12:How to modify/override Recovery Rate in Invoice Workbench (Doc ID 1588011.1)

We recently tried to apply the process to allow for ad-hoc changes to recovery rates and recovery percentages after the recovery tax had been determined. The Oracle note is ‘R12:How to modify/override Recovery Rate in Invoice Workbench (Doc ID 1588011.1)’ and its almost comical in its approach, click here, do that, count to 10, spin around ten times, touch your toes and then click here again. Ok so its not quite as bad as that but not far off.

We have a client who enters invoices for Danish car leasing which usually commands a 50% recovery rate but instead of outlining the exact amount of recovery tax by line, the supplier have an annoying habit of just saying heres the total VAT and here is your recovery tax without linking the two. We desperately wanted to use the ability to change the amount of recovery tax and whilst we were able to change the recovery tax, the percentage was not possible.

The point of this article however is not a ‘how to’ guide but instead to let you know to approach with caution the Oracle note 1588011.1 and in our opinion use a workaround instead where you split the line amount to accommodate this requirement.

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)


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.


Continue Reading

Oracle R12 eBTax ‘Query has exceeded 200 rows. Potentially more rows exists, please restrict your query’

What should you do if you receive this error message whilst trying to update look  up codes?

‘Query has exceeded 200 rows. Potentially more rows exists, please restrict your query’


To resolve this you need to update the profile value of

FND: View Object Max Fetch Size to equal 2000

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





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 – Withholding Tax on Receivables (AR) Transactions

This article explains how in Oracle R12, you can create an invoice that has the correct total amount but with the lines adjusted down in accordance with the potential to automatically adjust the AR transaction to record the withholding tax.

To do this you will need to create a separate tax regime which takes a % based on the compounded GB VAT tax rate, as shown below.

Oracle R12 Withholding Tax for AR







In the example above you will see the original line amount was for 1000 but had 20% tax applied to it. By withholding 20% of the total invoice you would get 240, meaning that the original line amount of 1000 is now actually only 760.

By modifying the auto-accounting rules you can set the withholding tax line to be the same as the revenue account. In the example below you can see that 1. Is the original tax line and 2. Is the withholding tax line.

Oracle R12 Withholding Tax for AR








Whilst withholding tax is only applied to the AP transaction, it is often required to show what amount of withholding tax is required on the AR invoice that is sent to the customer. With a modification to the way the invoice is formatted, it is possible to automate the creation of the withholding tax line on the AR transaction without actually effecting any of the normal tax calculation.



Oracle e-Business Suite R12 and Chinas Golden VAT system

Golden Tax is one of the Golden Projects initiated by the Chinese government to modernize the information technology of China. The Golden Tax project refers to an integrated nationwide value-added tax (VAT) monitoring system. For more information see our previous blog:

China’s VAT and the ‘Golden Tax system’

Continue Reading

Oracle R12 – EMEA VAT Reporting

Oracle R12 – EMEA Vat Reporting


In order to submit a VAT return or indeed any indirect fiscal tax reports a Tax (VAT) Registration Number (TRN) is needed to submit the reports. Because VAT reporting is based on a TRN number and a TRN can only belong to one legal entity, it cannot span across multiple legal entities or ledgers.

So why do we have the EMEA VAT Reporting functionality when the ability to run the Financial Tax register already exists?

• Report VAT based on the tax registration number associated with the legal establishment.
• Tax calendars can be used meaning that the VAT can be reported monthly, bi-monthly or quarterly even if the GL periods are of a 4-4-5 or 1-31st design.
• We can choose what taxes are reported based on set up linked to the tax itself. So an environmental tax that uses the same rules and conditions are a VAT tax can be excluded from the reporting.
• The intention of the selection process is to allow the data to be extracted for any point in time without it changing as more transactions are being added. The selection process can be run as many times as needed until the Tax Group are happy that the data they have is accurate before freezing these transactions to report them in the VAT returns.
• Once run in Final mode, the transactions are locked down and no more changes can be made for that tax reporting period’s data. This is intentional as it ensures 100% accuracy with the transactions being reported. It also means that if the selected data has been run in final mode then any transactions entered after this, even for the same month are captured in the following Tax period. This means that any and all corrections and new entries done in the existing or prior periods will still automatically be picked up in the open (chronologically first available as Not Closed) tax period.
• For some countries like Italy, Localisations applied allow for separate sequential document numbering control for tax transactions using the VAT registers.
• Marks each transaction reported to the authorities with information identifying the submission period and date and if all the changes to an invoice are made on that invoice then a full audit trail is maintained.
• Retain tax transaction history without affecting the performance of the current tax reporting processes.

Before the EMEA VAT selection Process or related reports can be run, there are several pre-requisites that need to be completed.

Continue Reading

Oracle R12 How to change a paid invoice when you need to make a tax corrections

Changing the tax rate or recovery rate on a paid invoice

First you need to make sure you know the policy of adjusting a paid invoice as many companies do not want to allow this option and by default it should certainly be set to NO. However, we have found that when a user applied the wrong tax code to a transaction and a correction is needed, it is far easier, cleaner and more accurate to change that invoice even if it has been paid.

Changing the original transaction instead of applying a credit note and reentering the invoice has many more benefits. Firstly, the time taken is much less than having to create a credit memo and a new invoice, both of which then have to be paid. Continue Reading

NATO does not have to pay VAT so we dont need a tax solution right? Wrong!

Surely they are registered somewhere for VAT?

It took me a while to get my head around the fact that even though NATO is exempt from VAT or rather VAT is not applicable, they don’t have a VAT registration number nor need to submit any VAT returns. Sure we have set up solutions for clients where they have exemption certificates both for the products and services that they buy and for items and services that they sell but they had a VAT registration number and had to submit a return. We also deal with suppliers and customers who don’t have tax registrations and set their tax profile as ‘NOT REGISTERED’ but this just means we still charge them standard VAT when we sell to them. So what do we do for NATO? Continue Reading

Fully Automating eBTax When Upgrading From 11i to R12 With New e-Business Tax Rules

We will be presenting at Oracle Colaorate 2014 on this topic

Andrew Bohnet

eBiz Answers LTD


We go through the stages of a best practice approach, explaining the global design that is scalable, designed to be future proof yet easy to maintain as well as looking at the migration issues. We examine how one legal entity in multiple countries was setup using both Global and Party specific rules, ensuring the right tax was used each time.
The challenge faced by any company implementing a new ERP solution, whether a single entity or a global conglomerate, is to capture the complex business requirements yet to keep the structure as simple as possible. Creating a highly complex organisation structure is likely to lead to greater data processing needs as well as a more labour intensive maintenance requirements but creating a risk that the solution initially designed to help the company, ultimately leads to issues that end up consuming more resource.
This document is intended to highlight the potential issues if the correct number of operating units are not used.

Risk of Non-Compliance

“The EU VAT system is in a mess. It is a dizzying mix of rates with a huge number of exemptions and special cases across its 28 countries. The VAT gap is now a stunning €100b, which is steadily rising due to fraud resulting from the fact that there is no taxation on EU cross-border purchases by businesses.
At the same time, companies, which effectively act as unpaid tax collectors, are facing a growing compliance burden. While governments agree they should reduce such costs and obligations, many are adding more compliance obligations and implementing disproportional sanctions, as they increasingly rely on VAT to help plug their fiscal gaps. Source: EY T-Magazine June 2012”.
With this in mind and the fact that the Oracle R12 e-Business Tax module allows for a fully automated solution, why do so many companies suffer such poorly designed indirect tax solutions? Companies waste so much time error checking after month end and risk being non-compliant because they don’t correctly stop the wrong determination of the tax rate when the transaction is entered.

Tax in Oracle

Prior to the tax module in R12, tax was setup in 11i on a module by module basis and whilst basic tax grouping/rules did exist, the primary way that a tax rate was determined was to default the tax rate from somewhere, most commonly from the supplier or customer site. This of course had many limitations primarily because there are many factors tat drive a tax so just defaulting one tax rate would often lead to a high failure rate. To compensate the wrong tax rate from being defaulted in, the user was able to override the defaulted tax rate with on of their own choice. Allowing a user with limited tax knowledge choose a tax rate is opening the company up to risk of non-compliance.
With the introduction of the tax module with Oracle R12, instead of setting up the tax rates on a module by module basis and defaulting the tax rates, the R12 tax module allows for a central repository for all the tax related information including the ability to create complex tax rules. These tax rules mean that there is no need at all to ever default a tax rate as the tax engine uses logic to determine the tax rate and users a large selection of determining factors to correctly drive the tax rate.

Upgrading from 11i to R12

Oracle designed the upgrade process from 11i to R12 to work with minimal setups and the tax module was also designed to accommodate this. The problem however is that the 11i tax solution is extremely basic providing a manual tax solution where the tax rate was defaulted and allowing the user to override the value. So whilst the rich functionality of the tax module exists, it is also possible for an upgraded solution to ignore this setup and stick with the manual 11i solution. But why would any company want to stay with an inferior tax solution when the opportunity to move to a fully automated solution exists?
If your solution integrator is on a fixed price, then why would they offer to extend the tax solution which does take a large amount of time to do correctly when they can just give you what you had previously. After all, if you don’t know what the tax module can do then you won’t ask for it.
Tax is complex and not everyone is going to be able to understand the tax law and also are not going to know the tax module well enough to be able to set it up and maximise its functionality. The majority of financial consultants will say they know how to set up tax but very few will actually know how to do it correctly. It is one thing to be able to create a tax rate and condition, it is something completely different to create a tax solution!
Some end customers will also have multiple 3rd party solutions that feed into Oracle and there may be concerns that a new tax solution will affect the tax solution too much. Of course this is not the case but without the correct knowledge of how the tax rules work and how the other modules can receive tax related data, how will the end customer know any different?
With 3rd party tax solutions such as Taxware, Vertex, one source or Avalara touting tax solutions outside of the oracle application, particularly for US tax solutions, the end customers may feel that Oracle is not capable of handling their tax requirements. The interesting point is that for any third party solution to work, it needs to be supplied with the correct information to use to determine the tax rate. If Oracle already has this information then why not use the tax module to determine the rate rather than mapping everything to a 3rdparty solution?
The major issue when doing an upgrade is that the time it takes to set up a well structured tax solution will be measured in days rather than hours and if you are doing an upgrade where you only have a small window for cutover then spending 15 days doing tax configuration is not a feasible options. The ‘eBTax Rapid Install™’ solution will however allow for 30 days of configuration to be done in under 4 hours – well within any cutover period!

Fully Automating Your Tax Solution

Every upgrade should undertake a full review and enhancement of the indirect tax solution with the intention of automating and reducing risk. The best approach is to create new tax ‘Regime to Rates’ for each country and not end dating the old upgraded tax regimes, instead, leave the old upgraded solution in place and instead ensure that the old tax regimes are not assigned to any Legal Entity or operating unit. By doing this, any 3rd party interface using an old tax rate will not fail. New tax rules can also be built around the old tax rates meaning that 3rd party products need not be changed for the upgrade and instead the old tax rates can be integrated into the new tax rules.
Now is also the opportunity for creating new tax rates with a better naming convention than was possible with 11i and designed around one module rather than separate tax rates for AP and AR.
Design the solution to minimise risk by minimising user interaction. The less ability a user has to change a tax rate the less risk there is in calculating the wrong tax rate. The end users are often the least educated when it comes to tax and allowing them to choose the tax rate could potentially lead to millions in fines. The tax logic can be setup to reduce the user interaction and automate the solution as much as possible. In fact, for receivables transactions, the tax should be fully automated because we know the product or service that we are selling, where we are selling it from and who we are selling it to and what exemptions or registrations are involved. It is only a very rare case that a user ever need to override a tax rate in the order to cash process. The purchase to pay process is different however. Whilst automation achieved in the majority of cases, until the invoice comes in from the supplier the tax is never truly known. An item could be exempt, a supplier non-registered or the tax amount out by a penny or two because of a rounding difference. For this reason, some manual intervention needs to be built in but not to the extent of allowing the user to always choose the tax rate.

Oracle 11i to R12 – Upgrade or Re-implement?

It will be sold as the easy, fast and cheapest way to go from Oracle 11i to R12 but is upgrading the right choice?

Unless my client has an extremely well refined 11i ERP solution working without fault with almost no customisation nor any country localisations, I would always say that a re-implementation is the right way forward. Time and time again, when working on upgrade projects, things are always a lot harder than if the project was being implementeded. I liken an 11i to R12 project to a house renovation. Consider 11i to be your house now and R12 to be the super environmentally friendly completely modern house of the future. If you have a very basic house that has been well constructed and has a great layout then making the renovation is straight forward, however, if that house is old, of a poor design and construction that needs all the pipe work and wiring replaced, making it extremely hard to get the underfloor heating in that you wanted, surely it would be better to knock it down and start again? Of course this is where my comparisons end because the costs of knocking down a house are usually the biggest factor as are the costs of any IT project, the 11i to R12 upgrade being one of them. But will the cost be any greater by doing a reimplementation instead of an upgrade? In the long run, with the delays to the project or the late surge in additional resources to fix the issues, update the customisations and ensure that the localisations are working to meet local fiscal policy, the upgrade becomes as costly, if not a higher cost than a clean re-implementation!

I will be putting together a document with all the pitfalls of doing an upgrade over an implementation based on mine and other colleagues experiences. But pleae feel free to comment with issues that you may have faced that support either option.

Oracle eBTax and Operating units

With the introduction of Oracle R12 and the greater emphasis on shared service centres, can you afford not to use multiple Operating units?


The challenged faced by any company implementing a new ERP solution, whether a single entity or a global conglomerate, is to capture the complex business requirements and yet keep the structure as simple as possible. Creating a highly complex organisation structure is likely to lead to greater data processing needs as well as a more labour intensive maintenance requirements. There is also a risk that the solution initially designed to help the company, ultimately leads to issues that end up consuming more resource.

Any solution architect will be considering this when they design the organisation structure deciding the best approach for the number of ledgers, what legal entities will use these ledgers and how many operating units will be needed to capture the data from the sub ledgers such as the Payables or Receivable modules. Continue Reading