IMS Setup
From ICISWiki
Contents |
SETTING UP AN IMS DATABASE
G.McLaren, L. Sison and A.Portugal
INTRODUCTION
The IMS database manages and controls inventory information on any entity including the breeders’ seed stock and genetic resources. Although there are only two tables in the IMS database, it accesses tables from other ICIS databases. Therefore, some tables need to be populated for the IMS to run correctly.
POPULATING OTHER ICIS DATABASES
The first step is to populate the tables in the GMS and DMS referenced by the IMS.
POPULATING TABLES IN THE GMS
The UDFLDS table (Central)
IMS storage locations should have a special location type to distinguish it from other locations meant for breeding and other purposes. The location type for IMS should be entered in the UDFLDS table similar to the one in Figure 1.2.1.1 IMS locations should then carry that type. The location type for IMS should also be assigned to the SEEDSTORAGELOCATION key of the IMS section in the INI file.
Figure 1.2.1.1 Example of location type for IMS
The Location Table (Local)
IMS is currently implemented as part of the local ICIS database. Hence, it is advisable that all its locations are stored in the local LOCATION table. For the IMS, the seed storage has four levels of hierarchy as shown in Table 1.2.1.2.
Table 1.2.1.2 Details of the Location table for the IMS
| LEVEL | SNL1ID | SNL2ID | SNL3ID |
|---|---|---|---|
| 1 | 0 | 0 | 0 |
| 2 | LOCID of level1 | 0 | 0 |
| 3 | LOCID of level1 | LOCID of level2 | 0 |
| 4 | LOCID of level1 | LOCID of level2 | LOCID of level3 |
For example:
| LEVEL | LNAME | SNL1ID | SNL2ID | SNL3ID | LOCID * |
|---|---|---|---|---|---|
| 1 | Unfumigated Room | 0 | 0 | 0 | -1 |
| 2 | Shelf 1 | -1 | 0 | 0 | -2 |
| 3 | Box | -1 | -2 | 0 | -3 |
| 4 | Plot No 1 | -1 | -2 | -3 | -4 |
* unique location identifier which is a negative incremental number for the inventory system.
(a) From an Excel File
Some breeding programs manage their stocks in Excel file. Locations from this file can be generated by creating a macro in Excel. The first column is treated as the second level, the second and third column as the third and fourth levels, respectively. The first 2 letters of the first column is treated as the first level. In this example, UF stands for Unfumigated Room.
| Shelf_No | Box_No | Entry | Trial_No |
|---|---|---|---|
| UFAB1-1 | 1 | 1-8 | A04DS-17 |
| UFAB2-1 | 2 | 9-16 | A04DS-17 |
| UFAB3-1 | 3 | 17-24 | A04DS-17 |
| UFAB4-1 | 4 | 25-31 | A04DS-17 |
When the macro is run, the results would be as follows:
| Shelf_No | Box_No | Entry | New_Location_Name |
|---|---|---|---|
| UFAB1-1 | 1 | 1 | Shelf UFAB1-1, Box 1, Trial Box 1 |
| UFAB1-1 | 1 | 2 | Shelf UFAB1-1, Box 1, Trial Box 2 |
| UFAB1-1 | 1 | 3 | Shelf UFAB1-1, Box 1, Trial Box 3 |
| UFAB1-1 | 1 | 4 | Shelf UFAB1-1, Box 1, Trial Box 4 |
| UFAB1-1 | 1 | 5 | Shelf UFAB1-1, Box 1, Trial Box 5 |
| UFAB1-1 | 1 | 6 | Shelf UFAB1-1, Box 1, Trial Box 6 |
| UFAB1-1 | 1 | 7 | Shelf UFAB1-1, Box 1, Trial Box 7 |
| UFAB1-1 | 1 | 8 | Shelf UFAB1-1, Box 1, Trial Box 8 |
| UFAB2-1 | 2 | 9 | Shelf UFAB2-1, Box 2, Trial Box 9 |
| UFAB2-1 | 2 | 10 | Shelf UFAB2-1, Box 2, Trial Box 10 |
| UFAB2-1 | 2 | 11 | Shelf UFAB2-1, Box 2, Trial Box 11 |
| UFAB2-1 | 2 | 12 | Shelf UFAB2-1, Box 2, Trial Box 12 |
| UFAB2-1 | 2 | 13 | Shelf UFAB2-1, Box 2, Trial Box 13 |
| UFAB2-1 | 2 | 14 | Shelf UFAB2-1, Box 2, Trial Box 14 |
| UFAB2-1 | 2 | 15 | Shelf UFAB2-1, Box 2, Trial Box 15 |
| UFAB2-1 | 2 | 16 | Shelf UFAB2-1, Box 2, Trial Box 16 |
| UFAB3-1 | 3 | 17 | Shelf UFAB3-1, Box 3, Trial Box 17 |
| UFAB3-1 | 3 | 18 | Shelf UFAB3-1, Box 3, Trial Box 18 |
| UFAB3-1 | 3 | 19 | Shelf UFAB3-1, Box 3, Trial Box 19 |
| UFAB3-1 | 3 | 20 | Shelf UFAB3-1, Box 3, Trial Box 20 |
| UFAB3-1 | 3 | 21 | Shelf UFAB3-1, Box 3, Trial Box 21 |
| UFAB3-1 | 3 | 22 | Shelf UFAB3-1, Box 3, Trial Box 22 |
| UFAB3-1 | 3 | 23 | Shelf UFAB3-1, Box 3, Trial Box 23 |
| UFAB3-1 | 3 | 24 | Shelf UFAB3-1, Box 3, Trial Box 24 |
| UFAB4-1 | 4 | 25 | Shelf UFAB4-1, Box 4, Trial Box 25 |
| UFAB4-1 | 4 | 26 | Shelf UFAB4-1, Box 4, Trial Box 26 |
| UFAB4-1 | 4 | 27 | Shelf UFAB4-1, Box 4, Trial Box 27 |
| UFAB4-1 | 4 | 28 | Shelf UFAB4-1, Box 4, Trial Box 28 |
| UFAB4-1 | 4 | 29 | Shelf UFAB4-1, Box 4, Trial Box 29 |
| UFAB4-1 | 4 | 30 | Shelf UFAB4-1, Box 4, Trial Box 30 |
Please take not that the first level (Unfumigated Room) is defined first in the Location table to determine its LOCID, then the second level follows, so forth and so on.
(b) From an existing database
Some organizations or divisions have already existing database table of seed stocks. From this current seed stock table, a stored procedure can be created which automatically assigns the tray number to the existing accessions. A tray can only hold a maximum of 40 accnos each. From here, the LOCID is generated (incremental), the SNL1ID (for level1) and SNLID2 (for level2) values are manually encoded and SNLID3 is linked to the LOCID of level3.
POPULATING TABLES IN THE DMS
The TRAIT table (Central)
The IMS scales should have an associated trait. The trait should be added in the central Trait table similar to the record in Table 1.2.2.1. Furthermore, that trait id should be assigned to the SEEDSTOCKTRAIT key of the IMS section in the INI file.
Table 1.2.2.1 Details of the TRAIT table
The Scale table (Local)
The scale used by the IMS is the unit of measurement for the amount. Since IMS is a local database, its scales can be stored in the local database with negative scale ids.
For example:
Table 1.2.2.2 Details of the SCALE table
POPULATING THE IMS DATABASE
There are two tables in the IMS database. The IMS_LOT, which stores the GID, scale unit and location ID, is the first table to be populated. After which the IMS_TRANSACTION table, which holds the quantity in storage and the commitment date, is populated next.
The IMS_LOT table
(a) From an Excel File
If the existing seed stock is in Excel, it can be imported to ACCESS database. The IMS_LOT table can be populated as follows: LOCID is selected from the LOCATION table, EID is the GID from the excel file, ETPE is the table that stores the GID (in this case GERMPLSM), STATUS is 0 (active), SOURCE is 0 and USERID is the default USERID. The LOCATION table and excel file is joined by LOCATION.LNAME and the combination of the Shelf, box and file no columns of the excel file.
Here is an example of the SQL statement:
INSERT INTO IMS_LOT ( LOCID, EID, ETYPE, SCALEID, STATUS, SOURCE, USERID )
SELECT LOCATION.LOCID, [03WS Bulk].GID, 'GERMPLSM' AS Expr1, -3 AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS Expr5
FROM [03WS Bulk], LOCATION
WHERE (((LOCATION.LNAME)=CStr([SHELVE])+'-B'+CStr([BOX])+'-T'+CStr([FILE_NO])) AND((LOCATION.LNAME) Is Not Null));
(b) From an existing database
One lot is created for each combination of GID from the current seed stock table, LOCID in the LOCATION table and SCALEID in the SCALE table.
The IMS_TRANSACTION table
After populating the IMS_LOT table, populating the IMS_TRANSACTION table is trouble-free. Append rows in the IMS_TRANSACTION table, setting LOTID to the LOTID in the IMS_LOT table, TRNQTY would be the amount column in the excel file or the current seed stock table (which ever migration method you are using). The amount is set to the actual balance there is in the storage location, therefore it is set to positive. TRNSTAT is set to 1 (committed), USERID is the default USERID and TRNDATE and CMTDATA is the current date.
