7 Setting Up Automatic Bank Statement Processing

This chapter contains the following topics:

Section 7.1, "Setting Up UDCs for Automatic Bank Statement Processing"
Section 7.2, "Setting Up Cross References"
Section 7.3, "Setting Up Automatic Bank Account Reconciliation"

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.

Figure 7-1 Revise Bank Account Cross Reference form

Description of ''Figure 7-1 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-2 Revise Transaction Type Cross Reference form

Description of ''Figure 7-2 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:

Set up automatic reconciliation rules.
Assign automatic reconciliation rules to groups.
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.

Figure 7-3 Revise Auto Reconciliation Rules form

Description of ''Figure 7-3 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

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.

Figure 7-4 Revise Auto Reconciliation Group form

Description of ''Figure 7-4 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.

Figure 7-5 Revise Auto Bank Statement Account Setup form

Description of ''Figure 7-5 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