19 Reconciling Bank Account Transactions

This chapter contains the following topics:

19.1 Understanding Bank Account Reconciliation

As part of your period-end processes, you might need to reconcile the balances in your bank accounts with your bank statements.

After you perform the initial setup tasks, you can easily reconcile bank accounts on a monthly basis using the Manual Reconciliation program (P09131).

19.2 Reconciling Voided Payments and Receipts

This section provides an overview of the auto reconciliation void programs and discusses how to:

  • Reconcile voided payments.

  • Reconcile voided receipts.

19.2.1 Understanding the Auto Reconciliation Void Programs

Instead of manually reconciling voided payments and receipts, you can save time during account reconciliation by running these programs:

  • Automatic Reconcile Void Payment (R09551)

  • Automatic Reconcile Void Receipt (R09552)

These programs automatically reconcile voided payments and receipts (and those with zero amounts) by updating the GLRCND field with the default reconciliation code R on the transactions in the F0911 table. When reconciling payments, the Automatic Reconcile Void Payment program selects payments that were voided manually and those voided during automatic payment processing.

You should run these programs before you run the Refresh Reconciliation File program (R09130) so that voided and zero amount payments and receipts are not included in the F0911R table.

The Automatic Reconcile Void Payments and Automatic Reconcile Void Receipts programs do not have processing options.

19.2.2 Reconciling Voided Payments

Select Bank Tape Reconciliation (G09213), Automatic Reconcile Void Payment.

19.2.3 Reconciling Voided Receipts

Select Bank Tape Reconciliation (G09213), Automatic Reconcile Void Receipt.

19.3 Refreshing Account Reconciliation Information

This section provides an overview of the Refresh Reconciliation File program and discusses how to:

  • Run the Refresh Reconciliation File program.

  • Set data selection for Refresh Reconciliation File.

  • Set processing options for Refresh Reconciliation File (R09130).

19.3.1 Understanding the Refresh Reconciliation File Program

Before you reconcile your accounts, you must run the Refresh Reconciliation File program (R09130). Typically, you run this program at the end of each period to reconcile transactions for the period. The Refresh Reconciliation File program copies the unreconciled transaction detail for all reconcilable accounts from the F0911 table into the F0911R reconciliation table.

Each time that you refresh the F0911R table, the system removes all previously reconciled transactions in the table and replaces them with new transactions that meet the processing option and data selection criteria for the Refresh Reconciliation File program. For example, if you run this program for the month of January and reconcile all transactions for that month, and then run the program again for February, the program removes all the reconciled transactions for January from the F0911R table and replaces them with the unreconciled transactions for February.

Transactions must meet these criteria for the Refresh Reconciliation File program to select records:

  • The account that you are trying to reconcile must be within the AAI range for GLRCxx.

  • The general ledger date for the transaction must be within the date range specified in the Beginning Date and Ending Date processing options.

  • The GLRCND field for the transaction must be blank (unreconciled).

When you run the Refresh Reconciliation File program, you can refresh the information for all accounts in the AAI ranges for GLRCxx at one time, or you can use data selection to refresh a specific account.

The Refresh Reconciliation File program is designed to build only one F0911R table. However, you can allow separate workfiles for specific accounts by using the Configured Network Computing function to point to the F0911R table locally.

19.3.2 Prerequisite

Before you complete the tasks in this section:

19.3.3 Running the Refresh Reconciliation File Program

Select Manual Reconciliation (G09209), Refresh Reconciliation File.

19.3.4 Setting Data Selection for Refresh Reconciliation File

For faster processing, use data selection to specify the accounts to reconcile.

To create a F0911R table for only one account, enter the short account ID number in the data selection. If you run the Refresh Reconciliation File program for a specific account and then run the program again for a different account, the system does not remove the previously reconciled transactions for the first account. Instead, it adds the records for the second account to the previously reconciled transactions of the first account. The Refresh Reconciliation File program only removes all previously reconciled transactions in the F0911R table when you run the program subsequently for the same account.

19.3.5 Setting Processing Options for Refresh Reconciliation File (R09130)

Use these processing options to select dates, reconciliation status, ledger type, transactions with an AE document type, and messages to print.

19.3.5.1 Select

1. Beginning Date and 2. Ending Date

Specify the dates that the system uses as the beginning and ending dates when selecting records from the F0911 table.

3. Reconciled Status

Values are:

Blank: Select only unreconciled records.

1: Select both reconciled and unreconciled records.

The F0911R can become unmanageable if you select both reconciled and unreconciled transactions on a regular basis. This can impact processing time. Include reconciled items only when it is necessary to correct a previous reconciliation.

19.3.5.2 Default

1. Ledger Type

Specify the ledger type (UDC table 09/LT) for the system to use when selecting records from the F0911 table. If you leave this processing option blank, the system selects all ledger types.

19.3.5.3 Display

1. Document Type Filter

Specify whether the system includes AE document type transactions when it selects records from the F0911 table. Values are:

Blank: Do not include AE transactions.

1: Include AE transactions.

19.3.5.4 Print

1. Messages

Specify which messages to print on the Refresh Reconciliation File report (R09130). Values are:

Blank: Print messages only for accounts that are refreshed.

1: Print messages for all accounts.

19.4 Reconciling Bank Account Transactions Manually

This section provides overviews of manual bank account reconciliation and the reconciliation report and discusses how to:

  • Set processing options for Manual Reconciliation (P09131).

  • Reconcile bank account transactions manually.

19.4.1 Understanding Manual Bank Account Reconciliation

When you reconcile your accounts on the Bank Account Reconciliation form, the system marks the transactions on the form with the default reconciliation code R or the code that you selected. As you reconcile transactions, the system updates the Outstanding Debits and Outstanding Credits fields in the F0911R1 table. The system also updates the Amt to Reconcile field using this formula:

(Bank Balance + Outstanding Debits + Outstanding Credits)–Book Balance = Amount to Reconcile

  • Bank Balance = the amount on your bank statement.

  • Outstanding Debits and Credits = the records in the F0911 table that have not been marked as reconciled.

  • Book Balance = the account balance in the F0902 table. The system includes balance forward amounts for balance sheet accounts.

19.4.2 Understanding the Reconciliation Report

After you reconcile your bank account transactions, run the Print Reconciliation Report (R09132P) for a list of any outstanding items that are unreconciled.

The date range used for the report is the same as the range entered in the processing options for the Refresh Reconciliation File program. To run the Print Reconciliation Report over a different date range, you must change the date range in the processing options and run the Refresh Reconciliation File program again.

19.4.3 Forms Used to Reconcile Bank Account Transactions Manually

Form Name Form ID Navigation Usage
Bank Account Reconciliation W09131A Manual Reconciliation (G09209), select Bank Account Reconciliation. Manually reconcile or unreconcile bank account transactions.
Bank Account Reconciliation Reference W09131J On the Bank Account Reconciliation form, select the row that corresponds to the desired transaction and then select Reconcile Toggle from the Row menu. Enter a reconciliation reference number for the transaction and specify the date that the transaction cleared the bank.

Note: You must set the processing option to 1 of either Cleared Date field or Bank Account Reconciliation to display the Bank Account Reconciliation Reference form.


19.4.4 Setting Processing Options for Manual Reconciliation (P09131)

Use these processing options to select default values.

19.4.4.1 Defaults

1. Ledger Type

Specify a ledger type from the UDC table 09/LT for processing. If you leave this processing option blank, the system uses the default ledger type AA.

2. Reconciliation Reference

Specify whether the system updates the Reconciliation Reference field on the record in the Account Ledger table (F0911) with a reconciliation reference. The system uses the Next Number Revisions program (P0002) to assign the reconciliation reference. This reconciliation reference can be overridden at the time of reconciliation. If you leave this field blank, the system does not update the Reconciliation Reference field.

3. Additional Ledger Type to Update

Specify an additional ledger to update with the same information as the system writes to JD Edwards EnterpriseOne tables for the AA ledger type. If you leave this processing option blank, the system writes data for only the AA ledger.

Note:

If you use cash basis accounting, specify ledger type AZ to have the system write reconciliation data to the records in the AZ ledger. You must run the Create Cash Basis Entries program (R11C850) to process cash basis transactions and generate records for the AZ ledger before you reconcile bank accounts.
4. Cleared Date

Enter 1 to display the Cleared Date field on the Reconciliation Reference Selection form.

Note:

If you leave this processing option and the Reconciliation Reference processing option blank, the system does not display the Reconciliation Reference Selection form.

19.4.4.2 Process

Use these processing options to select default values. The processing options on this tab are used only by the Debit Credit Match program.

1. Reconciliation Type

Select an option that specifies the type of processing for an event.

Blank: Bank account reconciliation.

1: Matching debit credit reconciliation

2. Debit Credit Balance

Specify whether debits and credits are required to balance.Values are:

Blank: Debits and credits are not required to balance.

1: Debits and credits are required to balance.

3. Tolerance Limit

Specify the value for the tolerance amount by which debit and credit transactions can be out of balance. If you enter an amount, the debit and credit transaction can be out of balance by this amount and still be considered reconciled. If you leave this field blank, debits and credits must balance.

You use this processing option only when you enter 1 in the Debit Credit Balance processing option. For example, if you enter 1 in the Debit Credit Balance processing option and you leave this processing option blank, debits and credits must balance exactly or the system generates an error. If you enter 1 in the Debit Credit Balance processing option and you enter 5.00 in this processing option, debits and credits can be out of balance by 5.00 and the system still considers the transactions balanced and does not generate an error.

Note:

This processing option is valid only for the Debit Credit Match program. This processing option is not valid for the Bank Account Reconciliation program.
4. Sort By

Specify the value to determine whether the system should sort transactions by amount or G/L date. Values are:

Blank: Sort transactions by G/L date.

1: Absolute value.

If you select this option, the system will sort the transactions by amount, regardless of whether they are debits or credits. You will be able to see an amount with the debit and credit together as opposed to searching for the offset of the amount by the G/L date.

19.4.5 Reconciling Bank Account Transactions Manually

Access the Bank Account Reconciliation form.

Figure 19-1 Bank Account Reconciliation form

Description of Figure 19-1 follows
Description of ''Figure 19-1 Bank Account Reconciliation form''

  1. Enter the bank account number for which you ran the Refresh Reconciliation File program (R09130).

  2. To view both unreconciled and reconciled items, do not select the Unreconciled Only option.

    You can review both unreconciled and reconciled items if you entered 1 for the Reconciled Status processing option of the Refresh Reconciliation File program.

  3. To limit the transactions that you view for the account, enter a general ledger date in the From Date field.

    You must enter a date that is within the date range that you entered in the processing options for the Refresh Reconciliation File program. To view transactions from a date prior to the beginning date in the processing option, you must run the Refresh Reconciliation File program again with different dates.

  4. Enter the balance that appears on your bank statement in the Bank Balance field and click Find.

  5. To mark a transaction as reconciled, select the row that corresponds to the transaction and then select Reconcile Toggle from the Row menu.

  6. To mark more than one transaction (or all transactions) as reconciled, select the rows for the transactions and then select Reconcile Toggle from the Row menu.

    When you mark transactions as reconciled:

    • If the Reconciliation Reference processing option for the Manual Reconciliation program is set to 1, the Bank Account Reconciliation form appears and displays the next available number from next numbers. The system displays this form each time you reconcile a transaction so that you can enter a new number for each transaction.

    • If the Cleared Date processing option is set to display the Cleared Date field, enter the date that the bank cleared the transaction. If you do not enter a date, the system uses the system date.

    • The system enters R (or the reconciliation code that you selected) in the Rec Code field on the Bank Account Reconciliation form.

    • Update the GLR3 field in the F0911R table with the reconciliation reference number for all reconciled transactions.

    • Update the GLRCND field in the F0911 table with the Rec Code R in the F0911R table for all reconciled transactions.

      Note:

      The system updates the F0911 table for ledger type AA and an additional ledger type, if you specified an additional ledger type in the processing options.
    • Update the GLR3 and GLRCND fields in the F0911 and F0911R tables if the Bank Account Reconciliation Reference processing options for the Manual Reconciliation program is set to 1.

  7. To print a report, select Reconciliation Print (R09132P) from the Report menu.

19.5 Changing a Reconciled Transaction to Unreconciled

After you finish the reconciliation process, you might need to change a reconciled transaction to unreconciled. When you change a transaction to unreconciled, the system removes the R (or the reconciliation code that you selected) from F0911R table. If you set the Reconciliation Reference processing option to 1 for the Manual Reconciliation program, the system also deletes the assigned reconciliation reference number from the transaction.

If you ran the Refresh Reconciliation File program since you reconciled the transaction, you must run the program again and set the Reconciled Status processing option to 1 to include reconciled transactions.

To change a reconciled transaction to unreconciled for a period that is different from the current period, you must run the Refresh Reconciliation File program again:

  • Set the Reconciled Status processing option to 1 to include reconciled transactions.

  • Change the Beginning Date and Ending Date processing options to include the general ledger date of the transaction that you want to change.