Continued from part 2

In parts 1 and 2 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.

The following table summarizes the changes in the inventTrans at various stages of the purchase order line.

field	create orderline	post confirmation	post arrival journal	post product receipt	post invoice
StatusReceipt	Ordered	Ordered	Registered	Received	Purchased
ItemId	ZF123	ZF123	ZF123	ZF123	ZF123
Qty	3	3	3	3	3
InventDimId	DIM00000001	DIM00000001	DIM00000001	DIM00000001	DIM00000001
InventTransOrigin	16984987792	16984987792	16984987792	16984987792	16984987792
Date expected	1-4-2015	1-4-2015	1-4-2015	1-4-2015	1-4-2015
Date status	1-4-2015	1-4-2015	7-4-2015	7-4-2015	7-4-2015
Date inventory				8-4-2015	8-4-2015
Date Physical				8-4-2015	8-4-2015
Date Financial					10-4-2015
CostAmountPhysical				274,62	274,62
CostAmountPost					205,96
CostAmountStd					205,96
PackingSlipId				PS001	PS001
InvoiceId					PI001
Voucher					GRB-15595
VoucherPhysical				ENG-0018171	ENG-0018171
CurrencyCode					EUR

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.

Relations

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

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 an 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 sized in megabytes, 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.

I can only hope there is more to it than that, but to be honest… I just don’t see it. It is rumored that Microsoft wants to depreciate the inventTransId field all together. Personally I think it’s an unholy mission, but I’m sure they have their reasons.

Why two cross-reference tables?

Don’t ask. The best I can come up with is that it’s some intermediate solution for a future SaaS oriented design that is already in the planning. That is nothing more than an uneducated guess. I simply fail to see the point.

It is what it is and we’ll have to deal with it. For example when we split a purchase order line.

Splitting it up.

What happens when you receive a partial order line?

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

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 quantity on the purchase order line, where the quantity is the sum of the quantity ordered and the delivered quantity.

In the snapshot below, you see an example of a purchase order line that had two partial receipts and a quantity remaining to be delivered.

Note that all three lines have the same inventTransOrigin reference. Our inventory lot now consists of two lines (the ones with a packing slip ID) and an expected remainder. Had we used batch number as an inventory dimension then the inventDimId would have been different on all three lines.

Really the only thing distinguishing the first two lines (the receipts) is the posting date and the packing slip ID. If the AX designers truly wanted to normalize the inventTrans then on might wonder why they don’t combine these lines and register the difference in the VendPackingSlipTrans. Remember that we started out by saying that the InventTrans is in essence a ledger, and a ledger contains a line for every transaction/mutation.

Unfortunately this comparison doesn’t hold.

Canceled quantity

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.

Benedikt Kotruljevic spins in his grave. Deleting a line in the ledger is of course not done.

When a single order line is canceled completely (before any receipts), then the corresponding records in inventTransOrigin, inventTransOriginPurch, and inventTrans are deleted completely. 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.