Tuesday 20 August 2013

Bank Statement Reconciliation process in Cash Management


                                                           The main reason for the Cash Management module is 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 2nd post illustrates how to reconcile the statements. The 1st post dealt with loading and importing bank statements received from the bank.
 
Auto-reconciliation
Login to Cash Management Administrator responsibility
Navigation: View > Requests
We can run the Bank Statement Loader program to load, import and reconcile a statement by selecting Process option “Load, Import, and AutoReconciliation”.
Bank Statement Load, Import & Reconcile
The requests which will run are,
Requests run by AutoReconciliation
The last request is AutoReconciliation Execution Report. The output of the request is,
AutoReconciliation Execution Report output
 
AutoReconciliation program
If the bank statement is loaded and imported, we can also run AutoReconciliation as a separate program.
AutoReconciliation program
 
Tables
After AutoReconciliation completes, the statement lines STATUS change to “RECONCILED” from “UNRECONCILED” for the lines which the program could reconcile, in the CE_STATEMENT_LINES table.
select * from CE_STATEMENT_HEADERS_ALL where statement_number =’0025/001 – 25-DEC-11′
CE_STATEMENT_HEADERS_ALL
select * from CE_STATEMENT_LINES where statement_header_id =16593
CE_STATEMENT_LINES
Note that AutoReconciliation program could not reconcile all the lines as there is an error in the opening and closing balances in the totals and that is why some of the line STATUS is UNRECONCILED.
We shall reconcile those lines using Manual Reconciliation.
 
Manual reconciliation
Login to Cash Management Administrator responsibility
Navigation: Bank Statements > Bank Statement Interface
Search for the Bank account for which the statement was imported
Reconciliation form
List of all the statements for the Bank Account comes up.
Statement list
Select the Statement here and press Review button.
 
The imported Statement header appears
Review statement
See the reconciled lines by clicking “Reconciled” button
Reconciled lines in statement
No lines have been reconciled which means we need to manually clear all the transactions. Close this window and go back to the previous window. Click Available button in the previous window to show the available transactions for reconciliation.
The Find Transactions form opens up.  
Find transaction lines
Enter the details to list lines only based on the criteria and click Find.
Find available transactions for reconciliation
Reconcile the lines by clicking on the checkbox and pressing the Reconcile button.
If the program finds a difference in the starting and ending balances you will get a message.
Reconciliation error
Click Yes to clear the transactions. Close window and go back to the previous window.
Back to Bank Statement form
Click on Reconciled button. Now the form shows some data, i.e. those reconciled transactions for this account and statement.
Reconciled transactions
If AutoReconciliation program had run successfully then we would not need to reconcile manually as we have done in the previous 2 steps as all the statement lines would have shown up on the Reconciled Transactions form.
 
Tables
Check the table CE_STATEMENT_LINES now for this statement
select * from CE_STATEMENT_LINES where statement_header_id =16593
Check CE_STATEMENT_LINES table
The status gives shows whether the statement line has been reconciled or not. Note that the first 4 lines (statement line id 219892 to 219895) have been reconciled except for statement line id 219891.
Once a line is reconciled it is copied to CE_STATEMENT_RECONCILS_ALL table.
If we check in the table for all the statement lines we shall find only the reconciled lines.
select * from CE_STATEMENT_RECONCILS_ALL where statement_line_id IN(219892,219893,219894,219895,219891)
CE_STATEMENT_RECONCILS_ALL table
Note the status of the reconciled lines. The status is M.
 

Unreconcile Transactions

The lines can be unreconciled as well. If we check the boxes in the window shown earlier and click on Unreconcile, the lines will again be available for reconciliation.
Unreconcile transactions
If we check the reconciliation table, CE_STATEMENT_RECONCILS_ALL, we will find an extra line for each of the previously reconciled lines. Now the previously reconciled lines have the same status, ‘M’, and the new lines added for unreconciling the transactions have status as ‘U’. 
Status change in CE_STATEMENT_RECONCILS_ALL table
 
Manual Reconciliation
We can also reconcile manually by going to,
Bank Statements > Manual Clearing > Clear Transactions
Clear transactions
The Find Transactions form opens up, which we have seen before as well.
In this case the system asks for the Bank Account first
Find transactions: Select bank account
Then we can give the same criteria as we had given before, in the Transaction tab.
Find transactions: Select dates
Notice that we are back in the same form for clearing transactions.
Clear transactions form
Now we can clear transactions as shown above. This way transactions can be reconciled manually. Once the transactions are reconciled the transactions are updated with RECONCILED status as well in the sub ledgers.

No comments:

Post a Comment