Understanding Class SysDatabaseTransDelete on Microsoft Dynamics AX 2012 R3 CU 8

Settle all cutomer transaction open within sum of AmountMST is zero for Microsoft Dynamic AX 2009 SP1 RU8

    static void MyCode_Cust_Settlement_Settle_Zero(Args _args)
    {
        CustTrans                       _custTransMark;
        CustTable                       _custTable, _custTableMark;
        MyCode_CustTransOpenSumZeroView    _transOpenZero;
        CustTransOpen                   _custTransOpen;
        AmountCur                       amountCur,netSettledAmt;
        AccountNum                      custNum;
        TransDate                       asOfDate,settleDate;
        CustVendTransData               custVendTransData;
        CustVendOpenTransManager        manager;
        Boolean                         goProcess, isInfolog;
        int                             countCust,countTrans;
        ;
        netSettledAmt = 0;
   
        isInfolog   = true;
        goProcess   = false;
   
        asOfDate    = str2date('30-09-2019', 123);
        settleDate  = str2date('01-10-2019', 123);
   
        /** 1. Get list from customer open transaction view (customized), summarized by AccountNum, having sum AmountMST = 0  **/
        while select _transOpenZero
        join _custTable
        where _transOpenZero.MaxOfTransDate <= asOfDate
           && _custTable.AccountNum == _transOpenZero.AccountNum
           && _custTable.CustGroup == 'CORPORATE'
           && _custTable.AccountNum == 'CUST01'
        {
            //1.a. Reset marked settlement per customer
            countCust++;
            if(goProcess)
            {
                _custTableMark  = CustTable::find(_transOpenZero.AccountNum);
                manager         = CustVendOpenTransManager::construct(_custTableMark);
   
                if (manager.getSpecTransCount() > 0)
                    manager.resetMarkedTrans();
            }
   
            /** 2. Get list customer open transaction per customer **/
            while select _custTransOpen
            where _custTransOpen.AccountNum == _transOpenZero.AccountNum
            {
                /** 3. Get CustTrans based on CustTransOpen**/
                countTrans++;
                select firstonly _custTransMark
                where _custTransMark.RecId == _custTransOpen.RefRecId;
                //netSettledAmt += _custTransMark.remainAmountCur();
   
                if(isInfolog) info(strfmt("CustTrans %1 %2 %3 %4 %5", _custTransMark.AccountNum,_custTransMark.Voucher, _custTransMark.remainAmountCur(), _custTransMark.Txt, _custTransMark.RecId));
   
                if(goProcess) custVendTransData = CustVendTransData::construct(_custTransMark);
                if(goProcess) custVendTransData.markForSettlement(_custTableMark);
            }
   
            if(goProcess) CustTrans::settleTransact(_custTableMark, null, true,SettleDatePrinc::SelectDate, settleDate);
        }
        info(strfmt('Settled done! Customers: %1 Transactions: %2', countCust, countTrans ));
        info(strfmt('As of date: %1', asOfDate));
    }

List all tables, include Table group description and company status for Microsoft Dynamics AX 2012 R3

static void Z_ListTable(Args _args)
{
    TextIo file;
    FileName filename = @"F:\TableList.txt";
    container con;
    FileIoPermission permission;
    #File

    TableId         tableId;
    SysDictTable    sysDictTable;
    TableGroup      tableGroupx;
    NoYes           isPerComp;
    String255       tableName;
    int             tablecounter;
    Dictionary      dict = new Dictionary();
    EnumId   Id        = enumNum(TableGroup);
    DictEnum dictEnum  = new DictEnum(Id);
    int             EnumValue;
    ;

    try
    {
    permission = new FileIoPermission(filename, #io_write);
    permission.assert();
    file = new TextIo(filename, #io_write);
    if (!file)
    throw Exception::Error;
    file.outRecordDelimiter(#delimiterCRLF);
    file.outFieldDelimiter(";");

        for (tablecounter=1; tablecounter<=dict.tableCnt(); tablecounter++)
        {
            tableId         = dict.tableCnt2Id(tablecounter);
            sysDictTable    = new SysDictTable(tableId);

            if (sysDictTable && !sysDictTable.isTmp() && !sysDictTable.isMap())
            {
                tableGroupx  = sysDictTable.tableGroup();
                EnumValue = enum2Int(tableGroupx);

                isPerComp   = sysDictTable.dataPrCompany();
                tableName   = sysDictTable.name();

                con = connull();
                con = conins(con, 1, tableName);
                con = conins(con, 2, tableGroupx);
                con = conins(con, 3, dictEnum.index2Symbol(EnumValue));
                con = conins(con, 4, isPerComp);

                file.writeExp(con);
            }
        }

    }
    catch(Exception::Error)
    {
        error("You do not have access to write the file to the selected folder");
    }
    CodeAccessPermission::revertAssert();

    info('File Created!');

}

Marking Customer Transaction with Specific Amount - Unposted Settlement for AX 2012 R3

static void Create_CustSettleOpen(Args _args)
{
    CustVendOpenTransManager manager;
    LedgerJournalTrans ljt;
    CustTransOpen ctoselect,cto;
    CustTrans ctselect,ct;
    AccountNum accnnumx;
    Voucher vchx1,vchx2,vchx3,vchx4;
    Amount toBeSettlex,selectSettlex,selectSettleEndx,diffSettlex,totSettledx;
    ;
    accnnumx    = '3';
    vchx1       = 'ARCI00000001';
    vchx2       = 'ARCI00000002';
    vchx3       = 'ARCI00000004';
    vchx4       = 'ARCI00000005';
    toBeSettlex = 200000000; // Specifict amount to be settled to multiple voucher
    selectSettlex = 0;
   
    //Check existing CustTrans with criteria
    select count(RecId) from ctselect
    where ctselect.AccountNum == accnnumx
          && ctselect.TransDate == str2Date('01/07/2018',123)
          ;

    if(ctselect.RecId > 0)
    {
        // Fetch the added LedgerJournalTrans
        ljt = LedgerJournalTrans::find('G000001521', 'RINV1000011910', true);
        manager = CustVendOpenTransManager::construct(ljt);

        //loop find any open CustTrans based on criteria
        while select * from ctoselect join ct
        where ct.AccountNum == ctoselect.AccountNum
        && ct.RecId == ctoselect.RefRecId
        && ct.AccountNum == accnnumx
        && ct.TransDate == str2Date('01/07/2018',123)
        {
            if (ctoselect.AmountCur != ct.AmountCur)
            {
                throw error('invoice has been partially paid');
            }
            // Mark the invoice.
            selectSettlex = selectSettlex + ctoselect.AmountCur;
            diffSettlex = selectSettlex - toBeSettlex;

            //info(strFmt('%1 %2 %3',selectSettlex,toBeSettlex,ct.Voucher));

            if( selectSettlex > toBeSettlex)
            {
                manager.updateTransMarked(ctoselect, true);
                manager.updateSettleAmount(ctoselect, toBeSettlex-selectSettleEndx);
                //info(strFmt('IF %1 %2 diff %3 %4',selectSettlex,toBeSettlex,diffSettlex,ct.Voucher));
                break;
            }

            manager.updateTransMarked(ctoselect, true);
            selectSettleEndx = selectSettlex;
            //info('marked');

        }

        totSettledx = manager.getMarkedTotalDisplayCur();

        ttsBegin;
            // Update journal trans.
            ljt.MarkedInvoice = ct.Invoice;
            ljt.SettleVoucher = SettlementType::SelectedTransact;

            if(totSettledx > 0)
            { ljt.AmountCurCredit =  abs(totSettledx); }
            else
            { ljt.AmountCurDebit =  abs(totSettledx); }

            ljt.update();
        ttsCommit;      

    }
    else
    {
        throw error('voucher could not be found');
    }
    info(strFmt('done. total %1',totSettledx));
}

Add field description / txt to Customer Aging Report detail for AX 2012 R3

1. Add field to table: CustAgingReportTmp. (i.e. field: my_txt)

2. Update class: CustAgingReportDP, method: insertCustAgingReportTmp
2.a. add new variable:
       CustTrans        _custtrans;
       str 200          my_txt;

2.b. add process to update temp table CustAgingReportTmp:

    update_recordSet custAgingReportTmp 
         setting my_txt = _custtrans.Txt
    join _custtrans
    where   custAgingReportTmp.Voucher == _custtrans.Voucher
         && custAgingReportTmp.AccountNum == _custtrans.AccountNum;

    return custAgingReportTmp; 
2.c. Compile the class
2.d. Build > Generate Incremental CIL

3. Edit SSRS Report : CustAgingReport
3.a. update/refersh Datasets
3.b. add my_txt field to the report.

4. Good Luck !!

Using Query with Range and Grouping in AX 2012 R3

static void Test_Query_CustTrans_with_Grouping(Args _args)
{
    Query query,queryParam;
    QueryRun qr;
    QueryBuildDataSource qbdsCustTransSum,qbdsQuery;

    CustTrans  _custTrans;
    TransDate               toDate;

    toDate = str2Date('31102016',123);

    if (toDate == dateNull())
    {
        toDate = DateTimeUtil::date(DateTimeUtil::utcNow());
    }

    query = new query();

    qbdsCustTransSum = query.addDataSource(tableNum(CustTrans));

    qbdsCustTransSum.addRange(fieldNum(CustTrans, AccountNum)).value(queryValue(strFmt('1051')));
    qbdsCustTransSum.addRange(fieldNum(CustTrans, TransDate)).value(SysQuery::range(dateNull(), toDate));

    qbdsCustTransSum.orderMode(ordermode::GroupBy);
    qbdsCustTransSum.addGroupByField(fieldnum(CustTrans, AccountNum));
    qbdsCustTransSum.addGroupByField(fieldnum(CustTrans, TransDate));

    qbdsCustTransSum.addSelectionField(fieldNum(CustTrans, AmountMST), SelectionField::Sum);
    query.addHavingFilter(qbdsCustTransSum, fieldStr(CustTrans, AmountMST), AggregateFunction::Sum).value(SysQuery::valueNot('0'));

    qr = new QueryRun(query);

    while(qr.next())
    {
        _custTrans = qr.get(tableNum(CustTrans));
        info(strFmt('%1 %2',_custTrans.AmountMST,_custTrans.TransDate));
    }

}

Create Number Sequence on AX 2012 R3

public static void Create(
NumberSequenceCode  _numSeqCode,
Name    _txt,
NumberSequenceRange _highest,
NumberSequenceRange _lowest,
NumberSequenceRange _nextRec,
Name    _prefix,
Name    _format,
str 1 _manual,
str 1 _cont,
Name    _numSeqScopeStr
)
{
    NumberSequenceTable numberSeqTable;
    NumberSequenceScope numberSeqScope;
    NumberSequenceReference numberSeqRef;
    RefRecId _numSeqScopeR,_numSeqIdR;
    container   segments, segmentReset;
    str annotatedFormat, format;
    ;
    _nextRec =1;
    select firstonly * from numberSeqScope where numberSeqScope.DataArea == _numSeqScopeStr;
    _numSeqScopeR = numberSeqScope.RecId;

    if(!NumberSequenceTable::findByNaturalKey(_numSeqCode,_numSeqScopeR))
    {
        ttsBegin;
        numberSeqTable.clear();
        numberSeqTable.NumberSequence   = _numSeqCode;
        numberSeqTable.Txt              = _txt;
        numberSeqTable.Highest          = _highest;
        numberSeqTable.Lowest           = _lowest;
        numberSeqTable.NextRec          = _nextRec;

        if(_manual == 'Y')
        { numberSeqTable.Manual           =  NoYes::Yes; }

        if(_cont == 'Y')
        { numberSeqTable.Continuous       =  NoYes::Yes; }

        segments = segmentReset;
        //segments += [[-1, conPeek(rec, 8)]];// 0 company, -1 constant, -2 alphanumeric
        if(_prefix)
        { segments += [[-1, _prefix]]; }
        segments += [[-2, _format]];

        annotatedFormat  = NumberSeq::createAnnotatedFormatFromSegments(segments);
        format           = NumberSeq::createAnnotatedFormatFromSegments(segments, false);
        numberSeqTable.AnnotatedFormat      = annotatedFormat;
        numberSeqTable.Format               = format;
        numberSeqTable.NumberSequenceScope  = _numSeqScopeR;
        numberSeqTable.validateWrite();
        numberSeqTable.insert();
        ttsCommit;
    }
}