CREATE OR REPLACE PROCEDURE Update_ims_lot_neg_gid IS

/*
16 Nov 2004
a procedure to update the ims_lot and accession_stored tables
*/


  xAccno NUMBER;
  xCropyear VARCHAR2(6);
  xGID		NUMBER;
  xCGID		NUMBER;


CURSOR GET_list IS
SELECT DISTINCT a.accno, a.cropyear, a.gid, b.cgid
FROM 	accession_stored a, TBL_GENERATION b
WHERE 	a.accno=b.accno AND a.cropyear=b.cropyear
	AND a.gid=b.gid
	AND a.gid<0 AND b.cgid>0;

BEGIN



OPEN GET_list;

	 LOOP
	 	  FETCH GET_list INTO xAccno,  xCropyear,  xGID,  xCGID	;
		  EXIT WHEN GET_list%NOTFOUND;
		  

		  /*
		  * update accession stored gid and ims_lot
		  * where gid is not positive
		  */
		  
		  UPDATE accession_stored
		  SET gid=xCgid
		  WHERE accno=xAccno AND
		  		cropyear=xCropyear AND
				gid=xGID;
				
		
		  UPDATE IMS_LOT
		  SET eid=xCgid
		  WHERE eid=xGID;
				
		  

	 END LOOP;
	 
CLOSE GET_list;


END;
/