TDM Inventory Management System

From ICISWiki

Jump to: navigation, search

Contents

DESIGN OF THE INVENTORY MANAGEMENT SYSTEM

The ICIS Inventory Management System is a module designed to manage inventory information for any entities, including genetic resources and breeders’ seed stocks. It interacts with interface modules needing to manage inventories such as the InTrack Application and modules of the Genetic Resources Information Management System. The Inventory Management System tracks individual entities, where they are stored, what units they are managed in, what quantities are in storage and what quantities are available for use. The IMS requires two tables: IMS_LOT Table and IMS_TRANSACTION Table. IMS will be treaded as a separate database by the DLL. That is, functions accessing the inventory tables will open a separate handle from the DMS and GMS. The tables themselves may reside either in Local GMS, Local DMS or in their own database depending on how many users need access to the same inventory information. IMS needs access to the following tables: LOCATION, USERS, UDFLDS, SCALE and CHANGES.

IMS_LOT TABLE

Columns - Long Name (Name) Description Type Length (bytes)
LOT_ID (LOTID) The unique ID for each inventory lot Long 4
USER_ID (USERID) ID of the user defining the current Lot. Links to the USERS table Integer 2
ENTITY_TYPE (ETYPE) This is the name of the ICIS table which contains the primary identification of the entities. For Example GERMPLSM if the entities are seed stocks. Text 15
ENTITY_ID (EID) Entity identification number belonging to the Lot. Eg GID for seed inventory. This field links to the table identified in ETYPE. Long 4
LOCATION_ID (LOCID) Location identification number links to the LOCATION table in the IMS database. Long 4
SCALE _ID (SCALEID) The scale in which the quantities of the entity are measured, for example, grams, kilograms, number of cans, number of packets. Links to the SCALE table in DMS Long 4
LOT_STATUS (STATUS) Lot Status (0=Active or 1=Closed) Integer 2
SOURCE (SOURCEID) LOT_ID of the source lot if this lot is derived from another, else zero Long 4
COMMENTS (COMMENTS) Description of lot or other notes Text 255

Each Lot is defined by a unique combination of ETYPE, EID, LOCID and SCALEID. The LOTID-ETYPE,EID relationship is a many to one relationship since there any be many lots of the same entity stored in different places or units. Each lot has a particular scale, for example grams, kilograms, number of cans or number or packets, etc. The field LOCID determines where the lot is stored. In the case of germplasm, lots can be identified with different generations of a line having the same GID but different storage location, or they can have different GIDs but be in the same location. Locations may be as precise as positions on a shelf, or as general as a single institute or even country.

IMS_TRANSACTION TABLE

Columns - Long Name (Name) Description Type Length (bytes)
TRANSACTION_ID (TRNID) Unique transaction identification number Long 4
USER_ID (USERID) ID of the user processing the current transaction. Links to the USERS table Integer 2
LOT_ID (LOTID) Identifies lot for this transaction. Links to LOT table Long 4
TRANSACTION_DATE (TRNDATE) Date of the current transaction (ICIS date format YYYMMDD) Long 4
TRANSACTION_STATUS (TRNSTAT) Transaction status: 0=Anticipated (Deposit or Reserved), 1=Confirmed (Stored or Retrieved), 9=Cancelled Transaction Integer 2
TRANSACTION_QUANTITY (TRNQTY) Quantity involved in transaction: Positive (+) for deposits, negative (-) for withdrawals Real 8
COMMENTS (COMMENTS) Description of transaction or other notes Text 255
COMMITMENT_DATE (CMTDATE) Commitment date for anticipated transactions, Zero for indefinite Long 4
SOURCE_TYPE (SOURCETYPE) The type of the transaction source (e.g. LIST or STUDY) Text 10
SOURCE (SOURCEID) The particular ID of the source. (e.g. LISTID or STUDYID) Long 4
SOURCE_REC_ID (RECORDID) The particular record where the transaction is obtained (e.g. specific LRECID in the LIST or OUNITID in the STUDY) Long 4
PREVIOUS_AMOUNT (PREVAMOUNT) The previous amount if a transaction was corrected Real 8
PERSON_ID (PERSONID) The ID of the requestor/donor. Links to the PERSONS table. Long 4

The Transaction Table records inventory movement for each particular LOTID. Any LOTID used in the Transaction table needs to be defined in the Lot Table first. Each lot transaction has a transaction date and a quantity which can be either positive or negative. A positive quantity indicates that inventory is being put into the storage location and a negative quantity indicates that inventory is being taken from storage The field TRNSTAT indicates whether the Transaction is anticipated or confirmed.

The TRNSTAT value and the sign of TRNQTY define four transaction types:

IMS TRANSACTION TYPES

Transaction type Transaction status Quantity When used
Store 1 - committed Positive (+) Inventory is physically placed in the storage location defined by the LotID in the Lot Table. The quantity “deposited” is added to the total quantity shown as “Available” for users
Remove 1 - committed Negative (-) Inventory is physically taken from the storage location defined for the LotID in the Lot Table
Deposit 0 - anticipated Positive (+) Inventory is NOT physically placed in storage yet, but is shown as existing.
Reserve 0 - anticipated Negative (-) Seed is NOT physically taken from storage location yet, but needs to be reserved for a particular use

Each transaction record has a USERID field and a comments field for more transaction description if required. For example stock may be removed for destruction, or added or removed to correct errors in stock measurement.

The balance for any Lot is calculated from all the transaction records for that lot. Two balances are required, actual balance which is the sum of all committed transactions (TRNSTAT=1) and available balance which is the total of all non-cancelled transactions. New transaction records are created when inventory is stored, removed, projected or reserved. Existing transaction records are edited when a transaction is committed or when transaction quantities are changed.

The commitment date can be used by stock managers to flag or cancel expired anticipated transactions.

Use Cases

Seed inventories are managed through the InTrack Application. SETGEN is linked with Intrack to be used by users and producers of the inventory to reserve or deposit stocks, both anticipated transactions. InTrack is also used by stock managers as a stand-alone application for stock control – confirmed transactions. The following use cases are concerned with transactions for seed stocks.

Material Storage

Storage of material requires identification of the entity type to be stored for example, GERMPLSM, the entity ID of the material to be stored for example GID, the location for storage, LOCID, quantity to be stored and the units or scale of this quantity.

The ETYPE, EID, LOCID and SCALEID are used to identify or create a lot with defined LOTID. If the lot needs to be created the ID of the user (USERID) is also required. When the LOTID is known, a transaction record is created showing the quantity, type of transaction (positive quantity), the transaction status (TRNSTAT), the USERID, and optionally, a commitment date for anticipated transactions (deposit).

Material Removal

Removal of material requires identification of the entity type to be retrieved for example GERMPLSM, the entity ID of the material to be removed for exmaple GID, the location where it is stored, LOCID, quantity to be removed and the units or scale of this quantity.

The ETYPE, EID, LOCID and SCALEID are used to find an existing lot with defined LOTID. When the LOTID is known, a transaction record is created showing the quantity, type of transaction (negative quantity), the transaction status (TRNSTAT), the USERID, and optionally, a commitment date for anticipated transactions (reservation).

Material Repackage/Move

Method 1 - Replace Repackaged or Moved Lots

When inventory location needs to be changed, a transaction showing retrieval of remaining balance is added to the transaction table for the lot to be moved and the current Lot is ‘closed’ by changing the status to “Closed” (STATUS =1).

Then a new Lot is created as follows:

IMS_LOT table

Field Name Value
LOTID ID of new LOT (system supplied)
ETYPE Unchanged
EID Unchanged
LOCID New location ID
SCALE Unchanged
STATUS 0 - active
COMMENTS Relocation of inventory


When inventory is repackaged and the scale changes, remaining balance is removed from the existing lot and its Lot status is changed to “Closed” (STATUS =1). Then a new Lot is created with a new scale.

Field Name Value
LOTID ID of new LOT (system supplied)
ETYPE Unchanged
EID Unchanged
LOCID Unchanged
SCALE New Scale ID
STATUS 0 - active
COMMENTS Repackage of inventory

In both cases, two transaction records in the IMS_TRANSACTION Table are created.

Field Name First Transaction Second Transaction
TRNID New, created by system New, created by system
LOTID Old LOTID (LOTID whose status is being changed to 1) New LOTID generated in LOT table
TRNDATE Transaction date Transaction date
TRNSTAT 1 - committed 1 – committed
TRNQTY Negative actual qty for the old LOT Positive qty of new LOT
USERID User identification User identification
COMMENTS Repackaged/ move Repackaged/ move

The lot IDs of all uncommitted transactions (deposit & reserve) of the current lot will be updated to the new LOTID.

Method 2 - Update LOT location or scale

Often it is necessary to move or repackage lots without changing the LOTID. For example packets labelled with the LOTID may be moved without re-labelling. To do this we must update IMS_LOT.LOCID or IMS_LOT.SCALEID with the new values and write the old values into the CHANGES table.

Material Split

Inventory lots can be split into various sub-lots for storage in different locations. Each new lot created, has a new LOTID. The original LOT may be closed or remain active.

IMS_LOT TABLE

LOTID ETYPE EID LOCID SCALE STATUS COMMENTS
1286 GERMPLSM 32471 xxxxx gms 0 none
…. …. …. …. …. …. ….
3409 GERMPLSM 32471 yyyyy 5 gm sachets 0 taken from lot 1286
3410 GERMPLSM 32471 xxxxx 10 gm container 0 taken from lot 1286

IMS_TRANSACTION TABLE

TRNID LOTID TRNDATE TRNQTY USERID CMTDATE COMMENTS
5603 1286 20031010 1 (anticipated) -20 split plot
5604 3409 20031010 1 (anticipated) +2 split plot
5605 3410 20031010 1 (anticipated) +1 split plot

If the source lot has uncommitted transactions, then the original lot must remain open. Otherwise all uncommitted transactions should be committed or cancelled before doing the split operation.

Material Merge

When different bags/containers of inventory material are merged together, the following records are generated by the inventory module. Transaction records removing remaining balance of the merged Lots are added and the status of these lots is set to “Closed” (STATUS=1).

When Inventory Lots have the same ETYPE and EID fields,

IMS_LOT table

LOTID ETYPE EID LOCID SCALE STATUS COMMENTS
2345 GERMPLSM 27895 xxxxx gms 0 none
2346 GERMPLSM 27895 yyyy 5g sachets 0 none
2347 GERMPLSM 27895 xxxxx 10g container 0 none

New Lot record

LOTID ETYPE EID LOCID SCALE STATUS COMMENTS
2345 GERMPLSM 27895 xxxxx gms 1 (closed) none
2346 GERMPLSM 27895 yyyy 5g sachets 1 (closed) none
2347 GERMPLSM 27895 xxxxx 10g container 1 (closed) none
3459 GERMPLSM 27895 zzzz gms 0 (active) Merged lots
When Inventory Lots have the same ETYPE field but different EID fields,

IMS_LOT table

LOTID ETYPE EID LOCID SCALE STATUS COMMENTS
3476 GERMPLSM 27895 xxxx gms 0 none
4652 GERMPLSM 27911 yyyy 5 gm sachets 0 none
7621 GERMPLSM 28345 xxxx gms 0 none

Balances are withdrawn from the lots to be merged and they are ‘closed’. A new entity of type ETYPE must be created and a new inventory lot with the new EID and a new LOTID is created.

The LOTIDs of all uncommitted transactions (deposit & reserve) of the current lot will be updated to the new LOTID.

Material Loss/Discard

When inventory lots need to be thrown away, or inventory discrepancies are found in storage, new transaction records are created to “correct” the inventory balance.

Interactive Seed Lot Creation

Input: The seed inventory item on the edit menu will query the IMS and report balances for existing seed lots and, optionally, create new lots with zero balance for non existing seed lots. When this item is selected with a list open in the Edit Window, the user is asked to specify the seed lot location and the storage units. Locations can be picked from a list of locations in the LOCATION table which have LTYPE set to the value of Initialisation Key: SeedStorageLocation. Scales can be picked for a list of scales for a trait defined by the value of Initialisation Key: SeedStockTrait. Default values are null indicating any location and any units. (For seed stocks ETYPE is GERMPLSM and EID is a GID value.)


Process: The procedure runs through the entries of the list and queries the IMS LOT table. If a Lot is found matching the GID, location and scale, an error message ‘Lot already exists’ is displayed. If no matching lot exists, the lot is created with the current date and USERID.

Interactive Seed Reservations

Input: Tag entries in the Edit Window for which to reserve seed. Select Reserve Seed from the Edit Menu. Specify Lot information and quantity to be reserved. The Lot information can be specified in two ways. Either indicate that the LotIDs are already in the Source field from a previous Seed Inventory Query, or specify the location and scale in the same way as for Seed Inventory Queries.

The quantity of seed to be reserved can be specified in an edit window. Individual quantities can be indicated for each tagged entry or the user can select ‘Set No. of Units to Reserve’ to indicate an amount for all entries. A number indicates the quantity to be reserved for all tagged entries (units assumed to match the Lot units). The user must also specify a Commitment Date after which the transaction will be cancelled if it is not confirmed by the store manager by then. A null or zero commitment date indicated no expiry will automatically be applied.

Process: The procedure runs through the tagged entries locating the lot (by the same procedure as for Seed Inventory Queries, except that Lots must exist or no transaction is possible). Both the available and actual balances are retrieved for the lot.

If the reserve quantity is not known, the user is shown the balance and can enter the reserve quantity (with an option to use the same quantity for all subsequent entries). Once the quantity is known, it is compared to the available balance and if there is sufficient stock, a transaction record is added to the transaction table for the specified Lot and quantity (which is made negative to indicate withdrawal) and with the current USERID and date. If there is insufficient stock, an error message occurs and the user is requested to specify a different quantity for that entry or skip reservation. The skip can apply to the current entry only or to all subsequent entries with insufficient stock also.

Interactive Seed Deposits

Input: Tag entries in the Edit list for which seed is to be stored. Select the Seed Deposit item from the Edit Menu and specify the lot information, the quantity to be stored and the confirmation date in the query window. The Lot information can be specified in two ways. Either indicate that the LotIDs are already in the Source field from a previous Seed Inventory Query, or specify the location and scale in the same way as for Seed Inventory Queries.

The quantity of seed to be stored can be specified in an edit window. Individual quantities can be indicated for each tagged entry or the user can select ‘Set No. of Units to Deposit’ to indicate an amount for all entries. A number indicates the quantity to be stored for all tagged entries (units assumed to match the Lot units). The user must also specify a Commitment Date after which the transaction will be cancelled if it is not confirmed by the store manager by then. A null or zero commitment date indicated no expiry will automatically be applied.

Process:

The procedure runs through the tagged entries locating the lot (by the same procedure as for Seed Inventory Queries, except that Lots must exist or no transaction is possible). Both the available and actual balances are retrieved for the lot.

If the quantity to be stored is not known, the user can enter the quantity (with an option to use the same quantity for all subsequent entries). Once the quantity is known a transaction record is added to the Transaction table for the specified Lot and quantity (which is positive to indicate deposit) and with the selected Donor (PERSONID) and date.

Batch Processing for Reservations and Deposits

Input: Reservations and deposits can also be performed in batch mode using Batch Inventory Buttons on the button bar. In either case the user is asked to specify a DMS Study where the inventory transactions are defined and a destinating list where the transaction report will be filed.

The user is asked to specify variables in the study identifying the germplasm, storage location and quantity. The variables must have the following Properties: Germplasm Identitfication; Location and Stored Seed Quantity with scaled GID, LOCID and seed quantity scale respectively.

Process: The routine queries the study for each germplasm and location pair with a stored seed quantity variable value. The Seed Lot is defined by the GID, LOCID and SCALEID of the quantity. If this Lot does not exist but the process is seed storage, it is automatically created. If the process is to reserve seed and the lot does not exist or there is insufficient balance, an error is reported in the SOURCE field of the output list. Otherwise appropriate transactions are generated and resulting balances reported in the SOURCE field of the output list.

Queries and Reporting

System Reports

The following reports are required of the Inventory System include:

  • Get Balance (Total, Reserved)
  • Get Transactions
  • Get Lots
  • Query by :
    • Etype
    • Etype and EID
    • Etype and Location
    • Etype, EID and Location

User-defined Reports

The following user defined reports have been identified:

  • Dormant Entries
  • Empty Shelves
  • Amount Less than minimum
  • Distribution of Materials
  • Storage Greater Than n years
Personal tools