Using the Helper rule functionality
Many investment companies offer Debit Orders, Regular Withdrawals and LA Payments to their clients. These are usually processed once or twice a month as part of a batch run. The result of this is hundreds to tens of thousands of cashbook transactions (1 or more for each client!) and only 1 transaction on the bank statement, making this a particularly difficult recon.
Bank Recon allows you to import helper data to assist with this recon. This helper data is the same set of instructions initially sent to the bank, with any upfront rejections excluded. For every bulk recon, one set of helper data is imported: 1 Helper bank transaction which must match the bank statement amount exactly, and a helper record for every client payment or collection. This data is imported into Bank Recon using the Bank Details Import functionality. It is a separate process to the Bank and Cashbook Imports. The linking is only done via the recon rules, and it doesn't matter in what order the different imports are run. But the Helper Data, Bank Transaction and enough Cashbook Transactions need to be present in the database for the Helper rule to work.
Once imported, you need to write a Helper rule which uses the helper data to find the correct cashbook transactions and then reconciles everything together. If there is a helper cashbook transaction not yet present in the cashbook transactions (maybe it hasn't allotted), Bank Recon will create a contra pair of helper balancing entries for this transaction, use one in the recon and leave the other as unreconciled on the Cashbook Transaction side. This transaction can then be used in a same side match with the actual cashbook transaction once it allots or against the rejection on the bank side if it occurs.
Here is a small example
Debit Order Expected Transactions allotted Bank Statement Record
Client 1 - R 1 000 DO ACME - R 10 000
Client 2 - R 5 000
Client 3 - R 2 000
Cashbook Helper Data - Bank Helper Data -
Individual Client Instructions to the bank Total Amount Instructed to the bank
Client 1 - R 1 000 DO ACME - R 10 000
Client 2 - R 5 000
Client 3 - R 2 000
Client 4 - R 2 000
The Helper import process will link the cashbook and bank helper transactions together
The Recon rule will use the helper data to identify the correct Bank and Cashbook transactions
The percentage cashbook to match is variable. You can have a 100% match (ie. find every client instruction allotted in the Cashbook) or any percentage less that that
In the above example Client 4 does not yet exist in the Cashbook, so the Rule will create a +R 2000 and a -R 2 000 with the details on Client 4's helper transaction in the cashbook. The +ve transaction will be used in the helper match and the -ve transaction will remain behind to match to the actual cashbook transaction when it allots, or the bank rejection record if applicable.
Writing a Helper Rule and a Same-side matching rule
Examples of both parts of a helper rule are attached - Main Rule.txt and Helper Rule.txt
A Helper rule joins the cashbook and bank transaction tables to the recon helper tables in the SQL query to correctly identify the transactions for the recon
The main section of the rule returns the bank and cashbook records and determines the percentage to match (Main Rule.txt)
You can group by in the Datasets and make use of the GetKeysCommand to return the actual transaction Id's
The helper section uses the same joins and where clauses and returns the recon helper transactions to match (Helper Rule.txt)
All transactions get the same Recon Id to prevent any of them being used in another match
A same side match is a standard rule that has both LeftDataSet and RightDataset as Cashbook
Writing a Helper Rule to create Cashbook Transactions
As mentioned above, you may encounter a scenario where you have already been able to import your Helper data into Bank Recon, but some Cashbook Transaction data is not present in your system yet. In the following scenario, all our Cashbook data is missing, but we have imported Helper data for each transaction that makes up a single consolidated Bank Transaction from our statement. We would like to populate the Cashbook with new Transactions that reflect our Helper Cashbook Transactions. Each created Cashbook Transaction will have a positive and negative entry, which can be reconciled later.
Examples for the following scenarios are attached – “Helper - Recon Rule.txt” and “Helper - Helper Rule.txt”.
For the scenario to work and create items that can be reconciled, the Main Rule’s LeftDataSet and RightDataSet should be configured to select the Recon Helper IDs and Sum Total Amounts. These will be compared and reconciled.
The GetKeys commands will refer to the underlying Bank and Cashbook Transactions. Although the Cashbook Transactions do not exist yet, we provide a query that would select them if they did. The Bank’s GetKeys command will refer to the actual Bank Transaction.
The Helper Rule will identify all the Recon Helper Cashbook Transaction and Recon Helper Bank Transactions items for the Recon Helper ID we identified in our Main Rule. If the Helpers correspond to each other and are correctly selected by the Helper Rule and the Recon Rule has “Create Journal for Differences” checked, Bank Recon will be able to make new Transactions using this data.