/*   Biotics Update Patch 0001b 02/19/03.   This incorporates changes to correct several
conversion errors.  Please refer to Test Track #2894 for the gory details.
Contact Carol Fogelsong at 703 908 1864 or carol_fogelsong@natureserve.org
if you have any questions regarding this patch.  */
/*  Part #1.  if you want to look over the items before changing them.  Not a bad idea.... */

select 	plant_est.element_subnational_id,
		plant_est.bcd_style_s_rank,
	tsd.d_origin_id,
	tsd.d_regularity_id,
	tsd.d_curr_presence_absence_id
From	(select		est.element_subnational_id,
                        est.bcd_style_s_rank
		from	element_subnational est,
			scientific_name
		where	est.sname_id = scientific_name.scientific_name_id and
			scientific_name.d_name_category_id in (4,5,6,7) and
			Est.bcd_style_s_rank = 'SRD') plant_est,
			taxon_subnatl_dist tsd
	where 	plant_est.element_subnational_id = tsd.element_subnational_id;

/* Part #1.  now perform the update */

update taxon_subnatl_dist tsd 
set 	tsd.d_origin_id = 3,
	tsd.d_regularity_id = 1,
	tsd.rec_last_mod_user = 'TestTrack2894'
where 	tsd.element_subnational_id in (select 	taxon_subnatl_dist.element_subnational_id                		
					from	element_subnational est,
						taxon_subnatl_dist,
						scientific_name
					where	est.sname_id = scientific_name.scientific_name_id and
						scientific_name.d_name_category_id in (4,5,6,7) and
						Est.bcd_style_s_rank = 'SRD' and
						est.element_subnational_id = taxon_subnatl_dist.element_subnational_id);

/* Part #2. This is done for nonvascular plants.  Look over the items before changing them. */

select 	plant_est.element_subnational_id,
		plant_est.bcd_style_s_rank,
	tsd.d_origin_id,
	tsd.d_regularity_id
From	(select		est.element_subnational_id,
                        est.bcd_style_s_rank
		from	element_subnational est,
			scientific_name
		where	est.sname_id = scientific_name.scientific_name_id and
			scientific_name.d_name_category_id in (5,6,19) and
			Est.bcd_style_s_rank = 'SR') plant_est,
			taxon_subnatl_dist tsd	
where plant_est.element_subnational_id = tsd.element_subnational_id;

/* Part #2.  now perform the update */

update taxon_subnatl_dist tsd 
set 	tsd.d_regularity_id = 1,
	tsd.rec_last_mod_user = 'TestTrack2894'
where 	tsd.element_subnational_id in (select 	taxon_subnatl_dist.element_subnational_id                		
					from	element_subnational est,
						taxon_subnatl_dist,
						scientific_name
					where	est.sname_id = scientific_name.scientific_name_id and
						scientific_name.d_name_category_id in (5,6,19) and
						Est.bcd_style_s_rank = 'SR' and
						est.element_subnational_id = taxon_subnatl_dist.element_subnational_id);



/*  Part #3.  Now do this whole thing over for the N-Ranks!  Wheeee! Look over the items before changing them.  Not a bad idea.... */

select 	plant_ent.element_national_id,
		plant_ent.bcd_style_n_rank,
	tnd.d_origin_id,
	tnd.d_regularity_id,
	tnd.d_curr_presence_absence_id
From	(select		ent.element_national_id,
                        ent.bcd_style_n_rank
		from	element_national ent,
			scientific_name
		where	ent.nname_id = scientific_name.scientific_name_id and
			scientific_name.d_name_category_id in (4,5,6,7, 19) and
			Ent.bcd_style_n_rank = 'NRD') plant_ent,
			taxon_natl_dist tnd	
where 	plant_ent.element_national_id = tnd.element_national_id;

/* Part #3.  now perform the update */

update taxon_natl_dist tnd 
set 	tnd.d_origin_id = 3,
	tnd.d_regularity_id = 1,
	tnd.rec_last_mod_user = 'TestTrack2894'
where 	tnd.element_national_id in (select 	taxon_natl_dist.element_national_id                		
					from	element_national ent,
						taxon_natl_dist,
						scientific_name
					where	ent.nname_id = scientific_name.scientific_name_id and
						scientific_name.d_name_category_id in (4,5,6,7, 19) and
						Ent.bcd_style_n_rank = 'NRD' and
						ent.element_national_id = taxon_natl_dist.element_national_id);

/* Part #4. This is done for nonvascular plants.  Look over the items before changing them. */

select 	plant_ent.element_national_id,
		plant_ent.bcd_style_n_rank,
	tnd.d_origin_id,
	tnd.d_regularity_id
From	(select		ent.element_national_id,
                        ent.bcd_style_n_rank
		from	element_national ent,
			scientific_name
		where	ent.nname_id = scientific_name.scientific_name_id and
			scientific_name.d_name_category_id in (4,5,6, 7, 19) and
			Ent.bcd_style_n_rank = 'NR') plant_ent,
			taxon_natl_dist tnd	
where plant_ent.element_national_id = tnd.element_national_id;

/* Part #4.  now perform the update */

update taxon_natl_dist tnd 
set 	tnd.d_regularity_id = 1,
	tnd.rec_last_mod_user = 'TestTrack2894'
where 	tnd.element_national_id in (select 	taxon_natl_dist.element_national_id                		
					from	element_national ent,
						taxon_natl_dist,
						scientific_name
					where	ent.nname_id = scientific_name.scientific_name_id and
						scientific_name.d_name_category_id in (4,5,6, 7, 19) and
						Ent.bcd_style_n_rank = 'NR' and
						ent.element_national_id = taxon_natl_dist.element_national_id);


/***************** update the db version tables ********************************/

INSERT INTO DB_VERSION (application_module, db_version)
VALUES ('BIOTICST', 'Version 1.0001b');

INSERT INTO DB_UPGRADE_HISTORY (application_module, script_name, db_version)
VALUES ('BIOTICST', 'biotics_update_0001b.sql', 'Version 1.0001b');

/************* commit the changes to the database *****************************/

commit;