This chapter discusses how to:
Update the Tax Reconciliation Repository.
Review the Tax Reconciliation Repository.
This section provides an overview of the process to update the tax reconciliation repository and discusses how to:
Run the Update Tax Reconciliation Repository Program.
Set processing options for Update Tax Reconciliation Repository (R0018R).
After you post vouchers and invoices and update the F0018 table, you run the Update Tax Reconciliation Repository program (R0018R). The system allocates tax amounts to the taxable general ledger accounts that were used to distribute voucher and invoice transactions, as well as any journal entries entered with valued-added tax (VAT). The system stores these amounts in the F0018R table for reporting purposes.
Important! Do not use data selection to update records to the F0018R table. Instead, use the processing options for the R0018R program.
Reconciling Tax Amounts by GL Account
The Update Tax Reconciliation Repository program uses this process to reconcile tax amounts by GL account and update the F0018R table:
Locates an unprocessed record from the F0018 table (an unprocessed record is one in which the Tax Processed Flag field (PROCFL) is not Y).
Uses these key fields in the order specified to locate the corresponding taxable records in the F0911 table:
Document number
Document type
Document company
GL date
Tax rate/area
Tax explanation code
Tax item number
Totals the F0911 records.
Determines a relative percent for each F0911 record by dividing the amount of each account by the total amount.
Multiplies the relative percent by the tax amount in the F0018 table to determine the tax amount per account.
Performs an allocation for each tax rate/area and tax-explanation code combination.
Creates records in the F0018R table.
Note. Although the system writes tax amounts to both the AA and CA ledgers in the F0911 table, it updates only AA amounts to table F0018R.
Updates the F0018 record to processed (PROCFL field is Y).
Revising Reconciled Transactions
If you revise transaction amounts or general ledger accounts, you must first post them to the F0018 table, and then rerun the Update Tax Reconciliation Repository program to update the F0018R table. When you post the revised transaction, the system creates a new record in the F0018 table for that pay item and increases the Line Number field by an increment of one. When you rerun the Update Tax Reconciliation Repository program, the system:
Locates unprocessed records from the F0018 table.
Locates processed records in the F0018 table that have the same key fields and removes the 1 from the PROCFL field so that all records can be considered for new tax calculations.
Deletes all records that have the same key fields from the F0018R table so that new relative percentages can be calculated.
Reruns the reconciliation process as described in steps 1-8.
Example: Updating the Tax Reconciliation Repository
This example displays how the system updates the F0018R table using the setup information in the tables:
Tax Rate Area Setup
This table discusses the Tax Rate Area fields and values:
Field |
Value |
Value |
Tax Rate Area |
G10 |
G20 |
Tax Authority |
4555 |
25551 |
GL Offset |
A1 |
A2 |
Tax Rate |
10 percent |
20 percent |
AAI Setup
This table describes the automatic accounting instruction (AAI) items and accounts:
AAI Item |
Account |
RTA1 |
1.4333 |
RTA2 |
1.4444 |
RC |
1.1210 |
Invoice Entry
Enter an invoice with three pay items for these taxable amounts using tax explanation code V and two tax rate/areas:
Invoice: 11223 RI 00001
This table describes the Invoice Entry fields and values:
Pay Item |
Gross Amount* |
Taxable Amount |
Tax Amount* |
Tax Rate Area |
Tax Expl. Code |
Item Number |
001 |
1,100 |
1,000 |
100 |
G10 |
V |
0 |
002 |
2,400 |
2,000 |
400 |
G20 |
V |
0 |
003 |
3,300 |
3,000 |
300 |
G10 |
V |
0 |
* The system calculates these amounts as:
Gross Amount = Taxable Amount + Tax Amount
Tax Amount = Taxable Amount x Tax Rate
Note. Because the invoice was entered manually and not created by the Update Customer Sales program (R42800), the system does not use or update the Item Number field.
Distribute the invoice to these general ledger accounts and tax rate/areas:
GL Distribution: 11223 RI 00001
This table describes the GL Distribution fields and values:
JE Line Number |
Account Number |
Track Taxes |
Amount |
Tax Rate Area |
Tax Expl. Code |
Item Number |
1 |
3.5010 |
Y |
1,600 |
G10 |
V |
0 |
2 |
3.5020 |
Y |
1,400 |
G10 |
V |
0 |
3 |
3.5030 |
Y |
1,500 |
G20 |
V |
0 |
4 |
3.5010 |
Y |
500 |
G20 |
V |
0 |
5 |
3.5040 |
Y |
1,000 |
G10 |
V |
0 |
The type of tax is VAT; therefore, the amounts to distribute do not equal the gross amount of the invoice. The amounts equal the taxable amount of the invoice.
Post Report
When you post the report, the system updates the accounts for these amounts:
Account |
Description |
Doc Type |
Amount |
|
3.5010 |
Store Sales 1 |
RI |
. |
- 2,100 |
3.5020 |
Store Sales 2 |
RI |
. |
- 1,400 |
3.5030 |
Store Sales 3 |
RI |
. |
- 1,500 |
3.5040 |
Store Sales 4 |
RI |
. |
- 1,000 |
1.1210 |
AR Trade |
AE |
6,800 |
. |
1.4333 |
VAT Payable G10 |
AE |
. |
- 400 |
1.4444 |
VAT Payable G20 |
AE |
. |
- 400 |
The system also updates the F0018 table accordingly:
Doc Number |
Doc Type |
Pay Item |
Taxable Amount |
Tax Amount |
Tax Rate Area |
Tax Expl. Code |
Processed Flag |
11223 |
RI |
001 |
1,000 |
100 |
G10 |
V |
. |
11223 |
RI |
002 |
2,000 |
400 |
G20 |
V |
. |
11223 |
RI |
003 |
3,000 |
300 |
G10 |
V |
. |
Update Tax Reconciliation Repository
When you run the Update Tax Reconciliation Repository program (R0018R), the system performs for each pay item:
Locates the first record in table F0018 that is not processed (PROCFL field is not 1):
Invoice 11223, RI, 001, G10, V
Locates the corresponding F0911 records based on the key fields (document number, document type, document company, GL date, tax rate/area, tax explanation code, and tax item number).
3.5010 (1,600)
3.5020 (1,400)
3.5040 (1,000)
Sums the amount of the records, and then calculates each percent of the total amount per account for the tax rate/area.
G10 |
1,600 + 1,400 + 1,000 = 4,000 (1,600 / 4,000) x 100 = 40 percent (1,400 / 4,000) x 100 = 35 percent (1,000 / 4,000) x 100 = 25 percent |
Note. The system performs soft rounding for these calculations.
Multiplies the percent by the tax amount to determine the tax amount per account.
Pay Item 001 - G10 |
.40 x 100 = 40.35 x 100 = 35.25 x 100 = 25 |
Note. The system performs soft rounding for these calculations.
Updates table F0018R.
This table displays the resulting records in the F0018R table:
Doc Number |
Doc Type |
Pay Item |
Account |
Journal Entry Line |
Amount |
Tax Amount |
Tax Rate Area |
Tax Expl. Code |
11223 |
RI |
001 |
3.5010 |
1.0 |
1,600 |
40 |
G10 |
V |
11223 |
RI |
001 |
3.5020 |
2.0 |
1,400 |
35 |
G10 |
V |
11223 |
RI |
001 |
3.5040 |
3.0 |
1,000 |
25 |
G10 |
V |
Updates the Processed Flag field (PROCFL) to P on the F0018 record.
This table displays the resulting records in the F0018 table.
Doc Number |
Doc Type |
Pay Item |
Taxable Amount |
Tax Amount |
Tax Rate Area |
Tax Expl. Code |
Processed Flag |
11223 |
RI |
001 |
1,000 |
100 |
G10 |
V |
P |
11223 |
RI |
002 |
2,000 |
400 |
G20 |
V |
. |
11223 |
RI |
003 |
3,000 |
300 |
G10 |
V |
. |
Locates the second record in table F0018 that is not processed: Invoice 11223, RI, 002, G20, V.
Locates the corresponding F0911 records:
3.5030 (1,500)
3.5010 (500)
Sums the amount of the records, and then calculates each percent of the total amount per account for the tax rate/area.
G20 |
1,500 + 500 = 1,500 (1,500 / 2,000) x 100 = 75 percent ( 500 / 2,000) x 100 = 25 percent |
Multiplies the percent by the tax amount to determine the tax amount per account.
Pay Item 002 - G20 |
.75 x 400 = 300.25 x 400 = 100 |
Updates table F0018R.
This table displays the resulting records in the F0018R table:
Doc Number |
Doc Type |
Pay Item |
Account |
Journal Entry Line |
Amount |
Tax Amount |
Tax Rate Area |
Tax Expl. Code |
11223 |
RI |
001 |
3.5010 |
1.0 |
1,600 |
40 |
G10 |
V |
11223 |
RI |
001 |
3.5020 |
2.0 |
1,400 |
35 |
G10 |
V |
11223 |
RI |
001 |
3.5040 |
3.0 |
1,000 |
25 |
G10 |
V |
11223 |
RI |
002 |
3.5030 |
1.0 |
1,600 |
300 |
G20 |
V |
11223 |
RI |
002 |
3.5010 |
2.0 |
500 |
100 |
G20 |
V |
Updates the Processed Flag field (PROCFL) to P on the F0018 record.
This table displays the resulting records in the F0018 table:
Doc Number |
Doc Type |
Pay Item |
Taxable Amount |
Tax Amount |
Tax Rate Area |
Tax Expl. Code |
Processed Flag |
11223 |
RI |
001 |
1,000 |
100 |
G10 |
V |
P |
11223 |
RI |
002 |
2,000 |
400 |
G20 |
V |
P |
11223 |
RI |
003 |
3,000 |
300 |
G10 |
V |
. |
Locates the third record in table F0018 that is not processed:
Invoice 11223, RI, 003, G10, V.
Locates the corresponding F0911 records:
3.5010 (1,600)
3.5020 (1,400)
3.5040 (1,000)
Because the first and third pay items have the same tax rate/area and tax explanation code, the relative percentages will be the same. For performance purposes the system does not recalculate them.
G10 |
1,600 + 1,400 + 1,000 = 4,000 (1,600 / 4,000) x 100 = 40 percent (1,400 / 4,000) x 100 = 35 percent (1,000 / 4,000) x 100 = 25 percent |
Multiplies the percent by the tax amount to determine the tax amount per account.
Pay Item 003 - G10 |
.40 x 300 = 120.35 x 300 = 105.25 x 300 = 75 |
Updates table F0018R.
This table displays the resulting records in the F0018R table:
Doc Number |
Doc Type |
Pay Item |
Account |
Journal Entry Line |
Amt |
Tax Amount |
Tax Rate Area |
Tax Expl. Code |
11223 |
RI |
001 |
3.5010 |
1.0 |
1,600 |
40 |
G10 |
V |
11223 |
RI |
001 |
3.5020 |
2.0 |
1,400 |
35 |
G10 |
V |
11223 |
RI |
001 |
3.5040 |
3.0 |
1,000 |
25 |
G10 |
V |
11223 |
RI |
002 |
3.5030 |
4.0 |
1,500 |
300 |
G20 |
V |
11223 |
RI |
002 |
3.5010 |
5.0 |
500 |
100 |
G20 |
V |
11223 |
RI |
003 |
3.5010 |
6.0 |
1,600 |
120 |
G10 |
V |
11223 |
RI |
003 |
3.5020 |
7.0 |
1,400 |
105 |
G10 |
V |
11223 |
RI |
003 |
3.5040 |
8.0 |
1,000 |
75 |
G10 |
V |
Updates the Processed Flag field (PROCFL) to P on the F0018 record.
This displays the resulting records in the F0018 table:
Doc Number |
Doc Type |
Pay Item |
Taxable Amount |
Tax Amount |
Tax Rate Area |
Tax Expl. Code |
Processed Flag |
11223 |
RI |
001 |
1,000 |
100 |
G10 |
V |
P |
11223 |
RI |
002 |
2,000 |
400 |
G20 |
V |
P |
11223 |
RI |
003 |
3,000 |
300 |
G10 |
V |
P |
From the Global Tax Reconciliation menu (G00217), select Update Tax Reconciliation Repository.
Processing options enable you to specify the default processing for programs and reports.
Selection Criteria
These processing options specify the records to select from the F0018 table that the system uses to locate corresponding records from the F0911 table.
Document Type |
Specify the document type that the system uses to select records from the F0018 table. |
Tax Rate/Area |
Specify the tax rate/area that you want the system to use to retrieve records from the F0018 table. |
Tax Explanation Code |
Specify the tax explanation code that the system uses to select records from the F0018 table. |
GL Date (general ledger date) |
Specify the GL date that the system uses to select records from the F0018 table. The system selects records with a GL date that is equal to or greater than the date that you enter. |
This section discusses how to review the tax reconciliation repository.
When you review tax records using the Review Tax Reconciliation Repository program (P0018R), the system automatically displays all records in the F0018R table. You can use the QBE line to limit the number of records that the system displays. You cannot add, delete, or revise records in the F0018R table; changes must be made to the transaction tables, and then updated to the F0018R table.
This section discusses how to review records in the Tax Reconciliation Repository.
Form Name |
FormID |
Navigation |
Usage |
Work With Tax Reconciliation Repository |
W0018RA |
Global Tax Reconciliation (G00217), Review Tax Reconciliation Repository. |
Review tax reconciliation records. |
Access the Work With Tax Reconciliation Repository form.