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 !! 

Generate DoCentric AX report and send to customer throw Email using email template

In this requirement, we need to generate a Docentric report for salesInvoice report and send the report as a email attachment using the SysEmailTemplates. Here is the X++ code to generate a Docentric report stream and send it as email attachment.  

internal final class DaxSendDocCentricInvoiceReport
{
    #PrintMgmtSetup
    public static void printSalesInvoiceToMemory(CustInvoiceJour  _custInvoiceJour)
    {
        SalesInvoiceController     salesInvoiceController;
        SalesInvoiceContract       salesInvoiceContract;
        Args                       args = new Args();
        CustInvoiceJour            custInvoiceJour;
        SalesInvoiceJournalPrint   salesInvoiceJournalPrint;
 
       // select custInvoiceJour where custInvoiceJour.SalesId != '';
 
        // Add record to be printed.
        // In order to have the context table we need to set args.record().
        args.record(_custInvoiceJour);
 
        salesInvoiceController = SalesInvoiceController::construct();
        salesInvoiceController.parmReportName(
        PrintMgmtDocType::construct(PrintMgmtDocumentType::SalesOrderInvoice).getDefaultReportFormat());
 
        salesInvoiceContract = salesInvoiceController.parmReportContract().parmRdpContract();
        salesInvoiceContract.parmCountryRegionISOCode(SysCountryRegionCode::countryInfo());
        salesInvoiceController.parmArgs(args);
        salesInvoiceController.parmExecutionMode(SysOperationExecutionMode::Synchronous);
 
        SRSPrintDestinationSettings pds = salesInvoiceController.parmReportContract().parmPrintSettings();
        pds.printMediumType(SRSPrintMediumType::Memory_DC);

        //PrintMgmtPrintContext printContext = new PrintMgmtPrintContext();

        //PrintMgmtPrintDestinationTokens destinationTokens = SrsPrintDestinationTokens::constructByTokenType(#SRSPrintDestinationTokenPrintMgmt);
        //destinationTokens.parmPrintContext(printContext);
        //destinationTokens.replace(pds);

 
        // Use the default Docentric design.
        pds.parmMemoryPrintDestSettings_DC().parmOutputFileFormat(DocOutputFileFormat::PDF);
        pds.parmMemoryPrintDestSettings_DC().parmUseSsrsBuiltInDesign(false);
        pds.parmSrsPrintReportSettings_DC().setProperty_PrintSrsOriginalDesign(false);


        /* Uncomment this code if you want to use the original SSRS design instead */
        //pds.parmMemoryPrintDestSettings_DC().parmOutputFileFormatSrs(SRSReportFileFormat::PDF);
        //pds.parmMemoryPrintDestSettings_DC().parmUseSsrsBuiltInDesign(true);
        //pds.parmSrsPrintReportSettings_DC().setProperty_PrintSrsOriginalDesign(true);
    
        // Initalize SalesInvoiceJournalPrint class instance because there is no other way
        // NOT to use Print Management.
        salesInvoiceJournalPrint = SalesInvoiceJournalPrint::construct();
        salesInvoiceJournalPrint.parmPrintFormletter(NoYes::Yes);
        salesInvoiceJournalPrint.parmUsePrintManagement(false);
        salesInvoiceJournalPrint.parmUseUserDefinedDestinations(true);
        salesInvoiceJournalPrint.parmPrinterSettingsFormLetter(salesInvoiceController.parmReportContract().parmPrintSettings().pack());
 
        args.caller(salesInvoiceJournalPrint);
        args.parmEnumType(enumNum(PrintCopyOriginal));
        args.parmEnum(PrintCopyOriginal::OriginalPrint);
 
        /* Collect the result of the execution */
        // Register event handler to the RenderingComplete event.
        salesInvoiceController.renderingCompleted +=
        eventhandler(DaxSendDocCentricInvoiceReport::printSalesInvoiceToMemory_renderingComplete);
 
        // Start the report execution.
        salesInvoiceController.parmShowDialog(false);
        salesInvoiceController.startOperation();
    }

    public static void printSalesInvoiceToMemory_renderingComplete(SrsReportRunController _sender, SrsRenderingCompletedEventArgs _eventArgs)
    {
        // Get the report execution info.
        DocReportExecutionInfo reportExecutionInfo = _eventArgs.parmReportExecutionInfo().parmReportExecutionInfo_DC();
        DocPrintReportToMemoryExecutionInfo printDestMemoryExecutionInfo = reportExecutionInfo.parmPrintToMemoryExecutionInfo();

        // Use the report content, e.g. upload it to Azure Blob storage.
        using (System.IO.MemoryStream reportMemoryStream = printDestMemoryExecutionInfo.getReportContentAsMemoryStream())
        {
           // File::SendFileToUser(reportMemoryStream, "TestInvoice.PDF");
            PROSendDocCentricInvoiceReport::sendInvoiceByEmail(_eventArgs.parmArgs().record(), "thirupathi.gandi@protivitiGlobal.in", reportMemoryStream );
           // DocAzureBlobHelper::uploadBlob('docentric-report-output', 'TestInvoice.pdf', reportMemoryStream);
        }

        // Do something else with the report content, e.g. send it to a web service...
    }

    public static void sendInvoiceByEmail(CustInvoiceJour _custInvoiceJour, Email _ToEmail, System.IO.MemoryStream _reportStream )
    {
        SysEmailParameters              sysEmailParameters = SysEmailParameters::find();
        #define.SMTP('SMTP')
        Map sysMailers = SysMailerFactory::getMailers();
        SysEmailSystemTable SysEmailSystemTable = SysEmailSystemTable::find("SalesInv");

        SysEmailMessageSystemTable SysEmailMessageSystemTable = SysEmailMessageSystemTable::find("SalesInv",SysEmailSystemTable.DefaultLanguage);

        str messageBody = SysEmailMessageSystemTable.Mail;
        str subject     = SysEmailMessageSystemTable.Subject + '' + _custInvoiceJour.InvoiceID;

        if (SysEmailMessageSystemTable)
        {
            SysMailerMessageBuilder messageBuilder = new SysMailerMessageBuilder();
            try
            {

                messageBuilder.setBody(messageBody);
                messageBuilder.setSubject(subject);
                messageBuilder.addTo(_ToEmail);
                messageBuilder.addCc("thiruvaroon555@gmail.com");
                messageBuilder.addAttachment(_reportStream, strFmt("Sales Invoice %1.PDF", _custInvoiceJour.InvoiceId));
                messageBuilder.setFrom(sysEmailParameters.SMTPUserName);

                SysMailerFactory::sendNonInteractive(messageBuilder.getMessage());
            }
            catch
            {
                exceptionTextFallThrough();
            }
        }
        else
        {
            throw error("Please verify the email template for SalesInvoice");
        }

    }

Thanks!!