Customer aging report for multiple legal entities

In one of our client, there were more than 5 legal entities which share the same customers as the customer were merged from Global address book.

System doesn't provide the aging for multiple legal entities, so it was very difficult for the user to export the aging report from each company and then merge them because there were thousands of customers.

Hence it was decided to create a separate aging report.

First I created a query in AOT.


Here is the contract class to get As on date.

[DataContractAttribute]
class DSS_CustomerAgingComparisonContract
{
    Amount      Amount;
    FromDate    asOnDate;

    [DataMemberAttribute("As On Date") ,SysOperationLabelAttribute(literalStr('As of date'))]
    public FromDate parmFromDate(FromDate _asOnDate = asOnDate)
    {
        asOnDate = _asOnDate;
        return asOnDate;
    }
}

I created a data provider class with the name 'DSS_CustomerAgingComparisonDP ' and added query and contract attribute and declared some global variables/buffers.

[
    SRSReportQueryAttribute(queryStr(DSS_CustomerAgingComparisonQuery)),
    SRSReportParameterAttribute(classStr(DSS_CustomerAgingComparisonContract))
]
class DSS_CustomerAgingComparisonDP extends SRSReportDataProviderBase
{
    DSS_CustomerAgingComparisonTmp      tmpCreditAging,
                                                                        tmpCreditAgingLocal,
                                                                        tmpCreditAgingFinilizedData;
    DueDate                             asOnDate;
    CustTrans                           custTrans;
    CustTable                           custTable;
    CustTransOpen                       custTransOpen;
    public  Name                        currentCompany;
}

to get the table for the report to show the data.

[SRSReportDataSetAttribute(tableStr(DSS_CustomerAgingComparisonTmp))]
    Public DSS_CustomerAgingComparisonTmp getTmpCreditAging()
    {
        select * from tmpCreditAging;
        return tmpCreditAging;
    }

In process report I used insert_recordset and update_recordset approach to generate the data fast.
For data the I used fixed aging periods.

[SysEntryPointAttribute(false)]
    public void ProcessReport()
    {
        Map                                 fieldMapping;
        QueryBuildFieldList                 fldList_custTable;
        
        Amount                              totalBalance;
        real                                amount = 0;
        QueryBuildDataSource                qbds_custTable;
        DSS_CustomerAgingComparisonContract creditAgingContract;
        Query                               query;
        QueryRun                            queryRun;
        CompanyInfo                         companyInfo;
       
        
        currentCompany = curExt();
        creditAgingContract = this.parmDataContract();
        asOnDate            = creditAgingContract.parmFromDate();


        query   = this.parmQuery();
        query.clearAllFields();
        qbds_custTable = query.dataSourceTable(tableNum(CustTable));
        fldList_custTable = qbds_custTable.fields();
        fldList_custTable.addField(fieldNum(CustTable, AccountNum));
        fldList_custTable.addField(fieldNum(CustTable, DataAreaId));
        fldList_custTable.addField(fieldNum(CustTable, Party));
        fldList_custTable.addField(fieldNum(CustTable, CustGroup));
        fldList_custTable.addField(fieldNum(CustTable, Currency));
        fldList_custTable.dynamic(QueryFieldListDynamic::No);

        fieldMapping = new Map(Types::String, Types::Container);
        fieldMapping.insert(fieldStr(DSS_CustomerAgingComparisonTmp, AccountId), [qbds_custTable.uniqueId(), fieldStr(CustTable, AccountNum)]);
        fieldMapping.insert(fieldStr(DSS_CustomerAgingComparisonTmp, DataArea), [qbds_custTable.uniqueId(), fieldStr(CustTable, DataAreaId)]);
        fieldMapping.insert(fieldStr(DSS_CustomerAgingComparisonTmp, Party), [qbds_custTable.uniqueId(), fieldStr(CustTable, Party)]);
        fieldMapping.insert(fieldStr(DSS_CustomerAgingComparisonTmp, CustGroup), [qbds_custTable.uniqueId(), fieldStr(CustTable, CustGroup)]);
        fieldMapping.insert(fieldStr(DSS_CustomerAgingComparisonTmp, Currency), [qbds_custTable.uniqueId(), fieldStr(CustTable, Currency)]);

        query::insert_recordset(tmpCreditAging, fieldMapping, query);

        while select DataArea from companyInfo
        {
            changecompany (companyInfo.DataArea)
            {
                this.prepareData();
                this.updateData();
            }
        }
    }

Here I'm preparing the data for the report.

public void prepareData()
    {
        this.clearBuffers();

        insert_recordset tmpCreditAgingLocal (Party, Day30)
            select custTrans
                join Party from custTable
                group by custTable.Party
                where custTable.AccountNum == custTrans.AccountNum
                && custTrans.TransDate <= asOnDate && custTrans.TransDate >= asOnDate - 30
            join sum(AmountCur) from custTransOpen
                where custTransOpen.AccountNum == custTrans.AccountNum
            && custTransOpen.RefRecId == custTrans.RecId;

        //this.clearBuffers();
        insert_recordset tmpCreditAgingLocal (Party, Day60)
            select custTrans
                join Party from custTable
                group by custTable.Party
                where custTable.AccountNum == custTrans.AccountNum
                && custTrans.TransDate <= asOnDate - 31 && custTrans.TransDate >= asOnDate - 60
            join sum(AmountCur) from custTransOpen
                where custTransOpen.AccountNum == custTrans.AccountNum
            && custTransOpen.RefRecId == custTrans.RecId;

        //this.clearBuffers();
        insert_recordset tmpCreditAgingLocal (Party, Day90)
            select custTrans
                join Party from custTable
                group by custTable.Party
                where custTable.AccountNum == custTrans.AccountNum
                && custTrans.TransDate <= asOnDate - 61 && custTrans.TransDate >= asOnDate - 90
            join sum(AmountCur) from custTransOpen
                where custTransOpen.AccountNum == custTrans.AccountNum
            && custTransOpen.RefRecId == custTrans.RecId;

        //this.clearBuffers();
        insert_recordset tmpCreditAgingLocal (Party, Day120)
            select custTrans
                join Party from custTable
                group by custTable.Party
                where custTable.AccountNum == custTrans.AccountNum
                && custTrans.TransDate <= asOnDate - 91 && custTrans.TransDate >= asOnDate - 120
            join sum(AmountCur) from custTransOpen
                where custTransOpen.AccountNum == custTrans.AccountNum
            && custTransOpen.RefRecId == custTrans.RecId;

        //this.clearBuffers();
        insert_recordset tmpCreditAgingLocal (Party, Day150)
            select custTrans
                join Party from custTable
                group by custTable.Party
                where custTable.AccountNum == custTrans.AccountNum
                && custTrans.TransDate <= asOnDate - 121 && custTrans.TransDate >= asOnDate - 150
            join sum(AmountCur) from custTransOpen
                where custTransOpen.AccountNum == custTrans.AccountNum
            && custTransOpen.RefRecId == custTrans.RecId;

        //this.clearBuffers();
        insert_recordset tmpCreditAgingLocal (Party, Above150)
            select custTrans
                join Party from custTable
                group by custTable.Party
                where custTable.AccountNum == custTrans.AccountNum
                && custTrans.TransDate <= asOnDate - 151 && custTrans.TransDate >= dateNull()
            join sum(AmountCur) from custTransOpen
                where custTransOpen.AccountNum == custTrans.AccountNum
            && custTransOpen.RefRecId == custTrans.RecId;
    }

Updating some data after inserting it in report's temporary table.

public void updateData()
    {
        Ledger                  ledger        = null;
        DirPartyTable           dirPartyTable = null;
        CurrencyExchangeRate    exchRate;

        select ledger where ledger.Name == curExt();
        exchRate = this.getExchangeRate(asOnDate, ledger.AccountingCurrency);

        tmpCreditAgingFinilizedData = null;
        insert_recordset tmpCreditAgingFinilizedData (Day30, Day60, Day90, Day120, Day150, Above150, Party)
            select sum(Day30), sum(Day60), sum(Day90), sum(Day120), sum(Day150), sum(Above150), Party from tmpCreditAgingLocal
            group by Party;

        update_recordset tmpCreditAging
            setting Day30 = tmpCreditAgingFinilizedData.Day30,
                    Day60 = tmpCreditAgingFinilizedData.Day60,
                    Day90 = tmpCreditAgingFinilizedData.Day90,
                    Day120 = tmpCreditAgingFinilizedData.Day120,
                    Day150 = tmpCreditAgingFinilizedData.Day150,
                    Above150 = tmpCreditAgingFinilizedData.Above150,
                    PartyName = dirPartyTable.Name,
                    Period1Start = asOnDate,
                    Period1End  = asOnDate - 30,
                    Period2Start = asOnDate - 31,
                    Period2End  = asOnDate - 60,
                    Period3Start = asOnDate - 61,
                    Period3End  = asOnDate - 90,
                    Period4Start = asOnDate - 91,
                    Period4End  = asOnDate - 120,
                    Period5Start = asOnDate - 121,
                    Period5End  = asOnDate - 150,
                    Period6Start = asOnDate - 151,
                    Period6End  = dateNull(),
                    ExchangeRate = exchRate,
                    Balance =  tmpCreditAgingFinilizedData.Day30   +
                                tmpCreditAgingFinilizedData.Day60   +
                                tmpCreditAgingFinilizedData.Day90   +
                                tmpCreditAgingFinilizedData.Day120  +
                                tmpCreditAgingFinilizedData.Day150  +
                                tmpCreditAgingFinilizedData.Above150
        join tmpCreditAgingFinilizedData
            where tmpCreditAgingFinilizedData.Party == tmpCreditAging.Party
        join dirPartyTable
            where dirPartyTable.RecId == tmpCreditAgingFinilizedData.Party;

        // deleting records with zero balances
        //delete_from tmpCreditAging
        //    where tmpCreditAging.Balance == 0;
    }

to get the exchange rate, as It was required to show the amount in USD as per the date provided.

public CurrencyExchangeRate getExchangeRate(TransDate _date, CurrencyCode _fromCurrency)
    {
        Ledger                      ledger = null;
        ExchangeRate                exchangeRate = null;
        ExchangeRateType            exchangeRateType = null;
        CurrencyExchangeRate        currencyExchangeRate = 0;
        ExchangeRateCurrencyPair    exchangeRateCurrencyPair = null;

        select ExchangeRate from exchangeRate
        join exchangeRateCurrencyPair
        where exchangeRateCurrencyPair.RecId == exchangeRate.ExchangeRateCurrencyPair
        && exchangeRateCurrencyPair.FromCurrencyCode == 'USD'
        && exchangeRateCurrencyPair.ToCurrencyCode == _fromCurrency
        && exchangeRate.ValidFrom <= _date && exchangeRate.ValidTo >= _date
            join exchangeRateType
        where exchangeRateType.RECID == exchangeRateCurrencyPair.EXCHANGERATETYPE
            join ledger
        where ledger.DEFAULTEXCHANGERATETYPE == exchangeRateType.RECID
            && ledger.Name == currentCompany;

        if(exchangeRate.ExchangeRate)
        {
            currencyExchangeRate = exchangeRate.ExchangeRate/100;
        }
        else
        {
            currencyExchangeRate = 1;
        }
        return currencyExchangeRate;
    }

Table buffers should set to null before using in cross company query otherwise data won't be inserted or wrong data will be inserted in the temp table.

    public void clearBuffers()
    {
        custTrans = null;
        custTable = null;
        custTransOpen = null;
        //tmpCreditAgingLocal = null;
    }

Thats it! Just deploy the report, create a new menu item and assign controller class to it and add it to menu. 
To know more on how ERP can help business book a free consultation session at Dynamics Stream Dubai at www.dynamicsstream.com
Follow us on LinkedIn for latest updates on Microsoft ecosystem - https://www.linkedin.com/company/dynamicsstream.com

Comments

Post a Comment

Popular posts from this blog

🚀 Export All D365 F&O Custom Models with a Single Batch File

  In Microsoft Dynamics 365 Finance and Operations (D365 F&O), managing and exporting models can be a repetitive task, especially when you're dealing with a large number of custom models. To streamline this process, you can automate model exports using a simple batch file. In this blog, I’ll walk you through how to create a .bat file that exports all your custom models in one go—without needing to execute each command manually. This is especially helpful during regular backups or before code deployments. 🧠 Why Use a Batch File? A batch file allows you to: Export multiple models in one go Create backups with date-based folders automatically Reduce human error and save time Reuse and modify easily 📂 Folder Structure Assumption In this example, we're assuming the following: D365 metadata is located at K:\AosService\PackagesLocalDirectory ModelUtil.exe is located in K:\AosService\PackagesLocalDirectory\bin You want to store exported models in  ...
Read more

SQL stored procedure to get Item's on hand on specified date

One of my client needed to have an inventory data text file generated directly from SQL query, In which there was a need have on hand inventory of items on specified date but not on inventory dimensions. So I went through system's class InventOnhand which is responsible for getting item's on hand on provided date and replicate the same logic to get item's on hand without considering inventory dimensions but a few tweaking will be required to get on hand on different dimensions. Here is the SP which require item number and date to get its on hand. USE [MicrosoftDynamicsAX] GO /****** Object:  StoredProcedure [dbo].[FG_ItemOnHand]    Script Date: 2/5/2018 10:56:49 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[FG_ItemOnHand] @ItemId nvarchar(20), @AsOfDate DATE, @OnHand DECIMAL(16, 2) OUTPUT AS DECLARE @Posted DECIMAL(16, 2), @Received DECIMAL(16, 2), @Deducted DECIMAL(16, 2), @Picked DECIMAL(16, 2), @Regi...
Read more