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()
{
    #Admin
    #Zebra
    DataArea                            dataArea;
    PriceDiscTable                      priceDiscTable;
    PriceDiscAdmTable                   priceDiscAdmTable;
    PriceDiscAdmTrans                   priceDiscAdmTrans;
    PriceDiscAdmCheckPost               priceDiscAdmCheckPost;
    InventDimId                         inventDimId;
    Ledger                              ledger;
    CompanyInfo                         legalEntity;
    LogisticsAddressCountryRegionId     countryRegionId;
    #define.ImportPriceDiscJournalName("Prices")
    PriceDiscAdmName                    priceDiscAdmName;

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

            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.clear();
                    priceDiscAdmTable.JournalName       = priceDiscAdmName.JournalName;
                    priceDiscAdmTable.Name              = priceDiscAdmName.Name;
                    priceDiscAdmTable.insert();

                    priceDiscAdmTrans.clear();
                    priceDiscAdmTrans.initValue();
                    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.initFromPriceDiscTable(priceDiscTable);
                    }
                    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;
                    priceDiscAdmTrans.insert();
                    if(priceDiscAdmTable.RecId)
                    {
                        priceDiscAdmCheckPost = new PriceDiscAdmCheckPost(false);
                        priceDiscAdmCheckPost.initJournalNum(priceDiscAdmTable.JournalNum);
                        priceDiscAdmCheckPost.run();
                        infolog.clear();
                    }
                }
            }
        }
    }
    ttscommit;
}
Advertisements

8 thoughts on “Dynamics AX 2012 R3: Global recommended retail price

  1. Hi Kurt,

    First of all, great job on a great blog.! It is very informative and insightful, only someone with hands dirty in an implementation can share such insights.

    I am someone with experience in oracle retail, but am interested in getting up to speed in DAX Retail.

    In that regard your statement ”prices is a property of a released item.” got me thinking, does this mean that Item-Price relationship is maintained as per the SKU, for e.g, 250gm Tortilla Chips Nature (as in your e.g)? Also shouldn’t this relationship be maintained as per Item-Store-Price, meaning, different stores could have different price for same product?

    All comments appreciated!

    Like

    • Thanks Narayanan. Yes, in AX there are all Levels of prices, discounts, price Groups etc. So there are features for having item-store-prices. The reason why I called the solution “Global Recommended Retail Price” was to give the possibility to have an overall global recommended price. Each store can certainly have induvidual prices 🙂

      But thanks for asking the question 🙂

      Like

  2. Hi Kurt, Thanks for your informative blog and Help, Its been really great checking out your posts. Please I have a question relating to AX Retail POS statement posting, It shows a negative Transaction Cash value when statement postings are calculated within two days only. I am not sure if your have encountered such an error in the past. But it would be great if you can share some information on this error. Thanks for your help in Advance.

    Like

  3. Hi Kurt
    Could this be adapted to create shelf edge and product labels for the different areas. I work for a company that is made up of different companies and some have different prices for the same products. We have price changes every day and it can range from 10 items to over a 300 items. At the moment the prices are controlled at a central office and then sent out to the different companies and there branches. This then generates the shelf edge labels for each of the branches matching there price group and for items which are not kept on shelves it generates product labels matching the amount of stock that each branch is holding at that time.

    Like

    • Hi Harold. I know the busniness requirements you are describing, and I have implemented this feature at some customers. But this solution don’t have this . It would have to be extended to support price label distribution and printout.

      Thanks for Reading my blog:-)

      –Kurt

      Like

  4. Hi Kurt. is a terrific blog what you have here! My question: is there a way to change massive retail prices and currencies in AX 2012 using X++? I have doing this in SQL and worked so far:

    update MicrosoftDynamicsAX.dbo.PriceDiscTable set CURRENCY=’MXN’,AMOUNT=round(round(AMOUNT*16.50*1.16,0)/1.16,2),
    where ITEMRELATION=’063535′ and DATAAREAID=’CORP’ and ACCOUNTRELATION=’AIRPORT’;

    In the example, I changed the currency of all products from USD to MXN, using an exchange rate of 16.50 and a VAT of 16% (México), first round the result (the sale price) to cero decimals, and second get rid the VAT, leaving the net price with two decimals.

    Some people told me it’s not good to do this in SQL, to use X++ instead, but I no idea how to do it.

    Thanks in advance.

    Like

    • Hi Juan. Thank you for reading my blog. I try not to do consultancy through the blog, but a general rule I have is to run all data changes through AX and the X++ logics. Remember that changing prices in AX is not just updating the PriceDiscTable, but it is actually posting a price journal. So you will be missing out on a lot of logics. Like having a documented changelog on your prices. Take care and happy DAX’ing.

      Like

      • You’re right, I’ve found another reference of the price in the price journal (PRICEDISCADMTRANS), looks like it worked in my case, because all the price in our system were created using CREATE COMERCIAL AGREEMENT directly in the products page, and not creating a journal in SALES AND MARKETING.

        Thanks for your help!

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s