CIMMYT Nursery System
From ICISWiki
Contents |
Introduction
Currently, CIMMYT uses Wheat Internation Nursery System (WINS) to manage International Nursery requests from its many cooperators. It also manages the cooperator's information and shipping details for each requests. A similar effort is done for Maize. Since both system share most functionalities, it was then ideal to create a new system that will support all these functionalities.
Current Workflow
- List of Cooperators and Institutions are entered into the database.
- Announcement of available nurseries for the next cycle is announced.
- Requests are received and recorded.
- Final Sets of Nurseries with complete germplasm entries is received from Maria Luisa.
- Initial Distribution List is created based from the requests and preliminary assignments done by Ana Luisa.
- Initial Distribution List is made available to the breeders and Tom Payne for modification.
- Modifications from the Initial Distribution List made by breeders and Tom Payne are recorded.
- Modified Distribution List is discussed in the Distribution Meeting.
- Finalize Distribution List.
- Occurrence assignment is done automatically.
- Phyto-sanitary Requirements and other certifications are satisfied.
- Prepare shipment
Suggested Web Workflow
- List of Cooperators and Institutions are entered into the database.
- Announcement of available nurseries for the next cycle is announced.
- Requests are received and recorded.
- Final Sets of Nurseries with complete germplasm entries is received from Maria Luisa.
- Initial Distribution List is created based from the requests and preliminary assignments done by Ana Luisa.
- Initial Distribution List is made available via web to the breeders and Tom Payne for modification.
- Tom Payne and breeders can modify the distribution list.
- Distribution List will show red colored value for the amount requested by a cooperator if the breeders assign diferent values. Value can be clicked to show each breeder’s modification.
- Finalize Distribution List in the Distribution Meeting.
- Occurrence assignment is done automatically.Phyto-sanitary Requirements and other certifications are satisfied.
- Prepare shipment
Development
System Schema
The system will be using tables from Data Management System ,Inventory Management System and new tables not in the current ICIS 5.6 schema
OLD SCHEMA
Cooperators
Nurs
Seed
NEW SCHEMA
DMS Tables
PERSONS,INSTITUT and ADDRESS tables from the DMS will be used to store information on Cooperator's details, institute and addresses respectively. All these information were previously stored in the WINS' Coop table.
Some information on COOPERATORS details cannot be directly mapped to the PERSONS table thus, DMSATTR will be used.The DMSATTR uses USER DEFINED variables which are stored in the UDFLDS table. The values for this USER DEFINED variables will then be stored in the DMSATTR.
Following are the information in the Cooperators table that cannot be directly mapped to the PERSONS table. The definition of the information is stored in the UDFLDS and the value will be stored in the DMSATTR table.
UDFLDS table
| FLDNO | FTABLE | FTYPE | FCODE | FNAME | FFMT | FDESC |
|---|---|---|---|---|---|---|
| 20001 | PERSON | ATTRIBUTE | OPDATE_BW | BREAD WHEAT OPTIMAL SOWING DATE |
- | Optimal Sowing date for bread wheat |
| 20002 | PERSON | ATTRIBUTE | OPDATE_TR | TRITICALE OPTIMAL SOWING DATE | - | Optimal Sowing date for Triticale |
| 20003 | PERSON | ATTRIBUTE | OPDATE_BA | BARLEY OPTIMAL SOWING DATE | - | Optimal Sowing date fro Barley |
| 20004 | PERSON | ATTRIBUTE | OPDATE_MA | MAIZE OPTIMAL SOWING DATE | - | Optimal Sowing date for Maize |
| 20005 | PERSON | ATTRIBUTE | SENDVIA | SEND VIA ANOTHER PERSON | - | Person who will receive a transaction on behalf of another |
Sample DMSATTR Entry
| DMSATID | DMSATYPE | DMSATAB | DMSATREC | DMSATVAL |
| 62732 | 20001 | PERSON | 1 | NOV |
| 62733 | 20002 | PERSON | 1 | DEC |
| 62734 | 20004 | PERSON | 1 | JUL |
This sample shows that the person in the PERSON table with personid = 1 has Nov as his desired optimal date for sowing bread wheat. He also has Dec for optimal sowing date for Triticale and July for Maize. With this structure, the flexibility of adding new optimal sowing dates for other crops will be achieved.
IMS Tables
IMS Tables will be used to store information previously held in the NURS and SEED table of the WINS system.
The IMS_LOT will be used to store information on nurseries or germplasms.
| Columns - Long Name (Name) | Description |
SAMPLE VALUES (NUSERY) |
SAMPLE VALUES (GERMPLASM) |
|---|---|---|---|
| LOT_ID (LOTID) | The unique ID for each inventory lot. A lot could a nursery or a germplasm | 10 (nursery Number 10) | 11 |
| USER_ID (USERID) | ID of the user defining the current Lot. Links to the USERS table | 3 (user ID) | 3 |
| 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. | LISTNMS or STUDY | GERMPLSM |
| ENTITY_ID (EID) | Entity identification number belonging to the Lot. Eg GID for seed inventory. This field links to the table identified in ETYPE. | 40280 (LISTID OR STUDYID) | GID |
| LOCATION_ID (LOCID) | Location identification number links to the LOCATION table in the IMS database. | 15994 (CIMMYT Location Number) | 4755013 |
| 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 | | |
| LOT_STATUS (STATUS) | Lot Status (0=Active or 1=Closed) | 1 (Active) | 1 |
| SOURCE (SOURCEID) | LOT_ID of the source lot if this lot is derived from another, else zero | 0 | 10 |
| COMMENTS (COMMENTS) | Description of lot or other notes | | GID 4755013 is one of the lines in Nursery 10 |
| WEIGHT | Weight of the Nursery or Germplasm | 9.0 | |
| NUMBER_OF_ENVELOPES | Number of Envelopes per plot | 6 | |
| SHIP_DATE | Approximate date ready for shipment | 20110220 (Feb. 20, 2011) | 20110320 (Mar. 20, 2011) |
The IMS_TRANSACTION table will be used to keep tract of transactions Deposits and Requests.
| Columns - Long Name (Name) | Description |
SAMPLE VALUES(DEPOSIT) | SAMPLE VALUES (INTERNATION NURSERY REQUEST) | SAMPLE VALUES (GERMPLASM REQUEST) |
|---|---|---|---|---|
| TRANSACTION_ID (TRNID) | Unique transaction identification number | 1 | 2 | 3 |
| USER_ID (USERID) | ID of the user processing the current transaction. Links to the USERS table | 3 (User ID) | 3 | 3 |
| LOT_ID (LOTID) | Identifies lot for this transaction. Links to LOT table | 10 (Nursery ID) | 10 | 11 |
| TRANSACTION_DATE (TRNDATE) | Date of the current transaction (ICIS date format YYYMMDD) | 20110219 (Feb. 19, 2011) | 20110221 (Feb. 21, 2011) | 20110221 (Feb. 21, 2011) |
| TRANSACTION_STATUS (TRNSTAT) | Transaction status: 0=Anticipated (Deposit or Reserved), 1=Confirmed (Stored or Retrieved), 9=Cancelled Transaction | 1 (Confirmed) | 0 (Reserved) | 0 (Reserved) |
| TRANSACTION_QUANTITY (TRNQTY) | Quantity involved in transaction: Positive (+) for deposits, negative (-) for withdrawals | 95 (Deposit) | -10 (Request) | -5 (Request) |
| COMMENTS (COMMENTS) | Description of transaction or other notes | | | Miscellaneous Request for a single Germplasm |
| COMMITMENT_DATE (CMTDATE) | Commitment date for anticipated transactions, Zero for indefinite | | | |
| SOURCE_TYPE (SOURCETYPE) | The type of the transaction source (e.g. LIST or STUDY) | LIST | LIST | GERMPLSM |
| SOURCE (SOURCEID) | The particular ID of the source. (e.g. LISTID or STUDYID) | LISTID | LISTID | GID |
| SOURCE_REC_ID (RECORDID) | The particular record where the transaction is obtained (e.g. specific LRECID in the LIST or STUDYID in the STUDY) | 40280 | 40280 | 4755013 |
| PREVIOUS_AMOUNT (PREVAMOUNT) | The previous amount if a transaction was corrected | 0 | | |
| PERSON_ID (PERSONID) | The ID of the requestor/donor. Links to the PERSONS table. | | 7 (Cooperator's ID) | 7 (Cooperators ID) |
| REQUEST_ID | Request ID if the transaction type is REQUEST; is automatically generated; | | 1 | 1 |
Note:
| New Field added |
NEW TABLES
SHIPPING
| COLUMNS | DESCRIPTION | Notes |
| SHIPPING_ID | Unique transaction identification number | |
| REQUEST_ID | Unique Request ID | |
| FINAL_DESTINATION | Final Destination Address ID | |
| SHIP_METHOD | Method for sending the shipment (courier, handcarry etc) | |
| TRACKING_ID | Tracking number of DHL or FedEx shipment | |
| SHIPMENT_DATE | Date of Shipment | |
| RECEIPT_DATE | Date of Receipt | |
| WEIGHT | Total Weight of shipment | |
| PACKAGE_COUNT | Number of boxes/packages | |
| SMTA_FILE | path/filename of SMTA | |
| IMPORT_FILE | path/filename of Import Permit | |
| PAYMENT_MODE | Mode of Payment | |
| COMMENT | Comments / Remarks | |
Prototype Screens
The prototype has the following screens:
1. Cooperator Screen - where user can add, update or view cooperator details
2. Institute Screen - where user can add, update or view institute details
3. Nursery Screen - where user can add, update or view nursery details for a specific cycle
4. Request Screen - where user can add or view Nursery requests from cooperators
5. Distribution List Screen - where user can view or modify the current number of sets assigned to cooperators
6. Assignment Screen - where user can view a summary of the reserved and available sets of nurseries.
7. Shipment Screen - where user can view shipment summaries, shipment status and other shipment details.
Other screens to consider :
- Web Cooperator Request screen where cooperator can view and choose nurseries and germplasm to request
- Crops Screen - where user can add, update or delete crops
- Cooperator Role/Access screen - where user can assign or modify cooperator roles, levels and access details
- Reports Screen - where user could generate or view report summaries
