oracle cash management-bank statement load

Bank Statement Loader Program

An Oracle White Paper


INTRODUCTION .......................................................................................... 2FUNCTIONALITY ......................................................................................... 2NEW TABLES AND VIEWS INTRODUCED:....................................... 4SETUP................................................................................................................ 5

BAI2................................................................................................................ 5SWIFT 940................................................................................................... 12OTHERS ..................................................................................................... 13

ERRORS AND TROUBLESHOOTING ................................................. 13ERROR NAMES AND TEXT: .............................................................. 14COMMON ERRORS ENCOUNTERED:........................................... 16FREQUENTLY ASKED QUESTIONS: ............................................. 17


INTRODUCTIONOracle Cash Management provides an open interface for loading electronic bank statement files into the bankstatement tables. The Bank Statement Open Interface consists of two interface tables and an importprogram. The tables contain information about the bank statement header and bank statement lines and theBank Statement Import program transfers information from the open interface tables to the bank statementtables.

In earlier releases, a custom program had to be developed to populate the Bank Statement Open Interfacetables from the bank statement file. The program would map the structure of the bank statement file to theopen interface tables, which would need a separate program for each unique file structure. In addition, acomplex file structure could not be handled by SQL*Loader. As a result, custom development involved a lotof effort and was time consuming.

To simplify the implementation of Oracle Cash Management, the Bank Statement Open Interface has beenenhanced to offer a complete solution for loading bank statement information from an external file. Usingthe new Bank Statement Loader feature, data can be quickly and easily loaded from BAI2 and SWIFT940bank statement files into the Bank Statement Open Interface tables without any programming. The BankStatement Loader also supports user-defined formats like French EDIFACT. Once data is populated intothe open interface tables, the Bank Statement Import program is used to transfer the data to the base bankstatement tables.

FUNCTIONALITYThe Bank Statement Loader program runs in two stages. In the first stage, the Bank Statement Loaderprogram uses a SQL*Loader script to insert data from the bank statement data file into the intermediatetable. Different SQL*Loader files are created per different bank formats. Users define bank statementmapping rules to link between the bank formats and the SQL*Loader script. When submitting the BankStatement Loader program, the user specifies which mapping rule to use and the concurrent programexecutes the proper SQL*Loader file tied to the mapping rule. The SQL*Loader file itself does not have theintelligence to distinguish between different bank formats. The intermediate table is simply a representationof the bank statement file in the database, where a row corresponds to a record in the file and a columncorresponds to a field in the record.

In the second stage, the Bank Statement Loader program uses a set of mapping rules to transfer data from theintermediate table to the Bank Statement Open Interface tables. These rules map the structure of the bank

statement file to the open interface tables. These rules can also perform simple pattern searches and datatransformations. After the Bank Statement Loader program completes, the Bank Statement LoaderExecution Report displays any exceptions encountered by the program.

The Bank Statement Loader program can be executed in one of these three modes:

1. Load

2. Load and Import

3. Load, Import and Auto Reconciliation

Option 1 is only to run the loader program. In option 2, the Bank Statement Import program starts after theBank Statement Loader completes successfully. In option 3, both the Bank Statement Import program andthe Auto Reconciliation program are launched after successful completion of the Bank Statement Loaderprogram. In either case, if the Bank Statement Loader program completes with errors or warnings, theconcurrent request terminates and no further process is started.

The loading of BAI2 and SWIFT940 file formats is mostly automated. Since Oracle Cash Managementprovides the SQL*Loader scripts and mapping templates for these two formats, you can run the BankStatement Loader program with minimal setup. In case a bank has made any variations to the standardformats, the default mapping information should be modified to match these variations.

In general, the Bank Statement Loader program supports these three types of modifications:

• Map data from a field in the bank statement file to one or more columns in Bank StatementOpen Interface tables:

For example, if the bank sends the same information for bank statement number and bank statementdate in one field that field can be mapped to the Bank Statement Number column and the BankStatement Date column in the Bank Statement Headers Interface table.

• Map data from part of a field in the bank statement file to one or more columns in the BankStatement Open Interface tables:

For example, if the transaction number is included along with other information in the descriptionfield, it must follow a pre-determined format so it can be easily recognized. This format is entered inthe mapping rules for the Transaction Number column, so the Bank Statement Loader program cancorrectly extract the number and transfer it to the Bank Statement Open Interface tables.

• Change the precision on the amounts:

Some banks send amounts as integers. For example, USD 100.00 is represented as 10000. Theprecision is implied to be 2. The Bank Statement Loader program is capable of handling anynumber of floating points, if the correct precision is set in the mapping rules. The BAI2 format usesa precision of 2, since all amounts are in USD. The SWIFT940 format does not use precision,because amounts are already decimal numbers.

In addition to BAI2 and SWIFT940, the Bank Statement Loader program can load user-defined formats.However, two customizations have to be created:

• A SQL*Loader script to read the bank statement file and copy the data to the intermediate table

• A mapping template to map the records and fields in the bank statement file to the columns inthe Bank Statement Open Interface tables

A bank statement file may contain bank statements for multiple bank accounts. The Bank StatementLoader program can be run to load the entire file, or data specific to a particular bank branch or bankaccount number.

Some bank statement files contain information other than bank statements. The SQL*Loader scripts forBAI2 and SWIFT940 exclude records that do not have the appropriate identifiers as defined by thespecifications of these two standards. If any bank uses a different format, this logic must beimplemented in the SQL*Loader script.

NEW TABLES AND VIEWS INTRODUCED:

Table 1: New tables introduced

TABLE NAME DESCRIPTION CE_STMT_INT_TMP Intermediate table, which stores the information loaded

from a bank statement file. This table is populated bythe SQL*Loader script.

CE_BANK_STMT_INT_MAP Stores the definitions of the mapping templates CE_BANK_STMT_MAP_HDR This table maps the columns the Bank Statement

Headers Interface table(CE_STATEMENT_HEADERS_INT_ALL) to thecolumns in the intermediate table(CE_STMT_INT_TMP).

CE_BANK_STMT_MAP_LINE Maps the columns in the Bank Statement Lines Interfacetable (CE_STATEMENT_LINES_INTERFACE) to thecolumns in the intermediate table(CE_STMT_INT_TMP).

CE_SQLLDR_ERRORS

Records the errors encountered by the Bank StatementLoader program when loading data from the bankstatement file into the intermediate table.

Table 2: New views introduced

VIEW NAME DESCRIPTION CE_BANK_STMT_INT_MAP_V View based on the CE_BANK_STMT_INT_MAP

table. The view will have the same columns as theCE_BANK_STMT_INT_MAP table plus a ROW_IDcolumn which is based on the ROWID ofCE_BANK_STMT_INT_MAP table.

CE_BANK_STMT_MAP_HDR_V View based on the CE_BANK_STMT_MAP_HDRtable. The view will have the same columns as theCE_BANK_STMT_MAP_HDR table plus aROW_ID column which is based on the ROWID ofthe CE_BANK_STMT_MAP_HDR table

CE_BANK_STMT_MAP_LINE_V

View based on the CE_BANK_STMT_MAP_LINEtable. The view will have the same columns as theCE_BANK_STMT_MAP_LINE table plus aROW_ID column which is based on the ROWID ofthe CE_BANK_STMT_MAP_LINE table.

SETUP

BAI2 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, map the BAI2 format to the BankStatement Open Interface tables. The mapping information can be created from the BAI2 mapping template.

The BAI2 SQL*Loader script is located in $CE_TOP/bin/BAI2.ctl

• Bank Account Setup

Create bank and bank account in Accounts Payable responsibility.

Navigation - Setup->Payment -> Banks. Enter the required information. Note the bank account numberand branch name. In this example, we will use the following:

Bank: ABC_BANK

Branch: ABC_BRANCH

Account number: 3340561

• Bank Transaction Codes

Set up the Bank Transaction Codes in Cash Management.

Navigation - Setup -> Bank Transaction Codes.

Query the bank account number , in this example 3340561 and enter the transaction codes as providedby the bank. These differ from bank to bank, and thus are bank specific. In this example, we will use thefollowing:

Type Code Description Transaction Source

Payment 100 Regular Payment AP Payments

Payment 130 EFT/Wire Payment AP Payments

Note: If Accounts Receivable is used, Receipt Type Bank Transaction Codes are defined in the sameway.

This step is extremely important because the code numbers defined are used for importing bankstatements. The successful import of bank statement files largely depend on the correctness of thesetransaction codes.

• Bank Statement Mapping

Set up the Bank Statement Mapping in Cash Management.

Navigation – Setup->Bank Statement Mappings. The seeded mappings can be copied and then modifiedfreely. When navigating to the form, it prompts the user to find a mapping. Cancel that dialog. Whenthe find window closes, give the new format, a name and a description.

Use the existing control file and supply the desired date format. Define the precision and choose theappropriate format type. Then click on Populate and save.

This will copy the default mapping to this new name. Then modify it to meet the requirements of the filestructure.

For this example, no changes were made to the default mapping.

• Creating the BAI2 Data File

If there already is a valid format data file from the bank, then can skip this step completely.

To create a sample BAI2 data file for testing purposes, follow the instructions below.

This is perhaps the most difficult step in the setup. A wrongly placed comma or incorrectly placed datavalue can wreak havoc on the Bank Statement Loader and cause a failure.

Be sure to use a text editor like vi or notepad. If using Microsoft Word or any other word processor,save the file as Plain Text. If the file is created on a PC, FTP it in ASCII format when moving it to theserver.

There are some mandatory header and footer records in a BAI2 data file, between which are the actualdetail (or line level) records. Each record is described in as much detail as is needed to get the programto work.

GENERAL DATA FILE STRUCTURE OF BAI2

01,121345678,7777777,011031,1431,1431,80,1,2/

02,7777777,121345678,1,011022,0000,USD,/

03,0007895624,USD,010,500000,015,52741100,045,100000,072,79694140,074,2160995,100,707755472/

88,102,013,400,655514372,402,011,954,486385540/16,100,76995,V,011020,,

Bank Reference Text,26446,Office Supplies/

16,100,812213,V,011015,,323532,A39599,Travel expenses/

16,100,242500,V,011017,,5434634N,46400-333,Rent expense/

16,100,21375,V,011019,,264,83832,Team Building Event/

49,2306166,6/

98,2306166,1/

99,2306166,1,10/

The above data file will be provided by banks that are under the purview of BAI, in a plain text format.To import this statement file into Oracle Cash Management, there are some set up steps required to befollowed. These are described below with an example:

First two digit of every line denote the record number.

• Record 01 - Mandatory

This should be the first record in the file. The format should be as follows:

01,<bank originator ID>,<bank customer ID>,<file creation date>, <file creation time>,<fileidentification number>,<physical record length>, <block size>,<version number>/

Considering the above format of the data file we get :

01,121345678,7777777,011031,1431,1431,80,1,2/

This means the file is from Bank 121345678 for its customer 7777777, and was created on 31-OCT-2001at 2:31 PM. It has 80 characters per record, 1 record per block and in BAI2 format (as denoted by theversion number 2). The version number has to be '2' notifying it's BAI version 2.If the version numberfield is other than 2 , the program will fail.


This should be the second record in the file. The format should be as follows:

02,<bank customer ID>,<bank originator ID>,<group status>,<as of date>, <as of time>,<currencycode>,<as of date modifier>/

The group status can be 1 for "update", 2 for "deletion", 3 for "correction”, or 4 for "test only".

In this example, the file will use the following values:

02,7777777,121345678,1,011022,0000,USD,/

This means we have received an update type file with USD transactions through 22-OCT-01 at midnight.


This should be the third record in the file. The format should be as follows:

03,<bank account number>,<currency code>,<type code>,<sign><amount>, <item count>,<fundstype>/

With the last 5 fields being repeated as many times as needed for each type code. Note that there is nodelimiter between the sign and the amount fields.

In this example, the file will use the following values:

03,0007895624,USD,010,500000,015,52741100,045,100000,072,79694140,074,2160995,100,707755472/

88,102,013,400,655514372,402,011,954,486385540/16,100,76995,V,011020,,

In the above example 88 is the continuation of record 03.

010 , 400,100 and 015 represent control_begin_balance, control_total_cr,control_total_dr andcontrol_end_balance respectively. These codes are not required to be mapped in the mapping template.When the bank statement loader program runs , it automatically picks up data depending upon the codesmentioned in the data file. It is recommended to have repeating sequence (<typecode>,<sign><amount>,<item count>,<funds type> in one line.

• Record 16 - Mandatory, multiple occurrences

The record type 16 will likely have many records, which should be the fourth and subsequent records.The format of each record should be as follows :

16,<transaction code>,<amount>,<funds type>,<bank ref #>,<customer ref #>,<text>/

In the example below, this is a regular payment (transaction code 100) for $769.95 (because precision is2), which has a value date of 20-OCT-2001 (as per fields 3 and 4). Once imported, we will see "BankReference Text" in the Agent field, "26446" in the Invoice field, and "Office supplies” in theDescription field.

16,100,76995,V,011020,,Bank Reference Text,26446,Office Supplies/

There will be one record for each payment. Sometimes, the text for a record type 16 will get really long.That is when you use an 88 record, or overflow record. For example:

16,100,1574543,V,011020,,A long amount of Text,Also a long amount of Text, Even still more Text/

could be broken up into 2 separate records such as:

16,100,1574543,V,011020,,A long amount of Text,Also a long amount of Text/

88, Even more Text/


This record should follow the last 16 record for the given bank account. The format should be asfollows:

49,<account control total>,<number of records for account>/

The account control total sums all the amounts in records 03, 16 and 88 and includes an amount sign.The record count field is the total number of records including 03, 16, 88 and 49 only.

In this example, the file will use the following values :

49,2306166,6/


This record follows the last 49 record. The format should be as follows :

98,<group control total>,<number of accounts>,<number of records in group>/

Group control total is the sum of all control totals in 49 records for this group. The number of accountswill be the same as the number of 03 records in the file.

For our example, the record would look like:

98,2306166,1/


This will be the last record in your file. The format should be as follows:

99,<file control total>,<number of groups>,<number of records>/

File control total is the sum of all group control totals in 98 records.

The number of groups should match the number of 02 records.

For our example, the record would look like: 99,2306166,1,10/

Concurrent programs to Run :

The Bank Statement Loader program can be executed in one of these three modes:

Load

Load and Import

Load, Import and Auto Reconciliation

The concurrent programs can be run in a variety of ways. When testing, it is recommended to run eachprogram one at a time to be able to identify the problematic process in case of errors. Once the setup iscompleted, all of the programs can be run in one step.

1.Bank Statement Loader :

The required parameters are :-

• Process Option - Choose "Load".

• Mapping Name - Pick the one created in the Bank Statement Mapping section, or any of thestandard ones.

• Data File Name - This is the name of the data file. Typical convention is to use the .dat extension.Example: TEST.dat

• Directory Path - If the data file is placed in the $CE_TOP/bin directory, leave this parameter blank.Otherwise, enter the entire path of the directory where the data file is located. Do NOT use anyenvironment variables in the pathname.

Bad example: $CE_TOP/out/TEST.dat

Good example: /amer/oracle/crmus01/crmus01appl/ce/11.5.0/out/ TEST.dat

• Display Debug - Defaults to "N", but it is recommended to set it to “Y" to aid in debugging issues.

This process kicks off three additional concurrent programs:

� Run SQL*Loader- <format name> - This program takes the data from the data file and loads it intothe CE_STMT_INT_TMP table. This program has no output, but the last page of the log fileshows the number of records loaded and those which were rejected.

� Load Bank Statement Data – This program takes the data from the CE_STMT_INT_TMPtable and loads it into the CE_STATEMENT_HEADERS_INTERFACE andCE_STATEMENT_LINES_INTERFACE tables. This program has no output file and the log fileis not helpful in debugging.

� Bank Statement Loader Execution Report - This program provides some information about whatthe previous programs did. It has an output report, which has minimal value. Occasionally, therecould be an error message or warning, but generally it shows a normal output even if there wereproblems encountered in the process. The log file is not helpful in debugging issues.

2.Bank Statement Import

a. Enter the bank branch name and the bank account number you want to load from the bankstatement file. If nothing is entered, the entire file is loaded. Otherwise, only statements belongingto the bank branch or the bank account are loaded.

b. If you selected either the option of Load and Import or Load,Import, and AutoReconciliation,enter the GL Date.

c. If you selected the option of Load, Import and AutoReconciliation,enter the Receivables Activityand NSF Handling. Also enter the Payment Method if you specified a bank account number.

Note: If multiple files have been loaded, but only one is to be imported , use the Statement Date orStatement Number range parameters to limit the import job.

On successful completion, this program moves records from theCE_STATEMENT_HEADERS_INTERFACE and CE_STATEMENT_LINES_INTERFACEtables into the CE_STATEMENT_HEADERS and CE_STATEMENT_LINES tables.

This concurrent program launches one other concurrent program:

� Auto Reconciliation Execution Report - This report shows exceptions that may have occurredduring the import.

3.AutoReconciliation

a. Enter the bank branch name and the bank account number that needs to be loaded from the bankstatement file. If nothing is entered, the entire file is loaded. Otherwise, only statements belongingto the bank branch or the bank account are loaded.

b. If either the option of Load and Import or Load, Import and AutoReconciliation is selected, thena GL Date needs to be entered.

c. If the option of Load, Import and AutoReconciliation is selected , then the Receivables Activityand NSF Handling needs to be entered. If a bank account number is specified then enter thePayment Method.

Note: If multiple files have been loaded, but only one is to be imported, use the Statement Date orStatement Number range parameters to limit the import job.

On successful completion , this program will reconcile the imported bank statement lines tooutstanding AP and AR transactions. If unsuccessful, it will mark the bank statement line with anerror and allow manual reconciliation of the transaction.

This concurrent program launches one other concurrent program:

� Auto Reconciliation Execution Report – Now the same report shows exceptions in matching up theimported bank statement transactions with the existing AP, AR,GL,Payroll and Miscellaneoustransactions in the system. It gives descriptive reasons why the line was not able to be reconciledautomatically.

SWIFT 940

SWIFT940 is a common format used by many banks to provide institutional Customers, electronic bankstatements. If the bank provides this type of statement, Bank Statement Open Interface can be used to loadbank statement information into Oracle Cash Management.

The Bank Statement Loader program uses a predefined SQL*Loader script to load SWIFT940 bankstatement files. However, since each bank adopts the SWIFT940 standard slightly differently, SWIFT940format has to be mapped to the Bank Statement Open Interface tables. The mapping information can becreated from the SWIFT940 mapping template.

The SWIFT940 SQL*Loader script is located in $CE_TOP/bin/SWIFT940.ctl.

After the SWIFT940 bank statement files are loaded into the open interface tables, new bank transactioncodes have to be defined in Cash Management. SWIFT940 transaction codes represent the type oftransaction. For example, TRF represents transfers. SWIFT940 transaction codes do not, however, containinformation about the debit or credit nature of the transaction. Instead, the Debit/Credit Mark field is usedto differentiate debit and credit entries, where D means debit and C means credit. When the Bank StatementLoader program populates the TRX_CODE column in the Bank Statement Lines Interface table, it appendsthe Debit/Credit Mark to the transaction code to form a new code. For example, debit transfers areidentified as TRFD and credit transfers as TRFC. Set up these new transaction codes before importing thebank statement information.

Use the Bank Statement Mappings window to map the structure of the bank statement file to the BankStatement Open Interface tables. Mapping has to be defined for each unique file structure.

The default mapping templates for BAI2, SWIFT940, and French EDIFACT are provided by CashManagement and cannot be changed. New mapping templates can be created by copying the existingtemplates.

• Open the Bank Statement Mappings window.

• To review a mapping template, choose one from the list of values. Otherwise, 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 mappingtemplate. Choose an existing script from the list of values or enter a new script that is created. If the bankuses the BAI2 format, SWIFT940 format or French EDIFACT format , choose BAI2.ctl, SWIFT940.ctlor EDIFACT.ctl respectively. If the bank uses a user-defined format, enter the SQL*Loader script that iscreated.

• In the Date Format field, enter the date format that is used in the bank statement file. The default formatfor BAI2 and SWIFT940 is YYMMDD. The default format for French EDIFACT is DDMMYY.

• In the Precision field, enter a precision for the amounts if they are expressed as integers and have thesame precision within the bank statement file. The Bank Statement Loader program uses the precision toconvert integer amounts into decimal numbers. For example, an amount expressed as 10000 with aprecision of 2 is interpreted and loaded as 100.00. Leave the precision blank if amounts are alreadydecimal 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 isspecified for each amount on the header and line records.

• In the Bank File Format Type field, enter the format type of the bank statement file. Enter a new formator choose from a list of previously defined formats. If a new format was not defined, the seeded choicesare BAI2, SWIFT940, and EDIFACT-France. Once a mapping template for a new format is created, thatformat is available in the list of values. Choose BAI2, SWIFT940 format or the French EDIFACTformat, depending on what the bank uses. If the bank uses a user-defined format, enter that format.

• Choose the Populate button. If an existing format is chosen as the Bank File Format Type, the rulesdefined in the mapping template for that format are automatically entered in the Header and Linestabbed regions. If multiple mapping templates are defined for that format, then one has to be selectedfrom the list of values. However, if a newly defined format is used, only the column names are entered inthe Header and Lines tabbed regions.

• The Headers tabbed region lists all the columns in the Bank Statement Headers Interface table. Definethe mapping for the columns according to the file format used by the bank. The mandatory columns inthe table are:

STATEMENT_NUMBER

BANK_ACCOUNT_NUM

STATEMENT_DATE

ORG_ID.

OTHERS If the bank uses a format other than BAI2 or SWIFT940, a SQL*Loader script has to be developed and anew mapping template created for that format.

To load user-defined formats, the following customizations are required:

• A SQL*Loader script to read the bank statement file and copy the data to the intermediate table

• A mapping template to map the records and fields in the bank statement file to the columns inthe Bank Statement Open Interface tables.

ERRORS AND TROUBLESHOOTING Warnings and errors encountered by the Bank Statement Loader program are listed in the execution report.The process can error out when :

• the file format for BAI2 bank statement files is incorrect

• the bank account listed in the bank statement files is not defined in the system.

On the other hand, the program completes but warnings are raised in the following scenarios:

• when the number of records does not match the record count specified in a BAI2 bank statementfile.

• when the same bank account number is used by multiple banks and the bank and bank branchinformation is not present in the open interface tables.These details have to be entered manually.

• when dates do not match the expected format.

• If data from a bank statement file already exists in the Bank Statement Open Interface tables, thetables will be purged and the file will be reloaded.

• when data cannot be parsed.

ERROR NAMES AND TEXT• Name: CE_CANNOT_CONVERT_DATE

Text: The string &DATE cannot be converted to the date type by using &FORMAT

format.

• Name: CE_ERROR_EXIST

Text: Import/Auto Reconciliation program cannot be submitted since the error was

generated during the loading process.

Fix the error before running Import/Auto Reconciliation program.

• Name: CE_LOOKUP_FAIL

Text: The loading program was not able to locate &FIELD information in the bank statement file.

• Name: CE_SQLLDR_MISS_REQ_FIELD

Text: The loading program was not able to locate &FIELD information which is required in the BankStatement Interface.

• Name: CE_SQLLDR_MISSING_RECORD

Text: Counted number of records (&COUNTED) do not match the number of records specified(&GIVEN) in the bank statement file.

• Name: CE_BANK_ACCNT_NOT_DEFINED

Text: Bank account &BANK_ACCNT is not defined. Set up the bank account first.

• Name: CE_TOO_MANY_BANK_ACCNT

Text: There is more than one bank with the same account number &BANK_ACCNT.

You may need to choose the bank and bank branch in Bank Statement Interface window manually.

• Name: CE_RECORD_FAIL

Text: Error &ERR was generated during the loading process.

• Name: CE_INVALID_BAI2

Text: The bank statement file format is not BAI2.

• Name: CE_RECID_IN HDR_LINE

Text: The record ID &RECID cannot be used for both header and line mapping.

• Name: CE_EXIST_HDR_DELETED

Text: The bank statement header information which is being loaded already exists in the Bank StatementInterface table. The &CNT existing bank statement header(s) is (are) deleted.

• Name: CE_ EXIST_LINE_DELETED

Text: The bank statement line information which is being loaded already exists in the Bank StatementInterface table. The &CNT existing bank statement line(s) is(are) deleted.

• Name: CE_INVALID_DATA_FILE

Text: The entered data file is not valid. The SQL*Loader program failed to load the data from&DATA_FILE.

• Name: CE_SQLLOADER_FAIL

Text: The submission for the SQL*Loader program did not complete successfully.

Check the SQL*Loader script.

• Name: CE_FORMAT_NOT_CORRECT

Text: The format entered is incorrect. See: Oracle Cash Management User Guide for formattingstandards.

• Name: CE_MISSING_MAPPING_INFO

Text: Enter a Rec ID and Position.

• Name: CE_MAPPING_NAME_EXIST

Text: This name already exists. Please, enter unique name.

COMMON ERRORS ENCOUNTERED

• ORACLE error 1 in FDPSTP

Cause: FDPSTP failed due to ORA-00001: unique constraint

(CE.CE_STATEMENT_HDRS_INTERFACE_U1) violated

ORA-06512: at "APPS.CE_BANK_STATEMENT_LOADER", line 2040

ORA-06512: at line 1

Solution

Go through the bank file and make sure the format is correct. If the transaction field in the bank file hasanything other than a numeric value this could be the reason you are getting the above error. Theapplication expects a numeric value in this field.

• Statement loads successfully, but Import fails with an error of "Check the opening and closingbalances in the control totals."

Solution

‘88’ records followed by ‘03’ has to start with a Type Code.(Opening Balance. etc)

• BAI2 Bank file successfully Loaded and Imported. When try to Auto reconcile, the system cannot.In the REFERENCE TAB (Bank Statements Window), you notice that in the fields titled: AGENTand INVOICE, the incorrect data exists.

Solution

The seeded mapping rule for BAI2 is created based on standard BAI2 specifications. However, theactual BAI2 data files sent from bank may have some variations.

In this case, create a new mapping rule based on the seeded BAI2 mapping rule. Remove themapping definition for invoice number and agent field in the mapping form so that the loaderdoesn't populate the incorrect data. As long as the Check Number is captured auto reconciliation ofthe check payment can be done.

However, BAI2 doesn't have a specific field for transaction numbers (check number). Banksnormally populate check numbers in the description field. Hence the mapping rule can be setup insuch a way that the loader can capture the transaction number from the description field.

FREQUENTLY ASKED QUESTIONS

• Why should 0 be prefixed to 10 & 15 in the bank statement file?

The control begin balance & control end balance are not being imported from CE_STMT_INT_TEMPtable to the cash management tables because the bank statement file BAI2 has some predefined codesfor identifying the columns. They are 010 -- for control_begin_balance ,400 -- for control_total_Dr ,100 -- for control_total_Cr and 015 -- for control_end_balance. In BAI2 and SWIFT940, user doesn't have toprovide mapping logic for these control field since the program takes care of it automatically.

• Why is data not populating the CE interface tables when it was correctly imported into thetemporary table CE_STMT_INT_TMP ?

When Bank Statement Loader is run it spawns two programs:

� Run SQL*Loader:

This program picks data from the data file and loads it into the CE_STMT_INT_TMP table. This is thefirst phase when the Bank Statement Loader uses a SQL*Loader script to insert data from the bankstatement file into the intermediate table. The SQL*Loader script understands the format of the file andparses the data accordingly. The intermediate table is simply a representation of the bank statement file inthe database, where a row corresponds to a record in the file and a column corresponds to a field in therecord.

� Load Bank Statement Data:

This program takes the data from the CE_STMT_INT_TMP table and loads it into theCE_STATEMENT_HEADERS_INTERFACE and CE_STATEMENT_LINES_INTERFACE tables.In this second phase, the Bank Statement Loader uses a set of mapping rules to transfer data from theintermediate table to the Bank Statement Open Interface tables. These rules map the structure of thebank statement file to the open interface tables. These rules may also perform simple pattern searches

and data transformations. Hence the validation of the format occurs at this stage. If the actual statementfile does not correspond to the mapping rules, it is rejected.

Hence the bank statement file should be designed to suit the BAI2 mapping rules. Using the mappingrules. Cash Management validates and populates the bank statement file into the interface tables.

• Why can a sign be attached to the control amount but not to the line amounts ?

The default format fixed for BAI2 file for record 03 is:

03,<bank account number>,<currency code>,<type code>,<sign& gt;<amount>,<item count>,<fundstype>/Type codes are the ones you define in Bank transaction codes window.

But the default format for record 16 (statement lines) is:

16,<transaction code>,<amount>,<funds type>,<bank ref #> ,<customer ref #>,<text>/

Note there is no "sign" here.

However this can be modified according to requirement, But a loader script has to be created to map thecustom format.

• How to define the record Id & Position for the columns - CONTROL_BEGIN_BALANCE,CONTROL_END_BALANCE, CONTROL_TOTAL_DR and CONTROL_TOTAL_CR?

This can be done in the mapping form by creating a new mapping rule and attaching the control file.Then press the ‘populate’ button to populate all the fields, attach the record id and position for theabove fields and finally save. In BAI2 and SWIFT940, the user does not have to provide mapping logicfor these control fields since the program takes care of it automatically.

REFERENCES

Note 136123.1, Note 155046.1, Note 201359.1, Note 161872.1

Bank Statement Loader-Feature Design Document

Bank Statement Loader Program2002Authors: Avijit Bhattacharyay Shome MukherjeeContributing Authors:

Oracle CorporationWorld Headquarters500 Oracle ParkwayRedwood Shores, CA 94065U.S.A.

Worldwide Inquiries:Phone: +1.650.506.7000Fax: +1.650.506.7200www.oracle.com

Oracle is a registered trademark of Oracle Corporation. Variousproduct and service names referenced herein may be trademarksof Oracle Corporation. All other product and service namesmentioned may be trademarks of their respective owners.

Copyright © 2002 Oracle CorporationAll rights reserved.

oracle cash management-bank statement load

Documents