Customer aging in D365fo x++

Customer aging in D365fo x++

//call
container con0To5       = this.calculateAging(monthToDate - 5, monthToDate, hcmworker.PersonnelNumber, salestable.InventSiteId, DefaultDimensionView.DisplayValue);
container con6To15      = this.calculateAging(monthToDate - 15, monthToDate - 6, hcmworker.PersonnelNumber, salestable.InventSiteId, DefaultDimensionView.DisplayValue);
container con15To31     = this.calculateAging(monthToDate - 30, monthToDate - 16, hcmworker.PersonnelNumber, salestable.InventSiteId, DefaultDimensionView.DisplayValue);
container con31To60     = this.calculateAging(monthToDate - 60, monthToDate - 31, hcmworker.PersonnelNumber, salestable.InventSiteId, DefaultDimensionView.DisplayValue);
container con61To90     = this.calculateAging(monthToDate - 90, monthToDate - 61, hcmworker.PersonnelNumber, salestable.InventSiteId, DefaultDimensionView.DisplayValue);
container con95ToAbov   = this.calculateAging(dateMax() ,monthToDate - 90, hcmworker.PersonnelNumber, salestable.InventSiteId, DefaultDimensionView.DisplayValue);




public container calculateAging(TransDate _fromDate, TransDate _toDate, str _empId, inventSiteId _inventSite, str _division)
{
    CustTrans               custtrans;
    HcmWorker               HcmWorker;
    SalesTable              salesTable;
    CustInvoiceJour         CustInvoiceJour;
    CustPaymModeTable       CustPaymModeTable;
    CustSettlement          CustSettlement;
    DefaultDimensionView    DefaultDimensionView;

    real customerSettleAmount, customerAmount, balance;
    int  customer;

    select count(RecId) , sum(REPORTINGCURRENCYAMOUNT)
    from custtrans
    WHERE (custtrans.TRANSDATE >= _fromDate && custtrans.TRANSDATE <= _toDate)
    exists join custinvoicejour
    where custinvoicejour.LedgerVoucher     ==  custtrans.voucher
    && custinvoicejour.InvoiceAccount       ==  custtrans.AccountNum
    && custinvoicejour.InvoiceId            ==  custtrans.Invoice
    && custinvoicejour.InvoiceDate          ==  custtrans.TransDate
    exists join salesTable
    where salesTable.SalesId   ==  CustInvoiceJour.salesid
    && salesTable.InventSiteId   ==  _inventSite
    exists join DefaultDimensionView
    where DefaultDimensionView.DefaultDimension == salesTable.DefaultDimension
    && DefaultDimensionView.Name == 'Division'
    && DefaultDimensionView.DisplayValue == _division
    exists join HcmWorker
    group by custtrans.AccountNum
    where HcmWorker.RecId == salesTable.WorkerSalesResponsible
    && HcmWorker.PersonnelNumber == _empId;

    customerAmount = custtrans.REPORTINGCURRENCYAMOUNT;
    customer = custtrans.RecId;

    custtrans.clear();
    HcmWorker.clear();
    salesTable.clear();
    CustInvoiceJour.clear();
    CustPaymModeTable.clear();
    CustSettlement.clear();
    DefaultDimensionView.clear();


    
    SELECT SUM(SETTLEAMOUNTMST)
    FROM CustSettlement
    where (CustSettlement.TRANSDATE >= _fromDate && CustSettlement.TRANSDATE <= _toDate)
    join sum(REPORTINGCURRENCYAMOUNT)
    from custtrans
    WHERE CustSettlement.TRANSRECID == custtrans.RecId
    exists join custinvoicejour
    where custinvoicejour.LedgerVoucher     ==  custtrans.voucher
    && custinvoicejour.InvoiceAccount       ==  custtrans.AccountNum
    && custinvoicejour.InvoiceId            ==  custtrans.Invoice
    && custinvoicejour.InvoiceDate          ==  custtrans.TransDate
    exists join salesTable
    where salesTable.SalesId   ==  CustInvoiceJour.salesid
    && salesTable.InventSiteId   ==  _inventSite
    exists join DefaultDimensionView
    where DefaultDimensionView.DefaultDimension == salesTable.DefaultDimension
    && DefaultDimensionView.Name == 'Division'
    && DefaultDimensionView.DisplayValue == _division
    exists join HcmWorker
    where HcmWorker.RecId == salesTable.WorkerSalesResponsible
    && HcmWorker.PersonnelNumber == _empId;
    
    customerSettleAmount = CustSettlement.SettleAmountMST;

    balance =   customerAmount - customerSettleAmount;
    return [balance, customer];

}

Comments

Post a Comment

Popular posts from this blog

D365FO – AX – X++ –Refresh, Reread, Research, and ExecuteQuery

 D365FO – AX – X++ – Tutorial: Choosing Between Refresh, Reread, Research, and ExecuteQuery X++ developers often face challenges with the four datasource methods—refresh, reread, research, and executeQuery—in Dynamics 365 Finance and Operations (D365FO). Regardless of their seniority in AX, developers frequently make mistakes in the sequence of calling these methods. To address this, I've created a brief hands-on tutorial illustrating the typical use cases for each method. I've organized the methods based on their impact on the displayed grid rows. 1. Common Mistakes: Developers often incorrectly call two of these methods in the following order: - formDataSource.refresh() followed by formDataSource.research(), or - formDataSource.reread() followed by formDataSource.research(), or - formDataSource.research() followed by formDataSource.executeQuery(), or - formDataSource.research() followed by formDataSource.refresh() / formDataSource.reread(). These sequences are either entirely...
Read more

Create Inventory Journal through Code in D365FO X++

 Create Inventory Journal through Code in D365FO X++ Code:  internal final class Cls_FPS_MOVJRNL {     /// <summary>     /// Class entry point. The system will call this method when a designated menu      /// is selected or when execution starts and this class is set as the startup class.     /// </summary>     /// <param name = "_args">The specified arguments.</param>     public static void main(Args _args)     {         CBLFPS_StkAdjTbl       CBLFPS_StkAdjTbl;         ttsbegin;             // Create journal table             InventJournalTable inventJournalTable = Cls_FPS_MOVJRNL::createJournalTable();                          // Initialize journal trans             ...
Read more

SalesLine Reservation in D365fo x++

SalesLine Reservation in D365fo x++ SQL: /// <summary> /// reserve sales line /// </summary> /// <param name = "_salesline">SalesLine</param> public static void reserveSalesLine(SalesLine _salesline) { InventBatch inventBatch; InventSum inventSum; SalesLine salesLine; InventDim inventDim; InventTable inventTable; int daysLeftforExpiry = 0; int shelflife; int rule = 0; int acceptedPR = 0; Qty qtyReserve = 0; Qty saleLineQty = 0; DSAPickingRulePerId pickingrule; boolean qtyReservedCheck = false; salesLine = _salesline; pickingrule = _salesline.salesTable().DSAPickingRule; if(pickingrule == '') { warning("Picking rule is not available on sales order for reservation."); } else { ...
Read more