AX 2012 Unbalanced Trial Balance

--Find all DAVC's that do not tie to the levelvalueview indicating that the either the DALV, DAV or DAVG was deleted.
Select * from DIMENSIONATTRIBUTEVALUECOMBINATION a join DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION davgc on a.RECID = davgc.DIMENSIONATTRIBUTEVALUECOMBINATION
       where not exists (select 'x' from DIMENSIONATTRIBUTELEVELVALUEVIEW b where a.RECID = b.VALUECOMBINATIONRECID)
The purpose of this code is to re-generate the missing records and make the system show in the Trial Balance the accounts that missed the account name and account code and be shown correctly. If the code works correctly, it will show this data and also will allow the user to navigate to the transactions posted to this account as in any other record. We have verified that even after running this code and correct the missing data, the Trial Balance still shows incorrect balance by 15688.

These are the instructions necessary to make the Job work:

1. Backup database.
2. Rebuild balances for all the possible Dimension Sets that requires to be updated (in your case, the 4 of them). To do that, go to GL\Setup\Financial Dimensions\Dimensions sets. Select them and click Rebuild balance button in each one of the dimension sets.
3. Execute the Job ONCE. Take note of the info that will appear at the end of the execution of it. It has to show a message telling you what dimension set has been updated.
4. Go back to the dimension sets (GL\Setup\Financial Dimensions\Dimensions sets). Select the one you have just updated and then click again into Rebuild balances.
5. Go to Trial balance. Select the Dimension set that you have just updated and check the records. Now any record should be showing empty account code or account name. It will allow also to navigate to correct Posted transactions for this account.
6. Re-do all steps mentioned here for all dimension sets that contains records with errors. If at some point you execute the job and it says this message:

There is no more missing DimensionAttributeLevelValue records linked to table DimensionAttributeValueCombination

It means that there is more records “corrupted” or incorrect in system and everything should be now correct.

Please find below the code for this code suggestion Job. You need to generate a Job in AX and copy and paste this code.

static void CorrectingRecords_MissingDALV_Values(Args _args)
{
    GeneralJournalAccountEntry  entry;
    DimensionAttributeValueCombination  comb;
    DimensionAttributeLevelValue levelvalue;
    DimensionAttributeValueGroup valuegroup;
    RecId                           hierrecID;
    DimensionFocusUnprocessedTransactions   unproc;
    DimensionFocusBalanceCalculationState   state = DimensionFocusBalanceCalculationState::InProcess;
    DimensionFocusLedgerDimensionReference  focusRef;

    ttsBegin;

    //select statement to detect missing data in DALV table
    select firstonly valuegroup
    notExists join levelvalue
    where levelvalue.DimensionAttributeValueGroup == valuegroup.RecId;

    // Dimension hierarchy to be updated - some missing record has been found on it
    hierrecID = valuegroup.DimensionHierarchy;   

    if (hierrecID)
    {
        info(strFmt("The Dimension set being corrected is %1", DimensionHierarchy::find(hierrecID).Name));
       
        //delete all unprocessed records that pertains to the Dimension Hierarchy to be corrected
        delete_from unproc where unproc.FocusDimensionHierarchy == hierrecID;

        //insert in unprocessed table all records from the scratch in order to collect all possible missing records previously.
        //This is done extracting data also from GJAE table
        insert_recordset unproc (FocusDimensionHierarchy, GeneralJournalEntry, State)
        select hierrecID, GeneralJournalEntry, state from entry
        group by GeneralJournalEntry;

        delete_from focusRef where focusRef.FocusDimensionHierarchy == hierrecID;

        new DimensionFocusUpdateAsync().deleteHierarchy(hierrecID);
   
        state = DimensionFocusBalanceCalculationState::Complete;

        update_recordSet unproc setting State = state where unproc.FocusDimensionHierarchy == hierrecID;

        ttsCommit;

        info(info(strFmt("Update task completed for Dimension set %1", DimensionHierarchy::find(hierrecID).Name));
    }
    else
    {
        info("There is no more missing DimensionAttributeLevelValue records linked to table DimensionAttributeValueCombination");
    }

}
Gill