/* This script will allow you to correct the problem of eos having eo_rank_dates in the future.
First you need to create a working list of EOs that have the problem dates.  In Query Builder, run the
following query and save it to an EO working list called 'fix EO Rank Date-bt2070' Name is case sensitive. */

select eo_id, eo_rank_date
from eo
where to_char(EO.EO_RANK_DATE) like '201%' 
or to_char(EO.EO_RANK_DATE) like '202%'
or to_char(EO.EO_RANK_DATE) like '203%' 
or to_char(EO.EO_RANK_DATE) like '204%' 
or to_char(EO.EO_RANK_DATE) like '2005%' 
or to_char(EO.EO_RANK_DATE) like '2006%' 
or to_char(EO.EO_RANK_DATE) like '2007%' 
or  to_char(EO.EO_RANK_DATE) like '2008%' 
or to_char(EO.EO_RANK_DATE)  like '2009%'

/* If you didn't get any rows back, you can skip this entire file and go about the rest of your life.
But, if you DID get rows back, here's what you do to correct them.  You must be logged in as biotics_user, 
not as system or sys. Please note that each time you run an update, you'll get back the message saing 'xxxx rows processed'.
This will be the number of items on your working list and will be the same number with each query.  This does
not correspond to the actual number of items changed, just the number of items evaluated. */
/* first you must set your date format so that it will match what's in the update statement */

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';

/* now you can copy and paste the following update statements into SQL+ and run them. */

update eo
set eo_rank_date = replace(eo_rank_date, '201', '191'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '202', '192'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '203', '193'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '204', '194'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '205', '195'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '206', '196'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '207', '197'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '208', '198'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '209', '199'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '2005', '1905'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '2006', '1906'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '2007', '1907'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);

commit;


update eo
set eo_rank_date = replace(eo_rank_date, '2008', '1908'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);
commit;

update eo
set eo_rank_date = replace(eo_rank_date, '2009', '1909'),
rec_last_mod_user = 'bt2070'
where 
eo.eo_id in (select wled.data_id
		from working_list_eo_data wled,
			working_list wl
		where	wl.working_list_name = 'fix EO Rank Date-bt2070' and
			wl.working_list_id = wled.working_list_id);

commit;

/* now go back to Tracker and perform the query at the top of this file.  You should get no rows returned. */