From ICISWiki

Back to ICIS 5.6 Technical Documentation Main Window  Back to ICISWiki TechDoc Main Window

Summary of Changes/New Features

Seed Acquisition Workflow

Implementation in GRIMS

Seed Multiplication Workflow

Implementation in GRIMS 

Requesting GID for Newly Harvested Seeds

Refering to the seed multiplication workflow above, GID is necessary for newly harvested seeds, before these seeds are stored in the holding room.

 1. The system shows the number of harvested seeds that are ineligible for storage due to null GID, and

 2. prompts the user to request GIDs from the GRC Administrator.

Clicking on the Request for GID button automatically sends an email notification to the GRC Administrator and attaches a list of accessions for GID assignment.

Take note that the GRC Administrator's email address is indicated in the INI file under [GRIMS] section, GRCADMIN key

Exporting Accessions for GID Assignment to a SetGen List

1. A menu item is added for Exporting/Importing Accessions for GID Assignment
   

1. The upper window on the form displays records for GID assignment.
2. The Export button inserts records to a Setgen list.

Viewing Exported Data in Setgen

Exported records for newly harvested seeds with null GID are exported to a Setgen List with:

1.  ListName equals the purpose plus the cropyear and the suffix '_SOURCE' (purpose+cropyear+"_SOURCE") to indicate that this list is the starting point for generating GIDs (in Setgen, it is called advancing a line one generation).
2. For generating GIDs, the ListName is  the purpose plus the cropyear (purpose+cropyear).

Generating GIDs using the  minus ( - ) button in SetGen

Generating GIDs for newly harvested seeds is done using the minus (-) button in SetGen

Using the germplasm bank accession ID as the name and the cropyear as suffix, the system will assign this combination as the designation of the generated GIDs.

Set entry code to the entries in the entry code column of the source list to carry the same plot numbers of each accessions.

Importing GIDs Assigned to Accessions from a SetGen List

After generating GIDs using Setgen, the administrator publishes(uploads) these germplasm records to the central ICIS database for public viewing (hence,  positive GIDs are generated for these accessions).
Clicking on the Import button adds the GID to the newly harvested seeds.

Going back to Seed Multiplication -> Field Management -> Store Harvest in Holding Room, Summary on Samples Ready for Storage has entries and Viewing List is enabled. The Store Harvest button is also enabled, which allows the user to store newly harvested seeds in the holding room.

Using the ICIS Workbook for Morphological and Agronomic Observation

Viewing Passport Information, including Collecting Mission Reports

Documentations

Work Around/Handling Application Errors

Unique Constraint Error when updating PASSPORT1 table

Diagnosis

Updating of passport information updates PASSPORT1 table which activates the trigger UPDATE_PASSPORT1_TGR, this trigger uses the UPD_NAMES procedure. This procedure inserts a row in the I_LOCAL.NAMES table.The sequence names_seq is used in the NAMES table as an autonumber field for NAMES.NID.
When the current value of names_seq is greater than the minimum value of NID, the error unique constraint occurs.

Solution

1. Check the minimum value of NID
   select min(nid) from names
   
2. Check the current value of the sequence names_seq
   select names_seq.nextval from dual (Note : you cannot execute currval without executing this first)
   select names_seq.currval from dual
   
3. Compare the two values (min(NID) and names_seq.currval)
   
4. if names_seq.currval > min(nid), drop the sequence
   drop sequence names_seq
   
5. Re-create the sequence
   create sequence names_seq
   start with <min(nid)-1>
   increment by -1;
   
   

Unable to assign alternate plot numbers

Diagnosis

Currently, GRIMS is unable to assign alternate plot numbers.

Solution

1. Get excel file from genebank manager(assigning of alternate plot numbers is done in IRGCIS)
2. Convert this file to a .csv --> from MSAccess, upload the excel file, then export this file to .csv (comma separated values file)
3. Create a temp table in Oracle using the following sql statement :
   create table temp_table
   (accno number, cropyear varchar2(6), purpose_plant varchar2(4));
   
4. Create a .CTL file for loading the data to the temp_table.
   Example CTL file below :
   load data
   infile '<full file path of the .csv file>'
   append into table temp_table
   fields terminated by "," optionally enclosed by '"'
   (ACCNO,
   CROPYEAR,
   PURPOSE_PLANT)
   
5. Use sqlloader to import the csv file to temp_table using the following function (Note: run this function on the machine where SQLLoader is installed) :
   sqlldr80 <username/password of database administrator> control=<full file path of .CTL file> log=<file path where you want to save the log file>
6. Update SAMPLES_PLANTED table using the sql below:
   update samples_planted a
   set a.plotno= (select b.plotno from temp_table b
   where a.accno =b.accno and a.cropyear=b.cropyear
   and a.purpose_plant=b.purpose_plant)
   where (a.accno, a.cropyear, a.purpose_plant) in
   (select accno, cropyear, purpose_plant from temp_table);
   
   

Assigning FAO/MLS Dates

Diagnosis

Currently, there are no written guidelines on how to designate or assign FAO/MLS dates

Solution

by NRSHamilton

• Agreement with FAO
  The agreement with FAO came into force in 1994 and has now lapsed, replaced with our agreement with the Governing Body of the Treaty. Our agreement with the GB came into force on 14 January 2007, and therefore that is the effective date of expiry of the FAO agreement. Our legal commitments with the Treaty are dependent on FAO status, and therefore it was important to keep the FAO status updated up to 14 Jan 2007. However, since the agreement has lapsed, we should not be assigning any new FAO dates after 14 Jan 2007.
  
• What is MLS?
  The legal procedure for the Treaty and Parties is:
  At first, the Treaty text is initially just a proposal, not in force and not legally binding. It is opened for signature for a limited period of time. By signing, a country gives a non-binding indication that it agrees in principle with the text and it will start the political process of debate within the country with the intention of becoming a Party. The Treaty comes into force only after 40 countries sign. Thus the primary purpose of signing is to make the Treaty come into force, and is not associated directly with becoming a Party.
  Once the Treaty is in force, a country becomes a Party by ratifying, accepting, approving or acceding. It becomes a Party 90 days after depositing the instrument of ratification, acceptance, approval or accession with the FAO. There is a small procedural difference: if a country has not signed (through Article 25), it cannot ratify, accept or approve; accession is the only way it can become a Party. Countries that have signed can ratify, accept or approve – the difference between these three is only a matter of legal wording allowed by the government of the country. For all practical purposes ratification, acceptance, approval and accession are synonyms – there are absolutely no differences between them in terms of the rights or obligations of the country under the Treaty.
  
• Guidelines on Assigning FAO/MLS Dates
  (Say ASS DATE = the date on which an ACCNO is assigned = accession date, perhaps months or years after ACQ DATE, after the incoming sample completes all its initial tests)
  
  • FOR ACCESSIONS WITH ACQ DATE < 19940101
    

1. FAO date=the later of
   a. 19940914, the date on which the FAO agreements came into force, or
   b. ASS DATE (or in practice the date on which GRC reviewed FAO status of newly acquired samples)
   c. If FAO date is still null as of 20070114, it will remain permanently null
   
2. MLS date=the later of
   a. 20040629, the date on which the Treaty came into force, or
   b. ASS DATE
   

• • FOR ACCESSIONS WITH 19940101 < ACQ DATE < 20070114
    

1. FAO date = the date on which the country gave its effective permission (or ASS DATE if later)
   a. Explicit permission required for FAO DATE < 20040629
   b. Permission not denied for FAO DATE ≥ 20040629
   c. Permission initially denied, subsequently given
   A common case is that countries deny permission until they become Party to the Treaty. In this case, FAO date = date on which the country became Party to the Treaty.
   d. Null if no permission
   e. If FAO date is still null as of 20070114, it will remain permanently null
   
2. MLS date = the later of
   a. 20040629, the date on which the Treaty came into force, or
   b. FAO date
   c. If FAO date is still null as of 20070114 (which will be the case if ASS DATE > 20070114 or permission to designate is still denied), MLS date = the date on which the country gave its effective permission (or ASS DATE if later)
   Including MLS date = the date on which the country became Party to the Treaty
   

• • FOR ACCESSIONS WITH ACQ DATE >= 20070114
    

1. FAO date null (for the reason explained above)
   
2. MLS date:
   a. For materials received with SMTA or with MTA/letter/contract allowing distribution under the MLS, MLS date = ASS DATE
   b. For materials received with MTA not allowing distribution under the MLS, MLS date = null

Use Cases Data Models and Schemata

PROCEDURES

FUNCTIONS

GET_REMNANT_GID

Get_Station_Name

TRIGGERS

INSERT_ACCESSION_STORED_TGR

 BEFORE INSERT ON GBUSER.ACCESSION_STORED REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE 
  xGID NUMBER;
  xLot NUMBER;

  xGID:= :NEW.gid;
  XUSERID:=GET_ICISUSERID(USER);
  select accession_stored_seq.nextval into :new.acs_id from dual;--adds acs_id per new record   
  IF xGID > 0 THEN --GID is positive, insert records in IMS_LOT tables
     xLot:=NVL(Get_Lotid(xGID,-1003),-1);

        --insert into ims_lot where scaleid is BULK  
   	 INSERT INTO i_local.IMS_LOT

        --insert into ims_lot where scaleid is APACk (aluminum packet (count))

     xLot:=NVL(Get_Lotid(xGID,-1004),-1);

        --insert into ims_lot where scaleid is BASE

  END IF; 

The IRRI-GRIMS Application

Previous Version (5.4)