Thursday, August 3, 2017

Trial balance dimension sets don't match

I had an issue with my customer where they would show one 'Closing amount' using Financial Dimension set A, and another 'Closing amount', using Financial dimension set B.

I was also getting strange behavior with reports as well.  If I ran the Trial balance in the form for Financial Dimension set A and then ran the Trial Balance Detail for the same set, it would differ in total amounts.

So, I ran a comparison of the two (if you click the 'Closing balance' it will show you the transactions that make up that amount).  I found the offending transactions fairly easily and then realized they weren't showing up in the 'Voucher transactions' form!

Further reasearch, mainly in table browsers, revealed that the GeneralJournalAccountEntry.LedgerDimension, for the above records, doesn't exist in DimensionAttributeValueCombination (RecId).  So, when that join is made, these records are left out.

How did this happen?  
Looks like, potentially, a developer from our partner deleted the record in order to fix another problem.  A user was attempting to enter a segmented ledger account (Financial Dimensions), and it kept defaulting a particular segment.  So, I think they went into DimensionAttributeValueCombination and deleted the record in the hopes of preventing that field from defaulting. (I think it was just some cache).

Well, I think the only solution, that is the quickest/simplest and lowest risk, is to recreate the record with the same RecId via SQL.  Then any of the other records that tied to this one via a LedgerDimension field on their respective table, should hook right back in.
Going through SQL directly didn't work. It apears, however, that doing a new record and associating the new RECID of that record from DimensionAttributeValueCombination to other records in GeneralJournalAccountEntry, DimensionAttributeValueCombinationGroup, and DimensionAttributeValueCombinationStatus.

What's the moral of this story?

Don't delete records from DimensionAttributeValueCombination!!!