Thursday, April 26, 2012

Steps to Implement Bank Statement Open Interface

Step 1: Define Internal Bank: Create your bank and bank account
Step 2: Define Bank Transaction Code:
In order to load electronic bank statements or use Cash Management’s Auto Reconciliation feature, Transaction codes has to be defined for each bank account. The transaction codes that your bank uses to identify different types of transactions on its statements. Define the bank transaction code for each code that you expect to receive from your bank with effective date range fields, Start Date and End Date, so that you can make a bank transaction code inactive.

Step 3: Define Mapping Rule
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. Then we are free to modify it.
Step 4: Placing of Statement File
Keep the bank statement file in the desired inbound folder. For example, $CE_TOP/DATA_IN
Step 5: Submission of Bank Statement import Program

The parameters are:-
·         Process Option - "Load". Or “Load and Import” or “Load, Import, and AutoReconciliation”
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 the Bank Statement Loader completes successfully. In option 3, both the Bank Statement Import program and the Auto Reconciliation program are launched after successful completion of the Bank Statement Loader program. In either case, if the Bank Statement Loader program completes with errors or warnings, the concurrent request terminates and no further process is started.

·         Mapping Name - Pick the one created in the Bank Statement Mapping section, or any of the standard 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 any environment variables in the pathname.
·         Display Debug - Defaults to "N", but it is recommended to set it to “Y" to aid in debugging issues.
·         Enter the bank branch name and the bank account number you want to load from the bank statement file. If nothing is entered, the entire file is loaded. Otherwise, only statements belonging to the bank branch or the bank account are loaded.
·         If you selected either the option of “Load and Import” or “Load,Import, and AutoReconciliation”, enter the GL Date.
·         If you selected the option of “Load, Import and AutoReconciliation”, enter the Receivables Activity and NSF Handling. Also enter the Payment Method if you specified a bank account number.
·         If multiple files have been loaded, but only one is to be imported, use Statement Date or Statement Number range parameters to limit the import job. 

This process kicks off the below additional concurrent programs:

Run SQL*Loader- - This program takes the data from the data file and loads it into the CE_STMT_INT_TMP table. This program has no output, but the last page of the log file shows the number of records loaded and those which were 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. This program has no output file and the log file is not helpful 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, there could be an error message or warning, but generally it shows a normal output even if there were problems encountered in the process. The log file is not helpful in debugging issues.

Bank Statement Import
On successful completion, this program moves records from the

Auto Reconciliation
On successful completion, 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 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 the imported bank statement transactions with the existing AP, AR, GL, Payroll and Miscellaneous transactions in the system. It gives descriptive reasons why the line was not able to be reconciled automatically.


Part1: Introduction and Graphical Representation of Bank Statement Import Process

Part 2: Step to implement Bank Statement Open Interface - Setups

Part 3: Table Details related to Open Interface Bank Statement

Part 4: Sample Script to submit Bank Statement loader program and capture the status of the concurrent programs

0 Responses to “Steps to Implement Bank Statement Open Interface”

Post a Comment


The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.