Upgrading code that relies on available and reserved inventory quantities in AX 2012 R3
Dynamics AX 2012 R3 features a new warehouse management solution. An essential part of this solution is the completely new reservation engine that was designed to separate order reservations from the reservations related to warehouse work. This allows the movement of goods within the warehouse even if they are already reserved. As a result, some of the quantity fields in the InventSum table may now produce incorrect results. Let me illustrate the problem with an example before we discuss the correct APIs for getting the available and reserved inventory quantities.
Why using available and reserved inventory quantities from the InventSum table directly is not valid for items that are using warehouse management processes
Imagine the following scenario
– There is an item that uses warehouse management processes
– The following physical inventory exists for the item (some dimensions like site, inventory status, license plate are irrelevant and are omitted throughout the scenario):
Warehouse: W1, Location: L1, Physical inventory: 6
– A sales order with one line is created and reserved:
Warehouse: W1, Quantity: 5
– The sales order is released to warehouse. As a result warehouse work is created:
Work type: Pick, Warehouse: W1, Location: L1, Quantity: 5
Work type: Put, Warehouse: W1, Location: Baydoor, Quantity: 5
– Now we have the following inventory transactions:
Reference |
Warehouse |
Location |
Status issue |
Quantity |
Sales line |
W1 |
Reserved physical |
-5 |
|
Pick work |
W1 |
L1 |
Reserved physical |
-5 |
Put work |
W1 |
Baydoor |
Ordered |
+5 |
– So once we roll up the transactions into the InventSum table the contents of the table will look like this:
Warehouse |
Location |
Physical inventory |
Reserved physical |
Available physical |
Ordered |
W1 |
0 |
5 |
-5 |
0 |
|
W1 |
L1 |
6 |
5 |
1 |
0 |
W1 |
Baydoor |
0 |
0 |
0 |
5 |
– And now let’s calculate on hand quantities for warehouse W1 like we used to do it in versions prior to AX 2012 R3 (sum up InventSum quantities for warehouse W1):
Warehouse |
Physical inventory |
Reserved physical |
Available physical |
Ordered |
W1 |
6 |
10 |
-4 |
5 |
At this point you will most likely exclaim: “Wait a second! This makes no sense! We just wanted to ship 5 items and we have 6 items on stock. One item should still be available! And how did we manage to physically reserve 10 items when we only have 6 items in the warehouse!”
And this is exactly the reason why summing up available and reserved quantities from the InventSum table may produce incorrect results for items that use warehouse management processes.
But this still doesn’t explain how we managed to physically reserve 5 items for the sales and another 5 items for the pick work when we only had 6 items on stock. The reason is that the sales reservation was taken on the warehouse level of the reservation hierarchy to ensure that we keep enough on hand in the warehouse to ship the reserved quantity. The pick work reservation was taken on the location level and it ensures that the reserved quantity will still be physically available when the warehouse worker comes to the warehouse location to pick it. Work reservation does not affect physical availability on the warehouse level. This means that the new reservation engine will be able to figure out that we still have one item available on the warehouse level.
How reservations affect available quantities on lower levels of the reservation hierarchy
Here is another important example that demonstrates how reservations that are done on the warehouse level affect available quantities on the location level:
– There is an item that uses warehouse management processes
– The following inventory transactions exist for the item
Reference |
Warehouse |
Location |
Status issue |
Status receipt |
Quantity |
Purchase line |
W1 |
L1 |
Purchased |
+10 |
|
Purchase line |
W1 |
L2 |
Purchased |
+20 |
|
Sales line |
W1 |
Reserved physical |
-25 |
||
Pick work |
W1 |
L1 |
Reserved physical |
-10 |
|
Put work |
W1 |
Baydoor |
Ordered |
+10 |
– After the transactions are aggregated the InventSum table contains the following information
Warehouse |
Location |
Physical inventory |
Reserved physical |
Available physical |
Ordered |
W1 |
0 |
25 |
-25 |
0 |
|
W1 |
L1 |
10 |
10 |
0 |
0 |
W1 |
L2 |
20 |
0 |
20 |
0 |
W1 |
Baydoor |
0 |
0 |
0 |
10 |
Now if you look at the available quantity for location L2 you will most likely ask: “But how is it possible that 20 items are available on location L2 when we only have 30 in the warehouse and 25 are already reserved? Only 5 items can possibly be reserved (e.g. for sales) on L2!”
The new reservation engine takes care of the issue. For each of the levels of the reservation hierarchy it stores available and reserved quantities in the WHSInventReserve table which allows the engine to calculate availability correctly.
Which InventSum fields and methods should not be used directly?
So now that it’s clear why using the InventSum table directly is incorrect it’s time to describe the correct approach. To work with available quantities regardless of whether the item is using warehouse management processes or not the InventIAvailability interface was introduced.
The following list contains the InventSum fields and methods that should not be used directly and the corresponding InventIAvailability methods that should be used instead:
Obsolete field/method |
Corresponding InventIAvailability method |
InventSum.AvailOrdered |
InventIAvailability.availTotal() |
InventSum.availOrderedCalculated() |
InventIAvailability.availTotal() |
InventSum.AvailPhysical |
InventIAvailability.availPhysical() |
InventSum.availPhysicalCalculated() |
InventIAvailability.availPhysical() |
InventSum.availReservation() |
InventIAvailability.availReservation() |
InventSum.Ordered |
InventIAvailability.ordered() |
InventSum.orderedSum() |
InventIAvailability.orderedSum() |
InventSum.ReservOrdered |
InventIAvailability.reservOrdered() |
InventSum.ReservPhysical |
InventIAvailability.reservPhysical() |
To support warehouse management processes you should remove any references to the fields and methods described above and replace them with appropriate calls to InventIAvailability.
Two major APIs were introduced for retrieving item availability:
– InventAvailabilityProvider
– InventAvailabilityFormCache
InventAvailabilityProvider
The InventAvailabilityProvider class provides availability information for on hand inventory based on various criteria. It can be used in business logic, e.g. if you need to check whether there is enough on hand available before you can proceed with an inventory operation. Following is an example of using the API:
availPhysical = InventAvailabilityProvider::findByItemDim(inventTable, inventDim).parmInventAvailability().availPhysical();
You can find more information in the XML documentation of the InventAvailabilityProvider class.
InventAvailabilityFormCache
In scenarios where you want to display available quantities on a form you can use the InventAvailabilityFormCache class. It is responsible for retrieving and caching available on hand quantities per item per inventory dimension combination. To use it on a form you need to:
– Declare an InventAvailabilityFormCache field in classDeclaration of the form.
– Initialize the cache field in the init method of the form:
public void init()
{
super();
inventAvailabilityFormCache = InventAvailabilityFormCache::construct();
}
– Flush the cache in the executeQuery method of the primary data source that the InventSum data source is joined to:
public void executeQuery()
{
inventAvailabilityFormCache.refreshCache();
super();
}
– Create display methods for available on hand fields that need to be displayed on the form.
If InventDim is joined to InventSum then the display methods should be declared on the InventSum data source:
public display InventQtyReservPhysical reservPhysical(InventSum _inventSum)
{
return inventAvailabilityFormCache.availabilityFromSumJoinedWithDim(_inventSum, inventDim_ds).reservPhysical();
}
Note that the availabilityFromSumJoinedWithDim method should be used.
Otherwise, if InventSum is joined to InventDim then the display method should be declared on the InventDim data source:
public display InventQtyReservPhysical reservPhysical(InventDim _inventDim)
{
return inventAvailabilityFormCache.availabilityFromDimJoinedWithSum(_inventDim, inventSum_ds).reservPhysical();
}
Note that the availabilityFromDimJoinedWithSum method should be used.
For an example in R3 please see the InventOnhandItem form.
Is code upgrade required for references to the InventOnHandQty class?
The InventOnHandQty class is one of the most frequently used APIs for retrieving on hand information in business logic. It has been modified to support warehouse management processes and no code upgrade is required for code that references the InventOnHandQty class.
Is code upgrade required if the solution does not need to support warehouse management processes?
The standard reservation engine is used for items that do not use warehouse management processes. Thus referring to InventSum directly will produce correct results if your solution does not need to support warehouse management processes (Warehouse and Transporation management configuration key under Trade is turned off). So in this case upgrading code as described above can be deferred as long as none of the AX installations with your solution use warehouse management processes.
Is code upgrade required for references to the AxdInventoryOnhand AIF service?
The service has been updated to provide correct results for items that use warehouse management processes. However you need to keep in mind that summing up available or reserved quantities that were retrieved through the service is not correct (see the first scenario in this blog post).
How to upgrade code that is using direct SQL for getting item availability?
If your solution refers to the available or reserved quantity fields from the InventSum table using direct SQL then you will need to implement the same algorithm that is implemented in the InventAvailabilityProvider class:
– If the item does not use warehouse management processes then get the total quantities from the InventSum table as before.
– If the item uses warehouse management processes then you can use the sp_WHSOnHand stored procedure to get AvailPhysical and AvailOrdered quantities.
– If the item uses warehouse management processes then you can take ReservPhysical and ReservOrdered quantities from the WHSInventReserve table. See the implementation in the WHSInventReserveQty class.