/*This query returns USESA and related fields by Element ID, ELCODE, and Scientific Name. It lists the USESA Synonym, if one exists, but otherwise leaves that column blank.*/

SELECT
ELEMENT_GLOBAL.ELEMENT_GLOBAL_ID,
ELEMENT_GLOBAL.ELCODE_BCD,
SCIENTIFIC_NAME.SCIENTIFIC_NAME,
D_USESA.USESA_CD,
d_usesa.display_value USESASTATUS,
TAXON_GLOBAL.INTERPRETED_USESA,
substr(TAXON_GLOBAL.USESA_DATE,1, 10) usesa_date,
substr(TAXON_GLOBAL.INTERPRETED_USESA_DATE,1, 10) int_usesa_date,
usesa_names.scientific_name usesa_syn,
D_FWS_REGION.DISPLAY_VALUE FWSREGION,
TAXON_GLOBAL.general_status_com,
TAXON_GLOBAL.usesa_com


FROM
ELEMENT_GLOBAL,
SCIENTIFIC_NAME,
D_USESA,
D_FWS_REGION,
TAXON_GLOBAL,
(select scientific_name.scientific_name,
sg.element_global_id

from synonym_global sg,
scientific_name

where sg.usesa_synonym_ind = 'Y' and
sg.scientific_name_id = scientific_name.scientific_name_id)
usesa_names

 WHERE
ELEMENT_GLOBAL.ELEMENT_GLOBAL_ID = TAXON_GLOBAL.ELEMENT_GLOBAL_ID
and SCIENTIFIC_NAME.SCIENTIFIC_NAME_ID = ELEMENT_GLOBAL.GNAME_ID
and TAXON_GLOBAL.D_FWS_REGION_ID = D_FWS_REGION.D_FWS_REGION_ID
and TAXON_GLOBAL.D_USESA_ID = D_USESA.D_USESA_ID (+)
and D_FWS_REGION.FWS_LEAD_REGION_CD is not null
and element_global.element_global_id = usesa_names.element_global_id (+)