In Microsoft Dynamics NAV, inventory is kept for items in locations using Item Ledger Entries and Value Entries. On top of this, we can use Stock Keeping Units to have different inventory settings per item, location, and variant.
Let's start by looking at the design patterns of the inventory in Microsoft Dynamics NAV:
Keeping inventory can be extended with the use of warehouse management. This is designed to run on top of the Basic Item Inventory functionality.
The item table hosts the master data for inventory management like a G/L Account does for financial management.
In this table, we can do the set up for each individual item such as pricing, inventory and production strategies, and tracking options.
The location table defines which level of inventory management is done. A location can either be a physical warehouse somewhere or a part of a warehouse, if one warehouse uses different warehouse strategies.
If we look at the Location Card, we see what we can set up:
Let's see these settings in detail:
Item variants is a powerful feature in Microsoft Dynamics NAV. It enables us to split an item into different categories without having to create a new item.
The variant code is maintained in the item ledger entries and used when applying them. Let's see an example of how this can be used.
Our company sells t-shirts. We have three sizes; small, medium, and large, and four colors; white, black, red, and blue. This enables us to create the following twelve unique variant codes:
Size and color | |||
---|---|---|---|
S-WHITE |
S-BLACK |
S-RED |
S-BLUE |
M-WHITE |
M-BLACK |
M-RED |
M-BLUE |
L-WHITE |
L-BLACK |
L-RED |
L-BLUE |
When we purchase or produce these t-shirts, we need to specify the variant code, which is inherited into the item ledger entry.
If we sell or transfer one of these items, we can specify the same variant code. Microsoft Dynamics NAV will then use this variant code when searching for inventory.
Sometimes, the same item can have more than one unit cost, replenishment system, or production method. To support this, we can use stock keeping units.
A stock keeping unit refers to an existing item, location, and variant. These three fields are the unique primary key. Let's see an example of how this can be used. Our t-shirts need to have different unit costs. In order to do this, we need to create a SKU for each variant we just created:
When we now create two purchase order lines for the same item with a different variant code, we can see that the Last Direct Cost is different for each variant.
When an item has many variants and locations, creating the SKU for each combination can be quite a challenge. To help in this process, we can use the Create Stockkeeping Unit report (5706).
The newly created SKU will inherit all the necessary fields from the item. After this, we can go in and make necessary changes to the individual SKU records:
The basic unit price of an item can be set in the Item table. This is a static field, which is used when a new sales document is created. To use more flexible unit prices, we can use the Sales Prices and Sales Discounts functionality:
More information about pricing can be found in Chapter 1, Introduction to Microsoft Dynamics NAV, and Chapter 2, A Sample Application.
When the inventory is created and used, the system will apply and close positive and negative item ledger entries with each other. This enables us to trace inventory.
The application is saved in Item Application Entry table (339). Let's have a look at the C/AL code that handles the item application.
Item application is done in codeunit Item Jnl.-Post Line (22) in the ApplyItemLedgEntry
function. The function starts with checking whether reservations are used. Using reservations changes the way inventory application is used. We'll discuss reservations later in this chapter.
ApplyItemLedgEntry
...
CLEAR(OldItemLedgEntry);
...
REPEAT
ItemJnlLine.CALCFIELDS("Reserved Qty. (Base)");
IF ItemJnlLine."Assemble to Order" THEN BEGIN
ItemJnlLine.TESTFIELD("Reserved Qty. (Base)");
ItemJnlLine.TESTFIELD("Applies-to Entry");
END ELSE
IF ItemJnlLine."Reserved Qty. (Base)" <> 0 THEN BEGIN
IF ItemLedgEntry."Applies-to Entry" <> 0 THEN
ItemLedgEntry.FIELDERROR(
"Applies-to Entry",Text99000000);
END;
...
END ELSE
StartApplication := TRUE;
If there are no reservations made, the system will start the application code. This allows two possibilities: manual application and automatic application.
Manual application is done when the user populates the Applies-to Entry field in the item journal line. This is also used when users change the application.
IF StartApplication THEN BEGIN ItemLedgEntry.CALCFIELDS("Reserved Quantity"); IF ItemLedgEntry."Applies-to Entry" <> 0 THEN BEGIN IF FirstApplication THEN BEGIN FirstApplication := FALSE; OldItemLedgEntry.GET(ItemLedgEntry."Applies-to Entry"); OldItemLedgEntry.TESTFIELD("Item No.",ItemLedgEntry."Item No."); OldItemLedgEntry.TESTFIELD("Variant Code",ItemLedgEntry."Variant Code"); OldItemLedgEntry.TESTFIELD(Positive,NOT ItemLedgEntry.Positive); OldItemLedgEntry.TESTFIELD("Location Code",ItemLedgEntry."Location Code");
In this case, the system checks whether the Item Ledger Entry we have specified matches the requirements. When the application is done automatically, the system will search for the best item ledger entry based on the same requirements.
END ELSE BEGIN IF FirstApplication THEN BEGIN FirstApplication := FALSE; ItemLedgEntry2.SETCURRENTKEY("Item No.",Open,"Variant Code", Positive,"Location Code","Posting Date"); ItemLedgEntry2.SETRANGE("Item No.",ItemLedgEntry."Item No."); ItemLedgEntry2.SETRANGE(Open,TRUE); ItemLedgEntry2.SETRANGE("Variant Code",ItemLedgEntry. "Variant Code"); ItemLedgEntry2.SETRANGE(Positive,NOT ItemLedgEntry.Positive); ItemLedgEntry2.SETRANGE("Location Code", ItemLedgEntry."Location Code"); IF ItemLedgEntry."Job Purchase" = TRUE THEN BEGIN ItemLedgEntry2.SETRANGE("Job No.",ItemLedgEntry."Job No."); ItemLedgEntry2.SETRANGE("Job Task No.", ItemLedgEntry."Job Task No."); ... END; IF ItemTrackingCode."SN Specific Tracking" THEN ItemLedgEntry2.SETRANGE("Serial No.", ItemLedgEntry."Serial No."); IF ItemTrackingCode."Lot Specific Tracking" THEN ItemLedgEntry2.SETRANGE("Lot No.",ItemLedgEntry."Lot No."); IF Location.GET(ItemLedgEntry."Location Code") THEN IF Location."Use As In-Transit" THEN ItemLedgEntry2.SETRANGE("Transfer Order No.", ItemLedgEntry."Transfer Order No."); IF Item."Costing Method" = Item."Costing Method"::LIFO THEN EntryFindMethod := '+' ELSE EntryFindMethod := '-'; IF NOT ItemLedgEntry2.FIND(EntryFindMethod) THEN EXIT;
The actual application entry is created in the InsertApplEntry
function.
In order to apply an item ledger entry to another item ledger entry, certain requirements should be taken into account. We can read these requirements from the C/AL code:
Other requirements are conditional based on system setup. For example, if the item uses a Lot No. or Serial No., this should also match.
When the system has defined the filter, it tries to find the first record. The search method depends on the costing method. If the cost method is LIFO, the system will try to find the last record in the filter. For all other costing methods, it will find the first.
We can also see that when using Lot numbers, the application and the costing is done within the Lot number.
In Microsoft Dynamics NAV, the physical information for Inventory is stored separately from the financial information. This information is stored in a one-to-many relation, meaning one Item Ledger Entry can have multiple Value Entries.
This enables us to specify the value information in detail in a time dimension and cost type dimension.
Each item ledger entry starts with at least one value entry of the type direct cost. This defines the initial value of the inventory. During the inventory lifetime, the item ledger entry can get the following four other types of value entries:
To move inventory from one location to another location, it is possible to do a negative and a positive adjustment in the Item Journal Line, but we can also use a Transfer Order, as shown in the following screenshot:
The Transfer Order creates the item ledger entries for each location and maintains the link for the value entries.
This means that if we move 100 items from location blue to green without having received the purchase invoice yet, the system will create value entries for the moved inventory when the invoice is posted. Let's try this for a new item.
The item we will use is Jeans. The first step is to create the item as follows:
This will result in five Item Ledger Entries with five Value Entries but the total cost is zero since we have not yet received the purchase invoice.
This results in a value entry for the original item ledger entry.
For trading companies, it is very important to have just enough inventories; not too many, not too few. In order to do this, we can use the requisition journals together with the reordering policy on the item.
The reordering policy tells the system how to calculate the moment and the quantity for item ordering. Microsoft Dynamics NAV uses the following four different reordering policies:
The quantity is calculated in the codeunit Inventory Profile Offsetting (99000854) in the CalcReorderQty
function.
The ordering policy algorithms in Microsoft Dynamics NAV are very static and some trading companies need more flexibility.
One example is seasonal and depends on the weather. Toy stores need extra inventory during Christmas and garden tool stores have their peak in spring. During these peaks, the delivery times and availability is also different compared to the other times of the year.