The EGT_USESA function:

-- Returns the USESA status of an EGT, when you pass in an element_global_id.
-- Returns the first non-null value from these fields in this order:
-- taxon_global.interpreted_usesa
-- d_usesa.usesa_cd
-- If it encounters "No Status", 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 EGT_USESA function in a query. This function will use the new USESA fields to calculate whether a global element has US federal status.

SAMPLE SQL for ELEMENT_GLOBAL records with USESA status;
includes the egt_usesa function
includes all element_global USESA fields
AUTHOR: Lynn Kutner, NatureServe
DATE: August 24, 2004

SELECT egt.element_global_id,
egt.elcode_bcd,
gname.scientific_name gname, egt.g_primary_common_name gcomname,
d_usesa.usesa_cd,
substr(taxon_global.usesa_date,1,10) usesa_date,
taxon_global.interpreted_usesa global_interpreted_usesa,
taxon_global.usesa_com,
egt_usesa(egt.element_global_id) egt_usesa_function

FROM
element_global egt,
d_usesa,
taxon_global,
scientific_name gname

WHERE
egt.element_global_id = taxon_global.element_global_id
and egt.gname_id = gname.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)