How to set up and use Bank Statement Loader
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. In our example, we will
use the following:
Bank: Bank of America
Branch: New York
Account number: 10271-17621-619
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.
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
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
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:
AutoReconciliation
Execution Report - Although misleadingly named, this report
is
useful. It will show exceptions which
may have occurred during the import.
3. AutoReconciliation
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.
