TDM Inventory Management System
From ICISWiki
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
