Friday, September 4, 2015

Digging up the Dirt. Inside the InventTrans (Part 2).

In part 1 of this series on the InventTrans, I explained how a purchase order line mutates in the InventTrans table during the various stages of its life.

We are going to stick with the purchase order line for a while longer. All inventory transactions type are the same in general, so anything that applies to the PurchaseLine can be applied to the SalesLine (for example) with some minor changes and common sense.

Now first, let’s take a look at what ties the InventTrans and the PurchLine together. The answer is simple…nothing.

inventtrans

With the introduction of AX6 (2012) and the mysterious data model that came with it, the PurchLine still has a field named InventTransId, but the InventTrans table no longer has a InventTransId. Makes perfect sense, doesn’t it?

There are two tables that bind the transaction table to the order table. These are the InventTransOrigin and the InventTransOriginPurchLine. Both provide a cross-reference between the InventTransId (sometimes referred to as LOT-ID) from the PurchLine, and the InventTransOrigin (which is actually the RecId of the InventTransOrigin table) from the InventTrans.

Are you still with me?

PurchLine is governed by the InventTransId, while InventTrans is governed by the InventTransOrigin. InventTransOrigin and InventTransOriginPurchLine live in both realms and provide the link between the other two tables. Both InventTransOrigin and InventTransOriginPurchLine are required for successful processing of the various steps in the purchase order line life.

Why a cross-reference table?

Well children, a long long time ago, when computer disks were expensive and measured by the megabyte, it was believed that data in a database should be normalized (only stored in one place) to save as much precious disk space as possible. Apparently MS brought out the dinosaurs when it was time to assign the engineering teams for AX6. From what I understand many of these engineering choices have been reversed in R3 after MS killed the dinosaurs (Boom, boom, shakalaka-laka-boom).

Why two cross-reference tables?

Apparently the dinosaurs in Redmond also had some special brothers and sisters who were not only allowed to color the Microsoft logo, but also had a vote in the design process. There are others who say that it was done for performance reasons, but they obviously never worked with AX6.

Splitting it up.

What happens when you receive a partial order line?

Simple, AX splits up your InventTrans record in the part that you register and the part that remains to be received.

So now we have two records in the inventtrans, both with the same InventTransOriginId, one with PurchStatus ‘registered’ and one with PurchStatus ‘Ordered’. In the other three tables from the picture above we still only have one record (each).

This splitting of the InventTrans record can happen as many times as is required. The sum of all the quantities in the inventTrans with the same InventTransOriginId should always be equal to the total number of transactions on the purchase order line.

When the quantity on the purchase order line is changed, the quantity on the ‘Ordered’ line in InventTrans changes too. When the quantity of an inventTrans record reaches zero (cancel deliver remainder on the PurchLine), then the record is deleted along with its kin in the the InventTransOrigin and the InventTransOriginPurchLine.

Therefore not every PurchLine record has a corresponding record in the InventTrans. Come to think of it, you will (obviously) not find any inventory transactions for purchase lines belonging to an order of type journal either. ‘t is good to know.