Data dictionary for Inventory Schema - IntegratedBreedingPlatform/Documentation GitHub Wiki
Introduction
Inventory managent schema of the BMS is based on the ICIS Inventory Management System. The ICIS Inventory Management System is designed to manage inventory information for any entities, including genetic resources and breeders’ seed stocks. Within BMS though the main usage is to track the germplasm seed stocks.
ims_lot Table
| Columns - Long Name (Name) | Description | Type | Length (bytes) |
|---|---|---|---|
| LOT_ID (LOTID) | The unique ID for each inventory lot | Integer | 4 |
| USER_ID (USERID) | ID of the user defining the current Lot. Links to the USERS table | Integer | 4 |
| ENTITY_TYPE (ETYPE) | This is the name of the 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. | Integer | 4 |
| LOCATION_ID (LOCID) | Location identification number links to the LOCATION table in the IMS database. | Integer | 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 | Integer | 4 |
| LOT_STATUS (STATUS) | Lot Status (0=Active or 1=Closed) | Integer | 4 |
| SOURCE (SOURCEID) | LOT_ID of the source lot if this lot is derived from another, else zero | Integer | 4 |
| COMMENTS (COMMENTS) | Description of lot or other notes | Text | 255 |
| CREATED_DATE | Date of creation | Timestamp | - |
| STOCK_ID | Stock Identifier | Text | 35 |
| LOT_UUID | Business Id (UUID) for the lot | Text | 36 |
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. Each Lot must have a unique Stock ID
ims_transaction Table
| Columns - Long Name (Name) | Description | Type | Length (bytes) |
|---|---|---|---|
| TRANSACTION_ID (TRNID) | Unique transaction identification number | Integer | 4 |
| USER_ID (USERID) | ID of the user processing the current transaction. Links to the USERS table | Integer | 4 |
| LOT_ID (LOTID) | Foreign Key to LOT table | Integer | 4 |
| TRANSACTION_DATE (TRNDATE) | Date of the current transaction (ICIS date format YYYMMDD) | Integer | 4 |
| TRANSACTION_STATUS (TRNSTAT) | Transaction status: 0=Pending, 1=Confirmed, 9=Cancelled | 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 pending transactions, Zero for indefinite | Integer | 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) | Integer | 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 |
| BULK_WITH | The list of stock ID that it can be bulked together (comma-separated values) | Text | 200 |
| BULK_COMPL | Indicates if bulking is completed (e.g. Y or N) | Text | 10 |
| TRNTYPE | Transaction Type. 1: Withdrawal, 2: Discard, 3: Adjustment, 4: Deposit | Integer | 11 |
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 penging or confirmed.
ims_experiment_transaction Table
| Columns - Long Name (Name) | Description | Type | Length (bytes) |
|---|---|---|---|
| TRANSACTION_ID (TRNID) | Foreign Key to ims_transaction table | Integer | 4 |
| EXPERIMENT_ID(ND_EXPERIMENT_ID) | Foreign Key to nd_experiment_id table | Integer | 4 |
| EXPERIMENT TRANSACTION TYPE (TYPE) | Planting Type is 1 and Harvesting Type is 2 | Integer | 4 |
The ims_experiment_transaction store relationships between transactions and experiments.
ims_lot_attribute Table
| Columns - Long Name (Name) | Description | Type | Length (bytes) |
|---|---|---|---|
| AID | Primary key | Integer | 11 |
| LOTID | Foreign Key to ims_lot table | Integer | 11 |
| ATYPE | Foreign Key to the attributs table | Integer | 11 |
| AVAL | Value for the attibute assigned to the lot | Text | 5000 |
| CVAL_ID | Foreign key to the cvterm table for the category selected | Integer | 11 |
| ALOCN | Foreign key to the location table | Integer | 11 |
| AREF | - | Integer | 11 |
| ADATE | Attribute date | Integer | 11 |
| CREATED_DATE | System creation date | TIMESTAMP | |
| CREATED_BY | User that created the row | Integer | 11 |
| MODIFIED_DATE | Modification date | TIMESTAMP | |
| MODIFIED_BY | Last user that modified the row | Integer | 11 |