How to find quickly a difference between G/L Entry and Value Entry transactions

The reason for this post has been the fact that I'm very often asked how to deal with the amount differences between G/L Entry and reports that are build based on (Detailed) Value Entry transactions. Thus I decided to share some techniques of detecting this difference.

As you know, NAV system supports different ledgers that should correspond to each other when posting a document. When user makes a posting, the result usually appears in several ledgers, and the transactions that are created have integrity with each other.

This is especially valid when you book transactions to Balance Sheet accounts, not directly, but using specific cards (Item Cards, Vendor/Cusomer Card, etc.). E.g., when you post a transaction using item card, the following ledgers are filled with data: G/L Entry, Item Ledger, Value Entry Ledger.

But, is it always the case that both General Ledger and Item Ledger match? It is not always so, and there might be different reasons for that.

First of all, there can be a normal situation when G/L does not match Item Ledger. You can have a posting option to create G/L entries not in the same time as you post the document, but create only Item Ledger and Value Entry transactions, and then in the night run a batch job that would reveal G/L transactions based on the postings made. This is done in order to increase the performance of the system and reduce deadlocks when many users operate with G/L postings. Thus, at some moment in time you will have a normal difference in G/L figures and Item Ledger figures as G/L transactions might be still missing – until you run a certain batch job to reveal missing G/L transactions.

But, there might be another source of difference. One of the worst nightmare of a consultant is to realize that users have fired “Direct Posting” checkmark on for the account where the item cost should be booked, and they posted directly some transactions there. This would mean that only G/L transaction has been created, but other ledgers were not filled with consistent data. It resulted in the fact that the item costing reports do not match the information showed on G/L accounts. That is, in G/L Ledger there are transactions which are not present in Value Entry ledger, where they should be. Having been working in Awara IT for 12 years, I had seen quite a bunch of such cases.

For many companies there might be hundreds of thousands of item transactions, or millions, and this is quite a headache to figure out where is the difference. Below I will describe some of the technics you can use to find the transactions that were directly posted to G/L on the item account without Item Ledger transactions.

As we talk about Balance Sheet account, the erroneous postings might exist in whatever past period, and still affect the Ending Balance for the item account we analyze. There might be way too many transactions you could even not export them all in Excel for further analysis.

Solution

First of all, you would need to identify (and narrow as much as possible) the period for the analysis. It is advisable to use “half-division” method for that purpose. In practice, when you do not know where the error is, you take the whole period from the very beginning of the first data posted to your system and find a middle of that period. Build the reports of Item Ledger at that middle date and compare them to your G/L account. If there is a difference, then the error you are looking is in the earlier (first) part of the period. If both ledgers match, the error is in the second half of the whole period. Knowing that, you take that part period that contains the error and again divide it to two periods. Apply the same logic as in the previous step, and continue to divide and build reports at a middle date of each new shorter period, until you narrow this period to one month, or one week, or even a certain day – depending on the quantity of the transactions you have in your system. Here you are – you had found a narrow period where the error appeared.

Then, you go to a second step – the verification. Remember that for items for each G/L transaction of item account there should exist a corresponding Value Entry transaction, and your task is to find those G/L transactions which do not have their twins in Value Entry. How to do that fast?

Unfortunately, just examining G/L Entry will most likely not bring you a result, as there is no specific field in G/L Entry table that will tell you if the transaction has been posted as a “direct posting” or by system via certain Item Card. Neither you will find a direct reference to G/L transaction in Value Entry (which is awkward, as e.g. in Customer or Vendor Ledger Entry there is such a reference). You can try to look at the Description field of the transaction and figure out some specific rules by which you can identify the incorrect transaction which has been "directly posted", but the probability to catch it is low.

Luckily, we have a special table 5823 “G/L - Item Ledger Relation” that will tell you which Value Entry transactions are posted in G/L Entry, and what are their relation to each other. For each G/L transaction in this table there should be related Value Entry transaction. So you can take the range of G/L transactions, posted to item accounts in the suspected date of the error which you found at the previous step, and export that in Excel. Then, filter out the same range of G/L transactions in 5823 table to get the transactions that relate to Value Entry and also export that to Excel.

Now you will have two ranges of G/L transactions which you need to compare – e.g., use MATCH() function to search what G/L transactions from G/L Entry table do not exist in G/L - Item Ledger Relation table. Those that do not exist are those erroneous that you are looking for!

I hope for those who had not done this yet this will be a good support.