Notification about an important update made to USESA (U.S. Endangered Species Act) domain values
USESA Codes, Descriptions, and Display Values in the table D_USESA have been updated to more accurately reflect the syntax of the statuses assigned by USFWS and USNMFS. This domain table is referenced by the TAXON_GLOBAL table only.
Fields in D_USESA which are affected:
- D_USESA.USESA_CD
- D_USESA.USESA_DESC
- D_USESA.DISPLAY_VALUE
Most important: "Listed" and "L" have been removed from the Endangered and Threatened values in the fields USESA_CD, USESA_DESC, and DISPLAY_VALUE. So:
- "Listed Endangered" and "LE" are now "Endangered" and "E"
- "Listed Threatened" and "LT" are now "Threatened" and "T"
Queries or reports that depend specifically on these values need to be altered. See below for instructions for searching for views that depend on this table.
Codes in the SEARCH_VALUE field still have "L" so that this field can be used to find all taxa which have federal protection easily (i.e., like '%L%').
In addition, minor changes have been made to values in USESA_DESC and DISPLAY_VALUE for consistency of form and abbreviation.
For a full list of the new values, see the Help Topic: USESA Status
Interpreted USESA fields
STATE INTERPRETED USESA FIELDS ARE NOT AFFECTED BY THIS CHANGE.
Note that we will be making the same change (removal of "L") to values stored in the field Interpreted USESA in the global and national tables (TAXON_GLOBAL.INTERPRETED_USESA and TAXON_NATIONAL.INTERPRETED_USESA). These updates may take a few weeks to appear in your local instance as part of ongoing Central to Local data exchange.
Calculated USESA
The Calculated USESA field will return the new codes when the result of the calculation is the value in D_USESA.USESA_CD (from TAXON_GLOBAL.D_USESA_ID).
To determine which of your local views use the D_USESA table:
- Within Query Builder, run the following query:
Select view_name, text from all_views where owner in ('BIOTICS_USER') and view_name in (select name from all_dependencies WHERE type = 'VIEW' AND referenced_owner = 'BIOTICS_USER' AND referenced_name = 'D_USESA') - Select HTML for the Query Output and open the results in a new window.
- Then use CTL-F to search for D_USESA in the views. It may also be useful to search specifically for D_USESA. USESA_CD, D_USESA. USESA_DESC, D_USESA. DISPLAY_VALUE as criteria in CASE statements and WHERE clauses. If so, you may need to address this changes. If you are using D_USESA.D_USESA_ID, this should not be an issue, as those values have not changed.