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
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
CE_STATEMENT_HEADERS_INTERFACE and
CE_STATEMENT_LINES_INTERFACE tables into the CE_STATEMENT_HEADERS and
CE_STATEMENT_LINES tables
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
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “Steps to Implement Bank Statement Open Interface”
Post a Comment