When we run a consolidation for a period that has already been consolidated, AX looks to see if the transaction exists already in the consolidation company’s consolidation history, and if it does the original transaction is removed and the new one is posted for the new consolidation. AX will not look at the actual ledger transactions themselves, but rather the consolidation history which is contained in the LEDGERCONSOLIDATEHISTREF table.
If you run the consolidation process and you notice transactions for a previous period are being duplicated, the likely cause of the issue is a separation between the ledger transaction (GENERALJOURNALACCOUNTENTRY) and the consolidation history reference (LEDGERCONSOLIDATEHISTREF). The consolidation history reference can be seen in General ledger | Inquiries | Consolidations | Transactions | Actuals. The problematic consolidation duplication occurs when we run a consolidation for a period that has already been consolidated, and does not have the transactions in the consolidation history reference. Since the source that AX looks at is the consolidation history reference rather than the ledger transactions, the new consolidation will post transactions that already exist again if there's no reference to the transaction in LEDGERCONSOLIDATEHISTREF.
To illustrate what this looks like, we'll post a single transaction in the subsidiary company, consolidate the transaction, delete the LEDGERCONSOLIDATEHISTREF (to recreate the data condition), show the duplication, and then how to fix it.
First, here's our original transaction in the subsidiary company:
This transaction is then consolidated, which can be seen in General ledger | Inquiries | Consolidations | Transactions | Actuals:
To show what is supposed to happen when we reconsolidate, let's run the consolidation again and examine the results. We can see that original consolidation's transactions have been removed, and the same transactions have been posted to the new consolidation:
In earlier versions of AX, this form contained a "Delete" button. The most common cause of consolidation duplicate transactions was a user deleting the transaction from this form, which leaves the transaction in the ledger but removes it from the consolidation history. As mentioned earlier, AX uses the LEDGERCONSOLIDATEHISTREF table to identify if a consolidation transaction already exists. By removing the transaction from this form, we're separating the ledger transaction from the consolidation history and the transactions will be duplicated upon reconsolidation. To simulate recreating this issue, we'll delete these records in SQL:
DELETE FROM LEDGERCONSOLIDATEHISTREF WHERE DATAAREAID = 'CEC'
We can see this removed the transactions from General ledger | Inquiries | Consolidations | Transactions | Actuals form, but the transactions still exist in the ledger:
The problem occurs when the data is in this state and we consolidate the same period. AX will look the LEDGERCONSOLIDATEHISTREF table, and since the transaction does not exist there a new one is posted even though it already exists in the system. We'll consolidate again to see the balances have doubled:
This is an issue of damaged data, and the only way to correct this will be in SQL. What we will do to isolate these records is look for any transaction that exists in the consolidation company that does not have a related record in consolidation history reference. This will include all transactions (revaluations, general journals, opening/closing transactions, etc.) so we'll need to restrict it further. Consolidation transactions do not have a voucher associated with them, so we query based on the voucher being blank and the accounting date. If you're not using a voucher on the opening/closing transactions, the following will not work as those transactions will also be removed. Here is what the select statement would look like to isolate these orphaned records, where the accounting date is the date of the subsidiary transaction to be consolidated and "CEC" is the legal entity name:
SELECT * FROM GENERALJOURNALACCOUNTENTRY GJAEINNER JOIN GENERALJOURNALENTRY GJE ON GJAE.GENERALJOURNALENTRY = GJE.RECID INNER JOIN LEDGER L ON GJE.LEDGER = L.RECIDWHERE L.NAME='CEC'AND ACCOUNTINGDATE BETWEEN '8-1-2014' AND '8-31-2014'AND GJE.SUBLEDGERVOUCHER = ''AND GJAE.RECID NOT IN (SELECT TRANSRECID FROM LEDGERCONSOLIDATEHISTREF WHERE DATAAREAID = 'CEC')
We can see the two orphaned records are returned:
If these transactions that are returned are the same records that are duplicated and you've verified that no other transaction types are included in the data, you can delete these transactions from the system. The query is similar to the select, but deleting from GENERALJOURNALACCOUNTENTRY:
DELETE GJAE FROM GENERALJOURNALACCOUNTENTRY GJAEINNER JOIN GENERALJOURNALENTRY GJE ONG JAE.GENERALJOURNALENTRY = GJE.RECID INNER JOIN LEDGER L ON GJE.LEDGER = L.RECIDWHERE L.NAME='CEC'AND ACCOUNTINGDATE BETWEEN '8-1-2014' AND '8-31-2014'AND GJE.SUBLEDGERVOUCHER = ''AND GJAE.RECID NOT IN (SELECT TRANSRECID FROM LEDGERCONSOLIDATEHISTREF WHERE DATAAREAID = 'CEC')
Since we are deleting records from GENERALJOURNALACCOUNTENTRY, you will need to rebuild your financial dimension set balances to ensure accurate information throughout the system. Once the rebuild is complete, we can see the trial balance is once again correct:
To ensure the fix is complete, we'll run a final consolidation and show the trial balance remains the same.
We can see future consolidations will process correctly, and the issue of duplicate transactions is resolved. Please note that these queries are not something that should be run in a production system, and these are provided for reference only, and will need to be modified for every environment.
Senior Support Engineer - Dynamics AX
Thanks for this interesting post.
Your created this corrupt data by deleting records via SQL, which normally no one would do. Is there are way to create this data without external data manipulation? If not, then this isn't really a problem or am I missing something?
Thanks for sharing this! As a financial controller responsible for the consolidation of my company with around 40 entities in AX 2012, I am very much interested in this topic. One question: if you never manipulate the database with direct sql commands, can it still happen that lines get deleted from LEDGERCONSOLIDATEHISTREF?
Maybe you can help me with another consolidation issue as well? When I consolidate a EUR company within my USD consolidation, the opening balance of the EUR company in the consolidation will keep the FX rate of 31 december. So, it does not get updated to the current month end FX rate (as it should be). Is there a solution for this?
As we started using AX only last year, this is the first year that we notice this problem. And if it cannot be solved, I am forced to do the consolidation outside of AX by the end of the year, because otherwise our auditors will not accept our figures...
Hi Jan -
The most common way that I see this data corruption happen is in earlier versions of AX, a user will delete the records in the Inquiries | Consolidations | Transactions | Actuals form using the "Delete" button instead of the "Remove transactions" button. To prevent user error, the later releases and cumulative updates have actually completely removed the "Delete" button.
I've seen cases where the LEDGERCONSOLIDATEHISTREF records are missing and the user states they have not used the "Delete" button, however this has never been recreated. If you're able to replicate this without the "Delete" button, be sure to open a support request right away so we can investigate what's happening.
I hope this helps!
Hi Daan -
As mentioned in my comment to Jan above, in some environments, users have the ability to delete the LEDGERCONSOLIDATEHISTREF records from the application in the Inquiries | Consolidations | Transactions | Actuals form using the "Delete" button instead of the "Remove transactions" button.
With regards to your exchange rate question - I know these can be tricky. We have another blog that has been extremely helpful for understanding currency revaluations in the consolidation company. Here's the link:
I know we've redesigned the consolidation form, depending on what build you're on. Earlier versions supported the option to run the foreign currency revaluation during the consolidation process, and this is not recommended (and has been removed in the later updates and releases). Best practice is to consolidate and run the foreign currency revaluation periodic process.
My advice would be to review the blog above as it demonstrates how to handle multiple consolidation currencies. If you still have an issue, I would recommend submitting a support case and we can take a look at what's happening.
Good luck, and thanks for reading!