Bank Statement Loader Functionality in Oracle Release 11i and Release 12

PURPOSE:

This document will assist you in setting up and using the Bank Statement Loader Functionality in Release 11i and Release 12. It will even take you through the process of creating your own data file for testing purposes.

SCOPE & APPLICATION:

If the user is already in possession of a valid data file, then these steps can be followed by a casual user. However, the creation of a new test data file should only be attempted only by an accomplished user. These procedures only apply to Release 11i and Release 12.

Set Up and Use Bank Statement Loader:

There are several different formats which can be used when utilizing the Bank Statement Loader. For purposes of example, this document uses the BAI2 format. There are several areas which must be setup before you will be able to use the Bank Statement Loader successfully:

Step 1 - Bank Account Setup:

Create your bank and bank account in Accounts Payable. Note your bank account number and branch name.

Navigation: Payables --> Setup --> Payment --> Bank and Bank Branches.

Step 2 - Bank Transaction Codes:

Setup your Bank Transaction Codes in Cash Management. Find the bank account number that you are using, and enter the transaction codes provided by your bank. These differ from bank to bank, and thus are client specific. In our example, we will use the following:

Type	Code	Description	Transaction Source
Payment	100	Regular Payment	AP Payments
Payment	130	EFT/Wire Payment	AP Payments

Note: If the customer has Accounts Receivable, they may also have Receipt Type Bank Transaction Codes to define the same way.

Navigation: Cash Management --> Setup --> Bank Statements --> Bank Statement Transaction codes.

Type your Bank account Number.

Step 3 - Bank Statement Mapping:

Set up your Bank Statement Mapping in Cash Management. It is a good idea to copy the seeded mappings to your own so that you can modify it freely. When you enter the form, it will prompt you to find a

mapping. Cancel that dialog. When the find window disappears, give your new format a name and description. Use the existing control file and supply the desired date format. Define the precision and choose the appropriate format type. Then click on Populate and save.

This will copy the default mapping to your new name. You are then free to modify it as you wish. For our example, no changes were made to the default mapping.

Step 4 - Creating the BAI2 Data File:

If you already have a valid format data file from your bank, you can skip this step completely. However, if you want to create a sample BAI2 data file for testing purposes, follow the instructions in this step.

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

You will want to use a text editor like vi or notepad. If you use Microsoft Word or another word processor, you will have to Save As Plain Text. If you create the file on your PC, remember to FTP it as ASCII when you move it to the server.

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

Record 01 - Mandatory

---------------------------------

This should be your first record in the file. It should be of the following format:

01,<bank originator ID>,<bank customer ID>,<file creation date>,

<file creation time>,<file identification number>,<physical record length>, <block size>,<version number>/

For example, our file will use the following:

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-2001 at 2:31 PM. It has 80 characters per record, 1 record per block and is BAI2 format (as denoted by the version number 2).

Record 02 - Mandatory

----------------------------------

This should be the second record in the file. It should be of the following format:

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

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

For example, our 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.

Record 03 - Mandatory

------------------------------------

This should be the third record in the file. It should be of the following format:

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

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

For example, our file will use the following values:

03,10271-17621-619,USD,400,1153083,4,/

This denotes that for our USD bank account number 10271-17621-619, we have 4 transactions that total $11530.83.

Record 16 - Mandatory, multiple occurrences Record 88 - Optional, multiple occurrences

-------------------------------------------

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

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

In our example below, this is a regular payment (transaction code 100) for

$769.95 (because precision is 2), which has a value date of 20-OCT-2001 (as per fields 3 and 4). Once imported, we will see "Bank Reference Text" in the Agent field, "26446" in the Invoice field, and "Office supplies" in the Description field.

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

You will have 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 still more Text/

Record 49 - Mandatory

-------------------------------------

This record should follow the last 16 record for the given bank account. It should be of the following format:

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 for the account is for all records up to and including the 49 record itself.

For our example, the record would look like:

49,2306166,8/

Record 98 - Mandatory

----------------------------------

This record follows the last 49 record. It should be of the following format:

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 accounts will be the same as the number of 03 records in the file.

For our example, the record would look like:

98,2306166,1/

Record 99 - Mandatory

------------------------------------

This will be the last record in your file. It should be of the following format:

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/

Here is resulting data file in entirety:

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

02,7777777,121345678,1,011022,0000,USD,/ 03,10271-17621-619,USD,400,1153083,4,/ 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,8/ 98,2306166,1/ 99,2306166,1,10/

Concurrent programs to Run:

You have the option of running the concurrent programs in a variety of ways. When testing, it is recommended to run each program one at a time, so that you can see where in the process you are in case an error

occurs. Once you have the setup completed, you can run all of the programs in one step.

1. Bank Statement Loader:

Navigation: Cash management --> View --> Request --> Submit New Request --> Single Request

Select Bank Statement Loader Program from LOV.

The required parameters to this job are the following:

Process Option – Choose "Load".

Mapping Name - Pick the one you created in the Bank Statement Mapping section, or if you used a standard one, pick that.

Data File Name - This is whatever you named your data file. Typical convention is to use the .dat extension. Example: bofa123101.dat

Directory Path - If you placed your data file in the $CE_TOP/bin directory, leave this parameter blank. Otherwise, you must provide the entire directory path to your data file. Do NOT use any environment variables in your pathname.

Bad example: $CE_TOP/out/bofa123101.dat

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

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

This job will kick off three additional concurrent programs:

Run SQL*Loader- <format name> - This program takes the data from your data file and loads it into the CE_STMT_INT_TMP table. This program has no output, but you can see on the last page of the log file how many records got loaded and how many got rejected.

Load Bank Statement Data - This program takes the data from the CE_STMT_INT_TMP table and loads it into the CE_STATEMENT_HEADERS_INTERFACE and CE_STATEMENT_LINES_INTERFACE tables. There is no output for this program, and the log file is useless in debugging.

Bank Statement Loader Execution Report - This program provides some information about what the previous programs did. It has an output report, which has minimal value. Occasionally, you will get a good error message or warning, but typically it reports everything is fine even if there was an issue. The log file is not helpful in debugging issues.

2. Bank Statement Import

Navigation: Cash management --> View --> Request --> Submit New Request --> Single Request

Select Bank Statement Import Program from LOV.

The required parameters to this job are the following:

Bank Branch Name - Provide the bank branch name that you setup in the Bank Account Setup section.

GL Date - Although this parameter is not marked as required, the import will often fail if you do not provide a value for this parameter. The date must in an open period in both AP and AR.

Note: If you have multiple files loaded, but only want to import one of them, use the Statement Date or Statement Number range parameters to limit the import job.

If successful, this program moves records from the CE_STATEMENT_HEADERS_INTERFACE and CE_STATEMENT_LINES_INTERFACE tables into the CE_STATEMENT_HEADERS and CE_STATEMENT_LINES tables.

This concurrent program will launch one other concurrent program:

Auto Reconciliation Execution Report - Although misleadingly named, this report is useful. It will show exceptions which may have occurred during the import.

3. AutoReconciliation:

Navigation: Cash management --> View --> Request --> Submit New Request --> Single Request

Select AutoReconciliation Program from LOV.

The required parameters to this job are the following:

Bank Branch Name - Provide the bank branch name that you set up in the Bank Account section.

GL Date - Although this parameter is not marked as required, the import will often fail if you do not provide a value for this parameter. The date must in an open period in both AP and AR.

Note: If you have multiple files loaded, but only want to import one of them, use the Statement Date or Statement Number range parameters to limit the import job.

If successful, this program will reconcile the imported bank statement lines to outstanding AP and AR transactions. If unsuccessful, it will mark the bank statement line with an error and allow you to manually reconcile the transaction.

This concurrent program will launch one other concurrent program:

AutoReconciliation Execution Report - This time this same report shows exceptions in matching up the imported bank statement transactions with the existing AP and AR transactions in the system. It gives descriptive reasons why the line was not able to be reconciled automatically.