Updating the Tax Reconciliation Repository

This chapter discusses how to:

• Update the Tax Reconciliation Repository.

• Review the Tax Reconciliation Repository.

Updating 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).

Understanding the Process to Update the Tax Reconciliation Repository

After you post vouchers and invoices and update the Taxes (F0018) table, you run the Update Tax Reconciliation Repository (R0018R) program. 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 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.

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:

1. Locates an unprocessed record from the F0018 table (an unprocessed record is one in which the Tax Processed Flag field (PROCFL) is not Y).

2. 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

3. Totals the records in the F0911 table.

4. Determines a relative percent for each F0911 record by dividing the amount of each account by the total amount.

5. Multiplies the relative percent by the tax amount in the F0018 table to determine the tax amount per account.

6. Performs an allocation for each tax rate/area and tax-explanation code combination.

7. 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.

8. 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:

1. Locates unprocessed records from the F0018 table.

2. 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.

3. Deletes all records that have the same key fields from the F0018R table so that new relative percentages can be calculated.

4. 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:

AAI Setup

This table describes the automatic accounting instruction (AAI) items and accounts:

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:

* 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:

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:

The system also updates the F0018 table accordingly:

Update Tax Reconciliation Repository

When you run the Update Tax Reconciliation Repository program, the system performs for each pay item:

1. Locates the first record in table F0018 that is not processed (PROCFL field is not 1):

   Invoice 11223, RI, 001, G10, V

2. 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)

3. 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.

4. 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.

5. 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

6. 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	.

7. Locates the second record in table F0018 that is not processed: Invoice 11223, RI, 002, G20, V.

8. Locates the corresponding F0911 records:

   • 3.5030 (1,500)

   • 3.5010 (500)

9. 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

10. 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

11. 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

12. 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	.

13. Locates the third record in table F0018 that is not processed:

    Invoice 11223, RI, 003, G10, V.

14. Locates the corresponding F0911 records:

15. 3.5010 (1,600)

    • 3.5020 (1,400)

    • 3.5040 (1,000)

16. 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

17. 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

18. 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

19. 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

Running the Update Tax Reconciliation Repository Program

Select Global Tax Reconciliation (G00217), Update Tax Reconciliation Repository.

Setting Processing Options for Update Tax Reconciliation Repository (R0018R)

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.

Reviewing the Tax Reconciliation Repository

This section provides an overview of reviewing the tax reconciliation repository and lists the form used to review the tax reconciliation repository.

Understanding How 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 Used to Review Records in the Tax Reconciliation Repository