|
Introduction
As the flow of goods in today’s supply chain becomes more and more complex, the ability to keep track of items is increasingly important to the companies involved. While monitoring an item’s transaction flow is a legal requirement in the business of medical and chemical supply, other businesses may wish to monitor products with warranties or expiration dates for customer service reasons.
Essentially, an item tracking system should provide a company with easy handling of serial and lot numbers taking into account each unique piece of merchandise: when and where received, where stored, when and where sold.
Navision has gradually expanded its coverage of this business requirement and today, the system provides application-wide functionality and a solid core on which to develop extensions. This document looks at the conceptual and logical contents of this achievement from a historic perspective.
Reservation
The document will periodically refer to the reservation system as this has become increasingly integral to item tracking. See also the Navision white paper, “Reservation, Tracking & Action Messaging” (RTAM).
The DOS-based Microsoft Navision version 3.53 provided a basic feature for handling items carrying serial and lot numbers. The design was simple and stable and although heavily customized to address customer needs, the W1 version remained unchanged until Microsoft Navision 3.00.
The item tracking number is entered straight onto the document line and carried to its item ledger entry when posting. Each unique number assigned to an item unit requires one document line:
Although very transparent and simple, the evident drawback of this design is the “line splitting”. Selling 1000 serial-numbered print cards can be quite tedious and the associated printout very large. Typical customizations include batch jobs to generate document lines and others to compress for reporting. The system offers no setup options and serial and lot numbers function strictly as dimensions.
Stable
Easy access to information
Reservation system unaffected
Clear transaction rules
Easy to customize
Line splitting
Limited functionality
Reservation
The first reservation system was introduced in Navision Financials 2.00. By version NM 2.60 it was more robust and had been expanded to provide functionality for order tracking and dynamic action messaging (RTAM). Also, a set of fundamental rules were implemented to govern the inventory application of items carrying item tracking numbers:
1) Demand with serial/lot number can only apply to supply with that serial/lot number.
2) Demand without serial/lot number can apply to any supply – with or without serial/lot number.
With Microsoft Navision 3.00 came a new and complete item tracking system. It was meant as an application-wide feature for handling serial and lot numbers according to a company’s specific requirements. Although the new item tracking system offers much functionality, advanced setup and a logical user interface, it unfortunately does not integrate with the existing RTAM and planning systems. Since a unique identifier assigned to an item is basically a kind of reservation facility, Microsoft Navision 3.00 actually contains two separate reservation systems. This can obviously cause serious conflict, and it generally means that order tracking and reservation must be inactivated for items carrying item tracking numbers.
This information is still valid with Microsoft Navision version 3.70 and 4.0.
Item tracking numbers do not exist on the document line but are entered and maintained in the Item Tracking Line table. These sub-descriptions hold information about the item’s serial and lot number, the quantity to handle and/or invoice, as well as warranty and expiration dates. The functionality covers most documents that represent supply or demand and thus affects inventory availability. Once posted, the item tracking information is transferred to document-specific history tables. From each of these posted items tracking line tables is a relation (Item Tracking No.) to the associated item ledger entries. This is primarily used to facilitate lookups between posted ledger entries and their item tracking entries:
Resolves the issue of split documents
Complete user interface
Flexibility of use
Complex implementation
Difficult to customize (dispersed in many objects)
Code and data redundancy (25 tables)
Poor integration to manufacturing
Conflicting with reservation system
No integration to supply planning
Because of the serious structural problems, it was decided that the item tracking solution be redesigned. The overall strategy was to merge RTAM and Item Tracking into a common structure, while keeping the user interface unchanged. However, the full scope of the project could not be reached in one go and, therefore, version 3.10 merely consists of a changed pointer structure in preparation for moving Item Tracking onto the RTAM structure. This change has no direct effect on the 3.00 functionality.
The objective of the item tracking project for Microsoft Navision
3.60 is full embedment in an optimized RTAM structure to ensure seamless integration of order tracking, reservation, and item tracking in the same engine. The item tracking functionality is stabilized, and RTAM expanded to non-order network entities such as journals, invoices, and credit memos. This incorporates item tracking entries in total availability calculations throughout the system, including planning, manufacturing, and warehouse.
The following sections merely outline the design. More details on individual objects and their relations are available in Appendix A.
The RTAM engine now handles a type of item specification that is permanent, and not merely a link between intermittent supply and demand. Additional objects are created to solve this, and the existing item repository reincorporated; the old concept of carrying serial and lot numbers to the item ledger entries is reintroduced to ensure simple access to historical data for item tracking purposes. Another different attribute of item tracking numbers compared to the conventional RTAM data is the fact that they may be posted – either fully or partially. Therefore, the Reservation Entry table now works in conjunction with a sister table, Tracking Specification, which manages and displays summing across active and posted item tracking quantities. The central posting object is redesigned to handle the unique sub-classifications of a document line in the form of serial/lot numbers, and a special relation table is added to create the one-to-many relations between posted documents and their split item ledger entries.
Posting Structure
CU22 now splits the posting according to the item tracking numbers specified on the document line. Each unique item tracking number on the line creates its own item ledger entry for the item. This means that the link from the posted document line to the associated item ledger entries is now a one-to-many relation. Special item tracking relation tables handle this for posting and lookup situations:
T6507, Item Entry Relation: between shipped/received lines and item ledger entries
T6508, Value Entry Relation: between invoiced lines and value entries
Active versus Historic Entries
When parts of a document line quantity are posted, only that particular quantity is transferred to the item ledger entries along with its item tracking numbers. However, we wish to access all the relevant item tracking information directly from the active document line. That is, not only do we want to see the entries related to the remaining quantity, we also want information about the units that have been posted. When the user views or modifies the Item Tracking Lines form, the collective contents of T336 and T337 are presented in a temporary version of T336. This ensures that historic and active item tracking data is accessed as one.
To clarify the usage of T336 and T337, we will demonstrate with use cases. See also Appendix B.
The table below describes the flow through a purchase order scenario. The shaded lines symbolize the lines shown in the Item Tracking Lines form, and the embossed figures are the ones typed in by the user before activating the posting procedure for the next step.
| |
Quantity |
Qty. to Handle |
Qty. to Invoice |
Qty. Handled |
Qty. Invoiced |
Create purchase order line of 7 pcs. with item tracking numbers
|
T337 |
7 |
|
|
|
|
(T336) |
|
|
|
0 |
0 |
Receive 4 pcs. |
| |
7 |
4 |
0 |
0 |
0 |
T337 |
3 |
|
|
|
|
T336 |
4 |
|
|
4 |
0 |
Receive 2 pcs. and invoice 2 pcs. |
| |
7 |
2 |
2 |
4 |
0 |
T337 |
1 |
|
|
|
|
T336 |
6 |
|
|
6 |
2 |
Receive 1 pcs. |
| |
7 |
1 |
0 |
6 |
2 |
T336 |
7 |
|
|
7 |
2 |
Invoice 5 pcs. |
| |
7 |
|
5 |
7 |
2 |
T336 |
7 |
|
|
7 |
7 |
End content |
| |
7 |
|
|
7 |
7 |
Item Tracking Lines Form
Item tracking records are created in the RTAM engine alongside reservation records and selected from their calculated availability at any time. Data entered in the Item Tracking Lines form is created in a temporary version of T336 and committed to T337 (live) and T336 (historic), when the form is closed. Lookups from the serial/lot no fields show availability based on both item ledger entry (T32) and reservation records (T337), with no date filter. The matrix of quantity fields in the form header dynamically displays the quantities and sums of item tracking numbers being defined in the form. The quantities must correspond to those of the document line, which is indicated by 0 in the Undefined fields.
To coordinate the flow of serial and lot numbers through inventory, a set of rules is implemented for handling these records via the Item Tracking Lines form:
Both for positive and negative item tracking lines, it is not permitted to use a serial number with/without lot number more than once in the same item tracking form. If a user tries to enter any combination of serial number with/without lot number that is already present in the form, the system will give an error message and prevent the record.
For positive item tracking lines, it is not permitted to post if an item of the same variant and with the same serial number is already in inventory. If a user tries to post a positive line when there are any items in inventory carrying the same variant and serial number, the system will give an error message and abort the posting. However, both for positive and negative item tracking lines on open documents, it is permitted to have the same combination of serial number with/without lot number relating to different source lines (that is, existing in different item tracking forms) until posting.
When requiring SN/LN specific tracking, it is not permitted to post an outbound line if there are no items with the defined serial number with/without lot number in inventory. If a user tries to post an outbound line for an item with a serial/lot number combination that is not in inventory, the system will give an error message and abort the posting.
The above rules for entering item tracking numbers in the item tracking form support the coupling principles that govern order tracking, planning and reservation. See below.
For details about the processes behind the Item Tracking Lines form, see Appendix A, Posting Structure.
Integration with Reservation and Planning Engines
Item tracking is fully integrated with reservations and order tracking. This means that items with reservation and/or order tracking records may be assigned item tracking numbers, and vice versa.
Note:
In this respect, both engines are concerned with specific item application only. Therefore, the item in question must be set up to use specific item tracking; SN/ Lot Specific Tracking check marks:
must carry serial/lot number when posted and
must apply to same serial/lot number when posted outbound
When, for example, order tracking exists for a given item, it implies that records will be in T337 even before item tracking numbers are defined. The system, therefore, dictates certain coupling restrictions concerning the item tracking numbers to be reserved or order tracked. The implemented principles are adopted from NM 2.60:
Demand with serial/lot number can only cover supply with the same serial/lot number
Demand without serial/lot number can cover any supply – with or without serial/lot number
Reservation with Item Tracking
Simultaneous use of reservation and specific item tracking is uncommon as they both create a coupling between supply and demand. Except for situations where a customer or production planner requests a specific lot, it will rarely make sense to reserve inventory items that already carry item tracking numbers for specific application. Similarly, if specific item tracking numbers are assigned to reserved items, the reservation will fall away. The ordinary reservation processes therefore remain unchanged concerning the user interface, whereas the reservation of an item carrying item tracking numbers will take the user into a special workflow based on the following design:
When opening the reservation window from a document line carrying item tracking and running specific item tracking, the system will open a list of all serial/lot numbers that exist on that line. The Reservation window will open only after selecting one of these, and the user can then reserve from the selected serial/lot number combination.
Planning and Order Tracking with Item Tracking
As implemented in the RTAM engine, the planning engine is only concerned with serial/lot numbers if set up for specific item application. The planning system will only require a specific match if specific item tracking is required. In all other cases, the system will ignore item tracking numbers when applying supply to meet the demand – and vice versa.
Normally, a serial/lot number will only be specified immediately before posting a replenishment order. When pre-specifying a serial/lot number on the demand side, that number will already be in inventory. Consequently, item tracking numbers are not much of an issue when planning supply. However, after calculating a plan, the order network must be in balance – also concerning item tracking. For items using specific item tracking, all demand carrying serial/lot numbers must be met by corresponding supply. In most cases, it does not make much sense to reorder a specific lot or serial number, but in transferring from one location to another it will be unnaturally not to ask for the transfer of a given needed lot.
Solution Synopsis
The following digs a little deeper into the concepts behind item tracking in planning. For more information, please refer to the Navision white paper, “Planning”.
If specific item tracking has been required, an order track will be made from all item tracking demand to any corresponding item tracking supply, with the sole limitation that supply should come before demand. If, under those circumstances, the system cannot find corresponding item tracking supply for the item tracking-specific demand, a new item tracking supply will be created immediately and without considering order sizing, planning parameters, or rescheduling existing supply of the same serial/lot number.
If item tracking numbers are specified on the demand side and/or the supply side, but without requiring specific item tracking application, the system will create an order track from the demand to that supply, which is the most suitable regarding timing and quantity (normal planning procedure). The specified item tracking number will go into the order tracking in the same way that any specified item tracking quantity defines the one end of the order track. This means that the system will preserve item tracking number entered, but let it be part of the order track.
If item tracking numbers are specified on the supply side, but without requiring specific item tracking application, the system will regard this supply as fixed from a planning perspective. The system will not suggest resizing or rescheduling of this supply, but it will of course take it into consideration when investigating how the gross requirement can be met.
Integration with Warehouse Management Systems
Serial/lot number handling is primarily a warehouse task, and accordingly, all the inbound and outbound warehouse documents have features for assigning and selecting item tracking numbers. The RAM engine does not consider the internal warehouse structure, meaning that it is only possible to assign reservation and item tracking to locations not zones or bins. Once an item is in the warehouse and part of the inventory, the warehouse employees can transfer, reclassify and move the item between zones and bins using an independent item tracking structure, which is synchronized with the RTAM engine.
In versions 3.60 and 3.70, the RTAM engine does not take warehouse activities into consideration when calculating availability. Therefore, a serial number or a lot number is only either inbound or in an inventory location. This has been changed in version 4.0, where the RTAM engine takes warehouse activities into consideration when calculating availability. Items that are allocated to picks or registered as picked cannot be reserved any longer.
For detailed information, please refer to the Navision white paper, “Warehouse Management Systems”.
Advantages
(Microsoft Navision 3.60)
No conflict with reservation and order tracking
Centralized
Easier to customize (changes to core affect all related areas)
Very intense central objects (F6510, CU6500)
RTAM = Reservation, Tracking & Action Messaging
Note: originally a Navision project that now designates a complete system foundation.
quantity to handle = quantity to receive/ship/transfer in Item Tracking Lines form
Note: The Item Tracking Lines form is almost the same for all document types and, therefore, the quantity fields are generic.
specific tracking = setup option for an item tracking number assigned to a specific item.
Note: Indicates that the item must be applied specifically against an existing serial/lot number when posted outbound.
split line = one document line split into several item ledger entries
Ex: One sales line of 4 serial numbers is posted as 4 individual item ledger entries
order network entity = any document line representing supply or demand
Abbreviation: ONE/NONE
Posting Structure – Microsoft Navision
3.60
In order to align with the costing area and also to get a more simple and robust solution, ILE (Item Ledger Entry) is used as the primary carrier of IT (Item Tracking). The main concept is that IT on ONEs and NONEs is specified in T337, whereas item tracking related to historical information is retrieved directly from the ILEs related to the transaction in question. This means that ILEs will be fragmented to the level of detail in the IT specification on the order line being posted.
The form used for showing IT is intelligent and retrieves the information from the ILEs and shows it in a temporary table. T336 is used as the basis of the temporary table, in which the IT information retrieved from T337 as well as ILE is shown. Furthermore, the table actually carries data in the database, when IT related to invoicing has been defined for an order, but not yet invoiced.
One-to-Many Relation
The relation table used for making the link between a posted document line and related ILEs consists of only two parts: a pointer to the document line and an entry number pointing at an ILE. The pointer for the document line is based on the standard 6-field type. The current ILE field on the posted document line is retained; in cases where IT is not applied, the functionality will be as today, that is, a one-to-one relation between the posted line and an ILE. This field is left blank when a one-to-many relation is present. If the posted line carries IT, but only relates to one single ILE, the entry no field on the line is used and no relation record created.
Code Unit 80/90
In order to achieve the splitting of ILEs, the parts of the code in CU80/90 that call CU22 with an item journal line have been encircled by loops running through some global temporary record variables initialized with the IT defined for the posted line. In order to keep the code simple, this looping structure is always used; if no IT is defined for the line, a single record is inserted and the loop runs only once.
Posting of Item Journal
IT is transferred via the reservation entries related to the ILE and looping through IT is done in CU22.
This concept works the same for a journal line used indirectly during a sales posting and a line used directly e.g. in connection with a positive adjustment posting the journal line directly. In the latter case, the Source RowID would then point at the line itself.
Code Unit 22
The IT posting structure is based on letting CU80 and CU90 loop the call of CU22 during invoicing of existing shipments/receipts.
When it comes to posting of availability, IT is retrieved by CU22 from the reservation entries related to the posting. These entries are placed directly on the journal line due to the transfer of reservations and it must be read from there.
CU22 will be responsible for looping the IT and split the posting into the ILEs necessary to carry the IT information. Information regarding which ILEs have been created is returned using a temp336 record. This record could have been transferred back and forth as part of the parameter list for CU22, but in order to make as little disturbance as possible, a procedure is added to CU22 for returning a temp336 data set. This procedure is called when CU22 has finished its run – at this point, the CU22 object contains the information.
After retrieving the Temp336, CU80/90 create T6507 records to link the created ILEs to the shipment/receipt line created. Last, CU80/90 convert the Temp336 records to real T336 records related to the line in question, however, only if the posted document line is not deleted (only partially posted).
In the following, we will illustrate the use of tables T336 and T337 with two technical use cases. As opposed to standard conceptual use cases, these will focus on describing actual implementation.
Purchase Order with Lot Number
Goal in context
The user orders 10 items, assigns Lot A and receives the items into inventory.
Precondition
The item uses specific lot tracking; (must carry lot number inbound + must apply to same when outbound
Normal Sequence
Step No.
|
Action |
Reaction |
1 |
User creates purchase line of 10 pcs. |
Normal |
2 |
User wants to define lot number and activates the item tracking form.
|
System will search for reservation entries pointing at the purchase line, place them in a temporary version of Tracking Specification table (T336) summed across different serial/lot numbers.
No records are found and an empty form is shown.
|
3 |
User defines Lot A for the purchase line |
A surplus record pointing at the purchase line is created in the Reservation Entry table (T337) carrying Lot A.
The record is shown in item tracking form using a temporary version of T336.
The record has quantity = 10 and the Quantity to Handle and Quantity to Invoice fields are initiated to this value.
|
4 |
User closes the form and returns to the purchase line.
|
None |
5 |
User activates posting procedure – receive and invoice.
|
System checks IT setup to determine whether lot number must be handled.
As this is the case, the system checks that lot number is stated on the reservation entry related to the purchase line and that the sum of quantity to handle and quantity to invoice in T336 corresponds to the quantity stated on the sales line (if not, a run-time error occurs).
The surplus record in T337 containing Lot A is transferred to the resulting positive item ledger entry.
The purchase line and related reservation entry are deleted.
|
Post-condition
One open item ledger entry of 10 pcs. carrying Lot A.
Extensions
Step No. |
Cause |
Extension |
5a |
User activates the posting procedure – receive only
|
The purchase line is not deleted, as the line is not invoiced
The item tracking lines are still available, showing that 10 are handled and 0 are invoiced. This information is contained in T336
|
Sales Order with Lot Number
Goal in context
The user wants to create a sales order for items in Lot A and ship them.
Precondition
The item uses specific lot tracking (must carry lot number inbound + must apply to same when outbound).
Normal Sequence
Step No. |
Action |
Reaction |
1 |
The user creates a sales line of 10 pcs. |
Normal |
2 |
The user wants to define lot number and activates the item tracking form.
|
The system searches for reservation entries pointing at the purchase line and places them in a temporary version of T336, summed within different serial/lot numbers.
No records are found and an empty form is shown.
|
3 |
The user activates F6 and defines Lot A. |
A surplus record pointing at the purchase line is created in T337 carrying Lot A. (This is linked to the surplus record from the supply pointed out.
The record is shown in the item tracking form using a temporary version of T336.
The record has quantity = 10 and the Quantity to Handle and Quantity to Invoice fields are initiated to this value.
|
4 |
The user closes the form and returns to the sales line.
|
None |
5 |
The user activates posting procedure – ship and invoice. |
The system checks the item tracking setup to determine whether lot number must be handled.
As this is the case, the system checks that lot number is stated on the reservation entry related to the sales line and that the sum of quantity to handle and quantity to invoice in T336 corresponds to the quantity stated on the sales line (if not, a run-time error occurs).
The reservation entry carrying Lot A is transferred through the posting procedures and guides the specific item application.
The reservation entry is transferred to the tracking specification entries that are linked to the resulting negative item ledger entry and the positive item ledger entry the reservation was lined to.
The sales line and related reservation entry are deleted.
|
Post-condition
A closed item ledger entry of 10 pcs. carrying Lot A.
Extensions
Step No. |
Cause |
Extension |
5a |
The user activates the posting procedure – ship only. |
The sales line is not deleted, as the line is not invoiced.
The item tracking lines are still available showing that 10 are handled and 0 are invoiced. This information is contained in the T336.
|
|
