This appendix describes the rules for the predefined mapping templates in Cash Management, BAI2, SWIFT940, and EDIFACT - France.
This appendix covers the following topics:
This section contains the default mapping rules created for the BAI2 format.
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 |
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 |
Additional Information: 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 |
This section contains the default mapping rules created for the SWIFT940 format.
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 |
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 |
Additional Information: [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 |
This section contains the default mapping rules created for the French EDIFACT format.
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 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 |
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 |