21 Reconciling Bank Tapes

This chapter contains the following topics:

• Section 21.1, "Understanding the Bank Tape Reconciliation Process"

• Section 21.2, "Reformatting the Bank Tape"

• Section 21.3, "Refreshing Account Reconciliation Information"

• Section 21.4, "Matching the Bank Tape to the Reconciliation Workfile"

• Section 21.5, "Verifying the Bank Tape Reconciliation"

See Also: Reconciling Voided Payments and Receipts.

21.1 Understanding the Bank Tape Reconciliation Process

If your bank provides you with a tape of the transactions that have cleared your bank account, you can use the JD Edwards EnterpriseOne General Accounting system to automatically reconcile the account. Your bank must provide magnetic media.

Before the system can automatically reconcile transactions, you must convert the data from the bank tape into a flat file called F095051. Then you can run the Custom Reformat UBE program (R09501), which converts the data in the F095051 to the F09505 workfile. For the Custom Reformat UBE program to convert the data, the data must be mapped to certain positions in the F095051 workfile. If it is not, you must create a new custom reformat program.

This graphic illustrates the bank tape reconciliation process:

Figure 21-1 Reconciliation process

Description of ''Figure 21-1 Reconciliation process''

21.2 Reformatting the Bank Tape

This section provides an overview of the program used to reformat bank tapes and discusses how to:

• Run the Custom Reformat UBE program.

• Set processing options for Custom Reformat UBE (R09501).

21.2.1 Understanding the Program Used to Reformat Bank Tapes

After you convert the bank tape to the F095051 flat file, run the Custom Reformat UBE program to convert the information to the F09505 workfile.

For the Custom Reformat UBE program to convert the data to the F09505 workfile, the data must be in these positions in the F095051 workfile:

Positions	Information	Specifications
Positions 1–8	Account ID	Enter the ID with or without leading zeros, right or left justified.
Positions 9–16	Payment number	Enter the payment number right justified with leading zeros.
Positions 17–46	Explanation	Enter the explanation for the payment.
Positions 47–61	Amount	If you are working with a decimal currency, enter the decimal or the whole monetary amount. For example, enter 200.00 CAD as either 200 or 200.00. If you enter the amount as 200000, the program tries to match the amount as 200000.00.
Positions 62–67	Payment date	Gregorian format

If data in the F095051 workfile is not in the positions specified, you must create a new custom reformat program. Use the Custom Reformat UBE program as a template for creating your custom program.

21.2.2 Running the Custom Reformat UBE Program

Select Bank Tape Reconciliation (G09213), Custom Reformat UBE.

21.2.3 Setting Processing Options for Custom Reformat UBE (R09501)

Use these processing option to specify when to purge workfiles.

21.2.3.1 Purge

1. Purge F09505 table before processing.

Specify whether the system purges the F09505 workfile before it runs the Custom Reformat UBE program. Values are:

Blank: Do not purge the F09505 workfile.

1: Purge the F09505 workfile. The system purges this workfile before it updates the F095051 workfile.

2. Purge F095051 table before processing.

Specify whether the system purges the F095051 workfile after it runs the Custom Reformat UBE program. Values are:

Blank: Do not purge the F095051 workfile.

1: Purge the F095051 workfile.

21.3 Refreshing Account Reconciliation Information

Before the system can match the data from the bank tape to your general ledger and reconcile your bank account, you must run the Refresh Reconciliation File program (R09130). Typically, you run this program at the end of each fiscal 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 to the F0911R reconciliation workfile.

See Also: Understanding the Refresh Reconciliation File Program.

21.4 Matching the Bank Tape to the Reconciliation Workfile

This section provides an overview of the program used to reconcile transactions and discusses how to:

• Run the Match Tape File to Recon File program.

• Set processing options for Match Tape File to Recon File (R09510).

21.4.1 Understanding the Program Used to Reconcile Transactions

After you run the Custom Reformat UBE program to convert the bank tape data from the F095015 workfile to the F09505 workfile, run the Match Tape File To Recon File program (R09510) to have the system reconcile the transactions that have cleared your bank account.

The Match Tape File To Recon File program reconciles the transactions by performing these steps:

1. Compares cleared transactions from the bank tape in the F09505 to unreconciled transactions in the F0911R reconciliation workfile.

2. Marks as reconciled all transactions with matching document numbers and amounts, and cleared dates greater than the document date in the F0911R workfile and F0911 table.

3. Prints four reports that show the results of the comparison and list any errors that occurred during the automatic reconciliation.

21.4.2 Running the Match Tape File To Recon File Program

Select Bank Tape Reconciliation (G09213), Match Tape File To Recon File.

21.4.3 Setting Processing Options for Match Tape File To Recon File (R09510)

Use these processing options to specify an account ID and assign a reconciliation number, if applicable.

21.4.3.1 Select

1. Account ID

Specify an account ID to automatically assign to each GL account to maintain an audit trail. The account ID is a unique identification number (account key) and a self-check digit. This number is also called the account serial number, the short account number, and the ID number.

2. Reference 3

Specify whether to automatically assign a reconciliation number to the Reference 3 field. Values are:

Blank: Do not update the Reference 3 field.

1: Update the Reference 3 field with a reconciliation number.

3. Reference 3 Value

Specify the reconciliation number to update in the Reference 3 field. If you leave this processing option blank, the system uses the Next Number Revisions program (P0002) to assign the reconciliation number.

4. Additional Ledger Type to be updated

Specify an additional ledger to update with the same information as the system writes to JD 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.

21.5 Verifying the Bank Tape Reconciliation

Use reports to verify the accuracy of the bank tape reconciliation and, if applicable, to determine the cause of any errors. If errors occur, the system does not mark the transactions as reconciled. You must manually reconcile them using the Manual Reconciliation program (P09131).

When you run the Match Tape File To Recon File program, the system generates these four reports:

Report	Description
Reconciliation Report	Prints a detail line for every transaction and, if errors occurred, prints a message such as: A payment cleared but was not issued. A payment cleared before it was issued. The payment cleared amount is not the same as the payment issued amount.
Cleared Not Issued Report	Summarizes any payments that cleared the bank but do not exist in the F0911 table. Payments appear on this report if: A manual payment number was entered in error. A manual payment was issued but not entered in the system. A MICR (magnetic ink character recognition) code on a payment was damaged and misrepresented on the bank tape.
Cleared Before Issued Report	Summarizes payments that have a cleared date prior to the payment date. Payments appear on this report if: A date was entered in error. The bank cashed a post-dated payment. The payment was released before it was supposed to be released.
Amounts Not Equal Report	Summarizes payments that have cleared the bank, but the cleared amount is not the same as the actual payment amount. Payments appear on this report if: An amount was entered in error. A bank error allowed the payment to be cashed for an amount that was different from the actual amount.