Cash Management module is used to reconcile all financial incoming and outgoing transactions related to Oracle. As the actual financial transactions have to be between supplier and customer banks it is necessary to get the bank statements on a regular basis and match the bank statement against all payments and receipts, etc. If for some reason certain transactions could not be reconciled then probing both the statement and the transaction in Oracle will bring out the discrepancy. Hence this is also very effective internal audit mechanism for the organization implementing Oracle.
This is a 2 part post. the current and 1st post deals with loading and importing the bank statements received from the bank. The 2nd post illustrates how to reconcile the statements.
Load Bank Statement
Login to Cash Management Administrator responsibility
Navigation: View > Requests
Run the program name, Bank Statement Loader
 |
| Bank Statement Loader program |
Enter the Parameters
 |
| Program parameters |
Available values in Process Option parameter LOV
 |
| Process Option values |
Drop the bank statement files on the file server. Note the location of the files as this to entered as a parameter.
 |
| Bank Statement files on server |
Enter parameters for the Bank Statement Loader program for 1 statement file
 |
| Bank statement loader with parameters |
All the requests fired for the Load program
 |
| Bank statement loader requests |
The request name, Run SQL*Loader- SWIFT940, gives the output to say how many records have been loaded into Oracle.
The program gives an output like,
 |
| Run SQL*Loader – SWIFT 940 output |
At this stage the records are loaded from the file into a staging table named, CE_STMT_INT_TMP.
Querying this table by, select * from CE_STMT_INT_TMP, will give us
 |
| CE_STMT_INT_TMP records |
The program Load Bank Statement Data validates the data against CE_STMT_INT_TMP table with the Mapping Name (This is the parameter entered at the beginning. The setup is given in Setup Steps in a later post). The statement moves to CE_STATEMENT_LINES_INTERFACE table.
SELECT *
FROM ce_statement_lines_interface
WHERE bank_account_num =’0110005173′AND trx_date =TO_DATE(’25-Dec-2011′,‘DD-MON-YYYY’)AND statement_number =’0025/001 – 25-DEC-11′
 |
| CE_STATEMENT_LINES_INTERFACE records |
The final request, Bank Statement Loader Execution Report, gives the overall status of the program. The output looks like the following,
 |
| Bank Statement Loader execution report output |
Import Bank Statement
There are multiple ways to import Bank Statements.
Step 1: Bank Statement Import program
Login to Cash Management Administrator responsibility
Navigation: View > Requests
 |
| Bank statement import program |
The utility of this program is that Bank Statements can be imported in bulk for a bank account and multiple statement numbers and dates.
Step 2: Bank Statement Interface form
Login to Cash Management Administrator responsibility
Navigation: Bank Statements > Bank Statement Interface
 |
| Bank Statement menu |
Bank Statement Interface form showing list of accounts on opening
 |
| Select a Bank Account |
We can select the bank account from here or if we cancel the list of values shown we come to the following form.
 |
| Select a Bank Account |
We shall now query for the account number and select the Statement according the date loaded.
 |
| Bank Account selected |
Click on Lines button to see the lines for this statement
 |
| Statement lines |
Close this window and click on Tools > AutoReconciliation on the menu
Now the statement will imported
Login to Cash Management Administrator responsibility
Navigation: Bank Statements > Bank Statement Interface
 |
| Bank Statement Interface form menu |
A popup window will appear showing 3 options, Import/reconcile/Import or reconcile. The option “Import” is selected and Submit button is pressed. If the option Import and Reconcile is selected then AutoReconciliation program will be executed after importing the statement. AutoReconciliation is explained in another post.
 |
| Import statement lines |
The concurrent programs executed for Statement Import is shown below. The last program to run is Bank Statement Import Execution Report output
 |
| Import requests |
The Bank Statement Import Execution Report output gives the overall status of the Import process. The report is shown below.
 |
| Statement import execution report output |
Tables
Once the Import program ends, the statement moves from CE_STATEMENT_LINES_INTERFACE table to CE_STATEMENT_HEADERS_ALL, CE_STATEMENT_LINES_ALL tables.
SELECT *
FROMce_statement_lines_interface
WHERE bank_account_num =’0110005173′AND trx_date =TO_DATE(’25-Dec-2011′,‘DD-MON-YYYY’)AND statement_number =’0025/001 – 25-DEC-11′
 |
| CE_STATEMENT_LINES_INTERFACE table |
Now are ready to reconcile the statement we have loaded and imported. In the next post I shall demonstrate how the reconciliation process takes place.
1 comments
hi jagadeesan,
ReplyDeleteThanks for the information,Could you post 'The setup is given in Setup Steps in a later post' as suggested in this post
Regards,
Krishna.