Use the Bank Statement Mappings window to map the structure of your bank statement file to the Bank Statement Open Interface tables. You need to define the mapping for each unique file structure. The default mapping templates for BAI2, SWIFT940, and French EDIFACT are provided by Cash Management and cannot be changed. You can create new mapping templates by copying the existing templates.
Open the Bank Statement Mappings window.
If you want to review a mapping template, choose one from the list of values, else cancel the list of values.
Create a new record for the new mapping template.
In the Name field, enter the name of the new mapping template.
In the Description field, enter the description for the new mapping template.
In the Control File Name field, enter the SQL*Loader script that should be used with this new mapping template. You can choose an existing script from the list of values or enter a new script that you have created. If your bank uses the BAI2 format, choose BAI2.ctl. If your bank uses the SWIFT940 format, choose SWIFT940.ctl. If your bank uses the French EDIFACT format, choose EDIFACT.ctl. If your bank uses a user-defined format, enter the SQL*Loader script you created.
In the Precision field, enter a precision for the amounts if they are expressed as integers and have the same precision within the bank statement file. The Bank Statement Loader program uses the precision to convert integer amounts into decimal numbers. For example, an amount expressed as 10000 with a precision of 2 is interpreted and loaded as 100.00. Leave the precision blank if amounts are already decimal numbers or if they have different precisions. In BAI2, the default precision is 2. In SWIFT940, precision is not used because amounts are already decimal numbers. In French EDIFACT, precision is specified for each amount on the header and line records.
In the Date Format field, enter the date format that is used in the bank statement file. You must enter a format that can be recognized by the Oracle database. The default format for BAI2 and SWIFT940 is YYMMDD. The default format for French EDIFACT is DDMMYY.
In the Timestamp Format field, enter the time format that is used in the bank statement file. This information is typically available on intra-day bank statements only. You must enter a format that can be recognized by the Oracle database. The default format for BAI2 is HH24MI. No defaults are provided for other file formats. Although the BAI2 standard uses the timestamp format of HHMM, where HH represents hours in a 24-hour format and MM represents minutes, you cannot use this format, because the Oracle database interprets MM as months instead of minutes.
In the Bank File Format Type field, enter the format type of the bank statement file. You can enter a new format or choose from a list of previously defined formats. If you have not defined a new format, the seeded choices are BAI2, SWIFT940, and EDIFACT-France. Once you create a mapping template for a new format, that format is available in the list of values. If your bank uses the BAI2 format, choose BAI2. If your bank uses the SWIFT940 format, choose SWIFT940. If your bank uses the French EDIFACT format, choose EDIFACT-France. If your bank uses a user-defined format, enter that format.
Choose the Populate button. If you choose an existing format as the Bank File Format Type, the rules defined in the mapping template for that format are automatically entered in the Header and Lines tabbed regions. If multiple mapping templates are defined for that format, you must select one from the list of values. However, if you are defining a new format, only the column names are entered in the Header and Lines tabbed regions.
Unlike others, PRECISION and STATEMENT_TIMESTAMP are not columns in the Bank Statement Headers Interface table. The Bank Statement Loader program uses the precision to convert integer amounts into decimal numbers. The Intra-Day Bank Statement Loader program appends the timestamp to the STATEMENT_DATE column in the Bank Statement Headers Interface table.
You should map the PRECISION column only if amounts are expressed in integers and have different currencies. In BAI2, the PRECISION column is not used because the default precision of 2 is defined on the mapping template. In SWIFT940, the PRECISION column is not used because all amounts are already decimal numbers. In French EDIFACT, the PRECISION column is used to convert the amounts for the AMOUNT column. If you do not enter a value for the PRECISION column, the value in the PRECISION column of the Bank Statement Headers Interface table is used.
You must map the STATEMENT_TIMESTAMP column if you want to load timestamp information from your intra-day bank statements. Since your bank may provide multiple intra-day bank statements within the same day, you can use the timestamp to determine how recent the information is. In the BAI2 mapping template, the STATEMENT_TIMESTAMP column is mapped to Record ID 02 and Position 5.
The Headers tabbed region lists all the columns in the Bank Statement Headers Interface table. Define the mapping for the columns according to the file format used by your bank. The four required columns in the table are:
STATEMENT_NUMBER
BANK_ACCOUNT_NUM
STATEMENT_DATE
ORG_ID
You must map all of the four columns, except for the ORG_ID column. The ORG_ID column is populated automatically by the Bank Statement Loader program.
Unlike others, PRECISION is not a column in the Bank Statement Headers Interface table. The Bank Statement Loader program uses this precision to convert integer amounts into decimal numbers. You should map this column only if amounts are expressed in integers and have different currencies.
In BAI2, the PRECISION column is not used because the default precision of 2 is defined on the mapping template. In SWIFT940, the PRECISION column is not used because all amounts are already decimal numbers. In French EDIFACT, the PRECISION column is used to convert amounts for the CONTROL_BEGIN_BALANCE column, the CONTROL_END_BALANCE column, the CASHFLOW_BALANCE column, the INT_CALC_BALANCE column, the ONE_DAY_FLOAT column, the TWO_DAY_FLOAT column, the AVAILABLE BALANCE column, the VALUE_DATED_BALANCE column, the AVERAGE_CLOSE_LEDGER_MTD column, the AVERAGE_CLOSE_LEDGER_YTD column, the AVERAGE_CLOSE__AVAILABLE_MTD column, and the AVERAGE_AVAILABLE_YTD column. If you do not enter a value for the PRECISION column, the precision you defined on the mapping template is used.
For each column, you need to specify the location of the source data for this column in the bank statement file. In the Record ID field, enter the type of the record, whether it is a header, line, or trailer. In the Position field, enter the placement of the field within the record, relative to the beginning of the record. For example, in the BAI2 mapping template, the BANK_ACCOUNT_NUM column is mapped to Record ID 03 and Position 1, meaning the data for this column is copied from the first field of a record that has 03 as the identifier.
You can map the same field to two different columns. For example, in the BAI2 mapping template, the STATEMENT_NUMBER column and the STATEMENT_DATE column are both mapped to Record ID 02 and Position 4.
You can map part of a field to a column if the field contains information other than the source data for this column. In the Format field, enter the format in which the source data appears. The Bank Statement Loader program searches for this pattern in the field and copies the data if a match is found. If multiple matches are found, the Bank Statement Loader program selects the first match.
The data in a field may come in one of several possible formats. In the Format field, you can enter multiple formats and separate them by commas. The Bank Statement Loader program applies the formats in the order they are specified and stops when a match is found. If multiple formats need to be applied, the Concatenate Format checkbox should be checked for the BANK_TRX_NUMBER field on the mapping template. If only one matching format is needed, do not check the Concatenate Format checkbox.
A format may contain an indicator. An indicator is a token that always appears with, before, or after the data. If you expect to find spaces between the indicator and the source data, you must explicitly specify these spaces in the format. The chart in step 13 describes the types of formats supported by the Bank Statement Loader program.
In the Include Indicator field, select the checkbox if you want to copy the indicator to the Bank Statement Headers Interface table. If you leave the checkbox blank, the Bank Statement Loader program excludes the indicator.
The Lines tabbed region lists all the columns in the Bank Statement Lines Interface table. Define the mapping for the column according to the file format used by your bank. The four required columns in the table are:
STATEMENT_NUMBER
BANK_ACCOUNT_NUM
LINE_NUMBER
TRX_DATE
You must map the TRX_DATE column. The Bank Statement Loader program copies the values for the STATEMENT__NUMBER column and the BANK_ACCOUNT_NUM column from the respective columns in the Bank Statement Headers Interface table, and automatically generates a sequential value for the LINE_NUMBER column.
For each column, you need to specify the location of the source data for this column in the bank statement file. In the Record ID field, enter the type of the record, whether it is a header, line, or trailer. In the Position field, enter the placement of the field within the record, relative to the beginning of the record.
In most formats, the position should be a positive number. In the BAI2 format, however, the position can be negative, meaning that a field can be found only by counting its position from the end of the record. For example, the number of fields in record ID 16 varies depending upon information such as Funds Type. 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. In the BAI2 mapping template, these fields are represented by negative positions. The BANK_TRX_NUMBER column and the TRX_TEXT column are mapped to the Text field as Record ID 16 and Position -1. The INVOICE_TEXT column is mapped to the Customer Ref No field as Record ID 16 and Position -2. The CUSTOMER_TEXT column is mapped to the Bank Ref No field as Record ID 16 and Position -3.
You can map the same field to two different columns. For example, in the BAI2 mapping template, the BANK_TRX_NUMBER column and the TRX_TEXT column are both mapped to Record ID 16 and Position -1.
You can map part of a field to a column if the field contains information other than the source data for this column. In the Format field, enter the format in which the source data appears. For example, in the BAI2 and SWIFT940 mapping template, the BANK_TRX_NUMBER column has the default format of Example (xxx). You should change this value to the actual format used by your bank. The Bank Statement Loader program searches for this pattern in the field and copies the data if a match is found. If multiple matches are found, the Bank Statement Loader program selects the first match.
The data in a field may come in one of several possible formats. In the Format field, you can enter multiple formats and separate them by commas. The Bank Statement Loader program applies the formats in the order they are specified and stops when a match is found. If multiple formats need to be applied, the Concatenate Format checkbox should be checked for the BANK_TRX_NUMBER field on the mapping template. If only one matching format is needed, do not check the Concatenate Format checkbox. For example, the format for the BANK_TRX_ NUMBER should be entered in the order specified in the Payroll Matching Order field when defining a transaction code for matching Payroll EFT payments. Additionally, you should check the Concatenate Format checkbox.
A format may contain an indicator. An indicator is a token that always appears with, before, or after the data. If you expect to find spaces between the indicator and the source data, you must explicitly specify these spaces in the format. In the example of the BANK_TRX_NUMBER column, the indicator is Example, followed by a space. In the Include Indicator field, select the checkbox if you want to copy the indicator to the Bank Statement Lines Interface table. If you leave the checkbox blank, the Bank Statement Loader program excludes the indicator.
The Bank Statement Loader program supports both fixed and variable length formats. The notations used to define fixed length formats are:
n a digit
a a non-digit character, like a letter or a special character
x any character, including digit, letter, or special character
Variable formats use only the ~ notation, which stands for any number of alphanumeric and special characters. While fixed length formats may come with or without an indicator, variable length formats must be accompanied by an indicator. If you use either the fixed length format without an indicator or the variable format to extract the source data from a field that contains multiple segments of information, these segments must be delimited by white spaces.
The following table summarizes the different types of formats and gives an example of each:
| Type | Description | Format |
|---|---|---|
| Fixed length with indicator | The information in the field has an indicator and the length of the data is fixed. | The format must consist of the indicator and a pair of parentheses containing the pattern of the data. Example: If the Transaction Number starts with CR or DR and is made up of six digits followed by one character, the correct format to define is: CR (nnnnnnx), DR (nnnnnnx) This is an example of multiple formats. If the data is CR 1234561, the Transaction Number populated in the Bank Statement Lines Interface table is 1234561. If the data is DR 654321G, the Transaction Number populated is 654321G. |
| Fixed length without indicator | The information in the field has no indicator and the length of the data is fixed | The format must be a pair of parentheses containing the pattern of the data. Example: If the Transaction Number has six digits followed by one character, the correct format to define is: (nnnnnnx). This is an example of a single format. If the data is 1234561, the Transaction Number populated in the Bank Statement Lines Interface table is 1234561. |
| Variable length with indicator | The information in the field has an indicator and the length of the data is variable. | The format must consist of an indicator and a pair of parentheses containing the ~ notation. Example: If the Transaction Number starts with CR or DR followed by a space, and the length of the data is variable, the correct format to define is CR (~), DR (~). This is an example of multiple formats. If the data is CR 99999, the Transaction Number populated in the Bank Statement Lines Interface table is 99999. If the data is DR 654G, the Transaction Number populated is 654G. |
| Variable length without indicator | The information in the field is variable. | This scenario cannot be supported. |
Select the Enabled checkbox to activate this mapping information.
Save your changes.