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:
- General: Here, we can specify the physical location of the warehouse. We can also specify Use As In-Transit. When this is specified, we can only use transfer orders to move inventory to this location.
- Warehouse: On this tab, we specify which level of warehouse management functionality we want to use. If everything is left blank, no warehouse entries are created when this location is used.
- Bins: This tab contains the default bins for most inventory activities, such as Receipt and Shipment. These values can be changed when creating the warehouse documents.
- Bin Policies: This tab contains some more advanced options for warehouse management.
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:
- The Item No. should be the same for both the entries.
- The old item ledger entry should be Open. When an item ledger entry is fully applied, the Boolean field Open is set to False.
- The variant code and location code should be the same.
- The Boolean field Positive should have a reverse sign. This results in the limitation of not being able to apply one negative entry to another negative entry.
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:
- Revaluation: This entry type is used when the item revaluation batch is started and the value of the item is different compared to the direct cost.
- Rounding: Sometimes, the inventory adjustment leads into rounding issues. The rounding is stored as a special entry type for traceability.
- Indirect Cost: When Indirect Cost % is used on the item card the system will create additional value entries for the indirect cost amount.
- Variance: When the item uses standard cost, the difference between the invoiced amount and the standard cost is saved as an entry type variance.
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:
- We only define the No., Description, Base UOM, and the Posting Groups.
- Now, we create a new purchase order with quantity 10 in location BLUE.
- We receive the purchase order.
- Using a new transfer order, we move the inventory from BLUE to RED.
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.
- Now, we create a new purchase invoice and get the receipt lines. We use a Unit Cost of 10.
This results in a value entry for the original item ledger entry.
- To create the value entries for the transfers, we need to run the Adjust Cost - Item Entries report (795). This results in all item ledger entries having the same value entries:
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:
- Fixed Reorder Qty.: Each time we run the requisition journal, the system will purchase the same, fixed quantity of items. This quantity is specified in the Reorder Quantity field.
- Maximum Qty.: The system will purchase as many items to meet the value of the Maximum Inventory field.
- Order: For each sales order, a purchase order will be created. This automatically enables the reservation process for this item.
- Lot-for-Lot: This option will calculate the required inventory necessary to deliver the outstanding sales orders.
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.