These are the basic mandatory tax setups to be performed in the
E-Business Tax Module in R12 to enter Tax lines manually or calculate taxes for
any transaction.
1. TAX REGIME: Tax Regime is setup
for the taxes in each country and a geographic region where you do business and
where a separate tax applies. A tax regime associates a common set of default
information, regulations, fiscal classifications and registrations to one or
more taxes with same tax requirement.
2. TAXES: Tax is classification
of a charge imposed by a Government through a fiscal or a tax authority. Each
separate tax in a tax regime includes records for statuses, tax rates and rules
used to calculate and report tax requirements
3. TAX STATUS: Tax Status is the
taxable nature of a product or service in the context of a transaction for a
tax.
4. TAX JURISDICTION: A geographic area
where a tax is levied by a specific tax authority.
5. TAX RATE: The rate specified for
a Tax status in effect for a period of time; the tax rate can be expressed as a
percentage or as a value per unit quantity
6. TAX RULES: E-business tax uses the
tax setups along with the tax rules to decide which taxes apply to a
transaction and how to calculate the tax amount for each tax that applies to a
transaction.
Business Requirement: Calculation of USE TAX in AP Invoices based on Ship To
Location Zip code and Balancing Segment of Chart of Accounts.
The requirement of client was as follows:
Use tax was calculated in an AP Invoice based on the combination
of the balancing segment in the chart of accounts and the zip code of a ship to
location.
In 11i different sales tax codes were setup for each combination
of balancing segment and ship to location. An offset tax code was attached to
each of the sales tax code.
For an invoice where use tax was applicable the user would enter
the sales tax code (which has been attached to an offset tax code) and validate
the invoice. This would generate two tax lines one each for sales tax and
offset tax.
After the upgrade to Oracle R12; Use tax can be applied on an
invoice in two ways:
1)
Similar to 11i make sales tax and offset tax codes to generate
the use tax lines in the AP invoice.
2)
Use feature of Self Assessed tax (newly introduced in R12) for
accounting of use tax lines. Concept of Self Assessed tax is explained later in
the document.
In this case study we have used option 2 as the base.
1) Define Tax Regime
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Regime – Create
Define one tax regime for country United States as shown below:
2) Define Taxes
Define one tax called US USE TAX under the tax regime created
for United States
Responsibility & Navigation:
Tax Managers – Tax Configuration – Taxes – Create
Click on Apply to save the changes
3) Define Tax Statuses
Define one tax status for the tax regime and tax setup in the
previous steps.
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Statuses – Create
4) Define Tax Jurisdictions
Define one tax jurisdiction for the tax regime and tax setup in
the previous steps.
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Jurisdictions – Create
5) Define Tax Rates
Define multiple rates for the combination of Corp and Ship to
location zip codes. A tax rate is made for the combination of Tax Regime, Tax
and Tax Status.
For instance:
Balancing Segments BAL_A and BAL_B
Ship to Location zip-code 100 and 101
The different rates which can be setup are
USE_BAL_1_100 – 5%
USE_BAL_B_101 – 10%
USE_BAL_A_101 – 2%
USE_BAL_B_100 – 1%
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Rates – Create
Enter the Tax Expense Account. If this is left blank the expense
account for ITEM line will be used.
The Tax Recoverable/Liability is the Use tax Liability Account
to be used.
6.
Define Tax Rules
Oracle provides a seeded set of Tax Rules which can be used by
the user for tax calculation. For any transaction made the tax determination
process uses these tax rules to decide the taxes which will apply and the
amount of tax which has to be applied for a transaction.
Prerequisites for setting up the Tax Rules:
Step1:
To decide whether use tax applied on any transaction within
Payables module a tax classification code called USE TAX APPLICABLE will be
made. A tax classification code is similar to a tax rate code with a tax rate
of 0%.
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Rates – Create
Step2:
Create Geography Hierarchy:
The various zipcodes which will be used in the tax rules have to
be setup in the geography hierarchy in the trading community architecture.
Responsibility & Navigation:
Trading Community Manager a Administration à Geography Hierarchy
Query for country United States in the below screenshot
Click on View Details tab
Chose the State required and click on View Details
Enter the various County details or enter a default county for
the state as shown below. Click on View Details
Enter the different cities for the county and click on View
Details to enter the zip code for each city.
Enter the zip codes which will be used for tax calculation with
the code type as “Tax Geography Code”. Click on Apply after creating/updating
any changes
Step3:
Create Tax Determining Factor Sets
Responsibility & Navigation:
Tax Managers – Advanced Setup Options – Tax Determining Factor
Sets – Create
The determining factor will work in the following manner:
Since use tax had to be calculated on invoices based on the
balancing segment and ship to location we chose the following values in a
determining factor set:
1.
Flex-field segment1 of the Line account is the balancing segment
2.
The ship to location postal code of the invoice
3.
Since use tax will apply only to certain invoices we chose a
transaction input factor and the value is called as tax classification code.
Step4:
Create Tax Condition Sets
Responsibility & Navigation:
Tax Managers – Advanced Setup Options – Tax Condition Sets –
Create
The tax condition set is made from the tax determining factor
set. In the above screenshot we have made a tax condition set for balancing
segment CIP and postal code 62201 using the determining factor set made in the
above set.
TAX RULE SETUP
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Rules
The following tax rules will be setup for use tax calculation:
1.
Determine Tax Applicability: Since the tax US_USE applies only
for use tax we make this tax applicable only if the tax classification code in
the invoice for an ITEM line is “USE TAX APPLICABLE”.
2.
Determine Tax Rate – This rule will derive the use tax rate for
the invoices using the determining factor set and condition set. While making
the tax rule we enter the determining factor set and the condition set made in
the previous steps as shown in below screenshot.
For each condition set there will be a different tax rate which
will be applied.
Example:
As shown in above screenshot there are 4 condition sets:
1.
This means that the balancing segment is CIP and ship to postal
code is 62201. If the invoice satisfies these two conditions and the tax
classification code is USE TAX APPLICABLE the tax rate used will be
USE_CIP_62201.
2.
COND_AMC_63103 – This means that the balancing segment is AMC
and ship to postal code is 63103. If the invoice satisfies these two conditions
and the tax classification code is USE TAX APPLICABLE the tax rate used will be
USE_AMC_63103.
For all the other rules the default values have been used.
7.
Concept of Self Assessed Tax
A self-assessed tax is a tax calculated and remitted for a
transaction, where tax was not levied by the supplier but is deemed as due (and
therefore needs to be paid by the purchaser).
In such cases the purchaser is responsible for calculating and
remitting the tax. Self-assessment is also known as reverse charge or use tax
in certain tax regimes.
When self-assessment applies to a tax line, E-Business Tax
creates the recoverable and/or non-recoverable distributions, and Payables
creates an additional accounting distribution to record the liability for the
self-assessment.
You can set the self-assessment option:
• At the tax profile level to default to the tax registrations
that you create for this party.
• At the tax registration level.
• On an individual tax line.
E-Business Tax applies self-assessment to Payables invoices
received by the first party according to the tax registration setting of the
Set for Self-Assessment/Reverse Charge option. The specific tax registration
record that E-Business Tax uses is derived either from Determine Tax
Registration rules or from the default tax registration.
We have setup the self-assessed tax feature at the Tax
Registration level.
Navigation: Tax Managers – Home – External Dependencies – Create
First Party : Legal Entity and Establishments
Query the Legal Entity -View Details – Establishments Tab –
Query Establishment (which is the OU here) – Registrations – Create Tax
Registration
Since we are using the self-assessed tax feature only for use
tax invoices we set up this option for the tax US_USE only. Other taxes will
not have this flag checked.
8.
Invoice Creation and Use Tax calculation
Invoice can be created in base table or interface table with the
tax classification code as USE TAX APPLICABLE. Use the correct ship to location
and balancing segments in the invoice.
The tax rules will be triggered on validation of the invoice in
base tables and the use tax line will be generated.
Since we are using the self-assessed tax feature there will not
be any tax displayed on the invoice line, the tax details will be visible in
the Tax Details form. They will also not be stored in ap_invoice_lines_all
table; instead they will be stored in zx_lines table with the trx_id which will
be the invoice_id of the invoice.
Tax lines form indicating the use tax details.
Sample Case Study:
Business Requirement: Calculation of SALES TAX in AP for
Invoices made from source ERS based on Supplier site pin code
The requirement of client was as follows:
There were many invoices created in AP using the ERS source i.e.
create the PO and Receipt in Purchasing module and then run the “Pay on
Receipt” program to generate the invoice in interface tables with source as
ERS.
The standard Payables Open Interface Import program will then
import these invoices into base oracle.
Sales tax applies on some of these ERS transactions based on the
zip code of supplier site.
For instance: An ERS invoice with a supplier site A having zip
code 63301 has a tax rate of 10% to be applied whereas supplier site B having
zip code 62284 has a tax rate of 6% to be applied.
Since the ERS invoice which is inserted into the interface table
consists of only ITEM line we needed to insert one more TAX line with the
correct amount. Additionally the total invoice amount also has to be corrected
to include the tax amount.
Detailed setups performed for the same.
1.
Define Tax Regime
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Regime – Create
Define one tax regime for country United States as shown below:
2.
Define Taxes
Define one tax called US SALES TAX under the tax regime created
for United States
Responsibility & Navigation:
Tax Managers – Tax Configuration – Taxes – Create
Click on Apply to save the changes
3.
Define Tax Statuses
Define one tax status for the tax regime and tax setup in the
previous steps.
Responsibility & Navigation:
Tax Managers à Tax Configuration à Tax Statuses à Create
4.
Define Tax Jurisdictions
Define one default tax jurisdiction for the tax regime and tax
setup in the previous steps.
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Jurisdictions – Create
5.
Define Tax Rates
Define tax rates for each supplier site zip code. A tax rate is
made for the combination of Tax Regime, Tax and Tax Status.
For instance:
Supplier site zip codes are 63119 and 61548.
The different rates which will be setup are
SALES_63119 – 7.575%
SALES_61548 – 7.25%
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Rates – Create
Click on Tax Accounts to enter the Expense and liability account
details
Enter the Tax Expense Account. If this is left blank the expense
account for ITEM line will be used.
The Tax Recoverable/Liability is the Use tax Liability Account
to be used. Since this is a sales tax rate we enter the default account of 000
for the same.
6.
Define Tax Rules
Oracle provides a seeded set of Tax Rules which can be used by
the user for tax calculation. For any transaction made the tax determination
process uses these tax rules to decide the taxes which will apply and the
amount of tax which has to be applied for a transaction.
Prerequisites for setting up the Tax Rules:
Step1:
To decide whether use tax applied on any transaction within
Payables module a tax classification code called SALES TAX APPLICABLE will be
made. A tax classification code is similar to a tax rate code with a tax rate
of 0%.
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Rates – Create
Step2: Create Geography Hierarchy:
The various zipcodes which will be used in the tax rules have to
be setup in the geography hierarchy in the trading community architecture. Set
up the zip codes to be used for the supplier sites where sales tax needs to be
calculated based on the step 2 in Point 6 in Case study 1.
Step3: Create Tax Determining Factor Sets
Responsibility & Navigation:
Tax Managers – Advanced Setup Options – Tax Determining Factor
Sets – Create
The determining factor will work in the following manner:
Since sales tax had to be calculated on invoices based on the
supplier site for ERS invoices the determining factors would be:
1. Geography – Ship From
postal code
2. Transaction Input
Factor – Tax classification code which will be used for ERS invoices.
Step4: Create Tax Condition Sets
Responsibility & Navigation:
Tax Managers – Advanced Setup Options – Tax Condition Sets –
Create
The tax condition set is made from the tax determining factor
set. In the above screenshot we have made a tax condition set for postal code
61548 and tax classification code SALES TAX APPLICABLE using the determining
factor set made in the above set.
TAX RULE SETUP
Responsibility & Navigation:
Tax Managers – Tax Configuration – Tax Rules
The following tax rules will be setup for use tax calculation:
3. Determine Tax
Applicability: Since the tax US_SALES applies only for ERS Sales Tax we make
this tax applicable only if the tax classification code in the invoice for an
ITEM line is “SALES TAX APPLICABLE”
4. Determine Tax Rate –
This rule will derive the sales tax rate for the invoices using the determining
factor set and condition set. While making the tax rule we enter the
determining factor set and the condition set made in the previous steps as
shown in below screenshot.
For each condition set there will be a different tax rate which
will be applied.
Example:
As shown in above screenshot there are 4 condition sets:
1.
COND_SALES_63119 – This means that if the ship from postal code
is 63119 and the tax classification code in the invoice is SALES TAX APPLICABLE
then the tax rate used will be SALES_63119.
2.
COND_SALES_61548 – This means that if the ship from postal code
is 61548 and the tax classification code in the invoice is SALES TAX APPLICABLE
then the tax rate used will be SALES_61548.
For all the other rules the default values have been used.
7.
Invoice Creation and Sales Tax calculation for ERS invoice.
Generate the ERS invoice in the invoice interface tables using
the program Pay on Receipt program.
Update the following values for the ERS invoice in the interface
table:
TAX CLASSIFICATION CODE – SALES TAX APPLICABLE
CALCULATE TAX AMOUNT DURING IMPORT – Set the flag to Y
ADD TAX TO INVOICE AMOUNT – Set the flag to Y
The calculate tax amount flag and add tax to invoice amount is
set to Y so that the Payables Open Interface Import program would trigger tax
rules and calculate the tax amount and also add the tax amount to the invoice amount.
Hence the invoice will be imported with the tax line and correct invoice
amount.
Following is the update script for the same:
UPDATE apps.ap_invoices_interface
SET add_tax_to_inv_amt_flag = 'Y'
AND calc_tax_during_import_flag = 'Y'
WHERE source = 'ERS'
UPDATE apps.ap_invoice_lines_interface
SET tax_classification_code =
‘SALES TAX APPLICABLE’
WHERE invoice_id IN (SELECT
invoice_id
FROM apps.ap_invoices_interface
WHERE SOURCE = 'ERS')
Snapshots of ERS invoices in the interface table.
Invoice header in the interface table before it was processed.
Invoice line in the interface table
Supplier site pincode – 62223
After importing this invoice; the tax line has got generated
based on the tax rule for Supplier site. The invoice amount has also changed
from 1000 to 1073.50
1 comment:
Thank u
Post a Comment