7 Setting Up Automatic Bank Statement Processing

This chapter contains the following topics:

The automatic bank statement process is an efficient way in which to reconcile your bank statement transactions against transactions in the JD Edwards EnterpriseOne General Accounting system.

7.1 Setting Up UDCs for Automatic Bank Statement Processing

This section provides an overview of UDC setup for automatic bank statement processing and discusses how to set up UDCs for automatic bank statements.

7.1.1 Understanding UDC Setup for Automatic Bank Statement Processing

You set up user-defined codes (UDCs) to facilitate the entry and reconciliation of transactions for automatic bank statement processing. The UDCs minimize the amount of exception processing and maximize the ability to automatically create and reconcile transactions for your bank accounts. The automatic bank statement programs use values from 11 UDC tables.

Caution:

Do not add codes to the UDC tables. The program logic for the automatic bank statement programs depends on certain codes and special handling code values to process information correctly. You can change the description and codes for UDCs that are not hard coded; however, you cannot change what the code represents nor can you change the special handling code value, if one exists.

For example, the code 2 in UDC table 09/DT is not hard coded but it has a special handling code of 20. You can change the code from 2 to 4 and the description from Today's Date to Current Date, but you cannot change the date that the code represents nor can you change the special handling code (20).

7.1.2 Setting Up UDCs for Automatic Bank Statements

This section discusses how to set up UDCs for automatic bank statements.

7.1.2.1 09/TT - Auto Bank Statement Transaction Types

UDC table 09/TT contains transaction types for bank account transactions.

The transaction types in UDC table 09/TT are user-defined and might include:

Code Description Special Handling Code
CK Payment Clear 08
CR Cash Receipt Clear 04
DP Draft Paid Clear 07
JE Journal Entry 01
RJE Reconcile Only Journal Entry 02

Note:

The transaction type code RJE (or other user-specified code with special handling code 02) is used for bank transactions that already exist as journal entries 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. You can use this transaction type code for bank transactions such as bank fees and transfer fees.

7.1.2.2 09/DT - Default Date

UDC table 09/DT contains codes that specify the default date to use for types of transactions. These codes are user-defined. This table lists the default date codes in UDC table 09/DT:

Code Description Special Handling Code
1 Bank Statement Date 10
2 Today's Date 20
3 Cleared Date 30

7.1.2.3 09/FT - E Bank Statement Format

UDC table 09/FT contains the codes for the two formats that are available for electronic bank statements.

This table lists the hard-coded format codes in UDC table 09/FT:

Code Description Special Handling Code
SWIFT Swift MT 940 1
BAI2 BAI2 2

7.1.2.4 09/TD - Transaction Debit/Credit Indicator

UDC table 09/TD contains debit and credit indicators, which specify the sign for transaction amounts.

The debit/credit indicator codes in UDC table 09/TD are hard-coded:

  • C: Credit

  • D: Debit

  • RC: Cancellation Credit

  • RD: Cancellation Debit

7.1.2.5 09/RR - Reconciled Reason Code

UDC table 09/RR contains reason codes that explain why certain transactions were reconciled. For example, the system uses reason code VWT if an amount is different than expected, but falls within the tolerance limit. The system displays the description of these codes on the Revise Auto Bank Statement and Reconcile Bank Statement Detail to Account Ledger forms.

This table lists the hard-coded reconciled reason codes in UDC table 09/RR:

Code Description Special Handling Code
ABR Auto Batch Receipts Created 10
DPC Draft Paid Clear 20
CR Consolidated Receipts 30
VWT Variance Within Tolerance 40

7.1.2.6 09/RU - Reconciled Code

UDC table 09/RU contains codes that indicate whether a transaction is reconciled. You can choose which transactions to view on the Reconcile Bank Statement Detail to Account Ledger form by specifying a reconciled code. These codes are user-defined.

This table lists the reconciled codes in UDC table 09/RU:

Code Description Special Handling Code
1 Unreconciled 10
2 Reconciled 20
3 All 30

7.1.2.7 09/UR - Unreconciled Reason Code

UDC table 09/UR contains reason codes that explain why certain transactions were not reconciled. The system displays these codes and their descriptions on the Revise Auto Bank Statement and Reconcile Bank Statement Detail to Account Ledger forms. The codes are stored in the F09617 table.

This table lists the hard-coded unreconciled reason codes in UDC table 09/UR:

Code Description Special Handling Code
ANC Auto Batch Rec Not Created 20
ANE Amounts Not Equal 30
CLD Cleared Date Prior to G/L Date 40
CNV Customer/Supplier Not Valid 50
DPN Draft Paid Not Found in F0411 120
IAC Invalid Account 60
IGO Invalid G/L Offset Account 160
IVA Invalid Variance Account 150
JE JE Creation Not Successful 70
NCS No Customer/Supplier Number 80
NMP No Matching Payment in G/L 90
NMR No Matching Receipt in G/L 100
OEN Offsetting Entries Not Created 140
PEC Posting Edit Code Invalid 110
VNT Variance Not Within Tolerance 130

7.1.2.8 09/BA - BAI2 Transaction Types

UDC table 09/BA contains the transaction types that are defined by BAI. The BAI2 format is the North American format used for electronic banking.

Transaction types for the BAI2 format are populated in the F09611 staging table. You use the Bank Account Cross Reference program to map the BAI2 transaction types to the EnterpriseOne transaction types in UDC table 09/TT.

This table lists the ranges that identify the types of BAI2 transactions:

Transaction Type Range Description
001–099 Account level status codes
100 Total credits summary
101–399 Credit summary and detail codes
400 Total debits summary
401–699 Debit summary and detail codes
700–899 Range not used by BAI
900–919 Account level status codes (customized UDCs)
920–959 Credit summary and detail codes (customized UDCs)
960–999 Debit summary and detail codes (customized UDCs)

All codes in UDC table 09/BA are hard-coded. You can view a complete list of the codes in documents that are provided by BAI. The JD Edwards EnterpriseOne software also contains the BAI2 codes. Ensure that you update the UDC table manually if BAI makes changes to the transaction types.

Examples of BAI2 transaction types include:

Code Description 01 Description 02 Special Handling Code
010 Opening Ledger Status None
105 Credits Not Detailed Summary CR
224 Commission Detail CR
400 Total Debits Summary DB
506 Book Transfer Debit Detail DB
622 Broker Debit Detail DB

7.1.2.9 09/ST - SWIFT Transaction Types

UDC table 09/ST contains the transaction types that are defined by the Society for Worldwide Interbank Financial Telecommunications (SWIFT). The SWIFT format is the European format for electronic banking. These transaction types are populated in the F09611 staging table. You use the Bank Account Cross Reference program (P09621) to map the SWIFT transaction types to the transaction types in UDC table 09/TT.

All codes in UDC table 09/ST are hard-coded. You can view a complete list of the codes in documents that are provided by SWIFT. The JD Edwards EnterpriseOne software also contains the codes. Ensure that you update the UDC table manually if SWIFT makes changes to the transaction types.

Examples of SWIFT transaction types include:

Code Description 01 Description 02
BOE Bill of Exchange None
COL Collections Used with principal amount
SEC Securities Used with principal amount
001 Personal Cheque Other than euro cheque
076 Telephone Order None
204 Letter of Credit None

7.1.2.10 09/BU - Offset Business Unit

UDC table 09/BU contains the codes that identify which business unit to use for offsetting accounts when the Process Auto Bank Statement program creates journal entries. When you create a cross reference for transaction code JE (journal entries), you specify whether the business unit is for a bank account or an alternate offset account.

Code Description Special Handling Code
1 Bank Account Business Unit 10
2 Alternate Offset Business Unit 20

7.2 Setting Up Cross References

This section provides an overview of cross references and discusses how to:

  • Set up a cross reference for a bank account.

  • Set up a cross reference for a transaction type.

7.2.1 Understanding Cross References

Bank account numbers and transaction types that appear on your electronic bank statements must be mapped to account and transaction types in the JD Edwards EnterpriseOne General Accounting system.

You use the Bank Account Cross Reference program (P09621) to cross reference (or map) an account number on an electronic bank statement to an EnterpriseOne account number. When you add or revise information, the system updates the Bank Account Cross References table (F09621).

You use the Transaction Type Cross Reference program (P09622) to cross reference a transaction type to the EnterpriseOne transaction type. When you add or revise information, the system updates the Transaction Type Cross References table (F09622).

The F09621 and F09622 tables include audit fields that are used for tracking and security purposes. The fields track the User ID, program ID, machine name, date, and time.

7.2.2 Forms Used to Set Up Cross References

Form Name Form ID Navigation Usage
Work With Bank Account Cross Reference W09621A Automatic Bank Statement Setup (G09214), Bank Account Cross Reference Review electronic bank information for your bank accounts, select a bank account to revise, or delete a bank account.
Revise Bank Account Cross Reference W09621B On the Work With Bank Account Cross Reference form, click Add.

On the Revise Bank Account Cross Reference form, you can select G/L Bank Acct from the Form menu to search for an existing bank account or set up a new one.

Set up cross references for bank accounts.
Work With Transaction Type Cross Reference W09622A Automatic Bank Statement Setup (G09214), Transaction Type Cross Reference Review transaction types that appear on electronic bank statements.
Revise Transaction Type Cross Reference W09622C On the Work With Transaction Type Cross Reference form, click Add. Set up cross references for transaction types.

7.2.3 Setting Up Cross References for a Bank Account

Access the Revise Bank Account Cross Reference form.

Electronic Bank Account Number

Enter the bank account number as it appears on the electronic bank statement.

Electronic Format Indicator

Enter a format code from UDC table 09/FT. Values are:

BAI2

SWIFT

Format codes are hard-coded.

EnterpriseOne Account Number

Enter the GL account number of the bank account on the electronic bank statement.

Alternate Offset Business Unit

Enter the business unit to use to create journal entries for offset accounts. This is the business unit for a bank account or an alternate offset account. (You identify which offset business unit to use as well as the object and subsidiary accounts on the Revise Transaction Type Cross Reference form.)

This field enables you to assign a different offset business unit to each transaction type. If you leave this field blank, the system uses the business unit of the bank account as the default value.

The Automatic Bank Statement Process program creates offset accounts for JE transactions only.

7.2.4 Setting Up a Cross Reference for a Transaction Type

Access the Revise Transaction Type Cross Reference form.

Figure 7-1 Revise Transaction Type Cross Reference form

Description of Figure 7-1 follows
Description of ''Figure 7-1 Revise Transaction Type Cross Reference form''

Electronic Format Indicator

Enter the format code from UDC table 09/FT for the transaction type.

Electronic Transaction Type

Enter the transaction type code from UDC table 09/ST (SWIFT) or 09/BA (BAI2) for the format that you entered in the Electronic Format Indicator field. These two fields work with each another.

Debit/Credit Indicator

If you enter a format and an electronic transaction type code for BAI2, the system displays the Debit/Credit Indicator field on the form. The value in the Special Handling Code field of the transaction type determines whether the amount is a debit (D) or credit (C).

If you enter a format for SWIFT, the bank flat file for SWIFT already includes the debit or credit indicator so the Debit/Credit Indicator field does not appear on the form.

Transaction Code

Enter a value from UDC table 09/TT. If you enter JE in this field, the Offset Business Unit, Object Account, and Subsidiary fields appear on the form.

Offset Business Unit

Enter a value from UDC table 09/BU to specify which business unit to use to create journal entries for offset accounts. Values are:

1: Bank Account Business Unit.

2: Alternate Offset Business Unit.

If you enter 2, you must specify the alternate offset business unit on the Revise Bank Account Cross Reference form. If you do not, the system uses the business unit of the bank account.

Object and Subsidiary

Enter the object account and, optionally, the subsidiary for the offsetting journal entry.

7.3 Setting Up Automatic Bank Account Reconciliation

This section provides an overview of reconciliation for automatic bank accounts and discusses how to:

  1. Set up automatic reconciliation rules.

  2. Assign automatic reconciliation rules to groups.

  3. Assign groups to automatic bank statement accounts.

7.3.1 Understanding Automatic Bank Account Reconciliation

To automatically reconcile electronic bank statements, the Process Automatic Bank Statement program uses:

  • Reconciliation rules

  • Reconciliation groups

  • Bank statement accounts

7.3.1.1 Reconciliation Rules

Reconciliation rules enable you to maximize the number of transactions that the system automatically reconciles and minimize any manual reconciliation. The Process Automatic Bank Statement program uses default rules to reconcile receipts, payments, and drafts in the F0911 table that have cleared your bank account.

Each reconciliation rule can have up to five transaction types. Each tab on the Revise Auto Reconciliation Rules corresponds to a transaction type. You assign a reconciliation code from UDC table 09/RC to each transaction type or let the system assign the default value R (Reconciled One for One Match). The reconciliation rules that you define for a transaction type consists of variance accounts, document types, tolerance limits, and so on. The system stores reconciliation rules in the F09626 table.

7.3.1.2 Reconciliation Groups

After you set up reconciliation rules, you set up reconciliation groups and assign rules to the groups. You can assign only one reconciliation rule to a group; however, you can use the same reconciliation rule for multiple groups.

The Process Automatic Bank Statement program accesses the reconciliation rule assigned to the group to retrieve the bank statement account. The system stores reconciliation groups in the F09627 table.

7.3.1.3 Automatic Bank Statements

Next, you assign a reconciliation group to the GL bank account. You can assign only one reconciliation group to an account; however, you can use the same group for multiple bank accounts. The bank account follows the reconciliation rule of the group, unless you override that information when you set up the automatic bank statement account. The system stores automatic bank statement accounts in the F09628 table.

7.3.2 Forms Used to Set Up Automatic Bank Account Reconciliation

Form Name Form ID Navigation Usage
Work With Auto Reconciliation Rules W09626A Automatic Bank Statement Setup (G09214), Automatic Reconciliation Rules Setup Review reconciliation rules, select an existing rule to revise, or copy or delete an existing rule.
Revise Auto Reconciliation Rules W09626D On Work With Auto Reconciliation Rules form, click Add or select an existing rule to revise. Set up a reconciliation rule.
Work With Auto Reconciliation Groups W09627A Automatic Bank Statement Setup (G09214), Automatic Reconciliation Group Review reconciliation groups, select an existing group to revise, or copy or delete an existing group.
Revise Auto Reconciliation Group W09627B On Work With Auto Reconciliation Groups form, click Add or select an existing group to revise. Set up a reconciliation group.
Work With Auto Bank Statement Account Setup W09628A Automatic Bank Statement Setup (G09214), Automatic Bank Statement Account Setup Review automatic bank statement accounts, select an existing account to revise, or copy or delete an existing account.
Revise Auto Bank Statement Account Setup W09628B On the Work With Auto Bank Statement Setup form, click Add or select an existing account to revise. Set up an automatic bank statement account.

7.3.3 Setting Up Automatic Reconciliation Rules

Access the Revise Auto Reconciliation Rules form.

Reconciliation Rule

Enter the name of the reconciliation rule for which you want to assign transaction type rules.

7.3.3.1 Payment Clear

Select this tab if the reconciliation rule applies to payments that have cleared the bank.

Payment Variance

Select this check box for payment variances. If selected, the system displays the Variance Account, Variance Date Mode, Tolerance Limit, and Variance Doc Type fields, which are required.

Payment Reconciliation Date Exception

Select this check box for the system to reconcile payments if the cleared date in the F09617 table is before the general ledger date in the F0911 table.

Reconciliation Code

Enter a value from UDC table 09/RC. This value is used by the Process Automatic Bank Statement program to indicate that a bank statement has been approved or processed, a payment was voided, and so on. If you leave this field blank, the system uses R (Reconciled One for One Match) as the default value.

Variance Account

Enter the general ledger account that the system uses to record any variance amounts for this transaction type and reconciliation rule. This field and the remaining fields on this tab appear only if the Payment Variance check box is selected.

Variance Date Mode

Enter a value from UDC table 09/DT to specify the default date for the system to use when it automatically creates journal entries for variances. Values are:

1: Bank statement date

2: Today's date

3: Cleared date

Tolerance Limit

Enter the maximum amount or highest percentage allowed for the system to create an automatic write-off record.

Variance Doc Type

Enter a value from UDC table 00/DT for the system to use to create variance journal entries for payments when you run the Process Automatic Bank Statement program. If you leave this field blank, the system uses PN (Manual Check) as the default value.

7.3.3.2 Receipt Clear

Select this tab if the reconciliation rule applies to receipts that clear the bank. The Reconciliation Code, Variance Date Mode, and Tolerance Limit fields function the same as those on the Payment Clear tab.

Receipt Variance

Select this check box for receipt variances. If selected, the system displays the Variance Account, Variance Date Mode, Tolerance Limit, and Variance Doc Type fields, which are required.

Receipt Reconciliation Date Exception

Select this check box for the system to reconcile receipts if the cleared date in the F09617 table is before the general ledger date in the F0911 table.

Create Auto Batch Receipts

Select this check box if you want the system to automatically create batch receipts. If selected, the system displays the Auto Batch Receipt Date Mode field, which is required. The system creates automatic batch receipts and applies them to invoices only for transactions with a customer number. If the customer number is blank, the system creates a logged receipt, which is not applied to invoices.

The system creates automatic batch receipts only if the receipt and invoice currency is the same. You cannot apply an alternate currency to an automatic batch receipt.

Auto Batch Receipt Date Mode

Enter a value from UDC table 09/DT to specify the default date for the system to use when it automatically creates batch receipts. Values are the same as those for the Variance Date Mode field. This field is required if you select the Create Auto Batch Receipts check box.

Variance Account

Enter the general ledger account that the system uses to record any variance amounts for this transaction type and reconciliation rule. This field and the remaining fields on this tab appear only if the Receipt Variance check box is selected.

Variance Document Type

Enter a value from UDC table 00/DT for the system to use to create variance journal entries for receipts when you run the Process Automatic Bank Statement program. If you leave this field blank, the system uses RC (Receipts) as the default value.

7.3.3.3 Journal Entry Create

Select this tab if the reconciliation rule applies to journal entries, such as those for bank fees, transfers, and interest earned. The Reconciliation Code and G/L Date Mode fields on this tab function the same as those on the Payment Clear tab.

Document Type

Enter a value from UDC table 00/DT for the system to use to create journal entries when you run the Process Automatic Bank Statement program. If you leave this field blank, the system uses JE (Journal Entry) as the default value.

7.3.3.4 Journal Entry Reconcile

Select this tab if the reconciliation rule applies to transaction type RJE (or other user-specified transaction code with special handling code 02) in UDC table 09/TT.

This transaction type is used for bank transactions that already exist as journal entries 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.

Journal Entry Reconcile Variance

Select this check box for journal entry variances. If selected, the system displays the Variance Account, Variance Date Mode, Tolerance Limit, and Variance Doc Type fields, which are required.

JE Reconciliation Date Exception

Select this check box for the system to reconcile journal entries if the cleared date in the F09617 table is before the general ledger date in the F0911 table.

Variance Account

Enter the general ledger account that the system uses to record any variance amounts for this transaction type and reconciliation rule. This field and the remaining fields on this tab appear only if the Journal Entry Reconcile Variance check box is selected.

Variance Doc Type

Enter a value from UDC table 00/DT for the system to use to create variance journal entries for journal entries when you run the Process Automatic Bank Statement program. If you leave this field blank, the system uses JE (Journal Entry) as the default value.

7.3.3.5 Draft Paid

Select this tab if a reconciliation rule applies to drafts paid by the bank.

G/L Date Mode (general ledger date mode)

Enter a value from UDC table 09/DT to specify the default date for the system to use when it automatically creates an entry between the general ledger bank account and the drafts payable account. This field is required.

Draft Document Type

Enter a value from UDC table 00/DT for the system to use to create journal entries when you run the Process Automatic Bank Statement program. If you leave this field blank, the system uses JE (Journal Entry) as the default value.

7.3.4 Assigning Automatic Reconciliation Rules to Groups

Access the Revise Auto Reconciliation Group form.

Reconciliation Group

Enter a name to identify the reconciliation group.

Reconciliation Rule

Enter the name of the reconciliation rule.

Update Reference 3 field with Bank Statement Number

Select this check box for the system to populate the GLR3 field in the F0911 table with the bank statement number. The reconciliation group identifies the default value. This check box also appears on the Revise Auto Bank Statement Account Setup form.

7.3.5 Assigning Groups to Automatic Bank Statement Accounts

Access the Revise Auto Bank Statement Account Setup form.

G/L Account Number (general ledger account number)

Enter the general ledger account number for the bank.

Reconciliation Group

Enter the code that identifies the reconciliation group that the system uses when it processes automatic bank statements for this bank account.

Reconciliation Rule

Enter the code that identifies the reconciliation rule for the system to use when it processes automatic bank statements for this bank account. The rule that you enter in this field must be part of the group that you entered in the previous field. The Reconciliation Group and Reconciliation Rule fields work with each another.

If you leave this field blank, the system uses the reconciliation rule assigned to the group that you entered in the previous field.

Track Taxes

Select this check box for the system to track taxes for this bank account. To update the Tax Explanation Code, Tax Rate Area, and Tax Item Number fields in the F0911 table, you must select this check box as well as enter values in the two remaining tax fields.

Tax Rate/Area

If you select the Track Taxes check box, this field is required.

Enter the code for the tax rate area for this bank account. This code must exist in the F4008 table. The general ledger date of all transactions for this bank account must be greater than or equal to the effective date and less than or equal to the expiration date of the tax rate/area that you enter in this field.

If you leave this field blank, the system uses the tax rate area specified for the GL account number in the F0901 table. If there is none, the system issues an error message.

Tax Explanation Code

If you select the Track Taxes check box, this field is required.

Enter a value from UDC table 00/EX that begins with the letter V (value added tax). The system uses this code with the tax rate area and tax rules to determine how to calculate a tax. This field is always required if the Tax Rate/Area field contains a value