Retail assortments and planned orders extensions

Microsoft have created an excellent description of this in the Assortment management doc-page. Retail assortments are essential to define what products that should be available across retail channels. Assigning a product to an assortment, will assign the product to the stores that have the assortment. This makes it possible to sell the product in the store.

But there is something missing, and that is to use assortments for replenishment and procurement. Retailers want the master planning to only suggest procurement and transfers on products that belongs to the stores assortments. You can control requirement parameters per warehouse, but there is no standard way to use the assortment to control the generation of planned orders.

This blog post is to show how to make a very small extension that allows you to make sure that only products that belongs to a stores assortment will generate planned orders. The solution I will make use of is to look into how the product lifecycle state functionality is working, and extend this with an assortment planning parameter. I have called this parameter “Is lifecycle state active for assortment procurement

What it will do, is to validate if a product is in the assortment of the store, and if it is, then the product will be requirement calculated and will generate planned orders. If the product is not in the assortment of the store, that no planned orders will be generated.

To make this happen, I needed to create 4 extensions. The three first is adding a new field on the product lifecycle form.  For an experienced developer this is easy to create, and no need to spend time on in this this blog-post.

The real fun is how to manage and control the planned order generation. This happens in in an extension to the ReqSetupDim class.  Here there is a lookup to the assortment on the product, and checks if the store have this assortment.  If not, then the masterplanning will not generate any planned orders. I therefore create an extention class and use the new method wrapping/CoC feature to add some additional code.

/// Contains extension methods for the ReqSetupDim class.
/// </summary>

final class ReqSetupDim_extension

    /// Validates if a product should be assortment planned
    /// </summary>

    /// The parm of the ReqSetupDim class.
    /// false if the product is not assortment planned; otherwise, return default value.
    public boolean  mustReqBeCreated(InventDim _inventDimComplete)
        Boolean ret = next mustReqBeCreated(_inventDimComplete);

        if (ret)
            if (inventdim.InventLocationId)
                InventTable                 inventtable;
                EcoResProductLifecycleState ecoResProductLifecycleState;

                //Fetching fields from  inventtable
                select firstonly ProductLifecycleStateId, Product from  inventtable where inventtable.ItemId == this.itemId();

                //validating if the product is active for planning and that also assortment planning is enabled.
                select firstonly RecId from ecoResProductLifecycleState
                        where   ecoResProductLifecycleState.IsActiveForAssortmentPlanning == true
                            &amp;&amp;  ecoResProductLifecycleState.IsActiveForPlanning == true
                            &amp;&amp;  ecoResProductLifecycleState.StateId == inventtable.ProductLifecycleStateId;

                    RetailStoreTable                    store;
                    EcoResProduct                       product;
                    RetailAssortmentLookup              assortmentLookupInclude;
                    RetailAssortmentLookup              assortmentLookupExclude;

                    RetailAssortmentLookupChannelGroup  assortmentLookupChannelGroupInclude;
                    RetailAssortmentLookupChannelGroup  assortmentLookupChannelGroupExclude;

                    //Finding OMOperatingUnitID from the inventlocationId
                    while select firstonly OMOperatingUnitID from store
                        where store.inventlocation == inventdim.InventLocationId
                        //Check if the product is in the assortment of the store in question
                        select RecId from product
                            where product.RecId == inventtable.product
                        exists join assortmentLookupInclude
                            where   assortmentLookupInclude.ProductId == product.RecId
                                &amp;&amp;  assortmentLookupInclude.lineType == RetailAssortmentExcludeIncludeType::Include
                        exists join assortmentLookupChannelGroupInclude
                                where   assortmentLookupChannelGroupInclude.OMOperatingUnitId == store.OMOperatingUnitID
                                    &amp;&amp;  assortmentLookupChannelGroupInclude.AssortmentId == assortmentLookupInclude.AssortmentId
                        notexists join assortmentLookupExclude
                            where   assortmentLookupExclude.ProductId == product.RecId
                                &amp;&amp;  assortmentLookupExclude.lineType == RetailAssortmentExcludeIncludeType::Exclude
                        exists join assortmentLookupChannelGroupExclude
                            where   assortmentLookupChannelGroupExclude.OMOperatingUnitId == store.OMOperatingUnitID
                                &amp;&amp;  assortmentLookupChannelGroupExclude.AssortmentId == assortmentLookupExclude.AssortmentId;

                        if (!product)
                            ret = false; //The product does NOT belong to the stores assortment, and should not be planned
        return ret;

I also have code to restrict creation of manual purchase orders, and where simular code can be used, but let’s hope that Microsoft can further extend standard Dynamics 365 with assortment based procurement planning.

Copy with pride, and let’s hope next year will give us 365 more opertunities.

DAX2012 R3 – Playing with Retail CRT

The Microsoft Dynamics AX commerce runtime (CRT) serves as the engine for a retail channel. It connects to a CRT database that stores business data for the channel.. Dynamics AX uses the CRT Architecture in the Retail ePOS, mPOS and in the Sharepoint based eCommerce solution. Also the SiteCore eCommerce solution uses the CRT as the integration to Dynamics AX.

Let’s look at some of the interesting things with CRT.

1. It is a well-documented framework supported by Microsoft. More details on it is available here.

2. It can make it easier to integrate business logics to other eCommerce solutions. Like Magento.

3. We can create CRT web-services that handles information like very advanced retail prices, on-hand shipment, check-outs etc.

4. And MUCH more. This CRT we will have almost everything we need to interact with external system. It Rocks !

Let’s take an example. I would like to utilize the CRT to calculate a sales price with discounts. Price/discounts in AX is very flexible and almost all options you can imagine. Here is a small example of the possibilities we get in Dynamics AX 2012 R3 CU 8.

Let’s say we would like to expose this price/discount structure as a web-service to external parties. Since the CRT is relying on it’s own channel database, it means that the much of the integration is actually not creating a heavy load on the Dynamics AX database. We also have the possibility to “scale out” on the architecture.

The CRT is developed in Visual Studio, and is basically just a bunch of dll’s. I have to admit, that Visual studio, Web-services and is not my strongest side. So I decided to see it I could access and use the CRT framework directly from Dynamics AX. The new call-center module in AX 2012 R3 CU8 is using this, and I wanted to create a very simple job that exemplifies how CRT operates and can be used. Basically I’m looking for this:

I use the standard AX 2012 R3 CU8 contoso demo data, but added the following quantity discount to the Houston store;

Since I know X++, I’ll use X++ job to try this. The infolog I got was the following:

Here we see that the CRT returned the sales price, discounted price, and also information like total amount. Surely it can give everything else, but I wanted to keep the example easy to understand.

I created the following job to play with the framework, and it gives you an idea of how to interact with the CRT from AX.

static void demo_CRT_GetSalesPrice(Args _args)
    //To test this code, use the AX 2012 R3 CU8, and use the USRT company
    Microsoft.Dynamics.Commerce.Runtime.Data.IPricingDataManagerV2                pricingManager;
    Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.DiscountParameters priceParameters;
    Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesLine                       crtSalesLine;
    Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesTransaction                crtSalesTransaction;
    System.Object                                                                 roundingRule, currencyConverter;
    CLRObject                                                                     clrSalesLines, enumeratorSalesLine;
    CLRObject                                                                     clrObjectSalesLines;
    System.DateTimeOffset                                                         activeDateTimeOffset;
    System.String                                                                 lineIdString;
    System.String                                                                 customerPriceGroup = "";
    Price                                                                         price;
    Qty                                                                           qty;
    InventDim                                                                     inventdim;

    RetailStoreTable    storeTable = RetailStoreTable::find("HOUSTON");   //Lets use houston as an example here
    RetailChannelRecId  channelId = storeTable.RecId;                     //This is the recid for a retail channel. A retail store is an extension of a retail channel.
    CurrencyCode        currencyCode = storeTable.Currency;
    appl.setDefaultCompany(, false);                  //Setting default company to houstons company

    //Creating a unique line ID
    lineIdString = System.Guid::NewGuid().toString();

    InventDim.InventLocationId = storeTable.inventLocation;
    InventDim.InventSiteId     = InventLocation::find(storeTable.inventLocation).InventSiteId;
    InventDim                  = InventDim::findOrCreate(InventDim);

    //Pricing manager : Encapsulates data access to pricing information.
    pricingManager          = Microsoft.Dynamics.Commerce.Headquarters.RetailProxy.AxPricingDataManager::BuildDataManager(channelId);

    //Price parameters : types of AX discounts (aka trade agreement discounts, aka not Retail Periodic Discounts) are currently activated and should be allowed on the transaction
    priceParameters         = Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.DiscountParameters::CreateAndInitialize(pricingManager);
    roundingRule            = Microsoft.Dynamics.Commerce.Headquarters.RetailProxy.ServiceHelpers::GetRoundingMethod(currencyCode);
    currencyConverter       = Microsoft.Dynamics.Commerce.Headquarters.RetailProxy.ServiceHelpers::GetCurrencyConverter(currencyCode);
    activeDateTimeOffset    = RetailPricingEngineHelper::getSessionDateTimeInChannelTimeZone(channelId);

    //Next we create a crtSalesLine, that represents a channel agnostic sales line.
    crtSalesLine = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesLine();

    crtSalesLine.set_ItemId("0001");        //Lets use product 0001 as our example
    crtSalesLine.set_Quantity(10);          //Lets use quantity 10 as our example
    crtSalesLine.set_LineId(lineIdString);  //Let give this line a uniqe ID

    crtSalesLine.set_UnitOfMeasureSymbol("ea"); //Let use 'ea' as the unit

    //We then create a sales transaction, and add the salesline to this.
    crtSalesTransaction = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesTransaction();
    clrObjectSalesLines = crtSalesTransaction.get_SalesLines();
    clrObjectSalesLines.Add(crtSalesLine);                      //This is where the we add the salesLine to the sales transaction

    //Lets calculate the prices

    //Lets calculate the discounts

    //And, let's show what the CRT can give us.
    qty   = crtSalesLine.get_Quantity();
    Price = crtSalesLine.get_Price();
    info(strFmt("Normal sales price is %1 when buying %2", price, qty ));

    Price = crtSalesLine.get_DiscountAmount();
    info(strFmt("Discount amount pr unit is %1, when buying %2", price/qty, qty ));

    Price = crtSalesLine.get_NetAmountPerUnit();
    info(strFmt("Net amount pr unit is %1, when buying %2", price, qty ));

    Price = crtSalesLine.get_TotalAmount();

    info(strFmt("Total amount is %1 when buying %2", price, qty ));

Maybe some of you can teach me how to expose the CRT in a web-service, so that we can easily




Dynamics AX 2012 R3- To exist or not, that is the question!

The advanced filter and query in Dynamics AX is a very powerful tool. Here you can search on most fields, and also add relations to the query.

You can open this advanced query screen from almost any AX screen, and the short-cut is “CTRL-F3”.

But there is one area that the advanced query screen is not handling. That is “Not-exists-join”. Let’s say I want a list of all the purchase orders that DON’T have purchase lines. The standard AX will not help you here. Often a developer must come inn and create a smart query or script to find this. With the introduction of attributes and retail categories, I see the need for this “not-Exists-join” feature to be able to create advanced queries.

I don’t accept this, because I want to empower the end-user to perform such queries. So here is my gift to the Dynamics AX ecosystem; NotExistsJoin directly on the advanced query screen:

Then I can look at the filtered purchase list page, and I only see purchase orders that DON’T have any purchase lines J

This will work allover dynamics AX, even on cue’s and on other query based functionality.

So what’s the trick? A few changes to the following two classes:

Let’s take the changes “one-by-one”, or you can download the code here :

Then here is the last change to get a icon on not-exists join:

Have fun DAX’ing 😉

Dynamics AX 2012 R3: Global recommended retail price

Maintaining retail prices in a multi company and franchise scenario can be a challenge in Dynamics AX 2012 R3. Let say we have a retailer with stores in many countries. We often also see owner structures that makes us model the retail chain with many companies.

I’m currently in an implementation to an international retailer present in 22 countries, and it keeps growing every day. To model this we need to create one Dynamics AX company per country and per partner.

The ability to maintain and control prices in such a scenario is a challenge, because prices is a property of a released item. (PriceDiscTable)

So we decided to create very small customization so that we could keep track of prices and item barcodes on the product instead of the released product level.

By doing this, the product managers have one screen where they can create and maintain product prices. The prices can be specified per currency, country and company/partner. But this is not just a simple table, but a two-way frontend for populating the standard AX price tables.

We therefore have a shared table:

The code for publishing prices into standard AX, using the price journal posting looks like this:

void publishProductRetailPrices()
    DataArea                            dataArea;
    PriceDiscTable                      priceDiscTable;
    PriceDiscAdmTable                   priceDiscAdmTable;
    PriceDiscAdmTrans                   priceDiscAdmTrans;
    PriceDiscAdmCheckPost               priceDiscAdmCheckPost;
    InventDimId                         inventDimId;
    Ledger                              ledger;
    CompanyInfo                         legalEntity;
    LogisticsAddressCountryRegionId     countryRegionId;
    PriceDiscAdmName                    priceDiscAdmName;

    while select Id from  dataArea where dataArea.Id != #DATCompany
            legalEntity             = CompanyInfo::find();
            ledger                  = Ledger::findByLegalEntity(legalEntity.RecId);
            countryRegionId         = CompanyInfo::find().postalAddress().CountryRegionId;

            if (this.RefCompanyId       == dataArea.Id ||
                this.CountryRegionId    == countryRegionId ||
               (this.RefCompanyId == "" && this.CountryRegionId == "" && this.CurrencyCode == ledger.AccountingCurrency))
                if (InventTable::exist(this.ProductNumber))
                    priceDiscAdmName = this.findOrCreatePriceDiscAdmName(#ImportPriceDiscJournalName,"@SYS342509");

                    priceDiscAdmTable.JournalName       = priceDiscAdmName.JournalName;
                    priceDiscAdmTable.Name              = priceDiscAdmName.Name;

                    inventDimId = InventDim::findOrCreateBlank().InventDimId;

                    while select firstonly priceDiscTable
                        where   priceDiscTable.Relation         == PriceType::PriceSales
                            &&  priceDiscTable.ItemCode         == TableGroupAll::Table
                            &&  priceDiscTable.ItemRelation     == this.ProductNumber
                            &&  priceDiscTable.AccountCode      == TableGroupAll::GroupId
                            &&  priceDiscTable.AccountRelation  == #RRP
                            &&  priceDiscTable.Currency         == this.CurrencyCode
                            &&  priceDiscTable.InventDimId      == inventDimId
                            &&  priceDiscTable.PriceUnit        == this.PriceUnit
                    priceDiscAdmTrans.Relation         = PriceType::PriceSales;
                    priceDiscAdmTrans.ItemCode         = TableGroupAll::Table;
                    priceDiscAdmTrans.ItemRelation     = this.ProductNumber;
                    priceDiscAdmTrans.AccountCode      = TableGroupAll::GroupId;
                    priceDiscAdmTrans.AccountRelation  = #RRP;
                    priceDiscAdmTrans.Currency         = this.CurrencyCode;
                    priceDiscAdmTrans.InventDimId      = inventDimId;
                    priceDiscAdmTrans.PriceUnit        = this.PriceUnit;
                    priceDiscAdmTrans.JournalNum       = priceDiscAdmTable.JournalNum;
                    priceDiscAdmTrans.QuantityAmountFrom  = 0;
                    priceDiscAdmTrans.Amount           = this.Amount;
                    priceDiscAdmTrans.UnitId           = InventTable::find(this.ProductNumber).salesUnitId();
                    priceDiscAdmTrans.SearchAgain      = NoYes::Yes;
                        priceDiscAdmCheckPost = new PriceDiscAdmCheckPost(false);

Generic prices in Dynamics AX 2013 R3

In Dynamics AX 2012 R3 you can specify a sales price on a released item. You cannot specify a price on a set of products using the trade agreements.

I wanted to change this, so that AX 2012 R3 do have the possibility to have a price that is used by many released products. Here is what I did:

First I created a new field on the item master sales (InventTableModule):

Then I made it possible to create sales price groups, and to specify prices per currency for this price group:

Then when creating sales order it selects the generic price.

With this little change I can now control prices on thousands of released products, and rather to put individual prices on each item, I just classify each item to a price group. Let’s say I have 10.000 released products and prices in 6 currencies this would minimum have resulted in 60.000 records in the price agreement table. If I in addition have 10 companies where I also want to use the prices, this results in 600.000 records on prices.

If I have standardized my pricing levels to consumer oriented prices, and using the sales price group, I would only need to maintain 60 * number of price groups. Just a fraction J

To make this happen, the following objects needs to be modified:

If you come across a similar requirement or you need this, feel free to contact me on the blog J

Happy DAX’ing

AX2012 : Use queries as criteria

In Dynamics AX 2012 the relation between different records is often defined as a “table-group-all” relation. One example of this is the setup of supplementary items, where you can define the relation as a Table-Group-All relation.


Even though this approach is easy to use and understand, it does have limitations.  Often you would like to pin-point the relationships even further.  It can be based on other fields in the source record.

I wanted to look against another approach, where I can use a query as a criteria.  Normally queries are used to fetch specific records based on a set of ranges and defined sorting criteria.

I have a label software, that integrates Dynamics AX 2012 with BarTender, and it is generic, meaning that it can produce labels based on all data sources in AX.


So in this case, I can setup that when the user wants to print a label, I can search through a set of label templates, and check this record if it fits the query criteria.  I then have a endless possibility to specify all kinds of criteria to select the best matching label template.

The trick is to add the source recid as a range to the packed query criteria exactly when checking for it.  Also to add the “firstonly” hint, so that you don’t query for more than you want.

Here is some sample job, I used to test out this approach:


Microsoft is also using the same approach when selecting is a WMSorder can be added to a existing shipment.  Take a look at  WMSShipment.canWMSOrderBeAdded() or at wmsShipmentTemplate.canWMSOrderBeAdded(), then you see the same pattern.

I hope you see the value in this, and that it can open a complete new way of adding rules and relational information to Dynamics AX 2012.  With this approach we can generically pinpoint our criteria for our rules.

Take care and happy DAXing