This chapter contains the following topics:
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).
If you are using the track taxes by tax rate/area method, you must run the Update Tax Reconciliation Repository (R0018R) program after you post vouchers and invoices and update the Taxes (F0018) table. 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 with valued-added tax (VAT).
The system stores these tax amounts in the Tax Reconciliation Repository (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 Update Tax Reconciliation Repository program. |
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 Account Ledger (F0911) table:
Document number
Document type
Document company
GL date
Tax rate/area
Tax explanation code
Tax item number
Totals the records in the F0911 table.
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).
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.
This example displays how the system updates the F0018R table using the setup information in the tables:
This table lists 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 |
This table lists the automatic accounting instruction (AAI) items and accounts:
| AAI Item | Account |
|---|---|
| RTA1 | 1.4333 |
| RTA2 | 1.4444 |
| RC | 1.1210 |
Enter an invoice with three pay items for these taxable amounts using tax explanation code V and two tax rate/areas:
This table lists the field values on the Invoice Entry form:
| 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:
This table describes the field values on the GL Distribution form:
| 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.
When you post the report, the system updates the accounts for these amounts:
| Account | Description | Doc Type | Amount | Amounts |
|---|---|---|---|---|
| 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 | . |
When you run the Update Tax Reconciliation Repository program, 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 the F0018R table.
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 | 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 | 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 table displays the resulting records in the F0018 table:
| Doc Number | Doc Type | Pay Item | Taxable Amount | Tax Amount | TaxRate 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 |
Select Global Tax Reconciliation (G00217), Update Tax Reconciliation Repository.
Processing options enable you to specify the default processing for programs and reports.
These processing options specify the records to select from the F0018 table that the system uses to locate corresponding records from the F0911 table.
Specify the document type that the system uses to select records from the F0018 table.
Specify the tax rate/area that you want the system to use to retrieve records from the F0018 table.
Specify the tax explanation code that the system uses to select records from the F0018 table.
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 provides an overview of the process to review the tax reconciliation repository and lists the form used to review the tax reconciliation repository.
When you review tax records using the Review Tax Reconciliation Repository (P0018R) program, 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.
| Form Name | Form ID | Navigation | Usage |
|---|---|---|---|
| Work With Tax Reconciliation Repository | W0018RA | Global Tax Reconciliation (G00217), Review Tax Reconciliation Repository. | Review tax reconciliation records. |