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

This appendix contains the following topics:


See Also:

C.1 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).

C.1.1 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.

C.1.2 Example: BAI2 Text File

This is an example of a BAI2 text file:

C.1.3 Record 01

Record 01 is the header record that indicates the beginning of the table. This record 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.

C.1.4 Record 02

Record 02 is the group header record. This record 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.

C.1.5 Record 03

Record 03 is the account identifier and summary status. This record 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.

C.1.6 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.

C.1.7 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.

C.1.8 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 system uses the information in the record 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.

C.1.9 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 system uses the information in the record 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.

C.1.10 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 system uses the information in the record 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.

C.2 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.

C.2.1 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.

C.2.2 Example: SWIFT Text File

This is an example of a SWIFT text file:

C.2.3 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.

C.2.4 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 EnterpriseOne 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.