·
6 min read

The InventTrans table. Explore various field usages.

Introduction

No doubt, the InventTrans table is one of the most important tables in the inventory engine within Microsoft Dynamics AX 2009. It stores inventory transactions, which include a lot of the business data that is crucial to the functionality like reservations, on-hand quantities, inventory value calculations, and so forth.

There are number of different fields in InventTrans that contain information about:

  1. Original source documents like purchase order or sales order lines – their states, items, quantities, document numbers, and so forth.
  2. Time and place differences when business events such as physical or financial update have occurred.
  3. Reference data which is important to different modules like Fixed Assets, Project Accounting.
  4. And much more …

Today we want to focus on group #3 and explore how the certain fields on InventTrans are used, and what are their primary purposes. Let’s take a look at:

  • Direction
  • ItemBOMId and ItemRouteId
  • AssetId and AssetBookId

The Direction field is used to group receipts and issues and it’s important to have this field set properly. Otherwise, consider using consistency checks to fix corrupted data.

The ItemBomId and ItemRouteId fields play a key role in integration with master planning to cover expected demand properly.

The AssetId and AssetBookId fields enable seamless integration with The Fixed Assets module and help to establish the link between General Ledger and Inventory modules.

Direction field

This field specifies whether the transaction line is an issue or receipt transaction.

clip_image001[4]

Conceptually, it can always be derived from the transaction quantity, and is determined with the following logic:

clip_image003[4]

You should not be able to find any transaction within your database where the Direction field is set to InventDirection::None. This would indicate that you have corrupted transactions, as the inventory engine classes which deal with the Invent Trans table make sure that no inventory transaction exists for order lines where the quantity is not set. As a simple example, if you create a sales order line with a quantity of zero, there will be no inventory transaction for this order line.

The InventTrans.Direction field is used in various places, for instance from Inventory Marking form to group issues and receipts. So it’s important to have this field set correctly, so that the system can report correct transaction quantities and the user can mark against correct lots.

Use the standard consistency checks to correct inventory transactions and the Direction field as well. The Direction field value will be set properly during the consistency check job. Navigate to Basic > Periodic > Consistency check. Select the Inventory management module, and then click the Item check box and the Inventory transactions check box. Then, execute the consistency check in fix error mode by selecting Fix error in the Check/Fix field to correct inventory transactions.

clip_image005[4]

ItemBOMId and ItemRouteId fields

These fields carry order line BOM and Route information which will be used to produce the expected item on the transaction line. Imagine a scenario where the Product Builder feature is used by a sales agent while creating sales order lines for a configurable item. The product builder feature lets you define the product model based on the customer requirements, and creates a new BOM structure and route to produce the item.

After the item has been configured with Product Builder, the BomId and RouteId information will be copied to the sales order line. The operator can always modify and preview the sub-bom and sub-route value for the selected sales order line on the main Sales Order form. Select the Lines grid > Other tab > BOM\route field group.

clip_image002

From the sales order line, select Inventory > Transaction to preview inventory transactions for the sales order lines and verify that a Sub-BOM and Subroute has been applied for the inventory transaction as well.

clip_image004

The InventTrans.itemBOMId and itemRouteId fields are used by master planning to fulfill the expected demand for the current item BOM structure. This creates a plan to produce or purchase all the components required to create the ordered goods within the expected lead time. The following code logic initializes data needed for the master planning run based on the existing inventory transactions.

clip_image006

You can simulate and explore master planning from the sales order line by navigating to Inquiries -> Net requirements. Note, the BOM structure and correct route will be used for the master planning engine only if the coverage group has been set correctly in Master planning > Setup > Coverage > Coverage groups form.

Please ensure that the Use BOM version requirement and Use Route version requirement has been selected for master planning on the Coverage groups form. If these are not set, production orders and derived requirements will not use the sub BOM and route, but instead will use the active BOM and Route for the issue transaction.

The most important consideration about Sub BOM and Route is that the values are not a part of the on hand information.

So even if an item is produced using a specific BOM or Route, the on hand information will show that one piece of the item is on hand, not any information about the BOM or Route.

If the specific BOM or route needs to be tracked as on hand information, please use one of the item dimensions for that. The item configuration dimension is a good candidate for this purpose. The BOM and Route can be automatically selected based on any item dimension, and this is fully integrated in master planning throughout production transfer purchase and on hand information.

image

AssetID and AssetBookId fields

Assume a company purchases an item which must became a part of the company’s fixed assets. Set asset fields on the original source documents to inform the inventory engine that the product receipt should be converted to a fixed asset, rather than being tracked and stocked in the warehouse.

There are two common ways to move inventory items to the fixed assets module in Microsoft Dynamics AX. First, we can purchase the items and set fixed asset fields on the purchase order line. Or we can post the special inventory journal to “convert” available inventory to fixed assets. The journal is available from the General Ledger Area Page > Journals > Inventory to Fixed Assets.

Let’s walk through the purchase order line life cycle as a simplified example. Assume that you have a purchase order line like the one in the screen shot and you purchase a quantity of a product. You want these products to be posted as fixed assets when they arrive.

clip_image001[6]

The asset fields can be set on the Fixed assets tab for the purchase order line grid. You select the Value model of MOULD and associate the Fixed Asset Number of Asset-0001. This will create the asset in the fixed asset list with the posting of the packing slip or invoice. If selected, enter the asset group for the new asset. Select the fixed asset transaction type to be used when the asset is posted.

clip_image002

When you press (Crtl+S) to save the purchase order line, the system:

  • Creates an instance of InventMov_Purch class. This is a child type of general InventMovement class which “wraps” original source document line (purchase order line) and will be processed further by inventory engines (InventUpdate classes) to generate expected receipt transactions.
  • Processes the movement using the InventUpdate_Estimated class and generates inventory transactions.
  • Sets the Fixed Asset field values on the purchase line and on the movement, so the “link” is established between inventory transactions and general ledger assets.

clip_image004[9]

The AssetInventTrans table keeps these relationships. In our case, the link is stored between the specified purchase line asset (Module, Asset-0001) and the newly created inventory lot value (InventTransId=00255_059)

clip_image005

After the financial update of the purchase order (invoice posting) is executed, the purchased items won’t be accounted for physically in the on-hand inventory, as they are posted as a fixed asset for the company. The inventory levels remain as they were before the original purchase order was placed in the system. The system will generate a special “system” issue transaction (TransType = FixedAsset) to settle the overall order line quantity to zero.

clip_image007

The new virtual movement (InventMov_Virtuel) was created and processed during financial update for purchase order to populate issue transactions. The movement was initialized in the InventMov_Virtuel > newAutoLossAsset method like:

clip_image009

The transactions with the “Fixed Asset” type can’t be previewed from purchase order line, as they have their own inventory lots. They refer to virtual movements system perform to settle the original purchase order quantity and they have nothing to do with the purchase order line.

You can always preview those transactions from the General Ledger module, which keeps tracks of company’s fixed assets and any movements associated to them.

You can navigate to General Ledger Area page > Places > Fixed Assets list page. Select the any fixed asset record on the main grid, and then navigate to Value Models > Transactions > Inventory Transactions. From this overview, you can find the original purchase order receipt (Receipts = Purchased) and corresponding fixed asset “auto loss” product issue record (Issue = Sold).

clip_image011

By Ievgenii Korovin, Inventory Management, Microsoft Dynamics AX.