Field Mapping for Automatic Bank Statement Processing

Appendix: Field Mapping for Automatic Bank Statement Processing

This appendix describes how the conversion service flows map data from bank flat files to fields in the JD Edwards EnterpriseOne staging tables. Specifically, this appendix describes how the conversion flows:

Map BAI2 fields to the F09610 and F09611 tables.
Map SWIFT fields to the F09610 and F09611 tables.

Mapping BAI2 Fields to the F09610 and F09611 Tables

This section describes how the Process Automatic Bank Statements Flat File Program (R09600) maps fields for the BAI2 bank format from the bank flat file to the EnterpriseOne bank statement staging header (F09610) and detail (F09611) tables.

These rules apply to the BAI2 bank format:

A comma (,) delimits fields within records.
A slash (/) indicates the end of the logical record, except for record 16.
The comma and slash characters are not allowed in any fields except for record 16 text.
Adjacent delimiters (, ,) identify a default or unspecified field value.
Amount fields do not contain decimals.

The decimal places are implied, based on the currency code.
The default currency code is USD (2 decimal places).

Records

The information in the BAI2 bank flat file is grouped by record. The tables in this section are organized by record and contain information about whether a field is required (R) or optional (O) according to BAI2 requirements, whether the conversion flow maps the field in a record to an EnterpriseOne field, and so on.

Example: BAI2 Text File

This is an example of a BAI2 text file:

Record 01

Record 01 is the header record that indicates the beginning of the table. It identifies the sender and receiver and describes the table structure. Record 01 ends with the file trailer record (99).

Required or Optional	Field Description	Field Position	EnterpriseOne Table and Field	Field Type and Length	Description
R	Record Code	1	Not applicable	Numeric 2	The value for this field is always 01.
R	Sender Identification	2	F09610.SGBSSID	String 9	The company or bank that sends the flat file.
R	Receiver Identification	3	F09610.SGBSRID	String 9	The company or bank that receives the flat file.
R	File Creation Date	4	F09610.SGSTMD F09611.BGSTMD	Numeric 6	The date of the bank statement, in the YYMMDD format.
R	File Creation Time	5	Not applicable	Numeric 4	This field does not map to an EnterpriseOne table.
R	File Identification Number	6	F09610.SGBSTN F09611.BGBSTN	Numeric 15	The bank statement number. Each flat file with the same creation date must have a different bank statement number.
O	Physical Record Length	7	Not applicable	Numeric 5	This field does not map to an EnterpriseOne table.
O	Block Size	8	Not applicable	Numeric 5	This field does not map to an EnterpriseOne table.
R	Version Number	9	Not applicable	Numeric 1	The value for this field is always 2.

Record 02

Record 02 is the group header record. It identifies the group of bank statements with the same as of date and time. Record 02 ends with the group trailer record (98).

Required or Optional	Field Description	Field Position	EnterpriseOne Table and Field	Field Type and Length	Description
R	Record Code	1	Not applicable	Numeric 2	The value in this field is always 02.
O	Ultimate Receiver ID	2	Not applicable	String 9	This field does not map to an EnterpriseOne table.
R	Originator ID	3	Not applicable	String 9	This field does not map to an EnterpriseOne table.
R	Group Status	4	Not applicable	Numeric 1	This field does not map to an EnterpriseOne table.
R	As-Of Date	5	F09611 BGVLDT	Numeric 6	The date of the transaction, in YYMMDD format.
O	As-Of Time	6	Not applicable	Numeric 4	This field does not map to an EnterpriseOne table.
O	Currency Code	7	F09610 SGOCRCD	String 3	The currency of the amount in record 03. The currency code must exist in the F0013 table.
O	As-Of-Date Modifier	8	Not applicable	Numeric 1	This field does not map to an EnterpriseOne table.

Record 03

Record 03 is the account identifier and summary status. It identifies the account number and shows account activity and status. Record 03 ends with the account trailer record (49).

Required or Optional	Field Description	Field Position	EnterpriseOne Table and Field	Field Type and Length	Description
R	Record Code	1	Not applicable	Numeric 2	The value in this field is always 03.
R	Customer Account Number	2	F09610 SGBAN F09611 BGBAN	String 29	The commercial account number. This number should include significant leading zeros.
O	Currency Code	3	Not applicable	String 3	This field does not map to an EnterpriseOne table.
O	Type Code	4	Not applicable	String 3	This field does not map to an EnterpriseOne table.
O	Amount - Summary	5	F09610 SGOPBAA	Numeric 15, 2	The opening and closing balances. Summary amounts are positive and do not include a sign.
O	Item Count	6	Not applicable	Numeric 5	This field does not map to an EnterpriseOne table.
O	Funds Type	7	Not applicable	Character 1	This field does not map to an EnterpriseOne table.
O	Value Date	8A	Not applicable	Numeric 6	This field does not map to an EnterpriseOne table.
O	Value Time	9A	Not applicable	Numeric 4	This field does not map to an EnterpriseOne table.
O	Amount - Immediate Availability	8B	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Amount - 1 Day Availability	8B	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Amount - 2 or more Day Availability	10B	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Number of Distributions	8C	Not applicable	Numeric 1	This field does not map to an EnterpriseOne table.
O	Day Availability 1	9C	Not applicable	Numeric 1	This field does not map to an EnterpriseOne table.
O	Amount Available 1	10C	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Day Availability 2	11C	Not applicable	Numeric 1	This field does not map to an EnterpriseOne table.
O	Amount Available 2	12C	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Day Availability 3	13C	Not applicable	Numeric 1	This field does not map to an EnterpriseOne table.
O	Amount Available 3	14C	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.

Record 16

Record 16 contains transaction detail with accompanying text and reference numbers. All type 16 records correspond to the account number in the Record 03.

Required or Optional	Field Description	Field Position	EnterpriseOne Table and Field	Field Type and Length	Description
R	Record Code	1	Not applicable	Numeric 2	The value in this field is always 16.
R	Type Code	2	F09611 BGTRANT	Numeric 3	The type code for the detail transaction. The value for this field must exist in UDC table 09/BA.
O	Amount - Transaction Detail	3	F09611 BGAA	Numeric 15, 2	Unsigned. The type code for the transaction amount indicates whether the amount is a debit or credit.
O	Funds Type	4	Not applicable	Character 1	This field does not map to an EnterpriseOne table.
O	Value Date	5A	Not applicable	Numeric 6	This field does not map to an EnterpriseOne table.
O	Value Time	6A	Not applicable	Numeric 4	This field does not map to an EnterpriseOne table.
O	Amount - Immediate Availability	5B	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Amount - 1 Day Availability	6B	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Amount - 2 or more Day Availability	7B	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Number of Distributions	5C	Not applicable	Numeric 1	This field does not map to an EnterpriseOne table.
O	Day Availability 1	6C	Not applicable	Numeric 1	This field does not map to an EnterpriseOne table.
O	Amount Available 1	7C	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Day Availability 2	8C	Not applicable	Numeric 1	This field does not map to an EnterpriseOne table.
O	Amount Available 2	9C	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Day Availability 3	10C	Not applicable	Numeric 1	This field does not map to an EnterpriseOne table.
O	Amount Available 3	11C	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
O	Bank Reference Number	5	F09611 BGBANKREF	String 16	A number defined by the bank. This number cannot include a comma or a slash.
O	Customer Reference Number	6	F09611 BGCUSTREF	String 16	A number defined by the bank. Typically, this is the deposit or payment number.
O	Text	7	F09611 BGINFO1	String 360	An informational field used for references purposes only.

Record 88

Record 88 is the continuation record for record 16. If the data in record 16 exceeds the maximum physical record length or if dividing the record is necessary for any reason, the continuation record allows the data to continue in the same format.

Required or Optional	Field Description	Field Position	EnterpriseOne Table and Field	Field Type and Length	Description
R	Record Code	1	Not applicable	Numeric 2	This field does not map to an EnterpriseOne table.
R	Format of continued record type	2	Not applicable	Not applicable	This field does not map to an EnterpriseOne table.

Record 49

Record 49 is the account trailer record. It provides account level control totals and includes the sum of all amount fields in record type 03 as well as the total number of records associated with an account from records 16 and 88. A record 49 exists for each record 03. Although the fields in record 49 do not map to EnterpriseOne fields, the information in the record is used to identify the end of the transactions and balances for an account.

Required or Optional	Field Description	Field Position	EnterpriseOne Table and Field	Field Type and Length	Description
R	Record Code	1	Not applicable	Numeric 2	The value in this field is always 49.
R	Account Control Total	2	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
R	Number of Records	3	Not applicable	Numeric 5	This field does not map to an EnterpriseOne table.

Record 98

Record 98 is the group trailer record. It provides group control totals and corresponds to the group header in record type 02. Although the fields in record 98 do not map to EnterpriseOne fields, the information in the record is used to identify the end of the bank statement.

Required or Optional	Field Description	Field Position	EnterpriseOne Table and Field	Field Type and Length	Description
R	Record	1	Not applicable	Numeric 2	The value in this field is always 98.
R	Group Control Total	2	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
R	Number of Accounts	3	Not applicable	Numeric 5	This field does not map to an EnterpriseOne table.
R	Number of Records	4	Not applicable	Numeric 5	This field does not map to an EnterpriseOne table.

Record 99

Record 99 is the file trailer record. It provides file control totals and corresponds to the file header in record type 01. Although the fields in record 99 do not map to EnterpriseOne fields, the information in the record is used to identify the end of the text file. Each text file has one record 99 and one record 01. Record 01 identifies the beginning of the text file.

Required or Optional	Field Description	Field Position	EnterpriseOne Table and Field	Field Type and Length	Description
R	Record Code	1	Not applicable	Numeric 2	The value in this field is always 99.
R	File Control Total	2	Not applicable	Numeric 15, 2	This field does not map to an EnterpriseOne table.
R	Number of Groups	3	Not applicable	Numeric 5	This field does not map to an EnterpriseOne table.
R	Number of Records	4	Not applicable	Numeric 5	This field does not map to an EnterpriseOne table.

Mapping SWIFT Fields to the F09610 and F09611 Tables

This section describes how the Process Automatic Bank Statements Flat File Program (R09600) maps fields for the SWIFT (Society for Worldwide Interbank Financial Telecommunications) bank format from the bank flat file to the EnterpriseOne bank statement staging header (F09610) and detail (F09611) tables.

These rules apply to the SWIFT bank format:

A } delimits the beginning of a block and a -} delimits the end of the block.
Fields within a block are enclosed with : :
If a delimiter is required between fields, a slash (/) is used.
A comma (,) is used instead of a decimal.
The amount is always in the currency of the bank account.

Blocks

The information in the SWIFT bank flat file is grouped by block. This section contains field information for blocks 2 and 4. It does not contain information for blocks 1 (basic header), 3 (user header), and 5 (trailers) because the fields in those blocks do not map to EnterpriseOne fields.

The tables in this section are organized by block and contain information about whether a field is required (R) or optional (O) according to SWIFT requirements, whether the conversion flow maps the field in a record to an EnterpriseOne field, and so on.

Because fields can be optional according to SWIFT requirements, the field positions listed in the tables in this section may not reflect the actual field positions in the text file. For example, assume that field positions 1–6 are required, positions 7–10 are optional, and positions 11–12 are required. If your bank text file does not contain values for positions 7–10, then positions 11–12 will become positions 7–8. Any subsequent field positions would be adjusted accordingly.

Example: SWIFT Text File

This is an example of a SWIFT text file:

Block 2

Block 2 contains messages and bank statement information.

SWIFT	Field Description	Field Position	EnterpriseOne Table and Field	Field Type and Length	Description
R	File Input/Output	1	F09610 SGFIO	Character 1	The value in this field is always 2.
R	Message Type	2–4	F09610 SGMESST	String 4	Only message type 940 is processed.
R	Time of Message	5–8	Not applicable	Numeric 4, 0	This field does not map to an EnterpriseOne table.
R	Bank Statement Date	9–14	F09610 SGSTMD F09611 BGSTMD	Date 6	The date of the bank statement.
R	Description	15–60	F09610 SGDESC04	String 46	A description of bank information.

Block 4

Block 4 contains opening balances, transactions amounts and dates, closing balances, and forecasting amounts. Fields 61 and 86 can be repeated, but they must always occur before field 62.

SWIFT	Field Description	Field	Field Position	EnterprisOne Table and Field	Field Type and Length	Description
R	Transaction Reference Number	20	1–16	F09610 SGETRN	String 16	The reference number of electronic statement.
R	Account Identification	25	1–35	F09610 SGBAN	String 35	The account number.
R	Statement Number	28C	1–5	F09610 SGBSTN F09611 BGBSTN	Numeric 15	The bank statement number.
O	Separator	28C	/	Not applicable	Character 1	This field is required only if there is a sequence number. This field does not map to an EnterpriseOne table.
O	Sequence Number	28C	7–11	F09610 SGBSSEQ F09611 BGBSSEQ	Numeric 5	The sequential number for each transaction.
R	Opening Balance Debit/Credit Indicator	60m or 60f	1	F09610 SGOBDBCR	Character 1	The debit (D) or credit (C) indicator for the opening balance.
R	Opening Booking Date	60m or 60f	2–7	F09610 SGOLVDT	Date 6	The value date for the opening balance, in YYMMDD format.
R	Opening Balance Currency Code	60m or 60f	8–10	F09610 SGOCRCD F09611 BGTCRCD	String 3	The 3-character ISO currency code associated with the opening balance amount.
R	Opening Balance Amount	60m or 60f	11–25	F09610 SGOPBAA	Numeric 15, 2	The opening balance amount.
R	Value Date	61	1–6	F09611 BGVLDT	Date 6	The date that the transaction clears the bank, in YYMMDD format.
O	Booking Date	61	7–10	Not applicable	Date 4	This field does not map to an EnterpriseOne table.
R	Detail Debit/Credit Indicator	61	11–12	F09611 BGDDBCR	String 2	The indicator for debit (D), credit (C), reversal of credit (RC), or reversal of debit (RD).
O	Type of Currency	61	13	F09611 BGCURRT	Character 1	The third character of the ISO currency code.
R	Transaction Amount	61	14–28	F09611 BGAA	Numeric 15, 2	The amount of the transaction.
R	Transaction Type	61	29–32	F09611 BGTRANT	String 4	The transaction type identification code.
O	Reference	61	33–48	F09611 BGCUSTREF	String 16	The customer reference, such as payment or receipt.
R	Separator	61	49–50	Not applicable	/	If an external bank reference exists, this field is required. This field does not map to an EnterpriseOne table.
O	External Bank Reference	61	51–66	F09611 BGBANKREF	String 16	The account servicing institution's reference.
R	Separator	61	67–70	Not applicable	/	If detail (positions 71-102) exists, this field is required. This field does not map to an EnterpriseOne table.
O	Detail	61	71–102	F09611 BGSUBF9	String 34	The supplementary details.
R	Informational line	86	All	F09611 BGINFO1	String 390	An informational line that can contain 65 characters. There are six informational lines.
R	Closing Balance Debit/Credit Indicator	62m or 62f	1	F09610 SGCBDBCR	Character 1	The indicator debit (D) or credit (C) for the closing balance amount.
R	Closing Booking Date	62m or 62f	2–7	F09610 SGCBDBCR	Date 6	The date of the closing balance, in YYMMDD format.
R	Closing Currency Code	62m or 62f	8–10	F09610 SGCCRCD	String 3	The 3-character ISO currency code for the closing balance.
R	Closing Balance Amount	62m or 62f	11–25	F09610 SGCLBAA	Numeric 15, 2	The closing balance amount.
R	Closing Available Balance Debit/Credit Indicator	64	1	F09610 SGCADBCR	Character 1	Field 64 is optional, but if it is used all subfields are required.
R	Closing Available Value Date	64	2–7	F09610 SGCAVLDT	Date 6	The value date for the closing available balance, in YYMMDD format.
R	Closing Available Currency Code	64	8–10	F09610 SGCACRCD	String 3	The 3-character ISO currency code for the closing available balance.
R	Closing Available Balance	64	11–25	F09610 SGCAVAA	Numeric 15, 2	The closing available balance amount.
R	Forward Available Balance Debit/Credit Indicator	65	1	F09610 SGFADBCR	Character 1	Field 65 is optional, but if it is used all subfields are required.
R	Forward Available Value Date	65	2–7	F09610 SGFVLDT	Date 6	The value date for the forward available balance, in YYMMDD format.
R	Forward Available Currency Code	65	8–10	F09610 SGFACRCD	String 3	The 3-character ISO currency code for the forward available balance.
R	Forward Available Balance	65	11–25	F09610 SGFAVAA	Numeric 15, 2	The forward available balance amount.