The InterRadish Blog

Warehouse-specific inventory transactions: Practical Example

feature review technical Jul 22, 2024

Warehouse-specific inventory transactions are Microsoft's newest mechanism for decoupling warehouse operations from standard inventory tracking. Our previous post covered the data structure, the reasoning behind it, and how Microsoft accomplished this decoupling.

Make sure to give it a read here first: Warehouse-specific inventory transactions

This blog is designed to teach partners and clients with D365 WMS experience how to evaluate and transform an X++ modification to account for the new Warehouse-specific inventory transactions structure through an example we encountered in our most recent project to uptake of this new functionality. It is designed for a more technical audience that is knowledgeable about how the standard D365 WMS tables relate to each other.

We hope this example will show the work involved in implementing the new functionality and provide a potential approach to how to undertake your own conversion to Warehouse-specific inventory transactions.

Client's modification

Our client extended the Work form for the work trans type of "Sales orders" with the following functionality:

Visualization of the Batch ID picked for each work line, when the item being picked is "batch below".
- If more than one batch is used to pick a work line, the visualization should be blank.
Visualization of the Batch Expiration Date for batches picked for a work header.
- This should only contain a value if all batches picked on initial pick work lines (meaning before the first put) have the same expiration date.

Business Assumptions driving Modification Scope

The client only ships "batch below" items, so the only way systematically to determine what has been picked is via the transactions created to record the picked quantities and dimensions. If the items were "batch above" the batches information would already be on the work line.

Initial X++ Code

For the first requirement (Batch ID) the following selects were placed into a display method on the WHSWorkLine table to retrieve the necessary data:

InventDim           inventDim;
InventTrans         inventTrans;
InventTransOrigin   inventTransOrigin;
WHSWorkInventTrans  workInventTrans;

select firstonly InventTransIdFrom from workInventTrans
    where workInventTrans.WorkId == this.WorkId
        && workInventTrans.LineNum == this.LineNum;

select firstonly InventBatchId from inventDim
    exists join inventTrans
        where inventTrans.InventDimId == inventDim.InventDimId
            && inventTrans.ItemId == this.ItemId
        exists join inventTransOrigin
            where inventTransOrigin.RecId == inventTrans.InventTransOrigin
                && inventTransOrigin.InventTransId == workInventTrans.InventTransIdFrom;

return inventDim.InventBatchId;

The second requirement (Batch Expiration Date) was developed in a display method on the WHSWorkTable table to retrieve the necessary data:

WHSWorkLine         workLine;
WHSWorkType         workTypePrev = WHSWorkType::None;
InventBatchExpDate  expDateCur, expDatePrev;

while select workLine
    order by workLine.LineNum asc
    where workLine.WorkId == _workId
    {
        InventDim               inventDim;
        InventBatch             inventBatch;
        WHSWorkInventTrans      workInventTrans;
        InventTransOrigin       inventTransOrigin;
        InventTrans             inventTrans;
                
        // are we done with initial pick lines?
        if (workTypePrev == WHSWorkType::Pick && workLine.WorkType != WHSWorkType::Pick)
        {
            break;
        }

        workTypePrev = workLine.WorkType;

        select firstonly InventTransIdFrom from workInventTrans
            where workInventTrans.WorkId == workLine.WorkId
                && workInventTrans.LineNum == workLine.LineNum;

        select firstonly expDate from inventBatch
            where inventBatch.itemId == workLine.ItemId
            exists join inventDim
                where inventDim.inventBatchId == inventBatch.inventBatchId
                exists join inventTrans
                    where inventTrans.InventDimId == inventDim.InventDimId
                        && inventTrans.ItemId == workLine.ItemId
                    exists join inventTransOrigin
                        where inventTransOrigin.RecId == inventTrans.InventTransOrigin
                            && inventTransOrigin.InventTransId == workInventTrans.InventTransIdFrom;

        if(expDatePrev == dateNull())
        {
            expDatePrev = inventBatch.expDate;
            expDateCur = inventBatch.expDate;
        }
        else
        {
            expDatePrev = expDateCur;
            expDateCur = inventBatch.expDate;
        }

        if (expDatePrev != expDateCur)
        {
            expDateCur = dateNull();
            break;
        }
    }

return expDateCur;

These changes have been proven to work and behave performantly for the client for several years, so extra care must be taken to prevent any disruption of existing functionality and the associated business processes.

Approach

Each modification should be evaluated to determine if the uptake of Warehouse-specific inventory transactions will impact the functionality. In the two modifications outlined in the Client's modification section we identified that they needed to be refactored by seeing this table relationship in the select statements:

The above diagram is made using crows-foot notation for data modeling.

When navigating from the WHSWorkInventTrans table to the InventTransOrigin or visa-versa, the InventTransIdFrom value will no longer be populated when Warehouse-specific inventory transactions are enabled.

Instead, the WHSInventoryTransaction and WHSInventoryItemSetElement table structures related to the WHSWorkInventTrans will manage this data. This is pictured below. After enabling Warehouse-specific inventory transactions for that work type, legacy relations are not created for new work table records.

Our previous post (referenced here) covers how these new tables are created and maintained through work execution. Please refer to that post if you need a deeper dive. This guide only mentions the relations and table names so that the subsequent code will make sense.

In addition to understanding the data structure, the following sections outline the considerations that need to be made for each modification and how they are applied to the practical examples outlined above in the Client's Modification section.

Database Retrieval Rules

Although the tables above are the tables to be joined to, in code, they are marked as internal, meaning that only Microsoft can access these tables directly (so no select statements over the tables are possible):

Properties above are from the WHSInventoryItemSetElement table.

Instead, Microsoft has presented several options for interacting with the records in these new tables:

Option 1: Utilize new Class/API: WHSWorkInventoryTransactionQuery

Use the new class (also called API by the product group) WHSWorkInventoryTransactionQuery. This API takes in the Work ID for the desired work header, and then a request for the issue or receipt WHSInventoryTransaction details can be retrieved.

There are only a few examples in the base functionality where this new class is utilized, but this example outlines one of the options:

This code is found on the WHSWorkLine table and has the following flow:

Check if the work header uses Warehouse-specific inventory transactions
- If yes:
  - Retrieve the first RegisteredIssue WHSInventoryTransaction detail with the associated WHSInventoryItemSetElement
    - By passing the TransactionQueryParameters as they are in the above screenshot, this query ignores any archived WHSInventoryTransaction records.
  - Get the associated InventDim record for further use.
- If no, handle this by utilizing the original lookups.

This is a good option when only a single transaction is required; we can easily use this for the first requirement listed in the Client's modification section.

However, this does not provide any complex query logic. This class only allows for querying the Warehouse-specific inventory transactions for a single work line, which means cross-work requirements such as the second requirement in the Client's modification section are dependent on the size of the work to determine performance.

The client routinely would create ~50 initial pick work lines, which means that using WHSWorkInventoryTransactionQuery would need to make 50 database queries to determine if all lines have the same expiration date. This was deemed non-performant by the developer working on this client modification and option 2 below was utilized instead.

Option 2: Query the WHSInventoryTransactionView

The WHSInventoryTransactionView is a publicly accessible view that can be used to create more extensive queries. It sits behind the WHSWorkInventoryTransactionQuery and is utilized by Microsoft to interact with the new table structure for Warehouse-specific inventory transactions.

This view is created from the WHSinventoryTransactionQuery. This query has the following data structures behind it:

WHSInventoryTransactionExpandedView
- This view contains the WHSInventoryTransaction and WHSInventoryItemSetElement tables.
WHSInventoryTransactionArchiveView
- This view contains the WHSInventoryTransactionArchive table.

This view can be queried via any standard X++ code offerings. The data relations between this view and WHSWorkInventTrans are the same as the relation to WHSInventoryTransaction. So that can be used as a guidepost for interacting with this view.

Understanding Enablement

Warehouse-specific inventory transactions are enabled per Warehouse scenario on the Warehouse management parameters form:

The Warehouse scenario types correspond to the Order type found on the work header. Enabling these flags will only apply Warehouse-specific inventory transactions to work created after enablement. No updates are made to already existing work. These flags, as of 16/07/2024, can be disabled by customers if needed, meaning the legacy structure can be used.

In release 10.0.41 (GA September 2024), the old inventory transactions table will be marked as deprecated. Existing customers will have a one-year grace period (September 2025). At the end of the grace period, everyone will be forced to switch to the new Warehouse-specific inventory transactions.

Based on this understanding of enablement, each modification should be reviewed if it needs to be account for both types of transactions and how the transactions will be enabled across legal entities for testing and review. It is very likely there will be a period of time where both Warehouse-specific inventory transactions and inventory transactions will need to be supported in code.

This was the case for this client, we ended up supporting both code paths because:

They have multiple legal entities that required a slower ramp out of the new transactions to be successful.
The modifications were display methods, so they could be viewed from work headers that were older then the enablement date and still need to see the requested data.

By supporting both code paths, there will be an eventual effort once all legal entities switch over to remove the legacy code as it will no longer be accessible.

Understanding Archiving

Unlike inventory transactions, Warehouse-specific inventory transactions can be regularly archived in another table called WHSInventoryTransactionArchive. With the records potentially being removed from WHSInventoryTransaction and WHSInventoryItemSetElement based on the configuration of the archival job, this may also need to be considered in the modification.

This was not an issue for the referenced client, as the display method data was only needed for a month, but their archival is run on a 3-month timeframe so the archived records can be ignored for the refactor.

Understanding SQL

Although this doesn't change the code path when rolling the new Warehouse-specific inventory transactions out to production, it is possible that SQL will begin recommending new indexes as the old InventTrans tables will no longer be hit nearly as often, nor from the same criteria as they are today. This may cause short-term production volatility that should be monitored when going live. This should not prevent the enablement of Warehouse-specific inventory transactions but should be closely monitored when enabled in production instances.

Code Changes

With the approach to translating Warehouse-specific inventory transactions having been explained, the code can be shared for these modifications.

These changes mirror, for the most part, what was done in the client's code base, but some liberties and simplifications have been taken. The goal is not to outline a solution that will work for any codebase but to provide an idea of what changes can look like and what decisions need to be made each time.

Requirement #1

The following code was implemented:

if (WHSInventoryTransactionConfigurationProvider::shouldUseWarehouseInventoryTransactionStack(this.WorkId))
{
	WHSWorkInventoryTransactionQuery workInventoryTransactionQuery = WHSWorkInventoryTransactionQuery::newFromWorkId(this.WorkId);

	WHSWorkInventoryTransactionDescriptor inventoryTransactionDescriptor = workInventoryTransactionQuery.registeredIssueByWorkLine(
		this.LineNum,
		WHSWorkInventoryTransactionQueryParameters::construct().setFirstOnly(true));

	WHSWorkInventoryTransactionDescriptorElement inventoryTransactionDescriptorElement = inventoryTransactionDescriptor.firstOrDefault();
   
	if (!inventoryTransactionDescriptorElement)
	{
		return ''; // no batch can be found
	}

	return inventoryTransactionDescriptorElement
		.parmInventoryDimensionsDescriptor()
		.parmProductTrackingDimensions()
		.parmInventBatchId();
}

InventDim           inventDim;
InventTrans         inventTrans;
InventTransOrigin   inventTransOrigin;
WHSWorkInventTrans  workInventTrans;

select firstonly InventTransIdFrom from workInventTrans
	where workInventTrans.WorkId == this.WorkId
		&& workInventTrans.LineNum == this.LineNum;

select firstonly InventBatchId from inventDim
	exists join inventTrans
		where inventTrans.InventDimId == inventDim.InventDimId
			&& inventTrans.ItemId == this.ItemId
		exists join inventTransOrigin
			where inventTransOrigin.RecId == inventTrans.InventTransOrigin
				&& inventTransOrigin.InventTransId == workInventTrans.InventTransIdFrom;

return inventDim.InventBatchId;

As you can see, this retains the original code, with an if block to handle new work headers using Warehouse-specific inventory transactions. All these methods exist today, so no additional methods are required to utilize this functionality.

This solution uses the new WHSWorkInventoryTransactionQuery class to retrieve the batch ID., but it's equally viable to query the WHSInventoryTransactionView directly. This would however, require more code to build a query that is close to identical to the one offered by the WHSWorkInventoryTransactionQuery class. However, it would have the advantage of providing a field list in the query, as we only need InventBatchId to be retrieved from the database.

Additionally, in this code we queried for the registered issue WHSInventoryTransaction, but the registered receipt would be equally viable for the sales order work type, so this option should also be considered in another implementation.

Requirement #2

This is a much more complicated requirement since it requires considering data across a work header. We avoided the simple solution of querying the WHSWorkInventoryTransactionQuery for each work line to minimize the number of database calls needed to find the correct data. Instead, a query is built using the SysDa framework over the WHSInventoryTransactionView is utilized.

As previously explained, the customer must show the expiration date if they match for all batches picked initially on a sales order work header. The solution reached by the team that combined the fewest database queries with the best readability was to do the following:

Make a database call for the first batch expiration date that can be found.
Make a second database call to get the first non-matching expiration date.
- If one is found, return dateNull() as the expiration date is not shared by the entire work header.
- If one is not found, return the first expiration date, as they all have the same date.

For the first database request, the WHSWorkInventoryTransactionQuery is acceptable; however, to show the other path, we've utilized SysDa. Which path to take should be decided by the developer tasked with uptaking the new functionality.

The following code was implemented:

if (WHSInventoryTransactionConfigurationProvider::shouldUseWarehouseInventoryTransactionStack(_workId)) // method is static so workId is passed into the method
{
	WHSWorkLineCache workLineCache = WHSWorkLineCache::newFromWorkId(_workId);
	WHSWorkLine firstPickWorkLine = WHSWorkLine::find(_workId, workLineCache.parmFirstPickLineNum());

	// query records
	InventBatch inventBatchToQuery;
	WHSInventoryTransactionView inventoryTransactionView;
	WHSWorkInventTrans workInventTrans;

		SysDaQueryObjectBuilder queryObjectBuilder = SysDaQueryObjectBuilder::from(inventBatchToQuery)
													.firstOnly()
													.select(fieldStr(InventBatch, expDate)) // selects only the expiration date field
														.where(inventBatchToQuery, fieldStr(InventBatch, itemId))
															.isEqualToLiteral(firstPickWorkLine.ItemId)
														.existsJoin(inventoryTransactionView)
															.where(inventoryTransactionView, fieldStr(WHSInventoryTransactionView, InventBatchId))
																.isEqualTo(inventBatchToQuery, fieldStr(InventBatch, inventBatchId))
															.where(inventoryTransactionView, fieldStr(WHSInventoryTransactionView, Type))
																.isEqualToLiteral(WHSInventoryTransactionTypeEnum::RegisteredIssue) // Always use the batch that was picked, but the registered receipt should also work
															.where(inventoryTransactionView, fieldStr(WHSInventoryTransactionView, Archived))
																.isEqualToLiteral(NoYes::No) // the only use case is for non-archived data points. This data is accessible elsewhere after archival
															.existsJoin(workInventTrans)
																.where(workInventTrans, fieldStr(WHSWorkInventTrans, InventoryTransactionOriginId))
																	.isEqualTo(inventoryTransactionView, fieldStr(WHSInventoryTransactionView, InventoryTransactionOriginId))
																.where(workInventTrans, fieldStr(WHSWorkInventTrans, WorkId))
																	.isEqualToLiteral(firstPickWorkLine.WorkId)
																.where(workInventTrans, fieldStr(WHSWorkInventTrans, LineNum))
																	.isEqualToLiteral(firstPickWorkLine.LineNum);


	SysDaSearchObject searchObject = new SysDaSearchObject(queryObjectBuilder.toSysDaQueryObject());
	SysDaSearchStatement statement = new SysDaSearchStatement();

	statement.findNext(searchObject); //adds a value to inventBatchToQuery.ExpDate

	InventBatchExpDate expDate = inventBatchToQuery.expDate;

	if (expDate != dateNull())
	{
		SysDaQueryObjectBuilder expDateTester = SysDaQueryObjectBuilder::from(inventBatchToQuery)
							.firstOnly()
							.select(fieldStr(InventBatch, expDate)) // selects only the expiration date field
								.where(inventBatchToQuery, fieldStr(InventBatch, itemId))
									.isEqualToLiteral(firstPickWorkLine.ItemId)
								.existsJoin(inventoryTransactionView)
									.where(inventoryTransactionView, fieldStr(WHSInventoryTransactionView, InventBatchId))
										.isEqualTo(inventBatchToQuery, fieldStr(InventBatch, inventBatchId))
									.where(inventoryTransactionView, fieldStr(WHSInventoryTransactionView, Type))
										.isEqualToLiteral(WHSInventoryTransactionTypeEnum::RegisteredIssue) // Always use the batch that was picked, but the registered receipt should also work
									.where(inventoryTransactionView, fieldStr(WHSInventoryTransactionView, Archived))
										.isEqualToLiteral(NoYes::No) // the only use case is for non-archived data points. This data is accessible elsewhere after archival
									.existsJoin(workInventTrans)
										.where(workInventTrans, fieldStr(WHSWorkInventTrans, InventoryTransactionOriginId))
											.isEqualTo(inventoryTransactionView, fieldStr(WHSInventoryTransactionView, InventoryTransactionOriginId))
										.where(workInventTrans, fieldStr(WHSWorkInventTrans, WorkId))
											.isEqualToLiteral(firstPickWorkLine.WorkId)
										.where(workInventTrans, fieldStr(WHSWorkInventTrans, LineNum))
											.isEqualToLiteral(firstPickWorkLine.LineNum);

		searchObject = new SysDaSearchObject(expDateTester.toSysDaQueryObject());
		statement = new SysDaSearchStatement();

		statement.findNext(searchObject); //adds a value to inventBatchToQuery.RecId if another record exists

		if (inventBatchToQuery.RecId != 0.0)
		{
			expDate = dateNull();
		}
	}

	return expDate;
}

WHSWorkLine	        workLine;
WHSWorkType         workTypePrev = WHSWorkType::None;
InventBatchExpDate  expDateCur, expDatePrev;

while select workLine
	order by workLine.LineNum asc
	where workLine.WorkId == _workId
{
	InventDim				inventDim;
	InventBatch				inventBatch;
	WHSWorkInventTrans		workInventTrans;
	InventTransOrigin		inventTransOrigin;
	InventTrans				inventTrans;
		
	// are we done with initial pick lines?
	if (workTypePrev == WHSWorkType::Pick
		&& workLine.WorkType != WHSWorkType::Pick)
	{
		break;
	}

	workTypePrev = workLine.WorkType;

	select firstonly InventTransIdFrom from workInventTrans
		where workInventTrans.WorkId == workLine.WorkId
			&& workInventTrans.LineNum == workLine.LineNum;

	select firstonly expDate from inventBatch
		where inventBatch.itemId == workLine.ItemId
		exists join inventDim
			where inventDim.inventBatchId == inventBatch.inventBatchId
			exists join inventTrans
				where inventTrans.InventDimId == inventDim.InventDimId
					&& inventTrans.ItemId == workLine.ItemId
				exists join inventTransOrigin
					where inventTransOrigin.RecId == inventTrans.InventTransOrigin
						&& inventTransOrigin.InventTransId == workInventTrans.InventTransIdFrom;

	if(expDatePrev == dateNull())
	{
		expDatePrev = inventBatch.expDate;
		expDateCur = inventBatch.expDate;
	}
	else
	{
		expDatePrev = expDateCur;
		expDateCur = inventBatch.expDate;
	}

	if (expDatePrev != expDateCur)
	{
		expDateCur = dateNull();
		break;
	}
}

return expDateCur;

In this case, the original code is retained. Using SysDa, imagining this selection criteria logic shared in code elsewhere is easy. For example, the two SysDa queries included in this code example are quite similar and logic could be moved to another method and shared.

Conclusion

Warehouse-specific inventory transactions is coming to GA in 10.0.41 (currently marked as September 2024) and the old InventTrans use will be marked as obsolete. This means that new clients will have it enabled at the start of their implementation, and older customers will officially be in the grace period for enablement.

For clients with extensive code changes, transforming these modifications to Warehouse-specific inventory transactions will require the team responsible to have the appropriate context to approach the transformation to these new transactions. Unfortunately, the process is not as simple as flipping the flags and using the new functionality without some ramifications.

However, we hope that with the help of this practical example and by understanding the new data structure, the code changes can hopefully be as minimal as possible. The performance gains far outweigh the effort to uptake, so we at InterRadish highly encourage teams to start looking into this now as we are quickly approaching the grace period! Of course, if you need help, don't hesitate to reach out to our team.