query::insert_recordset x++

[SRSReportParameterAttribute(classstr(ALM_QtySaleAnalysisContract))]

class ALM_QtySaleAnalysisDp extends SRSReportDataProviderPreprocess

{

    ALM_QtySaleAnalysisContract         contract;

    ALM_QtySaleAnalysisTmp              qtySaleAnalysisTmp;

    ALM_QtySaleAnalysisStaging          qtySaleAnalysisStaging;

    TransDate                           fromDate, toDate;

    Str1260                             retailStoreIdMulti;

    Str1260                             itemGroupIdMulti;

    Str1260                             itemIdMulti;

    

    [SRSReportDataSetAttribute("ALM_QtySaleAnalysisTmp")]

    public ALM_QtySaleAnalysisTmp getALM_QtySaleAnalysisTmp()

    {

        qtySaleAnalysisTmp.setConnection(this.parmUserConnection());

        select qtySaleAnalysisTmp;

        return qtySaleAnalysisTmp;

    }

    public void processReport()

    {

        contract            =   this.parmDataContract();

        fromDate            =   contract.parmFromDate();

        toDate              =   contract.parmToDate();

        retailStoreIdMulti  =   contract.parmRetailStoreIdMulti();

        itemGroupIdMulti    =   contract.parmItemGroupIdMulti();

        itemIdMulti         =   contract.parmItemIdMulti();

        if(itemGroupIdMulti)

        {

            this.inserDataWithParm();

        }

        else

        {

            this.inserDataWithoutParm();

        }

    }

    public void insertIntoStaging()

    {

        RetailTransactionSalesTrans     retailTransactionSalesTrans;

        InventItemGroupItem             inventItemGroupItem;

        InventItemGroup                 inventItemGroup;

        InventTable                     inventTable;

        RetailStoreTable                retailStoreTable;

        RetailChannelTable              retailChannelTable;

        OMOperatingUnit                 omOperatingUnit;

        DirPartyTable                   dirPartyTable;

        ttsbegin;

        delete_from qtySaleAnalysisStaging;

        ttscommit;

        ttsBegin;

        insert_recordset qtySaleAnalysisStaging

        (

            ItemId,

            StoreCode,

            Qty,

            TransDate,

            CostAmount,

            NetAmount,

            SalesUnit,

            ItemGroupId,

            ItemGroupName,

            StoreName

        )

        select ItemId,Store,Qty,TransDate,CostAmount,NetAmount,Unit from retailTransactionSalesTrans

            where retailTransactionSalesTrans.TransDate >= fromdate && retailTransactionSalesTrans.TransDate <= toDate

            join inventTable where inventTable.ItemId == retailTransactionSalesTrans.itemId

            join ItemGroupId from inventItemGroupItem where inventItemGroupItem.ItemDataAreaId == inventTable.dataAreaId

            && inventItemGroupItem.ItemId == inventTable.ItemId

            join Name from inventItemGroup where inventItemGroup.DataAreaId == inventItemGroupItem.ItemGroupDataAreaId

            && inventItemGroup.ItemGroupId == inventItemGroupItem.ItemGroupId

            && retailTransactionSalesTrans.costAmount != 0

            join retailStoreTable where retailStoreTable.StoreNumber == retailTransactionSalesTrans.store

            join retailChannelTable where retailChannelTable.RecId == retailStoreTable.RecId

            join Name from omOperatingUnit where omOperatingUnit.RecId == retailChannelTable.OMOperatingUnitID;

        ttsCommit;

        while select forupdate qtySaleAnalysisStaging

        {

            ttsbegin;

            qtySaleAnalysisStaging.ConvertedQty = this.qtyToConvert(qtySaleAnalysisStaging.ItemId, qtySaleAnalysisStaging.Qty, qtySaleAnalysisStaging.SalesUnit);

            qtySaleAnalysisStaging.BaseUnit = this.baseUnit(qtySaleAnalysisStaging.ItemId);

            qtySaleAnalysisStaging.update();

            ttscommit;

        }

        /*ttsBegin;

        update_recordSet qtySaleAnalysisStaging

        setting StoreName = omOperatingUnit.Name

        join retailStoreTable where retailStoreTable.StoreNumber == qtySaleAnalysisStaging.StoreCode

        join retailChannelTable where retailChannelTable.RecId == retailStoreTable.RecId

        join omOperatingUnit where omOperatingUnit.RecId == retailChannelTable.OMOperatingUnitID;

        ttsCommit;*/

    }

    public void inserDataWithoutParm()

    {

        Query                           qr = new Query();

        QueryRun                        qRun;

        QueryBuildDataSource            qbds;

        Map                             fieldMapping;

        QueryBuildFieldList             fldList_RetailTransactionSalesTrans;

        RetailTransactionSalesTrans     retailTransactionSalesTrans;

        InventItemGroupItem             inventItemGroupItem;

        InventItemGroup                 inventItemGroup;

        InventTable                     inventTable;

        RetailStoreTable                retailStoreTable;

        RetailChannelTable              retailChannelTable;

        OMOperatingUnit                 omOperatingUnit;

        DirPartyTable                   dirPartyTable;

        ttsbegin;

        delete_from qtySaleAnalysisTmp;

        ttscommit;

        qbds = qr.addDataSource(tableNum(RetailTransactionSalesTrans));

        if(fromDate && toDate)

        {

            qbds.addRange(fieldNum(RetailTransactionSalesTrans,TransDate)).value(queryRange(fromDate, toDate));

        }

        if(retailStoreIdMulti)

        {

            qbds.addRange(fieldNum(RetailTransactionSalesTrans,Store)).value(retailStoreIdMulti);

        }

        if(itemIdMulti)

        {

            qbds.addRange(fieldNum(RetailTransactionSalesTrans,itemId)).value(itemIdMulti);

        }

        qbds.addRange(fieldNum(RetailTransactionSalesTrans,CostAmount)).value(SysQuery::valueNot(0));

        qbds.fields().clearFieldList();

        fldList_RetailTransactionSalesTrans = qbds.fields();

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, ItemId));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, Store));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, Qty));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, TransDate));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, CostAmount));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, NetAmount));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, Unit));

        fldList_RetailTransactionSalesTrans.dynamic(QueryFieldListDynamic::No);

        fieldMapping = new Map(Types::AnyType, Types::Container);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, ItemId), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, ItemId)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, StoreCode), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, Store)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, Qty), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, Qty)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, TransDate), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, TransDate)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, CostAmount), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, CostAmount)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, NetAmount), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, NetAmount)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, SalesUnit), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, Unit)]);

        query::insert_recordset(qtySaleAnalysisTmp, fieldMapping, qr);

        /*ttsBegin;

        update_recordSet qtySaleAnalysisTmp

            setting ConvertedQty = this.qtyToConvert(qtySaleAnalysisTmp.ItemId, qtySaleAnalysisTmp.Qty, qtySaleAnalysisTmp.SalesUnit)

            where qtySaleAnalysisTmp.ItemId;

        ttsCommit;*/

        while select forupdate qtySaleAnalysisTmp

        {

            ttsbegin;

            qtySaleAnalysisTmp.ConvertedQty = this.qtyToConvert(qtySaleAnalysisTmp.ItemId, qtySaleAnalysisTmp.Qty, qtySaleAnalysisTmp.SalesUnit);

            qtySaleAnalysisTmp.BaseUnit = this.baseUnit(qtySaleAnalysisTmp.ItemId);

            qtySaleAnalysisTmp.update();

            ttscommit;

        }

        ttsBegin;

        update_recordSet qtySaleAnalysisTmp

            setting StoreName = DirPartyTable.Name

            join retailStoreTable  where retailStoreTable.StoreNumber == qtySaleAnalysisTmp.StoreCode

            join retailChannelTable where retailChannelTable.RecId == retailStoreTable.RecId

            join dirPartyTable where retailChannelTable.OMOperatingUnitID == dirPartyTable.RecId;

        ttsCommit;

        ttsBegin;

        update_recordSet qtySaleAnalysisTmp

            setting ItemGroupId = inventItemGroupItem.ItemGroupId,

                    ItemGroupName = inventItemGroupItem.ItemGroupId+" - "+inventItemGroup.Name

            join inventTable where inventTable.ItemId == qtySaleAnalysisTmp.itemId

            join ItemGroupId from inventItemGroupItem where inventItemGroupItem.ItemDataAreaId == inventTable.dataAreaId

            && inventItemGroupItem.ItemId == inventTable.ItemId

            join Name from inventItemGroup where inventItemGroup.DataAreaId == inventItemGroupItem.ItemGroupDataAreaId

            && inventItemGroup.ItemGroupId == inventItemGroupItem.ItemGroupId;

        ttsCommit;

        select firstonly forupdate qtySaleAnalysisTmp;

        qtySaleAnalysisTmp.CompanyLogo = FormLetter::companyLogo();

        ttsBegin;

        qtySaleAnalysisTmp.update();

        ttsCommit;

    }

    public void inserDataWithParm()

    {

        Query                   qr = new Query();

        QueryRun                qRun;

        QueryBuildDataSource    qbds;

        Map                     fieldMapping;

        QueryBuildFieldList     fldList_ALM_QtySaleAnalysisStaging;

        InventItemGroupItem     inventItemGroupItem;

        InventItemGroup         inventItemGroup;

        InventTable             inventTable;

        RetailStoreTable        retailStoreTable;

        RetailChannelTable      retailChannelTable;

        OMOperatingUnit         omOperatingUnit;

        DirPartyTable           dirPartyTable;

    

        ttsbegin;

        delete_from qtySaleAnalysisTmp;

        ttscommit;

    

        this.insertIntoStaging();

        qbds = qr.addDataSource(tableNum(ALM_QtySaleAnalysisStaging));

        if(retailStoreIdMulti)

        {

            qbds.addRange(fieldNum(ALM_QtySaleAnalysisStaging,StoreCode)).value(retailStoreIdMulti);

        }

        if(itemIdMulti)

        {

            qbds.addRange(fieldNum(ALM_QtySaleAnalysisStaging,itemId)).value(itemIdMulti);

        }

        if(itemGroupIdMulti)

        {

            qbds.addRange(fieldNum(ALM_QtySaleAnalysisStaging,ItemGroupId)).value(itemGroupIdMulti);

        }

        qbds.fields().clearFieldList();

        fldList_ALM_QtySaleAnalysisStaging = qbds.fields();

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, ItemId));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, ItemGroupId));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, ItemGroupName));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, StoreCode));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, StoreName));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, Qty));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, TransDate));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, CostAmount));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, NetAmount));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, SalesUnit));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, ConvertedQty));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, BaseUnit));

        fldList_ALM_QtySaleAnalysisStaging.dynamic(QueryFieldListDynamic::No);

    

        fieldMapping = new Map(Types::AnyType, Types::Container);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, ItemId), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, ItemId)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, ItemGroupId), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, ItemGroupId)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, ItemGroupName), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, ItemGroupName)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, StoreCode), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, StoreCode)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, StoreName), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, StoreName)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, Qty), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, Qty)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, TransDate), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, TransDate)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, CostAmount), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, CostAmount)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, NetAmount), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, NetAmount)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, SalesUnit), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, SalesUnit)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, ConvertedQty), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, ConvertedQty)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, BaseUnit), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, BaseUnit)]);

    

        query::insert_recordset(qtySaleAnalysisTmp, fieldMapping, qr);

        ttsBegin;

        update_recordSet qtySaleAnalysisTmp

            setting ItemGroupName = inventItemGroupItem.ItemGroupId+" - "+inventItemGroup.Name

            join inventTable where inventTable.ItemId == qtySaleAnalysisTmp.itemId

            join ItemGroupId from inventItemGroupItem where inventItemGroupItem.ItemDataAreaId == inventTable.dataAreaId

            && inventItemGroupItem.ItemId == inventTable.ItemId

            join Name from inventItemGroup where inventItemGroup.DataAreaId == inventItemGroupItem.ItemGroupDataAreaId

            && inventItemGroup.ItemGroupId == inventItemGroupItem.ItemGroupId;

        ttsCommit;

    

        ttsBegin;

        select firstonly forupdate qtySaleAnalysisTmp;

        qtySaleAnalysisTmp.CompanyLogo = FormLetter::companyLogo();

        qtySaleAnalysisTmp.update();

        ttsCommit;

    }

    public Qty qtyToConvert(ItemId _itemId, Qty _qty, UnitIDBase _fromUnit)

    {

        Qty                 qty;

        InventTable         inventTable;

        InventTableModule   inventTableModule;

        select firstonly inventTableModule

        where inventTableModule.ModuleType == ModuleInventPurchSales::Invent

        && inventTableModule.ItemId == _itemId;

        if(inventTableModule.UnitId)

        {

            qty =   UnitOfMeasureConverter::convert(_qty,

                    UnitOfMeasure::unitOfMeasureIdBySymbol(_fromUnit),

                    UnitOfMeasure::unitOfMeasureIdBySymbol(inventTableModule.UnitId),

                    NoYes::No,

                    InventTable::itemProduct(_itemId),

                    NoYes::No);

        }

        else

        {

            qty = _qty;

        }

        return qty;

    }

    public UnitIDBase baseUnit(ItemId _itemId)

    {

        UnitIDBase          baseUnit;

        InventTable         inventTable;

        InventTableModule   inventTableModule;

        select firstonly inventTableModule

        where inventTableModule.ModuleType == ModuleInventPurchSales::Invent

        && inventTableModule.ItemId == _itemId;

                        

        return inventTableModule.UnitId;

    }

}

SSRS Serial numbers

 Serial number

=RowNumber(Nothing)

serial number inside group values

=RowNumber("yourGroupName")

serial number on group level

=RunningValue(CountDistinct("yourDSName"),Count,"yourDSName")

=CountDistinct("yourGroupingColumnName")

Moving project from one environment to another (On perm)

 C:\AOSService\PackagesLocalDirectory\bin>ModelUtil.exe -export -metadatastorepath=C:\AosService\PackagesLocalDirectory -modelname="EEMC2DBarcode" -outputpath=c:\temp

C:\AOSService\PackagesLocalDirectory\bin>ModelUtil.exe -import -metadatastorepath=C:\AosService\PackagesLocalDirectory -file=c:\temp\EEMC2DBarcode-EEMC.axmodel

create Ledger dimension from value && get value(cust) from dimension

ledgerJournalTransUpdate.OffsetLedgerDimension = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(BankAccount.valueStr(),LedgerJournalACType::Bank);

To retrive cutomer value :

LedgerDynamicAccountHelper::getAccountNumberFromDynamicAccount(ledgerJournalTrans.LedgerDimension); 

Modify value inside default dimension

 salestable.DefaultDimension = EemcFOC::getNewDefaultDimension(salestable.DefaultDimension,"Worker" ,HcmWorker::find(salestable.WORKERSALESRESPONSIBLE).PersonnelNumber);

Sales responsible / sales taker lookup filter

 [ExtensionOf(classStr(HcmWorkerLookup))]

final class EEMCHcmWorkerLookup_Extension

{

    public Query updateQuery(HcmWorkerRecId _workerRecId, Query _queryToUpdate)

    {

        _queryToUpdate =next updateQuery( _workerRecId ,  _queryToUpdate);

        if(includeContractors==true&&CustParameters::find().SalesManWorker==true)

        {

            _queryToUpdate.dataSourceTable(tableNum(HcmWorker)).addRange(fieldNum(HcmWorker,SalesMan)).value(queryValue(1));

            _queryToUpdate.dataSourceName('HcmEmployment').addRange(fieldNum(HcmEmployment, EmploymentType)).value(queryValue(HcmEmploymentType::Employee));

        }

        return _queryToUpdate;

    }

}

COC for Form method

 [ExtensionOf(formStr(PurchReqTable))]

final class FCC_PurchReqTable_Extension

{

    void setFieldAccess()

    {

        next setFieldAccess();

        PurchReqLine  purchReqLine;

        FormDataSource fds;

        fds = this.purchReqLine_ds;

        fds.object(fieldNum(PurchReqLine, PurchPrice)).allowEdit(true);

        fds.object(fieldNum(PurchReqLine, PurchUnitOfMeasure)).allowEdit(true);

        

    }

}

Packingslip as Invoice Id while posting sales invoice

 [ExtensionOf(ClassStr(SalesInvoiceJournalCreateBase))]

final class salesInvoiceJournalCreateBase_Extension

{

    protected void initJournalHeader()

    {

        Str60                           packingslipId;

        SalesParmSubTable               SalesParmSubTableRef;

        CustPackingSlipJour             custPackingSlipJour;

        custInvoiceJour                 custInvoiceJourLocal;

        EEMC_PackingSlipConsumedTmp     packingSlipConsumedTmp;

        EEMC_PackingSlipTmp             packingSlipTmp, packingSlipTmpUpdate;

        CustParameters                  custParameters  =   CustParameters::find();

        next initJournalHeader();

        

        if(custParameters.EEMC_PackingSlipTOInvoice == NoYes::Yes)

        {

            while select SalesParmSubTableRef where SalesParmSubTableRef.ParmId==salesParmTable.ParmId && SalesParmSubTableRef.JournalRefTableId ==tableNum(CustPackingSlipJour)

            {

                select packingSlipTmp where packingSlipTmp.PackingSlipId == CustPackingSlipJour::findRecId(SalesParmSubTableRef.JournalRefRecId).PackingSlipId;

                if(!packingSlipTmp)

                {

                    packingslipId = CustPackingSlipJour::findRecId(SalesParmSubTableRef.JournalRefRecId).PackingSlipId;

                    packingSlipTmp.PackingSlipId = packingslipId;

                    packingSlipTmp.insert();

                }

            }

            select firstonly packingSlipTmp where packingSlipTmp.Marked == NoYes::No;

            if(packingSlipTmp)

            {

                if(packingSlipTmp.PackingSlipId  && custParameters.EEMC_PackingSlipTOInvoice == NoYes::Yes)

                {

                    if(custParameters.EEMC_InvoiceNonDuplicating == NoYes::Yes)

                    {

                        select custInvoiceJourLocal where custInvoiceJourLocal.InvoiceId == packingSlipTmp.PackingSlipId;

                        if(!custInvoiceJourLocal)

                        {

                            custInvoiceJour.InvoiceId       = packingSlipTmp.PackingSlipId;

                            custInvoiceJour.EEMC_InvoiceId  = packingSlipTmp.PackingSlipId;

                            select forupdate packingSlipTmpUpdate where packingSlipTmpUpdate.RecId == packingSlipTmp.RecId;

                            ttsbegin;

                            packingSlipTmpUpdate.Marked = NoYes::Yes;

                            packingSlipTmpUpdate.update();

                            ttscommit;

                            info(strFmt("Invoice Id : %1", packingSlipTmp.PackingSlipId));

                        }

                        else

                        {

                            throw error(strFmt("Invoice number : %1 Can't be duplicated", custInvoiceJourLocal.InvoiceId));

                        }

                    }

                    else

                    {

                        custInvoiceJour.InvoiceId       = packingSlipTmp.PackingSlipId;

                        custInvoiceJour.EEMC_InvoiceId  = packingSlipTmp.PackingSlipId;

                        select forupdate packingSlipTmpUpdate where packingSlipTmpUpdate.RecId == packingSlipTmp.RecId;

                        ttsbegin;

                        packingSlipTmpUpdate.Marked = NoYes::Yes;

                        packingSlipTmpUpdate.update();

                        ttscommit;

                        info(strFmt("Invoice Id : %1", packingSlipTmp.PackingSlipId));

                    }

                }

            }

        }

        else

        {

            select firstonly SalesParmSubTableRef where SalesParmSubTableRef.ParmId==salesParmTable.ParmId && SalesParmSubTableRef.JournalRefTableId ==tableNum(CustPackingSlipJour);

            if(CustPackingSlipJour::findRecId(SalesParmSubTableRef.JournalRefRecId).PackingSlipId  && CustParameters::find().UseDeliveryNoteAsInvoice==NoYes::Yes)

            custInvoiceJour.InvoiceId= CustPackingSlipJour::findRecId(SalesParmSubTableRef.JournalRefRecId).PackingSlipId;

            if(!custInvoiceJour.InvoiceId)

            custInvoiceJour.InvoiceId       = this.getJournalNumber();

        }

    }

}

Packingslip duplicate control while posting packingslip

 [ExtensionOf(ClassStr(SalesPackingSlipJournalCreate))]

final class EEMCSalesPackingSlipJournalCreate_Extension

{

    protected void initJournalHeader()

    {

        CustPackingSlipJour         custPackingSlipJourLocal;

        CustParameters              custParameters  =   CustParameters::find();

        if(custParameters.EEMC_PackingSlipNonDuplicating == NoYes::Yes)

        {

            select custPackingSlipJourLocal where custPackingSlipJourLocal.PackingSlipId == this.getJournalNumber();

            if(custPackingSlipJourLocal)

            {

                throw error(strFmt("Packingslip Id : %1 can't be duplicated", custPackingSlipJourLocal.PackingSlipId));

            }

        }

        next initJournalHeader();

    }

}

Item arravial journal x++

public void createJournalLines(WMSJournalTable  _wmsJournalTable, WMSJournalTrans  _wmsJournalTrans, real  _lineCount)
    {
        EEMC_SerialNumberTmp    serialNumberTmp;
        WMSJournalTable         wmsJournalTable, wmsJournalTableLocal;
        WMSJournalTrans         wmsJournalTrans, wmsJournalTransLocal;
        WMSJournalName          wmsJournalName;
        InventTrans             inventTrans;
        InventDim               inventDim, inventDimLocal;
        int                     lineNum = _lineCount + 1;
       
        ttsbegin;
        inventDim = _wmsJournalTrans.inventDim();
        wmsJournalTableLocal = _wmsJournalTrans.wmsJournalTable(true);
        while select serialNumberTmp
        {
            wmsJournalTrans.JournalId        =      _wmsJournalTable.journalId;
            wmsJournalTrans.LineNum          =      lineNum;
            wmsJournalTrans.TransDate        =      today();
            wmsJournalTrans.ItemId           =      _wmsJournalTrans.ItemId;
            wmsJournalTrans.Qty              =      1;
            wmsJournalTrans.inventTransId    =      _wmsJournalTrans.inventTransId;
            inventDim.inventSerialId         =      serialNumberTmp.SerialNumber;
            wmsJournalTrans.InventDimId      =      InventDim::findOrCreate(inventDim).InventDimId;
            wmsJournalTrans.insert();
            lineNum++;
        }
        select count(RecId) from wmsJournalTransLocal where wmsJournalTransLocal.journalId == _wmsJournalTable.journalId;
        wmsJournalTableLocal.numOfLines =  wmsJournalTransLocal.RecId - 1;
        wmsJournalTableLocal.update();
        ttscommit;
    }

Picking list registration lines serial number x++

public void createOrderLine(WMSOrderTrans  _wmsOrderTrans, real  _qty)
{
EEMC_SalesSerialNumberTmp   salesSerialNumberTmp;
WMSPickingRoute             wmsPickingRoute, wmsPickingRouteLocal;
WMSOrderTrans               wmsOrderTrans, wmsOrderTransUpdate;
WMSJournalName              wmsJournalName;
InventTrans                 inventTrans;
InventDim                   inventDim, inventDimLocal;
real                        insertedQty = 0;
real                        tmpQty;

ttsbegin;
select count(RecId) from salesSerialNumberTmp;
tmpQty  =   salesSerialNumberTmp.RecId;
if(_qty >= tmpQty)
{
while select salesSerialNumberTmp
{
inventDim                           =   _wmsOrderTrans.inventDim();
wmsOrderTrans.ToInventDimId         =   _wmsOrderTrans.ToInventDimId;
wmsOrderTrans.customer              =   _wmsOrderTrans.customer;
wmsOrderTrans.DeliveryPostalAddress =   _wmsOrderTrans.DeliveryPostalAddress;
wmsOrderTrans.dlvDate               =   _wmsOrderTrans.dlvDate;
inventDim.inventSerialId            =   salesSerialNumberTmp.SerialNumber;
wmsOrderTrans.inventDimId           =   InventDim::findOrCreate(inventDim).InventDimId;
wmsOrderTrans.expeditionStatus      =   WMSExpeditionStatus::Activated;
wmsOrderTrans.inventTransRefId      =   _wmsOrderTrans.inventTransRefId;
wmsOrderTrans.orderType             =   _wmsOrderTrans.orderType;
wmsOrderTrans.shipmentId            =   _wmsOrderTrans.shipmentId;
wmsOrderTrans.shipmentIdOriginal    =   _wmsOrderTrans.shipmentIdOriginal;
wmsOrderTrans.orderId               =   _wmsOrderTrans.orderId;
wmsOrderTrans.routeId               =   _wmsOrderTrans.routeId;
wmsOrderTrans.ItemId                =   _wmsOrderTrans.ItemId;
wmsOrderTrans.Qty                   =   1;
wmsOrderTrans.orderType             =   WMSOrderType::OrderPick;
WMSOrderTransType::newWMSOrderTrans(wmsOrderTrans);
wmsOrderTrans.inventTransId         =   _wmsOrderTrans.inventTransId;

wmsOrderTrans.insert();
insertedQty++;
}
if(tmpQty == _qty)
{
delete_from wmsOrderTransUpdate where wmsOrderTransUpdate.RecId == _wmsOrderTrans.RecId;
}
else if(tmpQty < _qty)
{
select forupdate wmsOrderTransUpdate where wmsOrderTransUpdate.RecId == _wmsOrderTrans.RecId;
wmsOrderTransUpdate.qty = _qty - tmpQty;
wmsOrderTransUpdate.update();
}
ttscommit;
}
else
{
warning("Serial numbers are greater than selected line qty, process is aborted.");
}
}

Where statement in ssrs expression

=Sum(IIF(Fields!DiscountAmount.Value <> 0, Fields!Qty.Value, 0))* Sum(Fields!DiscountAmount.Value)

Selected design name in print mgmt : Sales Invoice

static void reportName(Args _args)
{
   
    PrintMgmtDocInstance    printMgmtDocInstance;
    printMgmtReportFormat   printMgmtReportFormat;
    PrintMgmtSettings        printMgmtSettings;
    PrintMgmtReportFormatName  reportFormatName;
   
    select documentType, RecId from printMgmtDocInstance
        where printMgmtDocInstance.DocumentType ==PrintMgmtDocumentType::SalesOrderInvoice
        join parentId, ReportFormat from printMgmtSettings
        where printMgmtSettings.ParentId == printMgmtDocInstance.RecId
        join RecId, documentType, Name from printMgmtReportFormat
        where printMgmtReportFormat.RecId   ==  printMgmtSettings.ReportFormat
        && printMgmtReportFormat.DocumentType   ==  PrintMgmtDocumentType::SalesOrderInvoice;
   
    info(printMgmtReportFormat.Name);
}

date split into individual digits x++

public static void main(Args _args)
    {
        str             d1, d2, m1, m2, y1, y2, y3, y4;
        TransDate       targetDate   =   10\12\2019;
        int             day, mnth, year;

        day         =   dayOfMth(targetDate);
        mnth        =   mthOfYr(targetDate);
        year        =   year(targetDate);
       
        if(strLen(int2str(day)) == 1)
        {
            d1  = "0";
            d2  = int2str(day);
        }
        else
        {
            d1  = subStr(int2str(day), 1, 1);
            d2  = subStr(int2str(day), 2, 1);
        }

        if(strLen(int2str(mnth)) == 1)
        {
            m1  = "0";
            m2  = int2str(mnth);
        }
        else
        {
            m1  = subStr(int2str(mnth), 1, 1);
            m2  = subStr(int2str(mnth), 2, 1);
        }

        y4  = subStr(int2str(year), 4, 1);
        y3  = subStr(int2str(year), 3, 1);
        y2  = subStr(int2str(year), 2, 1);
        y1  = subStr(int2str(year), 1, 1);

        Info(strFmt("D1: %1, D2: %2, -- M1: %3, M2: %4, -- Y1: %5, Y2: %6, Y3: %7, Y4: %8", d1, d2, m1, m2, y1, y2, y3, y4));
    }

amount in words 3 decimal precision x++

static TempStr numeralsToTxtThreeDecimal(real _num)
{
int     numOfPennies = any2int(decRound(frac(_num), 3) * 1000) mod 1000;
real    test         = _num - frac(_num);

int64   numOfTenths;
str 20  ones[19], tenths[9], hundreds, thousands, millions, billions, trillions;

int64   temp;
str 200 returntxt;
int64   testLoc;

real modOperator(real a1, real a2)
{
int tmpi;
real tmp1, tmp2;
tmp1 = a1 / a2;
tmpi = real2int(tmp1);
tmp2 = tmpi;
return (tmp1 - tmp2)*a2;
}

real checkPower(real  _test, int64 _power)
{
int64   numOfPower;

if (_test >= _power)
{
testLoc = any2int64(_test);
numOfPower = testLoc div _power;

if (numOfPower >= 100)
{
temp = numOfPower div 100;
returntxt = returntxt + ' ' + ones[temp] + ' ' + hundreds;
numOfPower = numOfPower mod 100;
}
if (numOfPower >= 20)
{
temp = numOfPower div 10;
returntxt = returntxt + ' ' + tenths[temp];
numOfPower = numOfPower mod 10;
}
if (numOfPower >= 1)
{
returntxt = returntxt + ' ' + ones[numOfPower];
numOfPower = numOfPower mod 10;
}
switch(_power)
{
case 1000000000000 :
returntxt = returntxt + ' ' + trillions;
_test = modOperator(_test, 1000000000000.00);
break;
case 1000000000 :
returntxt = returntxt + ' ' + billions;
_test = modOperator(_test, 1000000000);
break;
case 1000000 :
returntxt = returntxt + ' ' + millions;
_test = modOperator(_test, 1000000);
break;
case 1000 :
returntxt = returntxt + ' ' + thousands;
_test = modOperator(_test, 1000);
break;
case 100 :
returntxt = returntxt + ' ' + hundreds;
_test = modOperator(_test, 100);
break;
}
}
return _test;
}
ones[1] = "@SYS26620";
ones[2] = "@SYS26621";
ones[3] = "@SYS26622";
ones[4] = "@SYS26626";
ones[5] = "@SYS26627";
ones[6] = "@SYS26628";
ones[7] = "@SYS26629";
ones[8] = "@SYS26630";
ones[9] = "@SYS26631";
ones[10] = "@SYS26632";
ones[11] = "@SYS26633";
ones[12] = "@SYS26634";
ones[13] = "@SYS26635";
ones[14] = "@SYS26636";
ones[15] = "@SYS26637";
ones[16] = "@SYS26638";
ones[17] = "@SYS26639";
ones[18] = "@SYS26640";
ones[19] = "@SYS26641";

tenths[1] = 'Not used';
tenths[2] = "@SYS26643";
tenths[3] = "@SYS26644";
tenths[4] = "@SYS26645";
tenths[5] = "@SYS26646";
tenths[6] = "@SYS26647";
tenths[7] = "@SYS26648";
tenths[8] = "@SYS26649";
tenths[9] = "@SYS26650";

hundreds    = "@SYS26651";
thousands   = "@SYS26652";
millions    = "@SYS26653";
billions    = "@SYS26654";
trillions   = "@SYS101697";


test = checkPower(test, 1000000000000);
test = checkPower(test, 1000000000);
test = checkPower(test, 1000000);
test = checkPower(test, 1000);
test = checkPower(test, 100);

if (test >= 20)
{
testLoc = any2int64(test);
numOfTenths = testLoc div 10;

returntxt = returntxt + ' ' + tenths[numofTenths];
numOfTenths = numOfTenths mod 10;
test = real2int(test) mod 10;
}
if (test >= 1)
{
numOfTenths = real2int(test);
returntxt = returntxt + ' ' + ones[numOfTenths];
}

if (numOfPennies)
{
returntxt = '***' + returntxt + ' ' + "@SYS5534" + ' ' + num2str(numOfPennies,0,0,0,0) + '/1000';
}
else
{
returntxt = '***' + returntxt + ' ' + "@SYS5534" + ' ' + '000/1000';
}
return returntxt;
}

dialog box x++

if (Box::okCancel("Settled records exceeded than page limit", DialogButton::Cancel) == DialogButton::Ok)

Ledger Journal marked settlement transactions x++

LedgerJournalTrans ledgerJournalTrans
SpecTrans        specTrans;
VendTransOpen    vendTransOpen;
VendTrans        vendTrans

select first only ledgerJournalTrans where ledgerJournalTrans.RecId == _recId; //pass line recId

while select specTrans
join vendTransOpen
join vendTrans
where specTrans.SpecCompany == ledgerJournalTrans.DataAreaId
&& specTrans.SpecTableId    == ledgerJournalTrans.TableId
&& specTrans.SpecRecId      == ledgerJournalTrans.RecId
&& specTrans.RefCompany     == vendTransOpen.DataAreaId
&& specTrans.RefTableId     == vendTransOpen.TableId
&& specTrans.RefRecId       == vendTransOpen.RecId
&& vendTransOpen.RefRecId   == vendTrans.RecId
&& vendTransOpen.AccountNum == vendTrans.AccountNum
{
info(strfmt("%1--%2--%3", ledgerJournalTrans.Voucher, vendTrans.Invoice, vendTransOpen.AmountCur));
}

SO Invoice / PO Conformation new design in D365 FO

Create a new report with proposed design (Report, designName)

//Print management SO&PO

class AC_PrintMgtDocTypeHandlersExt
{
    [SubscribesTo(classStr(PrintMgmtDocType), delegateStr(PrintMgmtDocType, getDefaultReportFormatDelegate))]
    public static void PrintMgmtDocType_getDefaultReportFormatDelegate(PrintMgmtDocumentType _docType, EventHandlerResult _result)
    {

        switch (_docType)
        {
            case PrintMgmtDocumentType::SalesOrderInvoice :
                _result.result(ssrsReportStr(AC_SalesInvoice, Report));
                break;

            case PrintMgmtDocumentType::PurchaseOrderConfirmationRequest :
                _result.result(ssrsReportStr(AC_PurchPurchaseOrder, Report));
                break;
        }
    }

}

//Controller CoC for SO

[ExtensionOf(classStr(SalesInvoiceController))]
final class AC_SalesInvoiceController_Extension
{
    protected void outputReport()
    {
        PrintMgmtPrintSettingDetail     printSettingDetail;
        FormLetterReport                formLetterReport;
        SRSCatalogItemName              reportDesign;

        formLetterReport = this.formLetterReport;
        printSettingDetail = formLetterReport.getCurrentPrintSetting();

        reportDesign = ssrsReportStr(AC_SalesInvoice, Report);

        this.parmReportName(reportDesign);
        this.parmReportContract().parmReportName(reportDesign);
        formletterReport.parmReportRun().settingDetail().parmReportFormatName(reportDesign);
        next outputReport();
    }

}

//Controller CoC for PO

[ExtensionOf(classStr(PurchPurchaseOrderController))]
final class AC_PurchPurchaseOrderController_Extension
{
    protected void outputReport()
    {
        PrintMgmtPrintSettingDetail     printSettingDetail;
        FormLetterReport                formLetterReport;
        SRSCatalogItemName              reportDesign;

        formLetterReport = this.formLetterReport;
        printSettingDetail = formLetterReport.getCurrentPrintSetting();

        reportDesign = ssrsReportStr(AC_PurchPurchaseOrder, Report);

        this.parmReportName(reportDesign);
        this.parmReportContract().parmReportName(reportDesign);
        formletterReport.parmReportRun().settingDetail().parmReportFormatName(reportDesign);
        next outputReport();
    }

}

//PO Header CoC

[ExtensionOf(tableStr(PurchPurchaseOrderHeader))]
final class AC_PurchPurchaseOrderHeader_Extension
{
    public void insert()
    {
        PurchTable                  purchTable;
        VendTable                   vendTable;
        PurchLine                   purchline;
        PurchRFQReplyLinePurchLine  purchRFQReplyLine;
        AmountMST                   totalValue;
        PurchLine                   purchLineLocal;
        LogisticsElectronicAddress  logisticsElectronicAddress;
        DirPartyTable               dirPartyTable;
        TaxJournalTrans             taxJournalTrans;
        VendPurchOrderJour          vendPurchOrderJour;

        purchTable = PurchTable::find(this.PurchId);
        vendTable = purchTable.vendTable_OrderAccount();

        select vendPurchOrderJour where vendPurchOrderJour.PurchOrderDocNum  ==  this.PurchOrderDocNum;

        select sum(TaxAmount) from taxJournalTrans
            where taxJournalTrans.TransTableId   ==  vendPurchOrderJour.TableId
            && taxJournalTrans.TransRecId   ==  vendPurchOrderJour.RecId;

        select firstonly * from purchline where purchline.PurchId == purchTable.PurchId;
        select sum(LineAmount) from purchLineLocal where purchLineLocal.PurchId == purchTable.PurchId;

        this.AC_VendCity        =   vendTable.countryRegionName();
        this.AC_VendTelephone   =   vendTable.phone();
        this.AC_VendFax         =   vendTable.telefax();
        this.AC_CompanyURL      =   purchTable.companyInfo().url();

        select firstOnly logisticsElectronicAddress
            where logisticsElectronicAddress.RecId == DirPartyTable::findRec
            (VendTable::find(vendTable.AccountNum).Party).PrimaryContactFax
            && logisticsElectronicAddress.IsPrimary ==  NoYes::Yes;
     
        this.CompanyVATNum      =   TaxRegistration::getCompanyTaxRegNum_W(this.PurchOrderDate);
        this.AC_TotalVATAmount  =   taxJournalTrans.TaxAmount;
        this.AC_TotalAmount     =   purchLineLocal.LineAmount + taxJournalTrans.TaxAmount;
        this.AC_AmountInWords   =   Global::numeralsToTxt(this.AC_TotalAmount);
     
        //this.CompanyVATNum =  companyInfo.CoRegNum;
        //this.INTC_VATNum = this.getTax(vendTable.postalAddress().Location);
        //this.AC_VendFax   = logisticsElectronicAddress.Locator;
        next insert();
    }

    public TaxRegistrationNumber getTax(LogisticsLocationRecId   _location)
    {
        TaxRegistration                         taxRegistration;
        TaxRegistrationType                     taxRegistrationType;
        TaxRegistrationTypeApplicabilityRule    taxRegistrationRule;
        LogisticsLocation                       logisticsLocation;
        DirPartyLocation                        dirPartyLocation;

        logisticsLocation = LogisticsLocation::find(_location);

        select taxRegistration
            join taxRegistrationRule
            where taxRegistrationRule.RecId ==  taxRegistration.TaxRegistrationTypeApplicabilityRule
            join taxRegistrationType
            where taxRegistrationType.RecId == taxRegistrationRule.TaxRegistrationType
            join dirPartyLocation
            where taxRegistration.DirPartyLocation == dirPartyLocation.RecID
            && dirPartyLocation.Location == logisticsLocation.RecId;

        return taxRegistration.RegistrationNumber;
    }

    public void initFromCompanyInfoOnBlankPaper(CompanyInfo _companyInfo)
    {
        next initFromCompanyInfoOnBlankPaper(_companyInfo);
        this.CompanyEmail   = _companyInfo.email();
    }

    public void initFromVendPurchOrderJour(VendPurchOrderJour _vendPurchOrderJour)
    {
        next initFromVendPurchOrderJour(_vendPurchOrderJour);
        this.NetAmount  = _vendPurchOrderJour.netAmount();
    }

}

//PO line CoC

[ExtensionOf(tableStr(PurchPurchaseOrderTmp))]
final class AC_PurchPurchaseOrderTmp_Extension
{
    public void insert()
    {
        PurchTable              purchTable;
        PurchLine               purchLine;
        VendPurchOrderJour      vendPurchOrderJour;
        PurchLineAllVersions    purchLineAllVersions;
        TaxJournalTrans         taxJournalTrans;
        PurchTableAllVersions   PurchTableAllVersions;
        TaxTable                taxTable;

        select VendPurchOrderJour
            where VendPurchOrderJour.RecId == this.JournalRecId
            join PurchTableAllVersions
            join PurchLineAllVersions
            join taxJournalTrans
            join purchLine
            where PurchTableAllVersions.PurchTableVersionRecId ==        VendPurchOrderJour.PurchTableVersion
            && PurchLineAllVersions.PurchTableVersionRecId == PurchTableAllVersions.PurchTableVersionRecId
            && PurchLineAllVersions.ItemId == this.ItemId
            && taxJournalTrans.TransTableId == vendPurchOrderJour.TableId
            && taxJournalTrans.TransRecId == vendPurchOrderJour.RecId
            && taxJournalTrans.InventTransId == PurchLineAllVersions.InventTransId
            && purchLine.SourceDocumentLine == PurchLineAllVersions.SourceDocumentLine;

        select taxTable where taxTable.TaxCode == taxJournalTrans.TaxCode;

        this.AC_VATAmount   =   abs(taxJournalTrans.SourceTaxAmountCur);
        this.AC_VATPerc     =   taxTable.showTaxValue();
        this.AC_TotalAmount =   this.AC_VATAmount + this.LineAmount;
       
        next insert();
    }

}

//SO Header CoC

[ExtensionOf(tableStr(SalesInvoiceHeaderFooterTmp))]
final class AC_SalesInvoiceHeaderFooter_Extension
{
    public void insert()
    {
        SalesTable                  salesTable;
        LogisticsElectronicAddress  logisticsElectronicAddress;
        DirPartyTable               dirPartyTable;
        TaxJournalTrans             taxJournalTrans;
        VendPurchOrderJour          vendPurchOrderJour;
        CustInvoiceJour             custInvoiceJour;

        salesTable  =   SalesTable::find(this.SalesId);
        select custInvoiceJour where custInvoiceJour.RecId == this.JournalRecId;
       
        this.AC_CustName    =   CustTable::find(this.InvoiceAccount).name();
        this.AC_CustCity    =   CustTable::find(this.InvoiceAccount).cityName_BR();
        this.AC_CustCountry =   CustTable::find(this.InvoiceAccount).countryName();
        this.AC_InvoiceNum  =   custInvoiceJour.InvoiceId;
     
        next insert();
    }

}

//SO Line CoC

[ExtensionOf(tableStr(SalesInvoiceTmp))]
final class AC_SalesInvoiceTmp_Extension
{
    public void insert()
    {
        SalesTable          salesTable;
        SalesLine           salesLine;
        real                tax1;
        InventTable         inventTable =   InventTable::find(this.ItemId);
        real                totalAmount;
        TaxTrans            taxTrans;
        CustInvoiceTrans    custInvoiceTrans;
        CustInvoiceJour     custInvoiceJour;

        real                taxAmount;
        real                salesPrice;
        select custInvoiceJour where custInvoiceJour.RecId == this.JournalRecId;

        select custInvoiceTrans
            where custInvoiceTrans.SalesId == custInvoiceJour.SalesId
            && custInvoiceTrans.InvoiceId   ==  custInvoiceJour.InvoiceId
            && custInvoiceTrans.InvoiceDate ==  custInvoiceJour.InvoiceDate
            && custInvoiceTrans.numberSequenceGroup ==  custInvoiceJour.numberSequenceGroup
            && custInvoiceTrans.ItemId  ==  this.ItemId;
     
        select taxTrans where taxTrans.InventTransId == custInvoiceTrans.InventTransId;
        this.TaxAmount          =   abs(taxTrans.TaxAmount);
        this.AC_VATPerc         =   taxTrans.TaxValue;
        this.AC_TotalAmount     =   this.LineAmount + this.TaxAmount;
        this.AC_SalesPrice      =   this.AC_TotalAmount/this.Qty;

        next insert();
    }

}

Build and deploy your report.
Select your design in print management settings level.