GL Trial Balance does not match transactions

(1) Share

Report

Posted on by b_radlyjames

3,155

If you go to 'Trial balance' in GL -> Common -> Trial Balance, you can get the balances for your main accounts.

Strange thing I found the other day... I put in a date range and get a balance for a particular account I'm looking at. I then click the 'Closing balance' which will pull up the transactions that make up that 'Closing balance'. Wierd thing is... They don't match! Never seen this before.

Any thoughts on what could cause these to be different?

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi bRradlyJames,

Are you operating an older AX2012 system?

There have a couple of hotfixes issued that deal with problems of the closing balance column in the Trial Balance form.

Please check out the available hotfixes at LCS to verify whether you miss one of those.

Best regards,

Ludwig

Was this reply helpful? Yes No
b_radlyjames 3,155 on at

Like (0)

Report

Ludwig,

I think you are correct. There seem to be several hotfixes that references my problem. Thank you!

Was this reply helpful? Yes No
b_radlyjames 3,155 on at

Like (0)

Report

Ok. I think I have found the issue here. I was finally able to track down the offending transactions and realized they weren't showing up in 'Voucher transactions' as well. In further research, I found that their GeneralJournalAccountEntry.LedgerDimension don't exist in DimensionAttributeValueCombination as a RecId.

This why when you run the Trial Balance, these transactions will show up sometimes but not others (Depends on whether they try to link to DimAttrValComb table).

How did this happen? I don't know. How do fix it? No clue at the moment...

Was this reply helpful? Yes No
b_radlyjames 3,155 on at

Like (0)

Report

I do have some customizations on the DAVC and supporting tables (some). This is a new position for me and I'm not 100% familiar with all the customizations yet. Not sure where else we have customizations that could have caused this.

Was this reply helpful? Yes No
b_radlyjames 3,155 on at

Like (0)

Report

Sukrut,

I have another theory... Our users were having issues with a GJ Line defaulting an incorrect financial dimension (incorrect worker) into the ledger account segmented entry. I think our contracted developer might have gone into the DAVC table and deleted that record in an attempt to prevent that defaulting from occuring.

Not sure, but seems very plausible at the moment.

Brad

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi Brad,

Do you have an older test/demo/backup system where the balances still match?

Then you can compare the tables and try to identify whether some records have been deleted from the DAVC table.

Best regards,

Ludwig

Was this reply helpful? Yes No
Suggested answer

MahmoudHakim 110 on at

Like (0)

Report

you can rebuild balances from dimension set to update trail balance.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

There are bugs where a simple rebuild of your Financial dimension set would repair the trial balance data (DimensionFocusBalance), but this is not one of them.

If you're missing a DimensionAttributeValueCombination record that is referenced in your GeneralJournalAccountEntry table, eventually you're going to have to replace the record (and all supporting records) if you want your ledger data to be consistent again.

Unfortunately a step by step on how to repair this is beyond what can probably be done online. If only 1 record from DAVC was deleted, then perhaps re-inserting it carefully and properly could be sufficient, and you would be very lucky in that case. As has been suggested, and old backup might reveal the original record before it was deleted. AX would not delete such a DAVC record, so without question a person who did not understand what they were doing did it.

Step 1 is to identify the problem and its scope.

You could do something like

SELECT * FROM GENERALJOURNALACCOUNTENTRY e WHERE NOT EXISTS (SELECT 1 FROM DIMENSIONATTRIBUTEVALUECOMBINATION d WHERE d.RECID = e.LEDGERDIMENSION)

to locate all GJAE records that are missing DAVC records. Or do a SELECT DISTINCT LEDGERDIMENSION instead, and that should tell you how many missing DAVC records you have (instead of how many inconsistent GJAE records you have, which is less useful).

Let us know what you find.

Was this reply helpful? Yes No