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 2^nd post illustrates how to reconcile the statements. The 1^st 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.