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
