CREATE OR REPLACE FUNCTION est_usesa(est_id_arg IN NUMBER) RETURN VARCHAR2 IS status VARCHAR2(80); BEGIN SELECT decode (STATE_INTERPRETED_USESA, 'LT', 'Listed threatened', 'LT,PDL', 'Listed threatened, proposed for delisting', 'No Status', 'No Status', 'PS', 'Partial Status', '', '') INTO status FROM taxon_subnational ts WHERE ts.element_subnational_id= est_id_arg; IF (status is null) THEN select decode (INTERPRETED_USESA, 'PS', 'Partial Status', 'PS:LE', 'Partial Status: Listed endangered', 'PS:LT', 'Partial Status: Listed threatened', 'PS:LT,PDL' , 'Partial status: Listed threatened, proposed for delisting', '', '') INTO status FROM element_subnational est, element_national ent, taxon_global tg WHERE est_id_arg = est.element_subnational_id AND est.element_national_id = ent.element_national_id AND ent.element_global_id = tg.element_global_id; IF (status is null) THEN SELECT decode (usesa_cd, 'LE', 'Listed endangered', 'LT', 'Listed threatened', 'LE, LT, XN', 'Listed endangered, listed threatened, nonessential experimental population', '', '') INTO status FROM element_subnational est, element_national ent, taxon_global tg, d_usesa WHERE est_id_arg = est.element_subnational_id AND est.element_national_id = 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; END IF; IF (status = 'No Status' or STATUS LIKE 'PS%') THEN status := ''; END IF; RETURN(status); END; /