GM-X ERP for Blockchain - The INVENTORY subsystem

By Tony Marston

16th July 2007
Amended 30th March 2024

Introduction

1. Inventory Item

2. Item Issuance

3. Item Receipt

4. Item Variance

5. Reserved Items

6. Item Status

7. Item Movements

8. Stock Checks

9. Facilities and Containers

10. Container Movements

11. Facility Contact Mechanism

12. Facility Role

13. Reorder Guidelines

14. Pick Lists

15. Inventory Control Data

Amendment History

Introduction

Many organisations keep a supply of their products readily available so that they may fulfil sales orders with the minimum of delay, and not have to wait for the products to be manufactured, assembled, or delivered from the supplier. This supply is known as INVENTORY or STOCK, and may be stored in any number of locations or FACILITIES. It is important for the organisation to know what quantities of each product are available, and where. The quantity on-hand is decreased each time items are issued from stock for an outgoing shipment, and increased each time items are replenished, either from an internal production run or an incoming shipment. Reorder levels help the organisation to know when quantities are getting low so that they may begin the replenishment procedure.

Inventory items may be serialised, which means that each individual item has its own unique identification number, or they may be unserialised. Some producers have the concept of a LOT, which is a grouping of items of the same type. This can be used to track inventory back to its source, which is usually a particular production run. It is also possible for a product to be tracked by both lot and serial number. This is specified in the inventory_type column on the PRODUCT record.

Inventory Item

This shows what quantities of which products are available at what locations. Each inventory item exists within a particular container at a particular facility, so it is possible for a product to have multiple items each of which will have a separate inventory_item_id.

In the situation where a GM-X installation is handling multiple Functional Units which buy and sell inventory among themselves it is vitally important that the owner of that inventory can be easily identified and only be altered in the right circumstances. The owner of each inventory item is denoted in the party_id column which is subject to the principles identified in FAQ35.

Figure 1 - Inventory Item

INVENTORY_ITEM table

A product may have more than one inventory item if quantities are stored in several FACILITIES, or if it was produced from a different LOT, or if the product is serialised, in which case there will be a separate inventory item for each serial number.

INVENTORY_ITEM_FEATURE table

Serialised products may have different sets of features from those which are available, so these can be recorded here.

INVENTORY_ITEM_FEATURE_CUSTOMISATION table

This table holds all the customised values for a feature which applies to an inventory item. Some features within a feature category may be able to be customised, such as engraving options for jewellery will need the text which is to be engraved.

INVENTORY_ITEM_PRICE table

Sometimes an item in inventory may have a price which is different from the current catalog price, perhaps because it was returned by the previous customer and can be sold at a discounted price.

LOT table

A LOT is a grouping of items of the same type, and is generally used to track inventory items back to their source, and is often the result of a particular production run. Another source of lot information may be from the shipment from suppliers, which could determine the lot. This information is very important in the event that a recall of items is required.

LOT_PRODUCT table

This identifies the products which are contained in a particular lot. Depending on the configuration option this may be restricted to no more than 1 or any number.

Item Issuance

An item issuance occurs when a quantity of a product is removed from inventory prior to being included in an outgoing shipment, moved to another facility/container, or converted into another product.

After a Sales Order or a Transfer Order has been authorised, a PICK LIST is generated which identifies which items are scheduled to be extracted from inventory. As each quantity is picked from inventory this is recorded on an ITEM_ISSUANCE record, which will immediately reduce the qty_on_hand of that product at that location.

Quantities of a product may exist at several locations, so at the issuance stage the user has to choose one.

If stock has been reserved for an order at a particular location the user cannot choose any other location. When the ITEM_ISSUANCE record is created the RESERVED_ITEM record will be deleted.

Figure 2 - Item Issuance

ITEM_ISSUANCE table

This identifies when a quantity was removed from stock prior to being included in an outgoing shipment (a customer shipment, or a transfer to another facility). Its effect is to decrement the qty_on_hand value for the INVENTORY_ITEM.

ITEM_ISSUANCE_FEATURE table

A product may come with a variety of different features, so it may be important to identify which features were included with a particular item issuance.

ISSUANCE_ROLE_TYPE table

This identifies the various roles that different parties may play with an item issuance.

Here are some examples:

• Issuing Manager
• Picker

ITEM_ISSUANCE_ROLE table

Each item issuance may have many people involved in the process, and therefore may have many ITEM_ISSUANCE_ROLEs for each party.

Item Receipt

An item receipt occurs when a quantity of a product is added to inventory as a result of an incoming shipment (a supplier shipment, a customer return, a transfer from another facility) or a product conversion.

Figure 3 - Item Receipt

ITEM_RECEIPT table

This identifies when a quantity was added to stock. Its effect is to increment the qty_on_hand value for the INVENTORY_ITEM. There may also be a link to an order item which may refer to either a goods order or a service order.

RECEIPT_ISSUANCE_LINK table

This links an item receipt and one or more item issuances. This is typically used when one or more inventory items are consumed in a conversion process in order to create another inventory item. Each consumed item will have an entry on the ITEM_ISSUANCE table, and the generated item will have an entry on the ITEM_RECEIPT table.

Item Variance

This keeps a history of inventory item shrinkage or overages that were noticed during physical inventories or inspections.

Figure 4 - Item Variance

INVENTORY_VARIANCE_REASON table

This identifies the reason for a particular variance.

Here are some examples:

• Theft
• Shrinkage
• Damage
• Unknown

INVENTORY_ITEM_VARIANCE table

This table identifies each adjustment to the stock level for an inventory item. These are usually noticed during a physical inventory or stock check to discover variances between book and physical inventory. A negative variance (such as damaged items) will require a negative adjustment to reduce the on-hand and total quantities, while a positive variance (such as repaired items) will require a positive adjustment to increase those quantities.

This table also identifies each adjustment to the value of the stock for an inventory item. These adjustments may occur when the cost per unit of a product is changed using the Product Costing system, and inventory items with a total quantity exist for that product.

Reserved Items

In some circumstances it may be a good idea to reserve stock for a sales order so that it is still available at the item issuance stage and not issued to someone else. Reserved quantities reduce the value in qty_on_hand to indicate that the stock is not available to others.

Entries on this table may be created at any of the following stages:

• When the order is taken.
• At the authorisation stage when the picklist entries are created. If a reservation already exists it will be reused and cannot be changed.

Entries are deleted when the items are picked from inventory at the issuance stage.

Figure 5 - Reserved Items

Item Status

Although items may be in stock, it is vital to know their current condition which may be 'OK', 'in quarantine', 'being repaired' or 'scrap'.

Note that only those items with an 'OK' status can be picked. Only those items with an 'OK' or 'in quarantine' status may be reserved.

Any item which is part of a LOT which has passed its expiry date will automatically be excluded from the ' OK' category.

Figure 6 - Item Status

INVENTORY_ITEM_STATUS table

This identifies all the possible status values for an inventory item.

Here are some examples:

• OK
• Being Repaired
• Damaged
• Defective
• Scrap
• In Quarantine
• Failed Quarantine

INVENTORY_ITEM_STATUS_HIST table

This keeps a history of all the changes in status for an inventory item. The entry with the highest sequence number is the latest.

Item Movements

It is possible for an item to be moved from one facility/container to another without that item being removed from stock. Different containers may identify different processes that are required, such as "to be polished", "to be engraved" or "to be inspected".

Note that this facility should only be used for local movements which do not require the use of a SHIPMENT from one location to another.

Figure 7 - Inventory Item Movements

INVENTORY_ITEM_MOVEMENT table

This keeps a record of each time an item is moved from one facility/container to another.

Stock Checks

A stock check is when inventory items are inspected to ensure that the quantity available agrees with the on-hand quantity recorded by the system. If there is a difference between the two counts it should be recorded as a VARIANCE so that the on-hand quantity can be kept up-to-date. As well as a simple counting error, items may exist but are not available for use because they are damaged or defective.

Inspections may be scheduled at each FACILITY at regular intervals, and each inspection may include just a selection of items instead of all items. The item selection is controlled by a stockcheck cycle number.

Figure 8 - Stock Checks

STOCKCHECK_HDR table

This indicates when a stock check is to take place at a particular location or FACILITY. When a new record is created the cycle number is incremented, and all those INVENTORY_ITEMs with that number will be added to the list of items to be checked.

STOCKCHECK_DTL table

This identifies which items are to be inspected during a particular stock check event.

Facilities and Containers

Facilities are locations, such as depots, buildings or rooms, where inventory is stored. Within a facility stock (one or more INVENTORY_ITEMs) is stored in containers. Facilities are immovable and fixed to a single location while containers are potentially movable. Each installation must have at least one facility, and that facility must have at least one container. A small organisation may have only a single stock room where goods are stored in boxes, so the facility will be of type 'room' and called 'stock room' (or whatever) with one or more containers of type 'box' (or whatever).

In larger organisations there may be different rooms in different buildings, so in this case there will be two facility types, one called 'building' and another called 'room'. By defining the 'room' type as a child of the 'building' type you are effectively creating a hierarchy of facilities within facilities. This means that when you create a facility of type 'room' you must link it with a facility of type 'building'. In this relationship 'building' is called the senior or parent and 'room' is called the junior or child. If the organisation has different buildings located at different postal addresses then it can create a facility type of 'depot' (or whatever) and change the 'building' type to be a child of the 'depot' type. This means that when you create a facility of type 'building' you must link it with a facility of type 'depot'. By doing this you have created a facility hierarchy of depot -> building -> room ('depot' contains 'building' contains 'room'). You may create facility types of your choosing and define hierarchies of your choosing.

Each container must be located within a facility, and if you have defined a facility hierarchy then you can only use a facility at the bottom of the hierarchy. Using the example above you may place a container in a 'room' but not a 'building' or 'depot'.

It is also possible to create hierarchies of containers in those circumstances where you have large containers containing smaller containers. For example, a 'room' may contain cabinets, and each cabinet contains a number of shelves, and on each shelf there is a number of boxes, so you will create a container hierarchy of cabinet -> shelf -> box ('cabinet' contains 'shelf' contains 'box'). You may create container types of your choosing and define hierarchies of your choosing.

When storing an INVENTORY_ITEM in a container that container must always be at the bottom of any hierarchy. Using the example above you may place inventory in a 'box' but not a 'cabinet' or 'shelf'.

Figure 9 - Facilities and Containers

FACILITY_TYPE table

This identifies the different types of facility that may exist within an organisation.

Here are some examples:

• Warehouse
• Office
• Depot
• Building
• Plant
• Floor
• Room

FACILITY table

This identifies all the locations where inventory may be stored.

CONTAINER_TYPE table

This identifies the various types of container that may exist within a facility.

Here are some examples:

• *unspecified*
• Barrel
• Bin
• Box
• Cupboard
• File Drawer
• Room
• Shelf

CONTAINER table

This identifies all the different containers that exist within each facility. An entry with the type of "unspecified" is used when a facility does not have different containers.

Container Movements

Instead of moving individual items from one location to another it may be better to move the entire contents of a container. For example, a container may contain all the parts which are needed for a run on the assembly line, so the whole container is moved from storage to the assembly area and individual items are picked from the container as and when required.

Figure 10 - Container Movements

CONTAINER_MOVEMENT table

This keeps a record of each time container is moved from one facility or parent container to another.

Facility Contact Mechanism

As well as being tied to a particular PARTY, such as a person's personal cellular telephone number, a CONTACT_MECHANISM may be tied to a particular location or FACILITY. These physical facilities are not postal addresses or parties (although they are associated with postal addresses and parties), and another entity is needed to describe them.

Figure 11 - Facility Contact Mechanism

FACILITY_CONTACT_MECHANISM table

This links a particular contact mechanism with a particular facility. Note a facility can have several contact mechanisms, and a contact mechanism may be shared by more than one facility. For example, several facilities may be grouped together within the same postal address.

Facility Role

Each facility may involve one or more parties, thus the FACILITY_ROLE entity maintains which PARTYs are playing what FACILITY_ROLE_TYPEs for what FACILITIES

Figure 12 - Facility Role

FACILITY_ROLE_TYPE table

This identifies the different types of role that a party may play with a facility.

Here are some examples:

• Leases
• Manages
• Owns
• Rents
• Uses

FACILITY_ROLE table

This identifies where a party has a role with a facility. Note that a facility can be linked to several parties, and a party can be linked to several facilities.

Reorder Guideline

This provides information on how best to reorder products. Each record identifies when replenishment is required, the quantity required, and perhaps the supplier or a choice of suppliers. These may also vary based on whether the need is for a particular geographic area (such as for a certain state), or a specific facility.

Figure 13 - Reorder Guideline

REORDER_GUIDELINE table

This holds the reorder levels and reorder quantities for each inventory item. There may be several entries with varying options.

Pick Lists

When an order is approved items will need to be picked from inventory before they can be shipped to the customer. This may involve different people at different stages:

1. Generate a pick list to identify which items need to be picked. Stock levels are not decremented at this point as this stage indicates "to be picked", not "has been picked".
2. Match each picklist item with an INVENTORY_ITEM to check that the required quantities are available. Note that a product may be stored in more than one FACILITY, or more than one CONTAINER within a facility. Stock may also be reserved at this point, or a reservation may have been made at the order entry stage.
3. When items are picked from inventory an ITEM_ISSUANCE record is created and any RESERVED_ITEM record deleted. This will decrease the on-hand quantity for that item, and, if this falls below the reorder_level on the REORDER_GUIDELINE record, will trigger a replenishment request.
4. After being picked the items can be put into packages ready to be shipped to the customer.

Figure 14 - Pick Lists

PICKLIST_HDR table

This links a particular picklist number with a particular order.

PICKLIST_DTL table

This identifies all the products which need to be picked from inventory to satisfy a particular order.

Inventory Control Data

This information exists within a shared table in the MENU database and not a separate table within the INVENTORY database. It holds values which can be varied to suit that particular installation. The current values are as follows:

Date created: 16th July 2007

Amendment history:

Copyright © 1999-2024 by Geoprise Technologies Licensing, All Rights Reserved.