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 completely 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 actua 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

ċ
Helper Rule.txt
(2k)
Unknown user,
2 Mar 2015, 23:56
v.1
ċ
Main Rule.txt
(5k)
Unknown user,
2 Mar 2015, 23:56
v.1
Comments