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

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

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.
G/L 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.

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

Form Name | FormID | Navigation | Usage
Work With Tax Reconciliation Repository | W0018RA | Global Tax Reconciliation (G00217), Review Tax Reconciliation Repository. | Review tax reconciliation records.