Oracle R12 – EMEA VAT Reporting

Oracle R12 – EMEA Vat Reporting

Introduction:

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.

• Tax Regime to Rate and Conditions need to be correctly set up so the right tax rates are determined.
• Country specific localisations such as allocation rules for Belgium and Portugal or the document sequencing for Italy need to be set up.
• The legal Entities involved need to be set up with a TRN as this is linked to the reporting and a tax calendar.
• Run the allocation process to allocate the transaction based on the tax boxes set up in the allocation rule.

EMEA VAT – Selection Process:

emea vat selection process submit request

When running the ‘EMEA VAT: Selection Process’, the options are;
• Ledger
• Legal Entity
• Balancing Segment Value

emea vat selection processreporting level
The correct value to use if you are on a new install of R12 is the ‘Legal Entity’ Value. The options of Ledger and BSV are provided for backward compatibility with 11i VAT reporting. These options should be used for data migrated from 11i.  So, if you have a new R12 implementations then always use the Legal Entity as the reporting level and if you have upgraded then use the Legal Entity level for newly entered transactions and the Ledger for historical reports. If you upgrade mid-month and have transactions that are both migrated and newly created then use the ‘Ledger’ option. For new R12 implementations using the Legal Entity is recommended.

The flow chart below explains this decision process.

emea vat selection process Reporting level flow chart

EMEA VAT selection process Reporting level flow chart

When the reporting is by legal entity, the reporting will be in Primary Ledger currency, however when the reporting is by Ledger, the reporting will be in the currency of the Ledger including for secondary or reporting ledgers. According to Oracle Support, the options of Ledger or BSV are also internally mapped to Legal Entity.

It is also important to realise that you can run the ‘Selection Process’ with reporting type as LEDGER and then run the final mode only to be able to run the ‘Selection Process’ again but this time as Legal Entity. So only one method should ever be used and once chosen, should be stuck to for that period.

Make sure you choose the correct Tax Registration number because Legal Entities can share a VAT registration number if they are part of the same VAT group.

emea vat selection process tax registration number

And ensure that you also select the ‘Debug Flag’ as yes as this will provide greater detail in the log file and let you know how many transactions have been picked up.

emea vat selection process parameters complete

Oracle documentation states “Note: The Selection process considers corrections or backdated transactions only when the previous period is finally reported. Otherwise it reports only the current period data.” What this means is that if you run the selection process for June but don’t run the final reporting process then any changes made to June after you have submitted your return in June will not be picked up in July. You have to run the Final Reporting process in June to close that Tax period off and then any subsequent changes made in June will be picked up in the July selection process.

EMEA VAT – Final Reporting Process:

If the EMEA VAT Selection Process is designed to be run as many times as  needed until the data is correct, the ‘EMEA VAT: Final Reporting Process’ can only be run once and this is very important to remember.

The intention is that the ‘EMEA VAT: Selection Process’ is to allow you to keep refreshing the Tax Reporting Repository with data that can be corrected until you are happy that the data is in a fit state to be submitted as part of the VAT return. It is at this point, when you do not want to make any more changes, you run the EMEA VAT: Final Reporting Process in the Submit Request window for stamping all the tax transactions as final.

This means that the order that you run your period close, your final reporting and changes made to transactions are very important.

Below are several scenarios that explain how the tax is reported when an invoice is entered, validated and accounted and then a change is made. For all examples, the original invoice is entered so that GB VAT EXEMPT is calculated (EXEMPT) but is later corrected to show GB VAT ZERO (ZERO). This is a change that is possible even if the invoice has been made because the actual tax amount is not changing only the tax rate name used.

For all scenarios, it is assumed that the ‘EMEA VAT: Selection Process’ has previously been run and the ‘EMEA VAT: Final Reporting Process’ is referred to in the diagrams as ‘Final Selection Run for X’.

VAT Date = GL Date

What is also very important is that the ‘EMEA VAT Reporting Entities Setup’ has a VAT Date that is set to ‘GL Date’

emea vat reporting VAT DATE GL

Scenario 1

The first Scenario shows an entry where no changes are made. Tax is determined as EXEMPT and this is how it stays.

January: Invoice entered and tax determined as EXEMPT.

scenario1

Scenario 2

This is a common scenario when an invoice is entered and a change made all within the same month.

January: Invoice entered and tax determined as EXEMPT. Change is then made shortly after to change invoice tax to ZERO.

Only ZERO tax rate is reported however the transaction itself will hold the history of the original EXEMPT tax rate as shown below;

Tax Details showing the original line of EXEMPT has been cancelled.

scenario2

Scenario 3

This scenario shows how the tax is reported when a change to a transaction is made in the following month after the tax has already been reported.

January: Invoice entered and tax determined as EXEMPT. The invoice is accounted. The tax is then reported as EXEMPT.

February: During the period of February, the tax is changed to ZERO from EXEMPT. During this process the invoice has to be re-accounted. The tax reported here is the reversal of the January EXEMPT tax and then the new ZERO tax. In reality, the reversed January invoice cancels out any new entries made by the ZERO rate invoice so all that is changing is the Tax Rate used.

scenario3

Traditionally this change would have been a manual journal entry but using this standard oracle functionality, the journal corrections are automatic. The VAT reporting also shows the correct reversal for the transactions so there is a full audit trail.

Scenario 4

There is always a risk that the Final Reporting Process can be run before it was meant to and before the end of the month. In this scenario we look at a situation where the final reporting was run before the month end was closed and then during the same month some changes were made.

January: Invoice entered and tax determined as EXEMPT. The invoice is accounted. Final Reporting is accidentally run before month end close. Then the invoice is changed to ZERO tax. The tax reported is still EXEMPT because once the final reporting process has been run, it locks the tax calendar down so no more changes can be made. The month end is then closed off.

February: No changes are made in February but when the selection process is run for February, the January changes that were made after January had the final reporting process run are picked up.  The tax reported in February for this transaction are the reversal of the EXEMPT rate and the new ZERO rate. So even if the final reporting is run accidentally, all transactions are always picked up.

scenario4

Scenario 5

In this scenario, the invoice is entered in January but the changes are made under the February GL date because the January month end process has been run. However the January Final Reporting Process has not been run in January yet so the tax calendar for January remains open.

January: Invoice entered and tax determined as EXEMPT. The invoice is accounted and the month end closed but the final reporting process has not taken place for January yet. As the Final Reporting has not been made, no tax for January is reported.

February: In February, the invoice is changed to ZERO tax. The EMEA VAT Selection Process is then re-run for January and the Final Process for January is also run. The tax reported is now ZERO and only reported once with no entries for EXEMPT but the tax is reported under the February Tax Calendar and not the January one. The reason for this is that because the GL period for January has been closed off in a month end process, all tax changes are recorded with the February GL date. Because the tax is being reported against the GL date and this date is in February, then the tax for this transaction is also reported in February and not January. Even the original EXEMPT tax line that does still have a January GL date against it is excluded from our reports because is has a reversal in February which cancels it out.

AP Invoice GL Date

 

VAT Date = Transaction Date

In contrast to use the GL Date as the VAT Date under the ‘EMEA VAT Reporting Entities Setup’, the following scenarios are based on the VAT Date being set to either the ‘Tax Invoice Date’;

VAT DATE is Tax Invoice Date

or the ‘Transaction Date’.

VAT DATE is Transaction Date

Scenario 1

The first Scenario shows an entry where no changes are made. Tax is determined as EXEMPT and this is how it stays.

January: Invoice entered and tax determined as EXEMPT.

scenario1

Scenario 2

This is a common scenario when an invoice is entered and a change made all within the same month.

January: Invoice entered and tax determined as EXEMPT. Change is then made shortly after to change invoice tax to ZERO.

Only ZERO tax rate is reported however the transaction itself will hold the history of the original EXEMPT tax rate as shown below;

Tax Details showing the original line of EXEMPT has been cancelled.

scenario2

Scenario 3

This scenario shows how the tax is reported when a change to a transaction is made in the following month after the tax has already been reported.

January: Invoice entered and tax determined as EXEMPT. The invoice is accounted. The tax is then reported as EXEMPT.

February: During the period of February, the tax is changed to ZERO from EXEMPT. During this process the invoice has to be re-accounted. The tax reported here is the reversal of the January EXEMPT tax and then the new ZERO tax. In reality, the reversed January invoice cancels out any new entries made by the ZERO rate invoice so all that is changing is the Tax Rate used.

scenario3

Traditionally this change would have been a manual journal entry but using this standard oracle functionality, the journal corrections are automatic. The VAT reporting also shows the correct reversal for the transactions so there is a full audit trail.

Scenario 4

There is always a risk that the Final Reporting Process can be run before it was meant to and before the end of the month. In this scenario we look at a situation where the final reporting was run before the month end was closed and then during the same month some changes were made.

January: Invoice entered and tax determined as EXEMPT. The invoice is accounted. Final Reporting is accidentally run before month end close. Then the invoice is changed to ZERO tax. The tax reported is still EXEMPT because once the final reporting process has been run, it locks the tax calendar down so no more changes can be made. The month end is then closed off.

February: No changes are made in February but when the selection process is run for February, the January changes that were made after January had the final reporting process run are picked up.  The tax reported in February for this transaction are the reversal of the EXEMPT rate and the new ZERO rate. So even if the final reporting is run accidentally, all transactions are always picked up.

Scenario 5

In this scenario, the invoice is entered in January but the changes are made under the February GL date because the January month end process has been run. However the January Final Reporting Process has not been run in January yet.

January: Invoice entered and tax determined as EXEMPT. The invoice is accounted and the month end closed but the final reporting process has not taken place for January yet. As the Final Reporting has not been made, no tax for January is reported.

February: In February, the invoice is changed to ZERO tax. The EMEA VAT Selection Process is then re-run for January and the Final Process for January is also run. Unlike Scenario 5 when the VAT date was set as the ‘GL Date’, the tax reported is now ZERO and only reported once with no entries for EXEMPT. The reason for this is that the tax is being reported against the Transaction or Tax date and if the transaction date is in January then providing the final reporting process has not been run for January, then all changes, regardless of the month they are done in, will be reported in the January VAT return

AP Invoiice Entry Transaction Date

The main advantage with the EMEA VAT reporting over the financial tax register which is run on a GL date basis is that you can guarantee that every transaction is going to be picked up and picked up in the correct tax reporting period. So with the above example, if the the earliest reporting period open was July but we entered a transaction with an invoice date in January, then because the tax calendar periods had all been finally reported to June, the next available period is July so all changes for the January invoice would be reported in July. All these changes will be captured in the July VAT report until the Final Reporting has been run for July.

Andrew Bohnet
Follow Us

Andrew Bohnet

Managing Director and Oracle Fusion Tax / eBTax expert at eBiz Answers Ltd
Andrew Bohnet is the current chair for the Oracle Tax Management SIG. He founded eBiz Answers to offer clients complete tax solutions making the most of the rich functionality of the R12 and Fusion tax modules. Whether it is analysis, design, configuration or support, eBiz Answers provide an unparalleled service when it comes to a complete tax solution. Having worked on one of the first Oracle R12 implementations in Europe, Andrew was exposed to the tax module early on and worked closely with Oracle to fix bugs, enhance functionality and present on numerous occasions on the subject of tax and Oracle. When it comes to presenting on eBTax, you wont find a more experienced consultant outside of Oracle.
Andrew Bohnet
Follow Us