The EO_USESA function:

-- Returns the USESA status of an eo, when you pass in an EO_ID.
-- Returns the first non-null value from these fields in this order:
-- eo.eo_interpreted_usesa_status
-- taxon_subnational.state_interpreted_usesa
-- taxon_global.interpreted_usesa
-- d_usesa.usesa_cd
-- If it encounters "No Status" at any level, it will stop there and return .
-- Tyler Christensen, 18 Feb 2004

Run the attached function while logged into SQLPlus as the biotics_dlink user.

Once the function has been created, it can be used in a query. An example follows from Lynn Kutner of how to use the EO_USESA function in a query. This function will use the new USESA fields to calculate whether an individual EO has US federal status.

-------------------------------------------------

SAMPLE SQL for EO with USESA status; includes the eo_usesa function
AUTHOR: Lynn Kutner, NatureServe
DATE: August 24, 2004

SELECT eo.eo_id,
egt.elcode_bcd,
eo.eo_num,
nation.iso_nation_cd nation,
subnation.subnation_code state,
gname.scientific_name gname, egt.g_primary_common_name gcomname,
sname.scientific_name sname, est.s_primary_common_name scomname,
d_usesa.usesa_cd,
substr(taxon_global.usesa_date,1,10) usesa_date,
taxon_global.interpreted_usesa global_interpreted_usesa,
taxon_subnational.state_interpreted_usesa,
eo.eo_interpreted_usesa_status,
eo_usesa(eo_id) eo_usesa_function

FROM eo,
element_subnational est,
element_national ent,
element_global egt,
subnation, nation,
d_usesa,
taxon_global,
taxon_subnational,
scientific_name gname,
scientific_name sname

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
and est.subnation_id = subnation.subnation_id
and ent.nation_id = nation.nation_id
and egt.element_global_id = taxon_global.element_global_id
and est.element_subnational_id = taxon_subnational.element_subnational_id (+)
and egt.gname_id = gname.scientific_name_id
and est.sname_id = sname.scientific_name_id
and taxon_global.d_usesa_id = d_usesa.d_usesa_id (+)
and (taxon_global.interpreted_usesa is not null or taxon_global.d_usesa_id is not null or
taxon_subnational.state_interpreted_usesa is not null or eo.eo_interpreted_usesa_status is not null)