The Bank Statement Loader program and the Intra-Day Bank Statement Loader program can load the following four bank statement file formats:
BAI2
SWIFT940
User-defined
CAMT
Before you can use the Bank Statement Loader program or the Intra-Day Bank Statement Loader program, you must complete the setup required for each format that you want to load.
The Bank Statement Loader program uses a predefined SQL*Loader script to load BAI2 bank statement files. However, since each bank adopts the BAI2 standard slightly differently, you need to map the BAI2 format to the Bank Statement Open Interface tables. You can create the mapping information from the BAI2 mapping template. See: Bank Statement Mapping
The BAI2 SQL*Loader script is located in $CE_TOP/bin/BAI2.ctl. The mapping rules for the BAI2 format are listed in BAI2 Mapping Template.
The Bank Statement Loader program uses a predefined SQL*Loader script to load SWIFT940 bank statement files. However, since each bank adopts the SWIFT940 standard slightly differently, you need to map the SWIFT940 format to the Bank Statement Open Interface tables. You can create the mapping information from the SWIFT940 mapping template. See: Bank Statement Mapping
The SWIFT940 SQL*Loader script is located in $CE_TOP/bin/SWIFT940.ctl. The mapping rules for the SWIFT940 format are listed in SWIFT940 Mapping Template.
After you load SWIFT940 bank statement files into the open interface tables, you may need to define new bank transaction codes in Cash Management. SWIFT940 transaction codes represent the type of transaction. For example, TRF represents transfers. However, SWIFT940 transaction codes do not contain information about the debit or credit nature of the transaction. Instead, 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 transaction codes before you can import the bank statement information. See: Bank Transaction Codes.
If your bank uses a format other than BAI2 or SWIFT940, you need to develop a SQL*Loader script and create a new mapping template for that format. You may want to consult the sample SQL*Loader script created for the French EDIFACT standard, located in $CE_TOP/bin/EDIFACT.ctl. The mapping template, EDIFACT-France, is also available for your reference. The mapping rules for the French EDIFACT format are listed in EDIFACT - France Mapping Template.
The bank statement information is delivered in a flat file.
The bank statement file has the following structure:
One Header record
One or more Line records that belong to the Header
One Trailer record
A file may contain multiple bank statements, but each bank statement must begin with a Header record, followed by a group of Line records, and optionally end with a Trailer record.
Each record begins with an identifier indicating the record type: Header, Line, or Trailer.
The field definitions within each record type are consistent within a file.
Each record has no more than 35 fields.
Each field has no more than 255 characters. Any field that exceeds this limit must be stored in COLUMN1 of the intermediate table.
The Bank Statement Loader program uses the SQL*Loader script to copy data from the bank statement file to the intermediate table. The intermediate table is a temporary storage of the file in the database, where a row corresponds to a record in the file and a column corresponds to a field in the record. The Bank Statement Loader program purges the table before inserting data into it. You can consult the SQL*Loader script provided by Cash Management for the French EDIFACT format for an example.
Your SQL*Loader script must reside in the $CE_TOP/bin directory, where $CE_TOP is the top directory for the Cash Management product. The name of the script must be in upper case and must end with the extension of ctl.
The SQL*Loader script needs to parse the bank statement file and exclude any extraneous records.
The SQL*Loader script must populate the REC_NO column and the REC_ID column in the intermediate table for each record loaded from the bank statement file. REC_NO indicates the order of a record in the file. You can use the SQL function of RECNUM to populate the REC_NO column. REC_ID is an identifier of the record type. Common record types found in most bank statement file formats are Header, Line, and Trailer.
The SQL*Loader script must store the data from each field as a character string in the intermediate table, except for the REC_NO, which is a number column.
If a field exceeds 255 characters, the SQL*Loader script must store the data in COLUMN 1 of the intermediate table, because COLUMN 1 can contain up to 2000 characters.
In some formats the record structure may change dynamically. For example, record ID 03 and record ID 16 in the BAI2 format have a variable number of fields. The SQL*Loader script must interpret the format and load the data into the appropriate columns in the intermediate table.
If zeros are used to fill a field, the SQL*Loader script should treat them as nulls. For example, In the French EDIFACT format, each field has a fixed position and a fixed length. If a field, such as CURRENCY_CODE, PRECISION, or BANK_TRX_ NUMBER, does not contain any value, it is filled with zeros. In this case, the French EDIFACT SQL*Loader script converts the zeros to nulls before loading the values into the intermediate table. Similarly, if a value does not fully occupy a field such as BANK_ACCOUNT_NUM, AMOUNT, BANK_TRX_NUMBER, CONTROL_BEGIN _BALANCE or CONTROL_END_BALANCE, the value is padded with leading zeros. The French EDIFACT SQL*Loader script ignores these leading zeros when loading the values into the intermediate table.
Amounts may need to be converted into a common format in the SQL*Loader script. For example, decimal points in the SWIFT940 format are not represented by periods. Since numbers stored in the ORACLE database use periods as the decimal separator, the SQL*Loader script must convert that character to a period. You can see an example of this conversion rule in the SWIFT940 SQL*Loader script. In other formats, amounts are expressed in integers qualified by a precision. If the precision varies because of the currency of the amounts, the SQL*Loader script needs to load the precision into the intermediate tables as well. See the French EDIFACT SQL*Loader script for an example.
Define new mapping rules using the Bank Statement Mappings window. See: Bank Statement Mapping
The CAMT053.001.02 format is a Payment Initiation and Account Reporting standard as per ISO 20022 Messages.
This new feature loads data from CAMT bank statement into Bank Statement Interface tables in CE. A new concurrent program "CAMT Bank Statement Loader" is defined in Cash Management responsibility.
With the introduction of new format of transaction codes in CAMT format, Bank Transaction Codes form has been changed accordingly. Three new columns namely, Domain, Family, Sub Family are added to the form. See: Bank Transaction Codes.