Duplicate consolidation transactions in AX 2012
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 GJAE
INNER JOIN GENERALJOURNALENTRY GJE ON GJAE.GENERALJOURNALENTRY = GJE.RECID
INNER JOIN LEDGER L ON GJE.LEDGER = L.RECID
WHERE 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 GJAE
INNER JOIN GENERALJOURNALENTRY GJE ONG JAE.GENERALJOURNALENTRY = GJE.RECID
INNER JOIN LEDGER L ON GJE.LEDGER = L.RECID
WHERE 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.
Tyler Lewin
Senior Support Engineer – Dynamics AX
