-- Returns the USESA status of an ENT, when you pass in an element_national_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 following while logged into SQLPlus as the biotics_dlink user:

CREATE OR REPLACE FUNCTION ent_usesa(ent_id_arg IN NUMBER)
RETURN VARCHAR2
IS status VARCHAR2(20);
BEGIN
SELECT tg.interpreted_usesa
INTO status
FROM element_national ent, taxon_global tg
WHERE ent_id_arg = ent.element_national_id
AND ent.element_global_id = tg.element_global_id;
IF (status is null)
THEN SELECT usesa_cd
INTO status
FROM element_national ent, taxon_global tg, d_usesa
WHERE ent_id_arg = ent.element_national_id
AND ent.element_global_id = tg.element_global_id
AND tg.d_usesa_id = d_usesa.d_usesa_id;
END IF;
IF status = 'No Status'
THEN status := '';
END IF;
RETURN(status);
END;
/