This query selects EO records with “Last Observation Date” (LASTOBS) values that potentially need review. 

ACTION: Please review the values in the EO LAST_OBS_DATE field and make updates as needed. Ideally, dates should be entered with the first four characters as a numerical year; this allows users to “substring” the LAST_OBS_DATE obtain the year the EO was last observed. Please also see the Last Observation Date help topic.

SELECT eo.eo_id, egt.elcode_bcd, egt.element_global_id,

eo.eo_num, sn.subnation_code,

gname.scientific_name gname, egt.g_primary_common_name gcomname,

egt.g_rank, egt.rounded_g_rank,

est.s_rank, est.rounded_s_rank,

eorank.basic_eo_rank_cd,

eo.last_obs_date

FROM eo, element_subnational est,

element_national ent, element_global egt,

subnation sn, scientific_name gname,

d_id_confirmed id, d_basic_eo_rank eorank

WHERE eo.element_subnational_id = est.element_subnational_id

and est.element_national_id = ent.element_national_id

and ent.element_global_id = egt.element_global_id

/* excludes inactive EGT */

and egt.inactive_ind = 'N'

/* zoological or botanical */

and egt.gname_id = gname.scientific_name_id

and gname.d_name_category_id in (1, 2, 3, 4, 5, 6, 7, 19)

/* not falsely indentified criteria */

and eo.d_id_confirmed_id = id.d_id_confirmed_id (+)

and (id.id_confirmed_cd <> 'N' or id.id_confirmed_cd is null)

and eo.D_BASIC_EO_RANK_ID = eorank.D_BASIC_EO_RANK_ID (+)

and est.subnation_id = sn.subnation_id

and eo.last_obs_date is not null

and (( nvl(substr(eo.last_obs_date, 1, 1),'*') not in ('1', '2')

or nvl(substr(eo.last_obs_date, 2, 1), '*') not in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', '-', '?', 'X', 'x')

or nvl(substr(eo.last_obs_date, 3, 1), '*') not in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', '-', '?', 'X', 'x')

or nvl(substr(eo.last_obs_date, 4, 1), '*') not in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', '-', '?', 'X', 'x') )

or ( (nvl(replace(replace(replace(replace(substr(eo.last_obs_date,1,4),'-','0'),'?','0'),'X','0'),'x','0'),'1') > to_char(sysdate,'YYYY'))

or (nvl(replace(replace(replace(replace(substr(eo.last_obs_date,1,4),'-','0'),'?','0'),'X','0'),'x','0'),'1') < '1700')

)

)

/* ONLY edit the following with the phrase(s) used by YOUR program when there is no LASTOBS date if they are not included in the sql below */

and eo.last_obs_date not in ('NONE', 'None', 'NO DATE', 'No Date', 'No date', 'no date', 'ND', 'nd', 'UNKNOWN', 'Unknown', 'unknown', 'UNK', 'XXXX-XX-XX', '9999-99-99', '????-??-??', '????', '----', '----------', '0000', '0000-00-00')