Question Status

Verified
Brandon Wiese asked a question on 25 May 2013 1:02 PM

There is a bug in Dynamics AX 2012 R2 regarding the Trial Balance where under the right conditions the total will drift out of balance with the underlying transactions.  Typically this will involve debits and credits the same ledger account within the same journal.  Doing a "Rebuild balances" on the affected dimension sets will clear the problem, but it can reoccur quickly.  In our case, the issue occurred multiple times daily until we implemented a fix ourselves.

My personal fix for this is attached.  It is provided for academic purposes only and no warranty is provided.  Use at your own risk.

The code responsible is in Classes\DimensionFocusUpdateBalance\createBalance where recently posted transactions waiting in the DimensionFocusBalanceTmp table are inserted or updated into the DimensionFocusBalance table.

The root cause of the problem is very subtle and revolves around a little known issue with doing a UPDATE in SQL against a 1:n JOIN.  The fix splits the update statement into two statements where debits and credits are applied separately.

The following SQL demonstrates the underlying root cause.  The results are different from what one would intuit at first glance.

 

create table #balances (   ACCOUNT nvarchar(10),   TOTAL NUMERIC(10,2) )

create table #balancestemp (   ACCOUNT nvarchar(10),   DEBIT NUMERIC(10,2),   CREDIT NUMERIC(10,2) )  

insert into #balances values (N'5000', 200.0)

insert into #balancestemp values (N'5000', 300.0, 0)

insert into #balancestemp values (N'5000', 0, -100.0)  

select * from #balances

select * from #balancestemp  

update b set b.total = b.total + t.debit + t.credit

  from #balances b

  join #balancestemp t on t.account = b.account  

select * from #balances

 

Reply
Gracey Gelizon responded on 29 May 2013 2:14 AM

Hello Sir Brandon,

Have you tried to run the hotfixes (KB2742864) to solved that issue.  That hotfixes can be downloaded in partnersource.  Here the lisst applicable for trial balance issue.  Apply only what is applicable in your environment

.

KB2738056  (To update Kernel version)

KB2742864 - Poor performance experienced while running “Rebuild balances” in Financial Dimensions set.

KB2754985 (Trial Balance Detail & Summary mis match error)

KB2810622 (The Summary Trial balance report is unbalanced by allocated amount)

KB2828929 – CU5 KB

KB2846993 (Detailed trial balance report performance needs improvement and accumulated totals are not including opening balances)

Reply
Bill Curtis responded on 18 Jun 2013 11:13 AM

Bamboogypsie,

Unless I'm mistaken, the hotfixes you list appear to be at least in part related to the RTM rather than R2 version of AX 2012 which Brandon is reporting about.

Reply
Verified Answer
Brandon Wiese responded on 27 Jun 2013 6:09 PM

This bug has been fixed in AX 2012 R2 CU 6.

2820691

Dimension set balance updates do not handle debits and credits in a single update

GFM\General Ledger

Reply
Verified Answer
Brandon Wiese responded on 27 Jun 2013 6:09 PM

This bug has been fixed in AX 2012 R2 CU 6.

2820691

Dimension set balance updates do not handle debits and credits in a single update

GFM\General Ledger

Reply