SQL script to delete all staging table data in D365FO

 declare cu cursor for SELECT [name] FROM SYSTABLEIDVIEW where [name] like '%Staging'

declare @table varchar(100)

declare @sql nvarchar(1000)

OPEN cu  

FETCH NEXT FROM cu INTO @table 

WHILE @@FETCH_STATUS = 0  

BEGIN  

    set @sql = N'delete from ' + @table

    PRINT @table

    EXEC sp_executesql @sql

    FETCH NEXT FROM cu INTO @table 

END   

CLOSE cu;  

DEALLOCATE cu;

Data management EXPORT operation using X++ in D365FO

         #DMF

        Query query;

        DMFEntityName entityName;

        boolean isGenerated = false;

        entityName = DMFEntity::findFirstByTableId(tableNum(BankPositivePayExportEntity)).EntityName;

            // Update query

            query = new Query(DMFUtil::getDefaultQueryForEntityV3(entityName));

            QueryBuildDataSource qbds = query.dataSourceTable(tableNum(BankPositivePayExportEntity));

            SysQuery::findOrCreateRange(qbds, fieldNum(BankPositivePayExportEntity, PositivePayNumber)).value(queryValue(bankPositivePayTable.PositivePayNum));

        // Export file

        DMFDefinitionGroupName definitionGroupName = strFmt('%1-%2', classStr(BankPositivePayExport), bankPositivePayTable.PositivePayNum);

        try

        {

            BankPositivePayFormat positivePayFormat = BankPositivePayFormat::findByFormatName(bankPositivePayTable.PayFormat);

            List xsltFileList = new List(Types::String);

            if (positivePayFormat.FileId != '')

            {

                xsltFileList.addEnd(this.copyTransformationFile(positivePayFormat));

            }

            DMFEntityExporter exporter = new DMFEntityExporter();

            fileId = exporter.exportToFile(

                entityName,

                definitionGroupName,

                '',

                positivePayFormat.DMFSourceName,

                #FieldGroupName_AllFields,

                query.pack(),

                curExt(),

                xsltFileList,

                true,

                false);

            if (fileId != '')

            {

                this.sendFileToDestination();

                DMFDefinitionGroup::find(definitionGroupName, true).delete();

                isGenerated = true;

            }

            else

            {

                // DMF execution failed and details were written to the execution log

                throw error("@CashManagement:DMFExportCallFailedToExecutionLog");

            }

        }

        catch

        {

            //

        }

        return isGenerated;

Error On data entity -> The data value violates integrity constraints in D365FO

Error: The data value violates integrity constraints

Solution:

1. Remove blank lines on sample file, If any (If in case, CSV or excel) or  Copy content alone from EXCEL or CSV, Create new workbook and paste it

2. Check all mandatory fields value filled in on input file or not and also check any index violation (duplicate values/Record) 

ODATA Request URL D365FO - Testing postman

 POST:

https://<D365FOURL>/data/<EntityPublicCollectionName>

Body:

{

    "dataAreaId": "USMF",

    "InvoiceId": "USMF00000040",

    "InvoiceAmount": 13110.540000,

    "Status": "Sent"

}

PATCH:

https://<D365FOURL>/data/<EntityPublicCollectionName>(dataAreaId='USMF',InvoiceId='USMF00000040')?cross-company=true

Body:

{

    "Status": "Paid"

}

Getting Infolog container to string in D365FO

        SysInfologEnumerator    sysInfologEnumerator;

        SysInfologMessageStruct infoMessageStruct;

        str                     logMessage;

       container logData = inputLogData;// Input 

        sysInfologEnumerator = SysInfologEnumerator::newData(logData );    

        while (sysInfologEnumerator.moveNext())

        {

            int i = 1;

            if (logMessage)

            {

                logMessage +=  '\n';

            }

            infoMessageStruct = SysInfologMessageStruct::construct(sysInfologEnumerator.currentMessage());        

            while (i <= infoMessageStruct.prefixDepth())

            {

                logMessage += infoMessageStruct.preFixTextElement(i) + '. ';                i++;

            }

            logMessage += infoMessageStruct.message();

        }

      info( logMessage);

Get SQL Table row counts

CREATE TABLE TSTRowCounts(RowCount1 BIGINT,TableName VARCHAR(128))

EXEC sp_MSForEachTable 'INSERT INTO TSTRowCounts

                        SELECT COUNT_BIG(*) AS RowCount1,

                        ''?'' as TableName FROM ?'

SELECT  top 100 TableName, RowCount1  FROM  TSTRowCounts ORDER BY RowCount1 DESC

select * from TSTRowCounts

where RowCount1 >100000

Calculate retail price and discounts in D365FO

Reference: RetailPricingSimulator (MS Standard OOB class) 

Pre requistie:

Customer, Item Number, Retail channel, Currency

Price: Original price (Sales price - Discount)

Code:

Class declaration

using System.Reflection;

using System.Collections.Generic;

using CrtRetailAffiliationType = Microsoft.Dynamics.Commerce.Runtime.DataModel.RetailAffiliationType;

public AmountCur GetRetailPriceAndDiscounts(

            str                     _customer,

            str                     _itemId,

            RetailChannelTable      _retailChannel,

            list                    _affiliationList = null,

            Str                     _currency = Ledger::accountingCurrency(),

            str                     _orderNo = '')

    {

        Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesLine                                     crtSalesLine;

        Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesTransaction                              crtSalesTransaction;

        Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngineDiagnosticsObject   diagnosticsObjectPrices;

        Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngineDiagnosticsObject   diagnosticsObjectDiscounts;

        Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesAffiliationLoyaltyTier                   crtSalesAffiliationList;

        Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.IPricingDataAccessor             pricingManager;

        container                                       resultCon = conNull();

        CLRObject                                       clrSalesAffiliations;

        CLRObject                                       clrSalesLines;

        RetailCustAffiliation                           retailCustAffiliation;

        str                                             defaultSalesUnit;

        str                                             lineId;

        RetailTempOrderItem                             tempOrderItem;

        System.DateTime                                 dateTime, dateTimeUtc;

        System.DateTimeOffset                           dateTimeOffsetUtc, dateTimeOffsetChannel;

        RetailCurrencyOperations                        currencyAndRoundingHelper;

        CLRObject                                       enumeratorSalesLine;

        RetailTransactionId                             transactionIdString         = System.Guid::NewGuid().ToString('N');

        CustTable                                       custTable                   = custTable::find(_customer);

        InventTable                                     inventTable                 =InventTable::find(_itemId);

        CurrencyCode                                    channelCurrency             = _currency;

        int                                             salesLinesOrderField        = 1;

        utcDateTime                                     simulationDateTime          = DateTimeUtil::utcNow();

        const str discountOfferIdProperty = 'DiscountOfferId';

        const str discountCodeProperty = 'Code';

        const str salesTransactionCouponsProperty = 'Coupons';

        defaultSalesUnit = InventTableModule::find(_itemId, ModuleInventPurchSales::Sales).UnitId;

        crtSalesTransaction = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesTransaction();

        crtSalesTransaction.set_Id(transactionIdString);

        clrSalesLines = crtSalesTransaction.get_SalesLines();

        crtSalesLine = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesLine();

        crtSalesLine.set_ItemId(_itemId);

        crtSalesLine.set_InventoryDimensionId('');

        crtSalesLine.set_Quantity(1);

        crtSalesLine.set_SalesOrderUnitOfMeasure(defaultSalesUnit);

        crtSalesLine.set_OriginalSalesOrderUnitOfMeasure(defaultSalesUnit);

if (<Variant Id>)

{

    InventDimCombination    dimCombination;

    dimCombination = InventDimCombination::findVariantId(rtSalesTrans.variantId);

    crtSalesLine.set_ProductId(dimCombination.DistinctProductVariant);

    crtSalesLine.set_MasterProductId(inventTable.Product);

//Refer this method getProductVariant on class RetailPricingSimulator 

    Microsoft.Dynamics.Commerce.Runtime.DataModel.ProductVariant productVariant = this.getProductVariant(<variantId>, <itemId>, <InventDimId>);

    crtSalesLine.set_Variant(productVariant);

}

        lineId = strRFix(int2str(salesLinesOrderField), 3, '0');

        crtSalesLine.set_LineId(lineId);

        crtSalesLine.set_LineNumber(salesLinesOrderField);

        clrSalesLines.Add(crtSalesLine);

        crtSalesTransaction.set_IsTaxIncludedInPrice(_retailChannel.PriceIncludesSalesTax);

        crtSalesTransaction.set_CustomerId(_customer);

        clrSalesAffiliations = crtSalesTransaction.get_AffiliationLoyaltyTierLines();

        // Add customer retail affiliations.

        If(_affiliationList == null)

        {

            while select retailCustAffiliation

                where retailCustAffiliation.CustAccountNum == _customer

            {

                crtSalesAffiliationList = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesAffiliationLoyaltyTier();

                crtSalesAffiliationList.set_AffiliationId(retailCustAffiliation.RetailAffiliationId);

 crtSalesAffiliationTransaction.set_AffiliationType(CrtRetailAffiliationType::General);

//crtSalesAffiliationTransaction.set_AffiliationType(CrtRetailAffiliationType::Loyalty); // in case of loyalty discount

                clrSalesAffiliations.Add(crtSalesAffiliationList);

            }

        }

        else

        {

            RetailAffiliation       retailAffiliations;

            ListEnumerator      enumerator;

            enumerator = _affiliationList.getEnumerator();

            while(enumerator.moveNext())

            {

                retailAffiliations.clear();

                retailAffiliations = RetailAffiliation::findByName(enumerator.current(), false);

                if(retailAffiliations)

                {

                    crtSalesAffiliationList = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesAffiliationLoyaltyTier();

                    crtSalesAffiliationList.set_AffiliationId(retailAffiliations.RecId);

                    clrSalesAffiliations.Add(crtSalesAffiliationList);

                }

            }

        }

        dateTime = Global::utcDateTime2SystemDateTime(simulationDateTime);

        dateTimeUtc = new System.DateTime(dateTime.get_Ticks(), System.DateTimeKind::Utc);

        dateTimeOffsetUtc = new System.DateTimeOffset(dateTimeUtc);

        dateTimeOffsetChannel =  System.TimeZoneInfo::ConvertTimeBySystemTimeZoneId(dateTimeOffsetUtc, _retailChannel.ChannelTimeZoneInfoId);

        if(_orderNo)

        {

            //Create CRT for COUPONS

            CLRObject couponCollection;

            PropertyInfo propertyInfo = crtSalesTransaction.GetType().GetProperty(salesTransactionCouponsProperty);

            if (propertyInfo)

            {

                couponCollection = System.Activator::CreateInstance(propertyInfo.PropertyType);

                propertyInfo.SetValue(crtSalesTransaction, couponCollection);

            }

            else

            {

                throw error("@Retail:CouponsCreationError");

            }

            System.Type couponType;

            if (couponCollection)

            {

                System.Type[] argumentTypes = couponCollection.GetType().GetGenericArguments();

                if (argumentTypes && argumentTypes.Length > 0)

                {

                    couponType = argumentTypes.GetValue(0);

                }

            }

            if (couponType)

            {

                PropertyInfo discountOfferIdPropertyInfo = couponType.GetProperty(discountOfferIdProperty);

                PropertyInfo codePropertyInfo = couponType.GetProperty(discountCodeProperty);

        

                if (discountOfferIdPropertyInfo && codePropertyInfo)

                {

                    RetailCouponUsage retailCouponUsage;

                    RetailCouponCodeTable retailCouponCodeTable;

                    RetailCoupon retailCoupon;

                    while select retailCouponUsage

                    where retailCouponUsage.SalesId == _orderNo

                        join  retailCouponCodeTable

                            where retailCouponCodeTable.CouponCodeId == retailCouponUsage.CouponCodeId

                        join retailCoupon

                            where retailCoupon.CouponNumber == retailCouponCodeTable.CouponNumber

                    {

                        SalesTable salesTable = SalesTable::find(_orderNo);

                        RetailSalesTable retailSalesTable = RetailSalesTable::findSalesTable(salesTable);

                    

                        if(!retailSalesTable.RetailChannel)

                        {

                            throw error("@Retail:CouponChannelNotFound");

                        }

                        // Add coupon to list if valid.

                        if (RetailCouponHelper::validateCoupon(retailCouponCodeTable, retailSalesTable.RetailChannel)

                            && RetailCouponHelper::ValidateCouponLimits(retailCouponCodeTable.CouponCodeId, _retailChannel.RetailChannelId, _customer, _orderNo))

                        {

                            CLRObject coupon = System.Activator::CreateInstance(couponType);

                            discountOfferIdPropertyInfo.SetValue(coupon, retailCoupon.DiscountOfferId);

                            codePropertyInfo.SetValue(coupon, retailCouponCodeTable.CouponCode);

                            couponCollection.Add(coupon);

                        }

                    }

                }

            }

        }

        pricingManager = new RetailPricingDataManagerSimulator(_retailChannel.RecId, transactionIdString, false, tempOrderItem, true);        

        tempOrderItem.clear();

        tempOrderItem.itemId = _itemId;

        tempOrderItem.Product = inventTable.Product;

        tempOrderItem.insert();

        currencyAndRoundingHelper = new RetailCurrencyOperations(CompanyInfoHelper::standardCurrency());

        Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::SetCollectDiagnostics(crtSalesTransaction, true);

        // Calculate prices.

        Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::CalculatePricesForTransaction(

            crtSalesTransaction,

            pricingManager,

            currencyAndRoundingHelper,

            custTable.PriceGroup,

            channelCurrency,

            dateTimeOffsetUtc); // dateTimeOffsetChannel (error at runtime)- replace with dateTimeOffsetUtc

        diagnosticsObjectPrices = Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::GetPricingEngineDiagnosticsObject(crtSalesTransaction);

        Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::SetCollectDiagnostics(crtSalesTransaction, true);

        // Calculate discounts.

        Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::CalculateDiscountsForLines(

            pricingManager,

            crtSalesTransaction,

            currencyAndRoundingHelper,

            channelCurrency,

            custTable.LineDisc,

            custTable.MultiLineDisc,

            custTable.EndDisc,

            true,

            false,

            dateTimeOffsetUtc); // dateTimeOffsetChannel (error at runtime)- replace with dateTimeOffsetUtc

        diagnosticsObjectDiscounts =  Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::GetPricingEngineDiagnosticsObject(crtSalesTransaction);

        

        clrSalesLines = crtSalesTransaction.get_SalesLines();

        enumeratorSalesLine = clrSalesLines.GetEnumerator();

        while (enumeratorSalesLine.MoveNext())

        {

            crtSalesLine = enumeratorSalesLine.get_Current();

            resultCon += [crtSalesLine.get_Price(), crtSalesLine.get_DiscountAmount()];

        }

        return  crtSalesLine.get_Price() - crtSalesLine.get_DiscountAmount(); //Considering price - discount as original price

    }

Changes To Internal Access modifier in X++ (From version 10.0.25/PU49) D365FO

Reference:  https://www.codecrib.com/2021/11/changes-to-internal-access-modifier-in-xpp.html

New operators are added in X++ D365FO

 These are new two operators added to X++ libraries.

 *= 

 /=

Example:

X *= 2
X /= 2

These are short for X = X * 2 and X = X / 2.

Quick entries on document (Sales order, Purchas order.. Etc) In D365FO using Excel

 Login to D365FO URL,

Navigate to Sales order (Sample)

Create new order

Prepare Excel with all Item list

Copy content from excel

Go to Order again, Add CTRL+V (To paste that content)

Result:

On popup Click -> SAVE

and Click -> Save