The automatic payment reconciliation of Dynamics NAV and Business Central is great feature – load a statement file and match the open invoices within the blink of an eye. It’s every accountants dream and a basic feature in today’s world. Let’s have a quick but informative look into how it works and fill the gap the official documentation leaves.
Payment reconciliation is an essential process in the finance department – every company, regardless of the industry, has to do it. When a payment is recorded in the bank account of the company, it is necessary to match this transaction against the related invoice in Business Central. This could be both, an incoming or outgoing payment. The related invoice is then marked as closed (or partially payed). This is in important, because the payment will become visible in the Customer or Vendor Ledger Entries. This allows to control certain processes like the decision if it is necessary to send a reminder to a Customer (Due Date is passed, but no payment received yet).
In case you are interested in further details about this piece of key knowledge, I can recommend to have a look on YouTube where a lot of great videos are available.
I want to use this space here to explain a piece of the process which is not mentioned in the official documentation.
How does the automatic application work
Most of the ‘magic’ happens in Codeunit 1255 “Match Bank Payments”. As one of the first steps, the Codeunit looks up all open Customer Ledger, Vendor Ledger, Bank Account Ledger and Direct Debit Collection Entries and stores the found entries in buffer tables. But this is just the pre-heating.
After that, the Codeunit tries to match each Payment Reconciliation Journal Entry against the entries of the buffer tables, starting with the ones for Customers. The below is repeated for Vendors and Bank Account Ledger Entries also. In the last step, the “Text to Account”-mappings are applied.
The first try to match is done using the “Transaction ID” of the Payment Reconciliation Journal Entry. The Transaction ID is looked up in the Direct Debit Collection Entries. If there is a match, it’s valued with a high matching confidence.
The second try is the so called “Related Party Matching”. First part is a try to match the Bank Account number, from where the payment has been made, against the Customer Bank Accounts in Business Central. If this doesn’t work out, the second part tries to match the Customer Name and Address against the payment. Of course, only when such information is available. For both types of matching, a low matching confidence is configured (but they can sum up – see below).
The third try is the “Document Matching”. The document matching is the most obvious one. The field “Transaction Text” is used to try if it either matches against the “Document No.” or the “External Document No.” of the invoice. In case a matching is possible for either one, it would be valued with a medium matching confidence.
The final try is the match against the Remaining Amount. For that, the Transaction Date is used additionally to filter on the buffer tables. This step is more an addition to the second and third step to increase the matching confidence level. In case the Remaining Amount is the only matching criteria, it is valued with low matching confidence.
The assembled information of all tries is then summed up to calculate a matching score. When the matching score is above a certain limit (lowest entry in the Bank Payment Application Rules – see below), an automatic application is done.
Bank Payment Application Rules
The Bank Payment Application Rules are stored in Table 1252 “Bank Pmt. Appl. Rule”. These rules are used to calculate a matching score and matching confidence (low, medium or high) against the outcomes of each matching try explained above. For example, a match on the Customer Bank Account and Document No. would result in a high matching confidence. Whereby a matching in either one only, results in a medium matching confidence. I’m not quite sure if this table is meant to be accessible through the menu or the GUI in general. I have doubts on this, because I couldn’t find a page by filtering in the Objects nor in the attributes of the Table-Object.
In my opinion, above matching steps are important to know. Because as soon as a Customer starts to use the Payment Reconciliation Journal, questions will pop up why certain payments do show a “Low”, “Medium” or “High” Matching Confidence.
Unfortunately, it is not possible to extend the matching process with further rules. When following the new development rules by using Extensions only, it is not possible to that which I mentioned in an earlier blog post. The Codeunit provides not many Events that could be utilized to try to match on additionally fields. I have raised this limitation in the Ideas-platform of Business Central – please have a look and upvote!