Calculate Trail Balances In D365 FO X++

We often go through the requirements where we need to compute the Trail balances to get the vouher transactions amounts. Here , different ways to compute the trail balance in D365 fo X++

1. This is an easy way to compute trail balances without dimension sets. If required we can add the further ranges below in the dynamic query.

 protected Amount getProjBalanceForRevenue(ProjId _projId, MainAccountNum _mainaccount)
    {
        LedgerAllocationRuleSource  ruleSource;
        RecId                       keyValue;
        Map                         dimensionRange = new Map(Types::Int64, Types::String);
        Set                         dimensionRangeIncludeEmpty = new Set(Types::Int64);
        str                         emptyQueryStr = SysQuery::valueEmptyString();
        GeneralJournalAccountEntry  result;
        Amount                      DebitAmount,CreditAmount, TotalAllocatedAmount;


        Query query = new Query();
        QueryBuildDataSource qbds = query.addDataSource(tableNum(GeneralJournalAccountEntry));

        // Add fields from GeneralJournalAccountEntry to query
        qbds.addSelectionField(fieldNum(GeneralJournalAccountEntry, TransactionCurrencyAmount), SelectionField::Sum);
        qbds.addSelectionField(fieldNum(GeneralJournalAccountEntry, AccountingCurrencyAmount), SelectionField::Sum);
        qbds.addSelectionField(fieldNum(GeneralJournalAccountEntry, ReportingCurrencyAmount), SelectionField::Sum);

        // Configure the query to do a Group By currency & account/dimension combination
        qbds.orderMode(OrderMode::GroupBy);
        qbds.addSortField(fieldNum(GeneralJournalAccountEntry, TransactionCurrencyCode));
        qbds.addSortField(fieldNum(GeneralJournalAccountEntry, LedgerDimension));
        //  qbds.addRange(fieldnum(GeneralJournalAccountEntry,IsCredit )).value(SysQuery::value(Noyes::No));
        QueryBuildDataSource qbdsHeader = qbds.addDataSource(tableNum(GeneralJournalEntry));
        qbdsHeader.relations(true);
        qbdsHeader.joinMode(JoinMode::InnerJoin);
        qbdsHeader.fetchMode(QueryFetchMode::One2One);
        //    qbdsHeader.addLink(fieldNum(GeneralJournalAccountEntry, GeneralJournalEntry), fieldNum(GeneralJournalEntry, RecId));
        qbdsHeader.addRange(fieldNum(GeneralJournalEntry, PostingLayer)).value(queryValue(CurrentOperationsTax::Current));
        qbdsHeader.addRange(fieldNum(GeneralJournalEntry, Ledger)).value(SysQuery::value(Ledger::current()));
        QueryBuildDataSource qbdsFiscalPeriod = qbdsHeader.addDataSource(tableNum(FiscalCalendarPeriod));
        /// qbdsFiscalPeriod.addLink(fieldNum(GeneralJournalEntry,FiscalCalendarPeriod), fieldNum(fiscalCalendarPeriod, RecId));
        qbdsFiscalPeriod.relations(true);
        qbdsFiscalPeriod.joinMode(JoinMode::ExistsJoin);
        qbdsFiscalPeriod.addRange( fieldNum(fiscalCalendarPeriod, type)).value(SysQuery::value(FiscalPeriodType::Operating));
        QueryBuildRange qbr = qbdsHeader.addRange(fieldNum(GeneralJournalEntry, AccountingDate));
        qbr.value(SysQuery::range(fromDate, toDate));

        // Build the account and dimension range string from LedgerAllocationRuleSource for this rule
        this.createRuleSourceCriteria( dimensionRange, _Mainaccount, _projId);

        QueryBuildDataSource qbdsDavc = qbds.addDataSource(tableNum(DimensionAttributeValueCombination));
        qbdsDavc.joinMode(JoinMode::ExistsJoin);
        qbdsDavc.addLink(fieldNum(GeneralJournalAccountEntry, LedgerDimension), fieldNum(DimensionAttributeValueCombination, RecId));
        qbdsDavc.fetchMode(QueryFetchMode::One2One);

        LedgerAllocationProcessRequest::createDimensionRanges(qbdsDavc, dimensionRange.getEnumerator(), dimensionRangeIncludeEmpty);

        QueryRun queryRunloc = new QueryRun(query);

        while (queryRunloc.next())
        {
            result = queryRunloc.get(tableNum(GeneralJournalAccountEntry)) as GeneralJournalAccountEntry;
            if(result.IsCredit)
            {
                CreditAmount += result.AccountingCurrencyAmount;
            }
            else
            {
                DebitAmount += result.AccountingCurrencyAmount;
            }
        }
        TotalAllocatedAmount = DebitAmount + CreditAmount;
        
        return TotalAllocatedAmount;
    }

    public void createRuleSourceCriteria(Map _dimensionRange, MainAccountNum _mainaccount, ProjId _ProjId)
    {
        DimensionAttributeRecId mainAccountDimAttrId = DimensionAttribute::getWellKnownDimensionAttribute(DimensionAttributeType::MainAccount);
        _dimensionRange.insert(mainAccountDimAttrId, _mainaccount);
        _dimensionRange.insert(DimensionAttribute::findByName("Project").RecId, _ProjId);
    }

2. Computing trial balances and getting required closing balance based on operating type with dimension set.

 private void calcClosingBalanceAC()
    {
        LedgerTrialBalanceListPageBalanceParms  balanceParameters;
        DimensionHierarchy                      dimHier;
        TransDate                               fromDate,toDate;
        List                                    selected = new List(Types::Enum);
 
        selected.addEnd(CurrentOperationsTax::Current);

        dimHier = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, '@PRO_Synamedia:MaserviceId');
  
        balanceParameters = LedgerTrialBalanceListPageBalanceParms::construct();
  
        balanceParameters.parmDateCode('');
        balanceParameters.parmDimensionSetHierarchy(dimHier);
        balanceParameters.parmStartDate(dateNull());
        balanceParameters.parmEndDate(TrandateFilter.dateValue());
        balanceParameters.parmPostingLayers(selected);
        balanceParameters.parmIncludeClosingAdjustments(false);

        select firstOnly RecId from ledgerTrialBalanceTmp;

        LedgerTrialBalanceTmp::calculateBalances_V2(ledgerTrialBalanceTmp,
                                                    dimHier.Name,
                                                    balanceParameters.getStartDate(),
                                                    balanceParameters.getEndDate(),
                                                    balanceParameters.getIncludeOpeningPeriods(),
                                                    balanceParameters.getIncludeClosingAdjustments(),
                                                    balanceParameters.getIncludeClosingTransactions(),
                                                    balanceParameters.getPostingLayers(),
                                                    true,
                                                    true,
                                                    Ledger::current(),
                                                    balanceParameters.getDisplayMainAccountCategory());
    }

 private Real getClosingBalanceAc(BusinessUnit _BU)
    {
        real CLosingamount;
        select firstonly *
            from ledgerTrialBalanceTmp
                 where ledgerTrialBalanceTmp.DimensionValues[1] == unbilledRevnueAccount
                    && ledgerTrialBalanceTmp.DimensionValues[2] == _BU;

        if (ledgerTrialBalanceTmp.RecId)
        {
            CLosingamount = this.getClosingBalance();
        }
            
        return CLosingamount;
    }

 private real getClosingBalance()
    {
        DimensionAttributeValueCombination      dimensionAttributeCombo;
        GeneralJournalAccountEntry              generalJournalAccountEntry;
        DAXGeneralJourAccountEntryOperatingView GeneralJourAccountEntryOperatingView;
        Query query = new Query();
        QueryBuildDataSource        GJAccountEntryDs, GJEntryDs, FCDS, DAVC_Ds;
        QueryBuildRange             Operatingrange;
        real                        closingBalance;

        GJAccountEntryDs = query.addDataSource(tableNum(DAXGeneralJourAccountEntryOperatingView));
        GJAccountEntryDs.addRange(fieldnum(DAXGeneralJourAccountEntryOperatingView, AccountingDate)).value(queryRange(datenull(), TrandateFilter.dateValue()));

        DAVC_Ds = GJAccountEntryDs.addDataSource(tableNum(DimensionAttributeValueCombination));
        DAVC_Ds.addLink(fieldNum(PROGeneralJourAccountEntryOperatingView , LedgerDimension) , fieldNum(DimensionAttributeValueCombination , RecId));

        select firstonly dimensionAttributeCombo
           where  dimensionAttributeCombo.Recid == ledgerTrialBalanceTmp.LedgerDimension;

       // DimensionHierarchy dimHierLoc = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, SubBillParameters::find().PRO_MonthEndAutomationJourDimSet);
        DimensionHierarchy dimHierLoc = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, '@PRO_Synamedia:MaserviceId');

        this.updateQueryForLedgerDimension(query, dimensionAttributeCombo,  dimHierLoc);

        QueryRun qr = new QueryRun(query);
        while (qr.next())
        {
            GeneralJourAccountEntryOperatingView = qr.get(tableNum(DAXGeneralJourAccountEntryOperatingView));
            closingBalance += GeneralJourAccountEntryOperatingView.AccountingCurrencyAmount;
        }
        return  closingBalance;
    }

3. As performance concerned create a view for GeneralJournalAccountEntry Table for applying theoperating type ranges.

 private void updateQueryForLedgerDimension(Query _query, DimensionAttributeValueCombination _davcSearchCriteria, DimensionHierarchy _dimHier)
    {
        // If the account transaction details are related to the dimension set containing Main account only, the filter can
        // be applied directly to GeneralJournalAccountEntry for the account filtering.
        //boolean mainAccountTransactionDetails = this.updateQueryForMainAccountOnly(_query, _davcSearchCriteria, _dimHier);

        //if (mainAccountTransactionDetails == false)
        //{
        QueryBuildDataSource davcDS = this.getDimensionAttributeValueCombinationDataSource(_query);
        DimensionAttributeLevelValueView dalvView;

        while select DimensionAttribute, DisplayValue from dalvView
                where dalvView.ValueCombinationRecId == _davcSearchCriteria.RecId
        {
            DimensionRefFieldName dimensionValueColumnName = DimensionAttribute::find(dalvView.DimensionAttribute).DimensionValueColumnName;
            
            QueryBuildRange qbr = davcDS.addRange(fieldName2Id(tableNum(DimensionAttributeValueCombination), dimensionValueColumnName));
            qbr.value(queryValue(dalvView.DisplayValue));
        }

        // For any dimension specified in the hierarchy but not having a value in the ledger account, add an empty string criteria
        if (_dimHier)
        {
            DimensionHierarchyLevel dimHierLevels;

            while select DimensionAttribute from dimHierLevels
                    where dimHierLevels.DimensionHierarchy == _dimHier.RecId
                    notexists join dalvView
                        where dalvView.DimensionAttribute == dimHierLevels.DimensionAttribute
                            && dalvView.ValueCombinationRecId == _davcSearchCriteria.RecId
            {
                DimensionRefFieldName dimensionValueColumnName = DimensionAttribute::find(dimHierLevels.DimensionAttribute).DimensionValueColumnName;
                
                QueryBuildRange qbr = davcDS.addRange(fieldName2Id(tableNum(DimensionAttributeValueCombination), dimensionValueColumnName));
                qbr.value(SysQuery::valueEmptyString());
            }
        }
        //}
    }

 private QueryBuildDataSource getDimensionAttributeValueCombinationDataSource(Query _query)
    {
        QueryBuildDataSource davcDS = _query.dataSourceTable(tableNum(DimensionAttributeValueCombination));
        davcDS.joinMode(JoinMode::InnerJoin);

        return davcDS;
    }

Thanks !!