24 Processing Bank Statements Automatically

This chapter contains the following topics:

• Section 24.1, "Understanding the Automatic Bank Statement Process"

• Section 24.2, "Processing Flat Files from the Bank"

• Section 24.3, "Revising Data in the Staging Tables"

• Section 24.4, "Loading Data into the Bank Statement Tables"

• Section 24.5, "Revising Data in the Bank Statement Tables"

• Section 24.6, "Processing Automatic Bank Statements"

• Section 24.7, "Reconciling Unreconciled Bank Statement Transactions"

• Section 24.8, "Purging Bank Statement Tables"

See Also: Setting Up Automatic Bank Statement Processing.

24.1 Understanding the Automatic Bank Statement Process

Automatic bank statement processing is used within the cash management process to reconcile bank account information that you receive from your bank against bank account information in the JD Edwards EnterpriseOne General Accounting system. This automated process saves you time as it creates accounts receivable and general ledger transactions when it reconciles your bank statement against your general ledger. Specifically, the automatic bank statement process creates:

• Entries for receipts, if an item has cleared your bank but has not yet been recorded in the system.

• Journal entries for receipt and payment variances, taxes, bank fees, and transfers.

• Offsetting journal entries for a bank account or an alternate offset account.

• Journal entries for the drafts payable account.

To use automatic bank statement processing, your bank must submit bank statement data electronically using flat files. To convert the data from the flat files to the JD Edwards EnterpriseOne General Accounting system, you must run the Process Automatic Bank Statements Flat File program (R09600), which transfers the data to the staging tables (F09610 and F09611).

Next, you run the Load Bank Statement program (R09612) to transfer the data to the bank statement tables (F09616 and F09617) and use the Revise Automatic Bank Statement program (P09616) to revise any data. Then run the Process Automatic Bank Statement program (R09616) to automatically reconcile data from the electronic bank statement against the bank account information in your general ledger. If there are transactions that do not reconcile automatically and a processing option is set accordingly, the program prints an unreconciled report. You can correct unreconciled exceptions through the Manual Bank Statement Reconciliation program (P09631).

This diagram illustrates the automatic bank statement process:

Figure 24-1 Automatic Bank Statements Process

Description of ''Figure 24-1 Automatic Bank Statements Process''

24.2 Processing Flat Files from the Bank

This section provides an overview of the Process Automatic Bank Statements Flat File program and discusses how to:

• Set processing options for the Process Automatic Bank Statements Flat File program (R09600).

• Run the Process Automatic Bank Statements Flat File program (R09600).

24.2.1 Understanding the Process Automatic Bank Statements Flat File Program

You use the Process Automatic Bank Statements Flat File program (R09600) to convert the data that you receive from the bank into a format that can be read and used by the JD Edwards EnterpriseOne General Accounting system. The bank file must be a text flat file. This program supports the BAI2 and SWIFT data formats. When you run this program, the system reads the data in the bank file and converts it to the appropriate format. It then writes data to the staging tables (F09610 and F09611). The R09600 calls these business functions to process bank statement data depending on the processing option settings:

• Process Automatic Bank Statements Flat File – BAI2 Format (B0900601)

  This business function is used only when the processing options of the R09600 are set to process BAI2 format. This function reads the text file as input, parses out the data and calls the B0900603 to write records to the staging tables. The system logs all errors that this function encounters in the jdedebug.log.

• Process Automatic Bank Statements Flat File – Swift Format (B0900602)

  This business function is used only when the processing options of the R09600 are set to process SWIFT format. This function reads the text file as input, parses out the data and calls the B0900603 to write records to the staging tables. The system logs all errors that this function encounters in the jdedebug.log.

• Process Automatic Bank Statements Flat File Cache Functions (B0900603)

  This business function is used when processing both the BAI2 and SWIFT formats. This function writes the formatted data to the staging tables.

24.2.2 Setting Processing Options for the Process Automatic Bank Statements Flat File Program (R09600)

You use processing options to define default processing information for a program.

24.2.2.1 Processing

1. Bank File Format

Use this processing option to specify the format you are using to process bank statements. Values are stored in UDC 09/FF and include:

BAI2: BAI2 Bank Format

SWIFT: Swift Bank Format

2. Text File Name

Use this processing option to specify the name of the electronic bank statement text file submitted for processing from the bank.

3. Text File Location

Use this processing option to specify the location of the bank statement text file. The system uses this value to locate the file so that it can process bank statement information.

4. Interm Text File Location

Use this processing option to specify the location where the text file is moved if the file is not deleted due to processing errors.

5. Delete Text File?

Use this processing option to specify whether the system deletes the bank-submitted text file after it has been processed and data has been written to the automatic bank statement staging tables. Values are:

1: Yes, delete file.

0: No, do not delete file.

6. Date Format Mask

Use this processing option to specify the date format that the system uses for dates in the electronic bank statement files. For example, use ROA for dates with a YYMMDD format. Values, used in combination, include:

D: Day number, 1 or 2 digits

A: Day number, zero pad to 2 digits

Y: Day number, space pad to 2 digits

M: Month number, 1 or 2 digits

O: Month number, zero pad to 2 digits

N: Month number, space pad to 2 digits

T: Month number, by language, from UDC

B: Abbreviated month name, by language, from UDC

R: 2 digit year

E: 4 digit year

S: Date separator character (slash), from configuration

C: Date separator character (comma), from configure

24.2.3 Running the Process Automatic Bank Statements Flat File Program (R09600)

From the Automatic Bank Statement Processing menu (G09212), select Process Electronic Bank Statements.

24.3 Revising Data in the Staging Tables

This section provides an overview of data revisions to the staging tables and discusses how to:

• Set processing options for Revise Electronic Bank Statement Staging (P09610).

• Revise staging data.

24.3.1 Understanding Data Revisions to the Staging Tables

If you have data that did not map correctly from the flat files to the staging tables (F09610 and F09611) when you ran the Process Automatic Bank Statements Flat File program (R09600), perform one of these tasks:

• Fix the errors and run the R09600 again.

• Revise data in the staging tables using the Revise Electronic Bank Statement Staging program.

  Some fields in the staging tables are disabled and the system does not allow you to revise data. Other fields in the staging tables can be revised. You use a processing option to control which of these fields can be revised.

You can only revise data in the staging tables; you cannot add or delete records. When you revise data that is in error, the Revise Electronic Bank Statement Staging program validates the data to ensure that the beginning balance and transaction amounts equal the ending balance for a bank statement.

The staging tables include audit fields that are used for tracking and security purposes. Records that are revised in the staging tables are tracked for internal audit purposes.

See Also: Field Mapping for Automatic Bank Statement Processing.

24.3.2 Forms Used to Revise Data in Staging Tables

Form Name	Form ID	Navigation	Usage
Work with Electronic Bank Statement Staging	W09610C	Automatic Bank Statement Processing (G09212), Revise Electronic Bank Statement Staging To purge records, on the Work with Electronic Bank Statement Staging form, select Purge Staging to access the Purge Staging Tables program (R09610).	Review a list of bank statements or limit your search to a specific bank statement.
Revise Electronic Bank Statement Staging	W09610A	On the Work with Electronic Bank Statement Staging form, select the bank statement to revise.	Revise data that is in error. You cannot delete or add data on this form. Instead, you must modify the mapping for one of the conversion flows and run it again.

24.3.3 Setting Processing Options for Revise Electronic Bank Statement Staging (P09610)

Use these processing options to restrict users from revising certain fields on the Revise Electronic Bank Statement Staging form.

24.3.3.1 Update

For each processing option on this tab, values are:

• Blank: Do not allow the field to be revised.

• 1: Allow the field to be revised.

The processing options for this program correspond to these fields on the Revise Electronic Bank Statement Staging form:

• Transaction Value Date

• Transaction Debit/Credit Indicator

• Transaction Amount

• Electronic Transaction Type

• Customer Reference

• Bank Reference

• Subfield 9

• Misc Information

24.3.4 Revising Staging Data

Access the Revise Electronic Bank Statement Staging form.

Figure 24-2 Revise Electronic Bank Statement Staging form

Description of ''Figure 24-2 Revise Electronic Bank Statement Staging form''

You cannot use this form to revise reconciled records.

24.3.4.1 Header Fields

Closing Balance

This field is required for the SWIFT format. The SWIFT bank flat file always includes a closing balance amount, which displays in this field.

If the closing balance is not in the bank flat file for the BAI2 format, the system calculates the closing balance in this field but it does not update any tables. This enables the staging tables to remain pristine, exactly as the data appeared in the bank flat file.

Not Loaded, Loaded, All

Select one of these options to identify the transactions that you want to display.

If you revise records that have not been loaded from the staging tables to the bank statement tables, run the Load Bank Statement program again so that the bank statement tables reflect the revisions.

24.3.4.2 Detail Fields

Processing option settings control whether you can revise certain fields in the detail area of this form.

Electronic Transaction Type

Displays the transaction type code from UDC table 09/BA (BAI2 format) or 09/ST (SWIFT format).

Customer Reference

Identifies a reference that appears on the electronic bank statement. The reference in this field links the bank statement transaction to the JD Edwards EnterpriseOne General Accounting system. This could be a deposit number for receipts or a payment number.

Transaction Value Date

Displays the date that the payment amount was debited or credited to the bank account. The transaction value date is not the date the bank reconciled the payment.

Trans Debit/Credit Indicator (transaction debit/credit indicator)

Indicates if a transaction is a debit or credit. Values are:

D: Debit

C: Credit

RC: Cancellation credit

RD: Cancellation debit

If you change the Electronic Transaction Type field and the bank format is BAI2, the system supplies the default value for this field. If the bank format is SWIFT, you must enter the indicator.

Transaction Currency Code

Displays the transaction currency code of the bank account.

In a multicurrency environment, if the bank account currency is foreign, the foreign currency is the transaction currency.

Bank Reference

Identifies a reference that the bank assigns to electronic bank statements.

Reconciliation Code

Displays the code from UDC table 09/RC that identifies whether a transaction has been reconciled. This field is always disabled.

Subfield 9

Displays information that appears on the electronic bank statement.

Misc Information (miscellaneous information)

Displays miscellaneous information that appears on the electronic bank statement.

24.4 Loading Data into the Bank Statement Tables

This section provides an overview of the Load Bank Statement program and discusses how to:

• Run the Load Bank Statement program.

• Set processing options for Load Bank Statement.

24.4.1 Understanding the Load Bank Statement Program

The Load Bank Statement program loads data from the F09610 and F09611 staging tables to the F0916 and F0917 bank statement tables in the JD Edwards EnterpriseOne General Accounting system. This program formats data according to the type of electronic format used (BAI2 or SWIFT) and edits values in the bank statement tables to ensure that they are valid. The Load Bank Statement report (R09612) shows the number of records loaded and, if applicable, not loaded.

To accommodate reference numbers such as those for receipts, payments, and bank wire transfers, the F09611 table includes a Customer Reference (CUSTREF) field that allows up to 16 alphanumeric characters and maps to the Receipt Number (CKNU) field in the F09617 table. The contents of the CUSTREF field also map to the Document Number (DOCM) and Reference 1 (REF1) fields and are truncated in the F09617 table.

If data was successfully mapped to the bank statement tables, the system sets a flag in the header record in the F09610 table that allows the record to be purged. You can set a processing option to purge the data from the staging tables when you run the Load Bank Statement program or you can purge the data at a later time.

If a bank statement does not contain transactions, the bank statement is not mapped to the bank statement tables because no detail records exist. The statement is, however, mapped to the staging tables to provide an exact duplicate of the bank flat file.

24.4.2 Running the Load Bank Statement Program

Select Automatic Bank Statement Processing (G09212), Load Bank Statement.

24.4.3 Setting Processing Options for Load Bank Statement (R09612)

Use this processing option to specify whether to purge records after running the Load Bank Statement program.

24.4.3.1 Process

Purge Staging Tables

Specify whether to purge staging records that have been loaded successfully into the bank statement tables. Values are:

Blank: Do not purge.

1: Purge.

The system runs the Purge Electronic Bank Statement Staging program after it successfully loads the staging records into the bank statement tables.

24.5 Revising Data in the Bank Statement Tables

This section provides an overview of data revisions to the bank statement tables and discusses how to:

• Set processing options for the Revise Automatic Bank Statement program (P09616).

• Revise bank statement data.

• Revise transactions without a customer number.

24.5.1 Understanding Data Revisions to the Bank Statement Tables

If errors occur when you run the Load Bank Statement program, you can use the Revise Automatic Bank Statement program to correct the errors in the bank statement tables (F09616 and F09617).

Some fields in the bank statement tables are disabled and the system does not allow you to revise data. Other fields in the bank statement tables can be revised. You use a processing option to control which of these fields can be revised.

You can revise data only; you cannot add or delete records.

The bank statement tables include audit fields that are used for tracking and security purposes. Records that are revised in the bank statement tables are tracked for internal audit purposes.

24.5.2 Forms Used to Revise Data in the Bank Statement Tables

Form Name	Form ID	Navigation	Usage
Work With Auto Bank Statement	W09616A	Automatic Bank Statement Processing (G09212), Revise Automatic Bank Statement On the Work With Auto Bank Statement form, select Customer/Supplier from the Form menu to revise transactions without a customer number.	Review a list of bank statements or limit your search to a specific bank statement. If a bank statement appears highlighted on this form, the statement contains a data error.
Revise Auto Bank Statement	W09616C	On the Work With Auto Bank Statement form, select the bank statement to revise. Select Bank Stmt Staging from the Row menu to revise data in the staging tables. Select Purge Bank Statement from the Form menu to access the Purge Automatic Bank Statement Tables program (R09615) and purge the F09616 and F09617 tables.	Correct data that is in error. You cannot delete or add records on this form.
Work With Bank Statement Customer/Supplier Entry	W09617A	Automatic Bank Statement Processing (G09212), Automatic Bank Statement Customer/Supplier Entry	Identify automatic batch receipt transactions that are missing a customer number.
Revise Bank Statement Customer/Supplier Entry	W09617B	On the Work With Bank Statement Customer/Supplier Entry form, select a transaction.	Enter a missing customer number for an automatic batch receipt transaction. This form contains detailed information associated with the bank statement transaction. All fields except the Customer/Supplier Number field are for reference purposes only and cannot be changed.

24.5.3 Setting Processing Options for Revise Automatic Bank Statement (P09616)

Use these processing options to restrict users from revising certain fields on the Revise Auto Bank Statement form. This form accesses records in the bank statement tables.

24.5.3.1 Revise Fields

For each processing option on this tab, values are:

• Blank: Do not allow the field to be revised.

• 1: Allow the field to be revised.

The processing options for this program correspond to these fields on the Revise Auto Bank Statement form:

• Cleared/Value Date

• Payment/Receipt Number

• Bank Statement Transaction Code

• Remark

• SWIFT Subfield 9

• SWIFT Miscellaneous Information

24.5.4 Revising Bank Statement Data

Access the Revise Auto Bank Statement form.

Figure 24-3 Revise Auto Bank Statement form

Description of ''Figure 24-3 Revise Auto Bank Statement form''

The system displays transactions by the date they cleared the bank in descending order, with the most recent transaction first. If a data error exists, the system displays a warning message at the top of the Revise Auto Bank Statement form.

For BAI2 electronic format, the system performs an integrity check that validates the data that you revise on this form. The integrity check ensures that the opening balance and the total debit and credit amounts equal the closing balance for an electronic bank statement. If there is a discrepancy in the amounts, the program displays a warning message, such as Closing balance is incorrect. If the bank statement does not include a closing balance, the amount calculated in the Closing Balance field becomes the closing balance.

24.5.4.1 Header Fields

Electronic Bank Account Number

Displays the bank account number from the F09610 staging table. You can revise this field, if necessary.

Display Transactions w/o Customer/Supplier Number for Auto Batch Receipts

Select this check box to display CR transactions that are missing a customer number. When the system processes automatic bank statements, it requires a customer number to create automatic batch receipts and apply them to invoices. If the customer number is blank, the system creates the automatic batch receipts as logged receipts. These receipts are not applied to invoices.

Display All, Display Reconciled Transactions, Display Unreconciled Transactions

Select one of these options to display only reconciled or unreconciled transactions, or both.

24.5.4.2 Detail Fields

Processing option settings control whether you can revise certain fields in the detail area of this form.

Cleared/Value Date

Displays the date that the payment amount was debited or credited to the bank account. The value date is not the date the bank reconciled the payment.

Payment/Receipt Number

Displays a reference number of up to 16 alphanumeric characters and is typically used for receipts, payments, and bank wire transfers.

The Customer Reference (CUSTREF) field in the F09611 staging table maps to this field (CKNU) in the F09617 bank statement table.

Electronic Bank Transaction Type

Displays a user-defined code from UDC table 09/BA (BAI2 bank format) or 09/ST (SWIFT bank format) that identifies the transaction type code.

Bank Statement Transaction Code

Displays a user-defined transaction type, such as CK, CR, DP, JE, or RJE.

24.5.5 Revising Transactions without a Customer Number

Access the Work With Bank Statement Customer/Supplier Entry form.

Figure 24-4 Work With Bank Statement Customer/Supplier Entry form

Description of ''Figure 24-4 Work With Bank Statement Customer/Supplier Entry form''

This form displays CR transactions only. The customer number is required to create automatic batch receipts and apply them to invoices; it is not required for all CR transactions. If the customer number is blank and the reconciliation rules are set up to create automatic batch receipts, the system creates a logged receipt and does not apply it to invoices.

Show Trans w/o Cust/Suppl No for Auto Batch Receipts (show transactions without customer/supplier number for automatic batch receipts)

Select this option to display CR transactions in which the customer number is missing. If a number is missing for a transaction and the transaction is marked as an automatic batch receipt, the Customer/Supplier Flag field on this form is 1.

Show All Transactions

Select this option to display CR transactions with and without customer numbers.

If the number exists for a transaction and the transaction is marked as an automatic batch receipt, the Customer/Supplier Flag field on this form is blank.

24.6 Processing Automatic Bank Statements

This section provides overviews of the automatic bank statement program and the automatic bank statement reports and messages and discusses how to:

• Set processing options for Process Automatic Bank Statement (R09616).

• Run the Process Automatic Bank Statement program.

24.6.1 Understanding the Automatic Bank Statement Program

After you run the Load Bank Statement program and, if necessary, revise data in the bank statement tables, you can run the Process Automatic Bank Statement program. This program reconciles data that has cleared your bank against data that has been entered in the JD Edwards EnterpriseOne General Accounting system. Specifically, the Process Automatic Bank Statement program:

• Reconciles data based on the reconciliation rules that you set up for transaction types (UDC table 09/TT).

• Uses the offset business unit code (UDC table 09/BU) that is assigned to transaction code JE (on the Revise Transaction Type Cross Reference form) to determine whether to create an offsetting journal entry using the business unit of an alternate offset account or a bank account.

• Processes the transactions in the F09616 and F09617 tables and reconciles them to the transactions in the F0911 table.

  
  

  Note: The Process Automatic Bank Statement program does not automatically reconcile manual journal entries entered in the JD Edwards EnterpriseOne General Accounting system; you must manually reconcile them. There is no reference number field to connect these transactions from the F09616 and F09617 tables to the F0911 table.

  
  

• Runs the Automatic Batch Receipt program (R03B551) to create batch receipts for CR transactions that are marked for automatic batch receipts.

  The system creates automatic batch receipts and attempts to apply the receipts to invoices if a customer number is assigned to a transaction. If the customer number is blank, the system creates a logged receipt, which is not applied to invoices.

  The Process Automatic Bank Statement program populates the GLR3 (Reference 3) field in the F0911 table with the batch number of the bank statement, not the batch number of the receipt or deposit number. If you want the bank statement number to appear on transactions, you must select a check box for the reconciliation rule on the Revise Auto Reconciliation Group form.

  For multicurrency processing, the program creates batch receipts only if the receipt and invoice currencies are the same.

• For transactions with customer numbers, runs the Apply Receipts to Invoices program (R03B50) to apply the batch receipts to invoices.

  If there is no matching invoice, the program creates an invoice with type RU.

• Updates records in the staging tables as reconciled, unless the records were purged earlier in the process.

  In a multicurrency environment, the bank account format uses the transaction currency of the bank account. If the bank account currency is foreign (not in the base currency of the company), the foreign currency is the transaction currency. The Process Automatic Bank Statement program cannot calculate gains and losses on foreign currency bank accounts because there are no domestic amounts on the transactions.

After you run the Process Automatic Bank Statement program, you can review transactions on the Reconcile Bank Statement Detail to Account Ledger form and manually reconcile any exceptions that did not reconcile and, if applicable, unreconcile reconciled transactions.

24.6.1.1 Journal Entry Reconciliation Only

Transaction type RJE (or other user-specified code with a special handling code 02) from UDC table 09/TT is used for bank statement transactions that already exist as journal entry transactions in the JD Edwards EnterpriseOne General Accounting system. For these transactions, the Automatic Bank Statement process does not create a duplicate transaction but, instead, reconciles the existing journal entry against the general ledger. Typically, this transaction type code is used for bank transactions such as bank fees and transfer fees.

24.6.2 Understanding Automatic Bank Statement Reports and Messages

Based on processing option settings, the Process Automatic Bank Statement program produces an Auto Bank Statement Processing report (R09616) that includes these reports:

• Summary Report - a summary report that shows the number of reconciled and unreconciled transactions as well as summary information about any new batches created.

• Reconciled Transactions report - a detailed report that shows the total amount of reconciled transactions as well as each reconciled transaction.

• Unreconciled Transactions report - a detailed report that shows the total amount of unreconciled transactions as well as each unreconciled transaction.

The reason codes and corresponding text for unreconciled messages are set up in UDC table 09/UR.

This list shows examples of unreconciled messages that might appear on the Reconcile Bank Statement Detail to Account Ledger form or on the Unreconciled Transactions report that is generated by the Process Automatic Bank Statement program:

Message	Description
Amt Not Equal (amount not equal)	This message appears if the program detects a difference between the bank statement and general ledger amounts and either of these applies: A tolerance limit was not set up for the reconciliation rule. The amount set up for the reconciliation rule is not within the tolerance limit set up.
No Matching Payment in G/L (no matching payment in general ledger)	This message appears if there is no matching payment in the F0911 table. You must create a manual payment and post it to the general ledger.
No Matching Receipt in G/L (no matching receipt in general ledger)	This message appears if there is no matching receipt in the F0911 table and the reconciliation rule is not set to create automatic batch receipts. Do one of these: Create a manual receipt, post it to the general ledger, and run the Process Automatic Bank Statement Program again. Revise the reconciliation rule to create auto batch receipts and run the Process Automatic Bank Statement program again.
Invalid G/L Offset Account (invalid general ledger offset account)	This message appears if the offsetting account for a transaction is not valid.
Auto Batch Rec Not Created (automatic batch receipt not created)	This message appears if the reconciliation rule for a CR transaction was not set to automatically create a batch receipt.

24.6.3 Running the Process Automatic Bank Statements Program

Select Automatic Bank Statement Processing (G09212), Process Automatic Bank Statement.

Do not use data selection for the Process Automatic Bank Statement program. Instead, use the processing options to select data by bank statement number, GL bank account number, and date range.

24.6.4 Setting Processing Options for Process Automatic Bank Statements (R09616)

Use these processing options to select the bank statement to reconcile and the reports to print.

24.6.4.1 Select

Bank Statement Number

Specify the bank statement number to reconcile. If you leave this processing option blank, the program processes all bank statements in the F09616 table.

G/L Bank Account Number (general ledger bank account number)

Specify the general ledger bank account number to reconcile. If you leave this processing option blank, the program processes all general ledger bank accounts in the F09616 table.

Beginning Date

Specify the beginning date in which to reconcile records. If you leave this field blank, the program processes all records in the F09616 table through the ending date. If you leave the Ending Date processing option blank, the program processes all records in the table.

Ending Date

Specify the ending date in which to reconcile records. If you leave this field blank, the program processes all records in the F09616 as of the beginning date. If you leave the Beginning Date processing option blank, the program processes all records in the table.

Additional Ledger Type

Specify an additional ledger to update with the same information as the system writes to tables for the AA ledger. If you leave this processing option blank, the system updates 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 statements.

24.6.4.2 Print

For these processing options, values are:

Blank: Print

1: Do not print

Suppress Printing Unreconciled Report, Suppress Printing Reconciled Report, and Suppress Printing Summary Report

Specify whether to print the report. To print all reports, leave these processing options blank.

24.7 Reconciling Unreconciled Bank Statement Transactions

This section provides an overview of the Manual Bank Statement Reconciliation program and discusses how to:

• Set processing options for Manual Bank Statement Reconciliation (P09631).

• Reconcile unreconciled bank transactions.

24.7.1 Understanding the Manual Bank Statement Reconciliation Program

After you run the Process Automatic Bank Statement program, you can use the Manual Bank Statement Reconciliation program (P09631) to revise unreconciled as well as reconciled transactions. This program accesses the Reconcile Bank Statement Detail to Account Ledger form. On this form, you can reconcile bank statement detail information from the F09617 table. You can also view and reconcile the corresponding general ledger information from the F0911 table in the Account Ledger region of the form. The Account Ledger region displays posted transactions only.

If the Process Automatic Bank Statement program is unable to process a transaction, the transaction appears as unreconciled (the Recon Code field is blank) on the Reconcile Bank Statement Detail to Account Ledger form with the reason why it was not reconciled.

See Understanding the Automatic Bank Statement Program.

To correct unreconciled transactions, you can exit to various forms from the Reconcile Bank Statement Detail to Account Ledger form. From the various forms, you can:

• Create manual receipts as well as manual payments with or without vouchers.

  When you create a manual receipt or payment, the Payment ID is stored in the F09617 table and the corresponding bank statement transaction is marked as reconciled. The system assigns the batch number of the bank statement to the manual payment and receipt batches. When you post the receipt or payment, the system updates the RCND (Reconciled) field and, if a processing option for the Process Automatic Bank Statement program is set, updates the GLR3 (Reference 3) field in the F0911 table with a next number.

• Revise automatic receipts created by the Process Automatic Bank Statement program.

• Create journal entries.

  When you create a journal entry, the system assigns a batch number from next numbers.

• Post unposted transactions.

After you correct the unreconciled transaction, you can either manually reconcile the bank statement transaction on the Reconcile Bank Statement Detail to Account Ledger form or rerun the Process Automatic Bank Statements program to have the system automatically reconcile it. When a transaction is reconciled, the corresponding F0911 transaction appears as reconciled in the Account Ledger region of the form.

You can use the Reconcile Bank Statement Detail to Account Ledger form to reconcile unreconciled transactions, as well as unreconcile reconciled transactions, if necessary.

After all transactions for a bank statement are reconciled in the F09617 table, the system marks the corresponding staging records as reconciled in the F09611 staging detail table unless either of these conditions exist:

• The records were purged from the F09610 and F09611 staging tables.

• The records were in error and did not get loaded to the F09611 staging detail table.

After all transactions in the F09611 table are reconciled, the system updates the header record in the F09610 table as reconciled.

24.7.2 Form Used to Manually Reconcile Bank Statement Transactions

Form Name	Form ID	Navigation	Usage
Reconcile Bank Statement Detail to Account Ledger	W09631A	Automatic Bank Statement Processing (G09212), Manual Bank Statement Reconciliation	Reconcile unreconciled transactions for a bank account and, if applicable, unreconcile reconciled transactions.
Reference 3	W09631B	Click Reconcile/Unreconcile on the Reconcile Bank Statement Detail to Account Ledger form.	Enter the cleared date and the reference number. The system updates the AA ledger and the additional ledger type that you specify in the processing options with the date and reference number that you enter. Note: The system displays the Cleared Date field only when you set the Cleared Date processing option to 1.

24.7.3 Setting Processing Options for Manual Bank Statement Reconciliation (P09631)

Use these processing options to specify default values on the Reconcile Bank Statement Detail to Account Ledger form.

24.7.3.1 Defaults

Suppress Reference 3 Update

Specify whether to update the Reference 3 (GLR3) field in the F0911 table with a number from next numbers. Values are:

Blank: Do not suppress the update of Reference 3. That is, update the Reference 3 field.

1: Suppress the update of Reference 3. That is, do not update the Reference 3 field.

Note: You can override this processing option value for transactions on the Reconcile Bank Statement Detail to Account Ledger form.

Reconciled Code

Specify the reconciliation code from UDC table 09/RC to use as the default value on the Reconcile Bank Statement Detail to Account Ledger form. If you leave this processing option blank, the system uses R (Reconciled One for One Match) as the default value.

Additional Ledger Type to Update

Specify an additional ledger type to update in the F0911 table. If you leave this processing option blank, the system updates only ledger type AA in the F0911 table.

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

Cleared Date

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

24.7.4 Reconciling Unreconciled Bank Transactions

Access the Reconcile Bank Statement Detail to Account Ledger form.

Figure 24-5 Reconcile Bank Statement Detail to Account Ledger form - Search Bank Statement region

Description of ''Figure 24-5 Reconcile Bank Statement Detail to Account Ledger form - Search Bank Statement region''

Account Number

Enter the general ledger number for the bank account.

From Date and Thru Date

Enter the beginning and ending dates in which to view transactions for the bank account.

Reconciliation Status

Select Reconciled, Unreconciled, or All from the drop down menu to display the transactions that meet your criteria.

24.7.4.1 Bank Statement Detail

The system displays bank statement transactions from the F09617 table that correspond to the account number and the date range specified in the header area of the form.

Figure 24-6 Reconcile Bank Statement Detail to Account Ledger form: Bank Statement Detail

Description of ''Figure 24-6 Reconcile Bank Statement Detail to Account Ledger form: Bank Statement Detail''

To correct an unreconciled transaction:

1. Select the transaction.

2. Scroll to the top of the Reconcile Bank Statement Detail to Account Ledger form.

3. From the Row menu, select the appropriate program.

   For example, to correct a transaction in which the payment has not been posted, select the transaction and then select Batch Review from the Row menu. On the Work With Batches form, select the unposted batch and then select Post by Batch from the Row menu.

   
   

   Note: If you use the Row menu to exit to a program and manually create a receipt or payment, the system assigns the bank statement batch number to the batch that you created. For manual journal entries, the system assigns a new batch number instead.

   
   

Statement D and Statement D...

Displays bank statement transactions based on the beginning and ending dates that you entered in the header area of the form.

To override the those dates, enter dates in these fields and click Find. The system displays transactions in the F09617 table that correspond to the statement dates that fall within the date or range of dates that you enter. To further limit the transactions that the system displays, enter values in the QBE row.

To display the corresponding F0911 information, which is based on the general ledger date, leave the From Date and Thru Date fields blank in the Account Ledger region, or broaden the date range if necessary, and click Find.

No. of Unreconciled Bank Statement Transactions (number of unreconciled bank statement transactions)

Displays the number of unreconciled transactions in the F09617 table that meet the criteria that you specified.

Recon Code (reconciliation code)

Displays the code from UDC table 09/RC that is used when you manually reconcile a transaction. The default value for this field is set in a processing option for the Manual Bank Statement Reconciliation program. You can override the default value.

Payment/Receipt Number

Displays the value stored in the CUSTREF (Customer Reference) field in the F09611 table which is mapped to the CKNU (Check Number) field in the F09617 table. This field allows up to 16 alphanumeric characters and accommodates reference numbers for receipts, payments, bank wire transfers, and so on.

Update Reference 3 Field

Select this check box to optionally update the Reference 3 field on journal entries that you manually reconcile. When you click Reconcile/Unreconcile to update the journal entries, the Reference 3 form appears with a reference number generated by next numbers. You can override the number with the bank statement number or any other number. Then, you can choose to update each selected F0911 record with the same reference number or with a different reference number. If you choose a different reference number, the system displays the Reference 3 form for each transaction.

Reconcile/Unreconcile

Select the transactions to reconcile and click the Reconcile/Unreconcile button, which is a toggle. For transactions that you manually reconcile, the system updates either or both of these tables:

F09617: Updates the reconciliation code and displays the reconciled transactions. If the transactions remain in the F09611 staging tables, they are updated as reconciled.

F0911: Updates the reconciliation code and, if applicable, the Reference 3 field and displays the reconciled transactions in the Account Ledger region of the form.

After you reconcile all unreconciled transactions in the F09617 and F0911 tables, the system marks the header record in the F09616 table with D (reconciled). If all transactions in the F09611 table are reconciled, the system updates the header record in the F09610 table with D.

24.7.4.2 Account Ledger

The system displays general ledger transactions from the F0911 table that correspond to the account number and date range specified in the header area of the form.

Figure 24-7 Reconcile Bank Statement Detail to Account Ledger: Account Ledger

Description of ''Figure 24-7 Reconcile Bank Statement Detail to Account Ledger: Account Ledger''

G/L Date and G/L Date

Displays general ledger transactions based on the beginning and ending dates that you entered in the header area of the form.

To override the dates in the header, enter dates in these fields and click Find. The system displays transactions in the F0911 table that correspond to the general ledger dates that fall within the date or range of dates that you enter.

To further limit the transactions that the system displays, enter values in the QBE row.

No. of Unreconciled G/L Transactions (number of unreconciled general ledger transactions)

Displays the number of unreconciled transactions in the F0911 table that meet the criteria that you specified.

Debit and Credit

Displays the unreconciled debit and credit amounts from the F0911 table for the bank account.

Book Balance

Displays the year-to-date balance amount from the F0902 table.

Bank Balance

Displays the ending balance amount of the latest bank statement for the account from the F09616 table. This ending balance is used as the bank account opening balance by the Cash Forecast Analysis program when calculating cash forecast amounts for an account, if a processing option is set accordingly.

See Understanding Bank Account Opening Balances.

24.7.4.3 Cleared Date

Access the Reference 3 form by clicking Reconcile/Unreconcile.

Note: You can update all selected records with one reference number and cleared date, or you can update each record with a different reference number and cleared date.

Reference 3 – Account Reconciliation

The Reference 3 form appears with a reference number generated by next numbers. You can override the number with the bank statement number or any other number. Then, you can choose to update each selected F0911 record with the same reference number or with a different reference number. If you choose a different reference number, the system displays the Reference 3 form for each transaction.

Cleared Date

Enter the cleared date from your bank statement.

The system updates the cleared date field in the F0911 table for the AA ledger and for the additional ledger that you specified in the processing options.

24.8 Purging Bank Statement Tables

This section provides an overview of the bank statement purge programs and discusses how to:

• Purge staging tables.

• Set processing options for Purge Staging Tables.

• Purge automatic bank statement tables.

• Set processing options for Purge Automatic Bank Statement Tables.

24.8.1 Understanding the Bank Statement Purge Programs

After bank statement records are reconciled, you can purge the records by running these programs:

• Purge Staging Tables (R09610).

• Purge Automatic Bank Statement Tables (R09615).

24.8.1.1 Purge Staging Tables

The Purge Staging Table program purges data in the F09160 and F09611 staging tables. If the data was successfully mapped to the bank statement tables, the Load Bank Statement program sets a flag in the header record in the F09610 table that allows the record to be deleted in both tables. Alternatively, you can set a processing option for the Load Bank Statement program to purge these tables.

24.8.1.2 Purge Automatic Bank Statement Tables

The Purge Automatic Bank Statement Tables program purges data in the F09616 and F09617 bank statement tables. If the data was successfully reconciled, the RECCODE field in the header record in the F09616 table is set to D (reconciled), which allows the record to be deleted in both tables. Alternatively, you can select Purge Bank Statement from the Form menu on the Revise Auto Bank Statement form.

24.8.2 Purging Staging Tables

Select Purges (G09215), Purge Staging Tables.

24.8.3 Setting Processing Options for Purge Staging Tables (R09610)

Use these processing options to select the date range and to specify whether to print detailed record information on the report.

24.8.3.1 Select

Bank Statement Beginning Date and Bank Statement Ending Date

Specify the date range in which to purge records from the F09610 and F09611 staging tables. The system uses these processing options to locate the available records.

If you leave the beginning date blank, all records with a statement date that is on or before the ending date are purged. If you leave the ending date blank, all records with a statement date that is on or after the beginning date are purged. If you leave both the beginning and ending dates blank, all records are purged.

24.8.3.2 Print

Report Output

Specify whether to print the purged records on the Purge Staging tables report (R09610). Values are:

Blank: Do not print records. The report prints the bank statement number, general ledger bank account number, statement date, number of detail records, and the total number of records purged.

1: Print records. The report prints detailed information about the records purged.

24.8.4 Purging Automatic Bank Statement Tables Program

Select Purges (G09215), Purge Automatic Bank Statement Tables.

24.8.5 Setting Processing Options for Purge Automatic Bank Statement Tables (R09615)

Use these processing options to select the date range and the type of information to print on the purge report.

24.8.5.1 Select

Bank Statement Beginning Date and Bank Statement Ending Date

Specify the date range in which to purge records from the F09616 and F09617 bank statement tables. The system uses these processing options to locate the available records.

If you leave the beginning date blank, all records with a statement date that is on or before the ending date are purged. If you leave the ending date blank, all records with a statement date that is on or after the beginning date are purged. If you leave both the beginning and ending dates blank, all records are purged.

24.8.5.2 Report Output

Report Output

Specify whether to print the purged records on the Purge Auto Bank Statement Tables report (R09615). Values are:

Blank: Do not print records. The report prints the bank statement number, general ledger bank account number, statement date, number of detail records, and the total number of records purged.

1: Print records. The report prints detailed information about the records purged.