Skip Headers

Oracle Cash Management User Guide
Release 12.1
Part Number E13483-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Bank Statement Mapping Templates

This appendix describes the rules for the predefined mapping templates in Cash Management, BAI2, SWIFT940, and EDIFACT - France.

This appendix covers the following topics:

BAI2 Mapping Template

This section contains the default mapping rules created for the BAI2 format.

Header Information

BAI2 header information is stored in record ID 02 and record ID 03. Record ID 88 is a continuation record that contains overflow information from record ID 03. The following is an example:

02, 0012344, 121000248, 1,970522, 0000, USD, /

03, 49999677822, USD, 015, 93711773,,, 045, 7167744,,, 040, 85015748,,, 400, 11790274,2,/

88, 010, 12450000, , /

The following table illustrates the mapping of BAI2 header information with record ID 02 and record name Group Header to the Bank Statement Headers Interface table:

BAI2 Field Intermediate Table Column Interface Table Column
Ultimate Receiver COLUMN1  
Originator ID COLUMN2  
Group Status COLUMN3  
As-of Date COLUMN4 STATEMENT_NUMBER,
STATEMENT_DATE
As-of Time COLUMN5 STATEMENT_DATE
Currency Code COLUMN6  
As-of Date Modifier COLUMN7  

The following table illustrates the mapping of BAI2 header information with record ID 03 and record name Account Identifier and Summary Status to the Bank Statement Headers Interface table:

BAI2 Field Intermediate Table Column Interface Table Column
Account Number COLUMN1 BANK_ACCOUNT_NUM
Currency Code COLUMN2 CURRENCY_CODE
Type Code COLUMN4  
Sign COLUMN5  
Amount COLUMN6  
Item Count COLUMN7  
Funds Type COLUMN8  

The following table shows how the remaining columns in the Bank Statement Headers Interface table are populated by the Bank Statement Loader program:

Column Name Source
BANK_NAME not populated
BANK_BRANCH_NAME not populated
CONTROL_BEGIN_BALANCE copied from record ID 03 and record ID 88 using the predefined transaction code of 010
CONTROL_TOTAL_DR copied from record ID 03 and record ID 88 using the predefined transaction code of 400
CONTROL_TOTAL_CR copied from record ID 03 and record ID 88 using the predefined transaction code of 100
CONTROL_END_BALANCE copied from record ID 03 and record ID 88 using the predefined transaction code of 015
CONTROL_DR_LINE_COUNT not populated
CONTROL_CR_LINE_COUNT not populated
CONTROL_LINE_COUNT not populated
RECORD_STATUS_FLAG defaulted to 'N'
ORG_ID populated by calling the FND_PROFILE API
CHECK_DIGITS not populated
CASHFLOW_BALANCE copied from record ID 03 and record ID 88 using the predefined transaction code of 045
INT_CALC_BALANCE copied from record ID 03 and record ID 88 using the predefined transaction code of 045
ONE_DAY_FLOAT copied from record ID 03 and record ID 88 using the predefined transaction code 072
TWO_DAY_FLOAT copied from record ID 03 and record ID 88 using the predefined transaction code 074
INTRA_DAY_FLAG 'N' if populated by the Bank Statement Loader program. 'Y' if populated by the Intra-Day Bank Statement Loader program
CREATED_BY updated at the time of insert
CREATION_DATE updated at the time of insert
LAST_UPDATED_BY updated at the time of insert and update
LAST_UPDATE_DATE updated at the time of insert and update

Line Information

BAI2 line information is stored in record ID 16 and record ID 88. Record ID 88 is a continuation record that contains overflow information from record ID 16. The information from record ID 88 is stored in the TRX_TEXT column. The following is an example:

16, 469, 1175000000, , , 00000001, ABC, COMPANY NATL

88, BANK

The following table illustrates the mapping of BAI2 line information with record ID 16 and record name Transaction Detail to the Bank Statement Lines Interface table:

BAI2 Field Intermediate Table Column Interface Table Column
Type Code COLUMN1 TRX_CODE
Amount COLUMN2 AMOUNT
Funds Type COLUMN3...? Variable
Bank Ref No COLUMN? CUSTOMER_TEXT
Customer Ref No COLUMN? INVOICE_TEXT
Text COLUMN? BANK_TRX_NUMBER, TRX_TEXT

The following table illustrates the mapping of BAI2 line information with record ID 88 and record name Continuation to the Bank Statement Lines Interface table:

BAI2 Field Intermediate Table Column Interface Table Column
Continuation COLUMN1 TRX_TEXT

Note: Since the number of fields in record ID 16 varies, depending upon information such as funds type, the location of these fields in the intermediate table cannot be determined in advance. However, Text, Customer Ref No, and Bank Ref No are always located at the end of the record, where Text is the last field, Customer Ref No is the second to the last field, and Bank Ref No is the third to the last field. They are represented by negative positions. The Text field is mapped to the BANK_TRX_NUMBER column and the TRX_TEXT column as position -1. The Customer Ref No field is mapped to the INVOICE_TEXT column as position -2. The Bank Ref No field is mapped to the CUSTOMER_TEXT column as position -3. If your bank uses different locations for these fields, you need to modify the mapping accordingly. In addition, the BANK_TRX_NUMBER column is populated with data from the Text field that matches the default format of Example (xxx). You must change the format to the actual format used by your bank.

The following table shows how the remaining columns in the Bank Statement Lines Interface table are populated by the Bank Statement Loader program:

Column Name Source
BANK_ACCOUNT_NUM copied from the header information
STATEMENT_NUMBER copied from the header information
LINE_NUMBER sequentially generated, starting with 1
TRX_DATE copied from the header information
EFFECTIVE_DATE populated with the value date, if the Funds Type field of record ID 16 contains the value of 'V'
BANK_ACCOUNT_TEXT not populated
CURRENCY_CODE not populated, but the Bank Statement Import program defaults the currency code from the header information
USER_EXCHANGE_RATE_TYPE not populated
EXCHANGE_RATE_DATE not populated
EXCHANGE_RATE not populated
ORIGINAL_AMOUNT not populated
CHARGES_AMOUNT not populated
CREATED_BY automatically populated at the time of insert
CREATION_DATE automatically populated at the time of insert
LAST_UPDATED_BY automatically populated at the time of insert and update
LAST_UPDATE_DATE automatically populated at the time of insert and update

SWIFT940 Mapping Template

This section contains the default mapping rules created for the SWIFT940 format.

Header Information

SWIFT940 header information is stored in records with tags of 25, 28C, 60a, and 62a. A tag behaves similarly as record ID in BAI2. When a record is copied from a SWIFT940 bank statement file to the intermediate table, the Bank Statement Loader program uses the tag as the record ID, but deletes the last non-numeric character. The following is an example:

:20:GL9903010486

:25:720520899611

:28:193

:60F:C990226BEF37692,

:62F:C990227BEF37812,12

The following table contains the legend for SWIFT940 tables:

Legend for SWIFT940 Tables Legend for SWIFT940 Tables
a character
n numeric
number numeric
x character or numeric
[ ] optional

The following table illustrates the mapping of SWIFT940 header information with record ID 25/25 and record name Account Identification to the Bank Statement Headers Interface table:

Format in SWIFT940 SWIFT940 Field Intermediate Table Column Interface Table Column
35x: Account Identification 35x COLUMN1 BANK_ACCOUNT_NUM

The following table illustrates the mapping of SWIFT940 header information with record ID 28C/28 and record name Statement Number/Sequence Number to the Bank Statement Headers Interface table:

Format in SWIFT940 SWIFT940 Field Intermediate Table Column Interface Table Column
5n: Statement Number [/3n]: Sequence Number 5n[/3n] COLUMN1 STATEMENT_NUMBER

The following table illustrates the mapping of SWIFT940 header information with record ID 60a/60 and record name Opening Balance to the Bank Statement Headers Interface table:

Format in SWIFT940 SWIFT940 Field Intermediate Table Column Interface Table Column
1a: D or C 1a COLUMN2  
6n: Opening Balance Date 6n COLUMN3  
3a: Currency Code 3a COLUMN4 CURRENCY_CODE
15number: Opening Balance 15number COLUMN5 CONTROL_BEGIN_BALANCE

The following table illustrates the mapping of SWIFT940 header information with record ID 62a/62 and record name Closing Balance to the Bank Statement Headers Interface table:

Format in SWIFT940 SWIFT940 Field Intermediate Table Column Interface Table Column
1a: D or C 1a COLUMN2  
6n: Closing Balance Date 6n COLUMN3 STATEMENT_DATE
3a: Currency Code 3a COLUMN4  
15number: Closing Balance 15number COLUMN5 CONTROL_END_BALANCE

The remaining columns in the Bank Statement Headers Interface table are populated as indicated in the table below:

Column Name Source
BANK_NAME not populated
BANK_BRANCH_NAME not populated
CONTROL_TOTAL_DR not populated, because SWIFT940 does not provide this information
CONTROL_TOTAL_CR not populated, because SWIFT940 does not provide this information
CONTROL_DR_LINE_COUNT not populated
CONTROL_CR_LINE_COUNT not populated
CONTROL_LINE_COUNT not populated
RECORD_STATUS_FLAG defaulted to 'N'
ORG_ID populated by calling the FND_PROFILE API
CHECK_DIGITS not populated
CASHFLOW_BALANCE not populated
INT_CALC_BALANCE not populated
ONE_DAY_FLOAT not populated
TWO_DAY_FLOAT not populated
INTRA_DAY_FLAG 'N' if populated by the Bank Statement Loader program. 'Y' if populated by the Intra-Day Bank Statement Loader program.
CREATED_BY automatically populated at the time of insert
CREATION_DATE automatically populated at the time of insert
LAST_UPDATED_BY automatically populated at the time of insert and update
LAST_UPDATE_DATE automatically populated at the time of insert and update

Line Information

SWIFT940 line information is stored in record ID 61. Record ID 61A and record ID 86 are continuation records that contain overflow information from record ID 61. The following is an example:

:61:9903020301D3,92N422NONREF

/OCMT/EURI,78

:86: 43.59.16.629 GTS OPERATIONS ADM

00011000000090 000000520012 9123456 19999

The following table illustrates the mapping of SWIFT940 line information with record ID 61/61 and record name Statement Line to the Bank Statement Lines Interface table:

Format in SWIFT940 SWIFT940 Field Intermediate Table Column Interface Table Column
6n: Transaction Date 6n COLUMN1 TRX_DATE
[4n] : Entry Date [4n] COLUMN2  
2a: Debit/Credit Mark 2a COLUMN3  
[1a] : Funds Code [1a] COLUMN4  
15number : Amount 15number COLUMN5 AMOUNT
axxx : Transaction Code axxx COLUMN6 TRX_CODE
16x : Reference for Account Owner 16x COLUMN7 CUSTOMER_TEXT
[//16x] : Account Servicing Institution's Reference [//16x] COLUMN8 BANK_ACCOUNT_TEXT
[34x] : Supplementary Detail      

The following table illustrates the mapping of SWIFT940 line information with record ID 86/61A and record name Information to Account Owner to the Bank Statement Lines Interface table:

Format in SWIFT940 SWIFT940 Field Intermediate Table Column Interface Table Column
65x: Information to Account Owner 65x COLUMN1 BANK_TRX_NUMBER, TRX_TEXT

Note: [34x] is stored as a separate record ID 61A and is mapped to the TRX_TEXT column. Overflow information from record ID 61 is stored in the same column as the supplementary details in record ID 61. The BANK_TRX_NUMBER column is populated with data from the Information to Account Owner field that matches the default format of Example (xxx). You must change the format to the actual format used by your bank. The Transaction Code field identifies the type of transaction. For example, transfers are identified as TRF. However, the Transaction Code field does not contain information about the debit or credit nature of the transaction. A separate field, the Debit/Credit Mark field, is used to differentiate debit and credit entries, where D means debit and C means credit. When the Bank Statement Loader program populates the TRX_CODE column in the Bank Statement Lines Interface table, it appends the Debit/Credit Mark to the Transaction Code to form a new code. For example, debit transfers are identified as TRFD and credit transfers as TRFC. You must set up these new bank transaction codes in Cash Management before you can import the bank statement information.

The remaining columns in the Bank Statement Lines Interface table are populated as the following table illustrates:

Column Name Source
BANK_ACCOUNT_NUM copied from the header information
STATEMENT_NUMBER copied from the header information
LINE_NUMBER sequentially generated, starting with 1
EFFECTIVE_DATE not populated
INVOICE_TEXT not populated
CURRENCY_CODE not populated, but Bank Statement Import program defaults the currency code from the header information
USER_EXCHANGE_RATE_TYPE not populated
EXCHANGE_RATE_DATE not populated
EXCHANGE_RATE not populated
ORIGINAL_AMOUNT not populated
CHARGES_AMOUNT not populated
CREATED_BY automatically populated at the time of insert
CREATION_DATE automatically populated at the time of insert
LAST_UPDATED_BY automatically populated at the time of insert and update
LAST_UPDATE_DATE automatically populated at the time of insert and update

EDIFACT - France Mapping Template

This section contains the default mapping rules created for the French EDIFACT format.

Header Information

French EDIFACT header information is stored in record ID 01. Trailer information is stored in record ID 07.

The following table illustrates the mapping of French EDIFACT header information with record ID 01 and record name Header to the Bank Statement Headers Interface table:

Position in French EDIFACT EDIFACT Field Intermediate Table Column Interface Table Column
1-2 :Record ID      
3-7 :Bank Code 3:7 COLUMN1  
8-11 :Blanks 8:11 COLUMN2  
12-16 :Bank Branch Code 12:16 COLUMN3  
17-19 :Currency Code 17:19 COLUMN4 CURRENCY_CODE
20 :Precision 20 COLUMN5  
21 :Blank 21 COLUMN6  
22-32 :Account Number 22:32 COLUMN7 BANK_ACCOUNT_NUM
33-34 :Blanks 33:34 COLUMN8  
35-40 :Previous Balance Date 35:40 COLUMN9 STATEMENT_NUMBER
41-90 :Blanks 41:90 COLUMN10  
91-104 :Previous Bank Balance 91:104 COLUMN11 CONTROL_BEGIN_BALANCE
105-120 :Blanks 105:120 COLUMN12  

The following table illustrates the mapping of French EDIFACT trailer information with record ID 07 and record name Trailer to the Bank Statement Headers Interface table:

Position in French EDIFACT EDIFACT Field Intermediate Table Column Interface Table Column
1-2 :Record ID      
3-7 :Bank Code 3:7 COLUMN1  
8-11 :Blanks 8:11 COLUMN2  
12-16 :Bank Branch Code 12:16 COLUMN3  
17-19 :Currency Code 17:19 COLUMN4  
20 :Precision 20 COLUMN5  
21 :Blank 21 COLUMN6  
22-32 :Account Number 22:32 COLUMN7  
33-34 :Blanks 33:34 COLUMN8  
35-40 :Ending Balance Date 35:40 COLUMN9 STATEMENT_DATE
41-90 :Blanks 41:90 COLUMN10  
91-104 :Ending Balance 91:104 COLUMN11 CONTROL_END_BALANCE
105-120 :Blanks 105:120 COLUMN12  

The remaining columns in the Bank Statement Headers Interface table are populated as illustrated in the following table:

Column Name Source
BANK_NAME not populated
BANK_BRANCH_NAME not populated
CONTROL_TOTAL_DR not populated
CONTROL_TOTAL_CR not populated
CONTROL_DR_LINE_COUNT not populated
CONTROL_CR_LINE_COUNT not populated
CONTROL_LINE_COUNT not populated
RECORD_STATUS_FLAG defaulted to 'N'
ORG_ID populated by calling the FND_PROFILE API
CHECK_DIGITS not populated
CASHFLOW_BALANCE not populated
INT_CALC_BALANCE not populated
ONE_DAY_FLOAT not populated
TWO_DAY_FLOAT not populated
INTRA_DAY_FLAG 'N' if populated by the Bank Statement Loader program. 'Y' if populated by the Intra-Day Bank Statemetn Loader program
CREATED_BY automatically populated at the time of insert
CREATION_DATE automatically populated at the time of insert
LAST_UPDATED_BY automatically populated at the time of insert and update
LAST_UPDATE_DATE automatically populated at the time of insert and update

Line Information

French EDIFACT line information is stored in record ID 04.

The following table illustrates the mapping of French EDIFACT line information with record ID 04 and record name Transaction to the Bank Statement Lines Interface table:

Position in French EDIFACT EDIFACT Field Intermediate Table Column Interface Table Column
1-2 :Record ID      
3-7 :Bank Code 3:7 COLUMN1  
8-11 :Blanks 8:11 COLUMN2  
12-16 :Bank Branch Code 12:16 COLUMN3  
17-19 :Currency Code 17:19 COLUMN4 CURRENCY_CODE
20 :Precision 20 COLUMN5  
21 :Blanks 21 COLUMN6  
22-32 :Account Number 22:32 COLUMN7  
33-34 :Transaction Code 33:34 COLUMN8 TRX_CODE
35-40 :Transaction Date 35:40 COLUMN9 TRX_DATE
41-42 :Bank Error Code 41:42 COLUMN10  
43-48 :Value Date 43:48 COLUMN11 EFFECTIVE_DATE
49-79 :Description 49:79 COLUMN12 TRX_TEXT
80-81 :Blanks 80:81 COLUMN13  
82-88 :Transaction Number 82:88 COLUMN14 BANK_TRX_NUMBER
89 :Bank Fee Flag 89 COLUMN15  
90 :Blanks 90 COLUMN16  
91-104 :Transaction Amount 91:104 COLUMN17 AMOUNT
105-120 :Blanks 105:120 COLUMN18  

The remaining columns in the Bank Statement Lines Interface table are populated as illustrated in the following table:

Column Name Source
BANK_ACCOUNT_NUM copied from the header information
STATEMENT_NUMBER copied from the header information
LINE_NUMBER sequentially generated, starting with 1
INVOICE_TEXT not populated
BANK_ACCOUNT_TEXT not populated
USER_EXCHANGE_RATE_TYPE not populated
EXCHANGE_RATE_DATE not populated
EXCHANGE_RATE not populated
ORIGINAL_AMOUNT not populated
CHARGES_AMOUNT not populated
CUSTOMER_TEXT not populated
CREATED_BY automatically populated at the time of insert
CREATION_DATE automatically populated at the time of insert
LAST_UPDATED_BY automatically populated at the time of insert and update
LAST_UPDATE_DATE automatically populated at the time of insert and update