Additional guidance to database upgrade scripts for D365 Finance and Operations. Part 2. Custom inventory dimensions data upgrade.

Introduction

About a year ago I posted an article about inventory dimensions data migration from Ax 2012 to D365 version. That post has general ideas regarding the inventory dimension data migration approach. This time I'm going to share some more consistent guidance. All my outcomes and ideas are based on my personal experience with custom dimensions data migration projects. The intercompany functionality is not revised in this post.

Before you go on reading this post, please read my other posts about the data upgrade process since some terms and definitions I use, were explained there.

Data requirements

The "InventDim" table is different in Ax 2012 and D365. In Ax 2012 you could add the custom inventory dimension as new fields to the "InventDim" table and change some InventDim* macros. In D365 you should map your custom dimension to the "InventDimension1" – "InventDimension10" based on this guide.  

In fact, during the data migration, you need to move values from Ax 2012 "InventDim" table fields to D365 "InventDimension1" – "InventDimension10"  "InventDim" table fields before the data upgrade logic is triggered. It should be done during the "PreSync" stage when the database has the table structure from Ax 2012 and D365 versions at that moment.

Also, the structure of the "WHSInventReserveTable" has been changed in D365 and those changes should be taken into consideration.

Functional requirements

During the data upgrade execution, the system should be configured properly, from an inventory management perspective. It means that:

1. The required configuration keys ("InventDimension1" – "InventDimension10") should be activated.
2. All reservation hierarchies should be set correctly.
3. All item dimension groups (Tracking, Storage, Product) should have proper setups.

If one of the mentioned requirements is not met during the date upgrade at the "PostSync" step, the custom inventory dimension migration will fail from a data consistency perspective.

Data upgrade

The "PreSync" stage

In order to meet the data requirements, we need to develop a script like this at the "PreSync" step:

[

  UpgradeScriptDescription("Script description"),

  UpgradeScriptStage(ReleaseUpdateScriptStage::PreSync),

  UpgradeScriptType(ReleaseUpdateScriptType::StandardScript),

  UpgradeScriptTable(tableStr(InventDim), false, true, true, false)

]

public void updateInventDimensionField()

{

    FieldName     field2012Name  = 'YOURDIMENSIONFIELD';

  FieldName     field365Name   = 'INVENTDIMENSION1';

  SysDictTable  inventDimDT    = SysDictTable::newTableId(tableNum(InventDim));

  TableName     inventDimName  = inventDimDT.name(DbBackend::Sql);

  str sqlStatement = strFmt(@"

                UPDATE [dbo].[%1]

                SET [dbo].[%1].[%2] = [dbo].[%1].[%3]

                WHERE [dbo].[%1].[%3] <> ''",

                inventDimName,

                field365Name,

                field2012Name);

  ReleaseUpdateDB::statementExeUpdate(sqlStatement);

}

It’s one of the possible options. The code is provided "as is" without any warranty. You can develop your own script in another way. It’s up to you. The key point is that the "InventDim" field values are to be moved at the "PreSync" stage properly.

The "PostSync" stage

At the "PostSync" stage when the database has a D365 table structure, the system performs the key steps from an inventory dimension data upgrade perspective.

There is a class "ReleaseUpdateDB73_WHS". It has the methods of the "WHSInventReserveTable" data upgrade. The key methods here are "populateParentInventDimIdOfWhsInventReserveMinor" and "populateParentInventDimIdOfWhsInventReserveMajor". Those methods call the "WHSInventReservePopulateParentInventDimId::populateForAllItems();" method. The method "populateForAllItems" populates the "WHSInventReserve" table based on the system setups that I mentioned in the functional requirements paragraph.

So, we need to develop an extension of this method and place the configuration keys activation code and update reservation hierarchies and dimension groups before the "next" method call. This is how we can meet the functional requirements before the data upgrade is triggered. In order to do it - add the extension to this method exactly since the data upgrade scripts can execute independently as I mentioned here.

The code changes can be like this:

/// <summary>

/// <c>WHSInventReservePopulateParentInventDimIdAXPUPG_Extension</c> class extension of <c>WHSInventReservePopulateParentInventDimId</c> class

/// </summary>

[ExtensionOf(classStr(WHSInventReservePopulateParentInventDimId))]

final class WHSInventReservePopulateParentInventDimIdAXPUPG_Extension

{

    /// <summary>

    /// Populates the <c>ParentInventDimId</c> field for all items.

    /// </summary>

    public static void populateForAllItems()

    {

        FieldId     fieldId2012; //you have to set your field identifier

        FieldId     fieldIdD365;

        ConfigurationKeySet keySet = new ConfigurationKeySet();

        SysGlobalCache      cache = appl.globalCache();

        boolean             isConfigChanged;

 

        void updateWHSReservationHierarchyElement()

        {

            WHSReservationHierarchyElement  hierarchyElement;

            hierarchyElement.skipDatabaseLog(true);

            ttsbegin;

            update_recordset hierarchyElement

            setting DimensionFieldId = fieldIdD365

                where hierarchyElement.DimensionFieldId == fieldId2012;

            ttscommit;

        }

 

        void updateEcoResTrackingDimensionGroupFldSetup()

        {

            EcoResTrackingDimensionGroupFldSetup     dimensionGroupFldSetup;

 

            dimensionGroupFldSetup.skipDatabaseLog(true);

 

            ttsbegin;

 

            update_recordset dimensionGroupFldSetup

            setting DimensionFieldId = fieldIdD365

                where dimensionGroupFldSetup.DimensionFieldId == fieldId2012;

 

            ttscommit;

        }

 

        if (//don’t forget to add a check 

            that the extension is calling during the data upgrade)

        {

            keySet.loadSystemSetup();

            if (

             !isConfigurationkeyEnabled(configurationKeyNum(InventDimension1)))

            {

                keySet.enabled(configurationKeyNum(InventDimension1), true);

                isConfigChanged  = true;

            }

 

            if (isConfigChanged)

            {

                SysDictConfigurationKey::save(keySet.pack());

                SysSecurity::reload(true, true, true, false, true);

            }

 

            fieldIdBPHContainer = fieldNum(InventDim, InventDimension1);

       

            updateWHSReservationHierarchyElement();

            updateEcoResTrackingDimensionGroupFldSetup();

        }

 

        next populateForAllItems();

    }

}

It’s one of the possible options. The code id provided "as is" without any warranty. You can develop your own script in another way. It’s up to you. The key point is to activate the required inventory setups and configuration keys before the system can start the upgrade of the "WHSInventReserve" table.

Final steps

When the data upgrade is completed the "InventDimension1" - "InventDimension10" configuration keys could be disabled. You should check their status on the form "License configuration" ( System Administration/Setup/License configuration) under the configuration key "Trade". You need to enable the required keys manually if needed and align SQL warehouse procedures with the command:

https://YOUR_ENVIRONMENT_URL/?mi=SysClassRunner&cls=Tutorial_WHSSetup

Then you can open the "On-Hand" form, and add your custom dimensions to display and verify the outcomes. Also, it makes sense to do some functional tests. You can pick items via inventory journals, for instance.