Techno-Functional Dynamics 365 Data Model Explanations for Reporting, Part 22 of 25: Understanding Inventory Consignment Process in Dynamics 365

Techno-Functional Dynamics 365 Data Model Explanations for Reporting, Part 22 of 25: Understanding Inventory Consignment Process in Dynamics 365

Optimizing Inventory Consignment Process with Dynamics 365

Inventory Consignment Setup and Process in Microsoft Dynamics 365 [Technical]

‘Optimizing Inventory Consignment Process’ is the second part of Dynamics 365 Inventory Consignment Setup and Process. It is equally important to understand.

This blog consists of 3 sections:

  1. In the 1st section, we will be discussing the Entity Relationship Diagram and tables for creation of a released item in Dynamics 365. For Developers, X++ code is added to directly create a released item in Dynamics 365.
  2. Moving towards the 2nd section, we will discuss Entity Relationship Diagram (ERD) and table details to understand the Inventory Owner Creation Process in Dynamics 365.
  3. Lastly, in the 3rd section, Inventory Consignment Journal creation will be discussed.

For tables and the relationship between them in the Inventory Consignment Journal Creation Process, ERD and table details are mentioned with the X++ code.

This has been done to make it easy for Developers to create journals directly from Visual Studio.

How do you optimize Inventory Consignment with Dynamics 365?

Let’s start with how we can easily optimize the Inventory Consignment Process with Microsoft Dynamics 365.

Section 1: Released Item Creation

Inventory Consignment Entity Relationship Diagram (ERD):

How to Optimize Inventory Consignment with Dynamics 365?

EcoresProductTranslation:

In this table; product name, product name language ID and relation to EcoResProduct tablet is stored.

EcoresProductTranslation table has a one-to-one relation with the EcoresProduct table.

RecId from EcoresProduct table becomes a Product in the EcoresProductTranslation table.

Below are some of the details which will be useful for this process:

Field Data Type Description
Name string This field used to save product names
LanguageId string This field stored LanguageId of product name
Product int This field stored corresponding recid from EcoresProduct table.

EcoresProduct:

In this table; product number/ID, product type, product number for display and search name are stored.

Sometimes, the product name and search name vary, which is why the product name and search name are both stored separately.

Field Data Type Description
DisplayProductNumber string This field is used to save product numbers for user display.
Product Type enum This field stores product type i.e item, service
RecId int This field stores record id (always unique)
Search Name string Product search name is stored in this field

EcoresProductMaster:

This table works as a base for the relationship of products with dimensions. It includes the color, size and style, with a record ID that is always unique.

It has a one-to-one relation with EcoresProduct table record and Ids (RecId) of both tables are the same.

For more details on this table, please see the columns below:

Field Data Type Description
RetailsSizeGroupId string This field is used to save corresponding size group id attached to product
RetailColorGroupId string This field is used to save corresponding color group id attached to product
RecId int This field stores record id (always unique)
RetailStyleGroupId string This field is used to save corresponding style group id attached to product

EcoresProductMasterColor:

This table is used to store relations between EcoresColor table and EcoresProductMaster.

Field Data Type Description
Color int In this field corresponding recIds are stored from EcoresColor table
ColorProductMaster string In this field recid of corresponding record in EcoresProductMaster is stored
RecId int This field stores record id (always unique)

EcoresProductMasterSize:

This table is used to store relations between EcoresSize table and EcoresProductMaster.

Field Data Type Description
Size int In this field corresponding recIds are stored from EcoresSize table
SizeProductMaster string In this field recid of corresponding record in EcoresProductMaster stored
RecId int This field stores record id (always unique)

Code:

class CreateandReleaseProductRunableClass
{        
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        EcoResReleasedProductCreationV2Entity ecoResReleasedProductCreationV2Entity;
        try
        {
            ecoResReleasedProductCreationV2Entity.clear();
            ecoResReleasedProductCreationV2Entity.initValue();
            ecoResReleasedProductCreationV2Entity.ItemNumber = "IB-TestProduct2";
            ecoResReleasedProductCreationV2Entity.ItemModelGroupId = "STD";
            ecoResReleasedProductCreationV2Entity.InventoryUnitSymbol = "ea";
            ecoResReleasedProductCreationV2Entity.BOMUnitSymbol = "ea";
            ecoResReleasedProductCreationV2Entity.ProductGroupId = "Audio";
            ecoResReleasedProductCreationV2Entity.ProductType = EcoResProductType::Item;
            ecoResReleasedProductCreationV2Entity.ProductNumber = "IB-Test Product2";
            ecoResReleasedProductCreationV2Entity.ProductSearchName = "IB-TestProduct2";
            ecoResReleasedProductCreationV2Entity.ProductName = "IB Test Product 2";
            ecoResReleasedProductCreationV2Entity.TrackingDimensionGroupName = "Owner";
            ecoResReleasedProductCreationV2Entity.ProductSubType = EcoResProductSubtype::Product;
            ecoResReleasedProductCreationV2Entity.SearchName = "IB-TestProduct2";
            ecoResReleasedProductCreationV2Entity.SalesUnitSymbol = "ea";
            ecoResReleasedProductCreationV2Entity.StorageDimensionGroupName = "SiteWH";
            ecoResReleasedProductCreationV2Entity.PurchaseUnitSymbol = "ea";
            ecoResReleasedProductCreationV2Entity.insert();
        }
        catch
        {
            error(enum2Str(Exception::CLRError));
        }
    }

}

Section 2: Inventory Owner Creation Process

Inventory Consignment Entity Relationship Diagram (ERD):

Section 2: Inventory Owner Creation Process

CustTable:

Field Data Type Description
AccountNum string This field contains the customer account number
CustGroup string This field contains the customer group
BankAccount string Bank account for customer
InvoiceAccount string Invoice account on which invoice will be posted
InventSiteId string Site for sales order
InventLocation string Warehouse for sales order

VendTable:

Field  Field Data Type Description
AccountNum string This field contains Vendor Account for Vendor
VendGroup string It has information of Vendor Group
PaymentTermId string This field contains Terms of Payment
InvoiceAccount string It contains the account number on which invoice will be posted
ItemBuyerGroupId string It has location information for which purchase order is created
InventSiteId string Site information is found in this field

DirPartyTable:

Fields Data Type Description
Name string Name field includes the name (Vendor name)
NameAlias string This field contains name Alias i-e Search Name
PartyNumber string The Party Number which identifies the ‘type’ is found in this field.

Code:

  1. Create new Data Entity using InventOwner_RU table as a data source
  2. Override the insert method of data entity and past given below code
public void insert(InventOwnerAccountType_RU    _InventOwnerType = InventOwnerAccountType_RU::Vend, InventOwnerId   _InventOwnerid = '')
    {
        InventOwner_RU      inventOwner;
        
        super();
        if(!InventOwner_RU::exist(_InventOwnerid))
        {
            ttsbegin;
            inventOwner.clear();
            inventOwner.InventOwnerId = _InventOwnerid;
            inventOwner.Party = VendTable::find(_InventOwnerid).Party;
            inventOwner.AccountType = _InventOwnerType;
            inventOwner.insert();
            ttscommit;

            Info(strFmt('New inventory owner %1 inserted!', _InventOwnerid)); 
        }
    }

  1. Add new runnable class in project and post below given code
class CreateInventoryOwnerRunableClass
{        
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        InventOwnerDataEntity     inventOwnerDataEnity;
        InventOwnerId             inventOwnerId;
        
        inventOwnerId = VendTable::find('1003').AccountNum;
        
        if(inventOwnerId)
        {
            inventOwnerDataEnity.insert(InventOwnerAccountType_RU::Vend, inventOwnerId);
        }
        else
        {
            throw Global::error('Incorrect vendor account');
        }
    }

}
  1. Set a runnable class as Startup Object and start the Visual Studio project.

Section 3: Create Inventory Consignment Journal

Inventory Consignment Entity Relationship Diagram (ERD):

Section 3: Create Inventory Consignment Journal

Code:

    1. Create new Data Entity, set ConsignmentReplenishmentOrderHeader table as datasource
    2. Form new methods in this data entity like mentioned below
public void insert(ConsignmentReplenishmentOrderNumber  _orderNum = '', AccountNum   _accountNum = '', InventSiteId   _siteId = '', InventLocationId   _locationId = '')
    {
        ConsignmentReplenishmentOrderHeader     consignmentJournalHeader;
        LogisticsLocationDefault                locationDefault;
        LogisticsLocation                       location;
        
        
        super();
        try
        {
        ttsbegin;
            consignmentJournalHeader.ReplenishmentOrderNumber = _orderNum;
            consignmentJournalHeader.OrderVendorAccountNumber = _accountNum;
            consignmentJournalHeader.DefaultReceivingSiteId =   _siteId;
            consignmentJournalHeader.DefaultReceivingWarehouseId = _locationId;
            consignmentJournalHeader.RequestedDeliveryDate = today();
            consignmentJournalHeader.DeliveryPostalAddress = this.getDeliveryAddressFromSite(_siteId).RecId;
            location = LogisticsLocation::find(LogisticsPostalAddress::findRecId(consignmentJournalHeader.DeliveryPostalAddress).Location);
            consignmentJournalHeader.DeliveryAddressName = location.Description;
            consignmentJournalHeader.insert();
        ttscommit;
            
        }
        catch
        {
            Info(strFmt('%1', Exception::CLRError));
        }
        
    }

    public LogisticsPostalAddress getDeliveryAddressFromSite(InventSiteId    _siteId)
    {
        InventSite                  inventSite;
        InventSiteLogisticsLocation inventSiteLogisticsLocation;
        LogisticsLocation           logisticsLocation;
        LogisticsPostalAddress      logisticsPostalAddress;
        select logisticsPostalAddress 
            join logisticsLocation where logisticsPostalAddress.Location == logisticsLocation.RecId
            join inventSiteLogisticsLocation where inventSiteLogisticsLocation.Location == logisticsLocation.RecId
            join inventSite where inventSite.RecId == inventSiteLogisticsLocation.Site && inventSite.SiteId == _siteId;

        return logisticsPostalAddress;
    }

    /// <summary>
    ///
    /// </summary>
    public ConsignmentReplenishmentOrderHeader getReplenishmentOrderHeader(ConsignmentReplenishmentOrderNumber   _orderNumber)
    {
        ConsignmentReplenishmentOrderHeader consignmentReplenishmentOrderHeader;

        select consignmentReplenishmentOrderHeader where consignmentReplenishmentOrderHeader.ReplenishmentOrderNumber == _orderNumber;

        return consignmentReplenishmentOrderHeader;
    }

    public ConsignmentReplenishmentOrderNumber initJournalNum()
    {
        NumberSeq   num = new NumberSeq();
        num = NumberSeq::newGetNum(PurchParameters::numRefReplenishmentOrder());

        return num.num();

    }

    1. Create another data entity and set ConsignmentReplenishmentOrderLine table as datasource
    2. Add below methods in this data entity methods
public void initFromHeader(ConsignmentReplenishmentOrderHeader  _header, ConsignmentReplenishmentQuantity _quantity, ItemId _itemId)
    {
        ConsignmentReplenishmentOrderLine   replenishmentOrderLine;
        InventDim                           inventDim;
        
        try
        {
            ttsbegin;
            replenishmentOrderLine.ConfirmedDeliveryDate = today();
            replenishmentOrderLine.DeliveryAddressName = _header.DeliveryAddressName;
            replenishmentOrderLine.DeliveryPostalAddress = _header.DeliveryPostalAddress;
            replenishmentOrderLine.InventTransId = NumberSeq::newGetNum(InventParameters::numRefInventTransId()).num();
            replenishmentOrderLine.ItemId = _itemId;
            replenishmentOrderLine.LineNumber = this.getNextLineNumber(_header.ReplenishmentOrderNumber);
            replenishmentOrderLine.RemainingInventoryPhysicalQuantity = _quantity;
            replenishmentOrderLine.RemainingReplenishmentPhysicalQuantity = _quantity;
            replenishmentOrderLine.ReplenishmentInventoryQuantity = _quantity;
            replenishmentOrderLine.ReplenishmentQuantity = _quantity;
            replenishmentOrderLine.ReplenishmentOrderLineStatus = ConsignmentReplenishmentOrderLineStatus::OpenOrder;
            replenishmentOrderLine.ReplenishmentOrderNumber = _header.ReplenishmentOrderNumber;
            replenishmentOrderLine.ReplenishmentUnitId = InventTableModule::find(replenishmentOrderLine.ItemId,ModuleInventPurchSales::Invent).UnitId;
            replenishmentOrderLine.RequestedDeliveryDate = _header.RequestedDeliveryDate;

            inventDim.InventLocationId = _header.DefaultReceivingWarehouseId;
            inventDim.InventSiteId     = _header.DefaultReceivingSiteId;
            inventDim.initFromInventLocation(inventDim.inventLocation());
            inventDim = InventDim::findOrCreate(inventDim);

            replenishmentOrderLine.InventDimId = inventDim.inventDimId;

            replenishmentOrderLine.insert();
            ttscommit;

            Info(strFmt('%1', replenishmentOrderLine.InventDimId));

        }
        catch
        {
            Info(strFmt('%1', Exception::CLRError));
        }
    }

    public LineNumber getNextLineNumber(ConsignmentReplenishmentOrderNumber _orderNumber)
    {
        ConsignmentReplenishmentOrderLine   replenishmentOrderLine;
        LineNumber lineNumber = 0;
        select maxof(LineNumber) from replenishmentOrderLine where replenishmentOrderLine.ReplenishmentOrderNumber == _orderNumber;
        return lineNumber+1;
    } 

    1. Add new runnable class in project and then add mentioned code into the class
class CreateConsignmentReplenishmentOrderRunableClass
{        
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        ConsignmentReplenishmentHeaderEntity    replenishmentHeaderEntity;
        ConsignmentReplenishmentLinesEntity     replenishmentLinesEntity;
        ConsignmentReplenishmentOrderNumber     orderNumber;

        orderNumber = replenishmentHeaderEntity.initJournalNum();
        AccountNum          accountNum = "1002";
        InventSiteId        inventSiteId = "1";
        InventLocationId    inventLocationId = "11";

        if(InventOwner_RU::exist(accountNum))
        {
            replenishmentHeaderEntity.insert(orderNumber, accountNum, inventSiteId, inventLocationId);
            Info(strFmt('%1', replenishmentHeaderEntity.ReplenishmentOrderNumber));
        }
        else
        {
            Info('This vendor account is not inventory owner');
        }

        replenishmentLinesEntity.initFromHeader(ConsignmentReplenishmentOrderHeader::find(orderNumber),200,"IB-TestProduct2");
        
    }

}
  1. Set this runnable class as Startup Object and execute project by clicking Start button

Conclusion: 

In this Technical Blog Post Optimizing Inventory Consignment Process with Microsoft Dynamics 365, we have:

  1. Covered the Setup of a released product using X++ code in Dynamics 365 F&S
  2. Discussed the structure of tables and filled information in released Product Creation Process
  3. Covered setup and creation of an Inventory owner using X++ code in Dynamics 365 F&S
  4. Discussed table structure from Entity Relationship Diagram involved in inventory owner creation
  5. Covered creation of Inventory consignment journal using X++ code
  6. Discussed table structure from entity relationship diagram used in consignment journal creation process

In addition, we sincerely hope that you enjoyed the technical blog post on Optimizing Inventory Consignment Process with Microsoft Dynamics 365. We aim to provide quality service at all times. Moreover, if you have any queries, you know how to get in touch by reaching out to me here.  – Brandon Ahmad, founder of Instructor Brandon and Dynatuners.

If you wish to see more from our series, you can visit our website Instructor Brandon for more information.

As always, we are available to help guide you on your journey through our blogs. If you wish to get in touch, leave us a comment in the section below.

Videos

Leave a Reply

Your email address will not be published. Required fields are marked *