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 |