TDM Inventory Management System 5.4
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.
