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.
0 comments