Tuesday, August 11, 2015

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

I thought about naming this post ‘InventTrans for Dummies’, but we have to consider the source. We all know that the beating heart of the AX in- and outbound process is the inventtrans table. In essence this table is a ledger of any and all inventory movements and as such it is the ultimate source for answers to inevitable questions about changes to inventory cost, inventory value and inventory levels.

The table itself is no great mystery, but considering how crucial it is for the working of your organization and AX in general, it is a shame that there is no decent documentation for it to be found.

The InventTrans has undergone some significant changes from AX 2012 up. I won’t go into that, but be aware that this does not apply to older versions of AX.

The InventTrans is an inventory ledger, so incoming lines (e.g. purchase order lines) have a positive quantity, and outgoing lines (e.g. sales order lines) have a negative quantity.

Incoming lines have a receipt status (field StatusReceipt), while outgoing lines have an issue status (Field StatusIssue).

Receipt statuses:

  • Purchased (posted financially, i.e. ‘bought and paid for’)
  • Received (posted physically, i.e. ‘we got the goods’)
  • Registered (registered receipt, i.e. ‘signed for the parcel’)
  • Arrived
  • Ordered (purchase order line created)

Issue statuses:

  • Sold (posted invoice)
  • Deducted (posted packing slip, i.e. ‘signed, sealed, delivered’)
  • Picked (registered pick list, i.e. ‘ready to ship’)
  • ReservPhysical (earmarked items in warehouse)
  • ReservOrdered (marked items expected to arrive in the foreseeable future)
  • OnOrder (sales order line created)

Some basic rules for messing with data in the InventTrans:

  1. Don’t!
  2. Never-ever-ever-ever-EVER delete a line in InventTrans (unless you are absolutely sure)
  3. Always recalculate InventSum after you make changes to inventTrans
  4. Try to recreate the data from normal AX functionality. For example, cancel the deliver remainder on a sales order, then change it back to the original value. This will ‘reset’ your inventtrans for that line.

Purchase order

When we confirm a purchase order, we create a new record in InventTrans. Initially this record contains:

  • Date expected (expected date of inventory change for planning purposes)
  • Date status (not what you, or rather I, would think)
  • InventDimId (Order receipt location)
  • InventTransOrigin (RecId from table InventTransOrigin)
  • ItemId
  • Qty
  • StatusReceipt-Ordered (PO = receipt in inventory, current status is ordered)

This all makes perfect sense, doesn’t it? It is the short version of our purchase order line. From it we can see that on date x, we will receive quantity y of item z on location …what comes after z?

This record will accompany your purchase order line for the rest of its natural life and beyond. Yes, there is a one on one relationship…unless:

  1. You cancel the purchase order line.
    Note that there is no receipt status ‘canceled’ or anything similar. That’s because inventory doesn’t care why or how. If the goods don’t show, we don’t want to know. Canceling the (qty on the) purchase order line) will delete the inventtrans record.
  2. You split the purchase order line.
    ”But, but, I didn’t split the line. Why would I split the line?”
    You did when your supplier decided to send you only half the quantity you ordered. You did when you decided to work with an arrival schedule. You did when you rejected part of your delivery. Whenever the quantity of your order line is split two ways, there is an additional record created in InventTrans.

The InventTransOrigin refers (through a cross-reference on the InventTransOrigin table) to the purchase line InventTransId. Therefore all inventTrans records with the same InventTransOrigin tell the whole story of (for example) a purchase order line. The sum of the quantities for these records should match the quantity on the purchase order line.

When we (using WMS) post the arrival journal of this purchase order line, the following fields are added to the record:

  • Date inventory (receipt date)
  • MarkingRefInventTransOrigin (what other inventTrans record put dibs on this line?)

Further more the date status changes and the receipt status is bumped up to ‘registered’.

Did we skip the ‘Arrived’ stage? Yes we did. Unless you are running a major WMS controlled logistics operation, you probably won’t bother with this. If you are, you might not want to bother with AX (although I have heard good things about R3 on this matter).

When we post the purchase order receipt, the following fields are added to the inventtrans record:

  • CostAmountPhysical
  • DatePhysical
  • PackingSlipId
  • VoucherPhysical

It is clear that this is the posting of the physical receipt. In addition the date status field changes and the receipt status kicks it up a nudge to ‘received’.

Finally, we can post the invoice for the purchase order line. This adds the final fields to the inventtrans record:

  • CostAmountPost
  • CostAmountStd
  • CurrencyCode (Am I the only one who finds it odd that costs require a currency, but value does not?)
  • DateFinancial
  • InvoiceId
  • Voucher

The status issue goes to ‘Purchased’ and yes, the DateStatus is set to the same value as DateFinancial.

So en résumé:

Field

Create orderline

Post confirmation

Post receipts list

Post arrival journal

Post product receipt

Post invoice

StatusReceipt Ordered Ordered Ordered Registered Received Purchased
ItemId ZF123 ZF123 ZF123 ZF123 ZF123 ZF123
Qty 3 3 3 3 3 3
InventDimId

DIM00000001

DIM00000001

DIM00000001

DIM00000001

DIM00000001

DIM00000001

InventTransOrigin 16984987792 16984987792 16984987792 16984987792 16984987792 16984987792
Date expected 1-4-2015 1-4-2015 1-4-2015 1-4-2015 1-4-2015 1-4-2015
Date status 1-4-2015 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

(to be continued)