Archive for March, 2013

Oracle R12. What is the Usage and Purpose of the Customer Taxpayer Id for eBTax?

Metalink Note (this is deailing with AP transactions only)

From Metalink, article ‘

The issue has been analyzed on Bug.8418407:  R12 CUSTOMER TAXPAYER ID FIELD ON INVOICE WORKBENCH, USAGE AND PURPOSE
Per R12 design the operating unit and legal entity do not hold one-to-one relationship anymore, which is in sharp contrast to 11i. Now one operating unit can be attached to many legal entities in a ledger. Under General Ledger (GL) responsibility, Setup : Financials : Accounting Setup Manager, all the legal entities of a primary ledger or secondary ledger can be found.

Here, customer refers to the Apps deploying agency that is liable to pay a supplier invoice and is accountable to legal authorities for it. So once the “customer taxpayer id” is selected at the header then the system doesn’t default legal entity on the basis of “bill to location” of the selected supplier site and the invoice liability account. The “customer taxpayer id” gives a way to explicitly override the default legal entity derived from supplier site and liability account and default org id. “Customer taxpayer id” is the unique reg. number used to identify an LE. Also “Customer Taxpayer ID” can be used for any type of invoices. The system does not consider it to be mandatory.

In summary, the customer taxpayer id is used to explicitly override the default legal entity derived from supplier site. On the contrary, the taxpayer id is used to enter tax information about the individual or legal entity, which can be a corporation or a partnership.

So the Legal entity that is chosen is first determined by the balancing segment value that is linked to the liability account and then the legal entity that has that balancing segment associated to it is chosen. You can then mnually change this by choosing the value in the ‘Customer Tax Payer ID’

How does this affect ebtax?

You can link the tax regime to either the OU or the LE and therefore use this as the way for driving the tax for your AP transaction. Ideally, you will have a solutoin where there is only one LE linked to your OU and in this case youc an only ever choose one value for the customer tax payer ID so you would never have to choose it as it will be the default. But if you do have more than one LE linked to your OU then you can only determine the place of supply for the tax regime rules with the bill to or ship to. As there could be multiple ship to values then it the only other approach is to change the bill to and to do this you need to change the legal entity (customer tax payer ID).

Useful Excel Macros for eBTax.

Excel Macros to help with formatting data

This Macro is useful when extracting data from the database and you want to insert a line between groups of data. When ever there is a change int he value, a line is inserted, so if we have the following

GB VAT
GB VAT
FR VAT

then after the Macro is run we get
GB VAT
GB VAT

FR VAT

Insert lines
————————————————————————————————–
Sub InsertLine()
Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i – 1, 1) <> Cells(i, 1) Then
Rows(i).Insert
End If
Next
End Sub

————————————————————————————————–

The following is particularly useful when you are trying to format the Financials Tax Register. If you have the Financials Tax Register in Excel, you can remove any column which has no entries with this macro.

Remove blank columns when there is a header – if no header change value to 1048576
————————————————————————————————–
Sub Delete_Empty_Columns()

first = Selection.Column
last = Selection.Columns(Selection.Columns.Count).Column

For i = last To first Step -1

If WorksheetFunction.CountBlank(ActiveSheet.Columns(i)) = 1048575 Then
Columns(i).Delete
End If

Next i

End Sub
—————————————————————————————————-

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 Receivables Activities – Asset Tax Rate code not in LOV

When setting up  Receivables Activities and you need to associate a tax rate to the ‘Asset Tax Rate Code’ or the Liability Tax Rate Code’ but the tax rate you’re looking for is not visible then it is probably because you have set up the tax rate but not associated any tax account or not associated a tax account that is linked to the same ledger used by the operating unit that the receivables activity is linked to.

 

To resolve the issue, you must add the tax account under the same ledger.

Triangulation VAT, recorded on AP transactions??

By default, when selling to a registered EU supplier, I will either set the tax solution to calculate an EU Sales Goods, EU Sales services or an EU Sales Triangulation tax rate – all with a 0% rate but often with different parameters to allow me to report them differently. On the flip side, when receiving an invoice from a supplier, i would only split between the purchases from Europe of goods and services. But a client of mine recently has insisted that for invoices received from suppliers they need to record separately the tax rate when the supplier charged triangulation tax.

Is this the norm? is this a legal requirement? what countries would need this if it were a legal requirement as my client is located in Spain.

your comments will be greatly appreciated

Oracle eBTax quick one time US tax solution

Need a quick US Sales and Use tax solution in Oracle but don’t want to pay the large sums for a 3rd party solution?

There are several 3rd party providers out there such as Taxware, Sabrix (One Source), Vertex who provide an indirect tax solution, primarily for the US Sales and Use market but also for other countries. I have never not been able to meet the needs of any of my clients for any GST/VAT based solution and Oracle can easily handle the tax requirements so I would never suggest to my clients that they should use a 3rd party provider for their non-US tax solution. However, when it comes to the US, there is a clear advantage to using a 3rd party provider.

But….

Continue Reading

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’

Row 1 Error – You can use the same determining factor class and class qualifier as often as necessary only with “Not Equal To” operator and with unique values

‘Row 1 Error – You can use the same determining factor class and class qualifier as often as necessary only with “Not Equal To” operator and with unique values’

If you get this error but you know that this is the first time you have created this tax condition then you have hit a bug!

There is an easy work around to this. If this happens, click the ‘Ignore Condition’ and then save the condition.
oracle R12 tax conditions row 1 error

Then navigate away from the page, click on the ‘Tax Zone Type’ for example and then click back to the ‘Tax Condition Sets’ and query back your condition.

Now remove the ‘Ignore Condition’ tick and save – things should save correctly now.

Always calculate a tax rate

When setting up and VAT or GST based tax, I would advise that a tax rate is always calculated, so even when the tax is not known, use a tax rate call ‘tax not known’ for example.

This way, you will have a tax rate on every transaction, if one is not there then you know there has been an error rather than not knowing if the system has calculated the tax correctly or not! Secondly, if you don’t calculate a tax rate then you cannot report it. Having a tax rate on every transaction means that your tax reports will also have every transaction on them making the reporting thorough and reconciliation easier.

Of course there will be times when you won’t need a tax, on prepayments for example or if you create internal transactions that are just used for internal use and no tax is required, but if its for a sale or purchase, then have a tax rate.


The US Sales and Use eBTax solution is a jurisdictional based solution and many of the exemptions are handled by item exceptions or customer exemptions. But a VAT or GST based eBTax solution is a rules based solution and instead of using exemptions and exceptions, rules should be used to handle any exemptions.If we use rules, we can easily determine a tax rate such as ‘NO TAX’ or ‘OUT OF SCOPE’ where no tax is needed but with the US, the exemptions and exceptions either remove the tax line completely or reduce the tax amount to zero. Neither of these ways make it easy to identify that the right tax was calculated or not. So, should we also look at using rules for the exemptions and exceptions in a US tax solution and calcualte a rate of NO TAX instead of the current way exemptions are handled?

 

Oracle Fusion/eBTax – FR VAT – French VAT considerations

Oracle Fusion/e-Business Tax – French VAT Considerations

If anyone says that European vat is harmonised, then they are wrong! Whilst those countries in the euro zone adhere to similar principles with the core tax rules being the same, most European countries will have specific tax legislation of their own, often required purely for statistical reporting but nearly always forming part of the countries localisation requirements.

Whilst French vat itself has few localisation, certainly around tax, they do have additional requirements. At eBiz Answers, we have a fully tried and tested fully automated indirect tax solution where along with the common tax rate names, STANDARD, EXEMPT etc., we also need to report the VAT separately for the following events;

  • Purchase of assets
  • 80% recovery rates
  • Purchase and sales to Monica
  • Sales to the French territories

And depending on the client, you may also be required to defer you VAT too.

In my opinion, the tax solution should be designed around how you need to report the tax. It is a lot easier to recorded at source the fact that your vat is linked to an asset and report it separately as you can easily add it to the standard rate amounts for the vat submission. But if you had not done this then at the end of the years, you will have to find a way to go through all your transactions for asset purchases and then try and work out an amount which is going to take a lot longer to do that reporting it correctly first time.

Oracle themselves may argue that tax reporting codes should be used and I would agree but for the fact that the reporting codes are not visible anywhere in reports or when the transaction has been calculated so how then will the user know that a tax rate is actually a service item or an asset purchase unless we have an individual tax rate for the purpose?

There is also another common situation I. France where a French company exports a lot more of its product than selling locally but purchases the raw materials locally. In this situation, the suppliers would be charging vat for our French company to recover but because they are exporting, do VAT is being generated. In this situation, known as ‘Non-Dom’, an exemption certificate is often sought and then provided to the suppliers to stop the VAT from being levied. I would advise against using a tax exception, commonly adopted by a US tax solution and instead manage your exemptions by tax rules. To do this, create a party classification code (Exempt) via the trading community manager and then associate this classification with your FR VAT tax regime for France. Assign the ‘Exempt’ party classification code to your supplier or even better if you want all your supplier to be exempt, assign the party classification code to the legal entity establishment that is making the purchase. Create a tax rule that calls either the EXEMPT OR NON-DOM tax rate for these domestic purchases.

If anyone has any other French vat requirements that they would like to share, please add as a comment.

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

new ebtax patch set from Oracle



Oracle Support Document 1481235.1 (NEW! R12.1: E-Business Tax Recommended Patch Collection (ZX RPC), March 2013) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1481235.1

Oracle eBTax – How to handle bonded warehouses?

There are many things that the Oracle e-Business Tax module cannot do and one of those is to handle product that is located in a warehouse that is holding product that is not customs cleared, also know as ‘Bonded’.

So what is the best way to be able to let the tax engine know that the item you’re selling is in fact in a bonded state and thus if sold in that state is not subject to VAT.

Currently my solution is to use an attribute flag (captured as a DFF) as a a way of indicating that either the locator or the sub-inventory is holding bonded product and then at the time the order is created to trigger an even to allow this information to be used to calculate the tax.

Has anyone had this situation before as I would be interested to hear of your solutions or indeed issues. I think what should also go hand in hand with the tax solution is a way to leverage the workflow so that when any product is being either sold directly from the bonded state or is being moved from the bonded to a custom cleared state should be put on hold until the appropriate paper work has been processed by the customs officials. Selling product that is assumed to have cleared customs but has otherwise not been can lead to very big fines indeed.

Oracle eBTax Taz Zone and Geographies tables

Taken from the Oracle eTRM site

 

12