WHY PERFORM THE QC: During the exchange of ELEMENT_SUBNATIONAL data from a local Network Program database to the central NatureServe database, a record is rejected if the Sname does not (a) match the Gname or (b) one of the names in the Global Synonyms grid.

WHAT THE QUERY DOES: This query finds ELEMENT_SUBNATIONAL records where the Subnational Scientific Name (Sname) is not the same as the Global Scientific Name in the related ELEMENT_GLOBAL record, and is also not included in the set of global synonyms for that ELEMENT_GLOBAL.

ACTION: Check the Sname to make sure it is correct and is a synonym for the same concept as indicated in the parent ELEMENT_GLOBAL. If not, update the Sname or move the subnational data and any EOs to a more appropriate element. If you believe the name is a synonym that needs to be added to the list of global synonyms, notify Central Science Staff by creating a support ticket to request the addition (in Biotics: click “Contact Support” at the bottom of any screen, select “New support ticket” and fill in the form, entering Application: Biotics, Component: Science Request).

SELECT

name_cat.name_type_desc name_type,

informal_tax(egt.element_global_id) informal_tax_grp,

est.element_subnational_id,

egt.elcode_bcd elcode,

sname.scientific_name sname,

gname.scientific_name gname,

DelimList ('SELECT syname.scientific_name

FROM scientific_name syname, synonym_global

WHERE synonym_global.scientific_name_id = syname.scientific_name_id

and synonym_global.element_global_id = ' || egt.element_global_id || 'ORDER BY syname.scientific_name', ', ') AS global_synonyms,

est.s_rank srank,

d_eo_trk.eo_track_status_cd eo_trk,

(Select Count (*) From eo

Where est.element_subnational_id = eo.element_subnational_id ) eos,

nation.iso_nation_cd nation,

subnation.subnation_code subnation,

egt.inactive_ind inactive,

d_maint.maintained_by_status_cd est_maint,

est.element_subnational_ou_uid,

est.element_subnational_seq_uid,

sname.scientific_name_id sname_id,

sname.scientific_name_ou_uid sname_ou_uid,

sname.scientific_name_seq_uid sname_seq_uid

FROM

element_subnational est,

element_national ent,

element_global egt,

scientific_name gname,

scientific_name sname,

nation,

subnation,

d_name_category name_cat,

d_maintained_by_status d_maint,

d_eo_track_status d_eo_trk

WHERE

est.element_national_id = ent.element_national_id

and ent.element_global_id = egt.element_global_id

and egt.gname_id = gname.scientific_name_id

and gname.d_name_category_id = name_cat.d_name_category_id

and est.sname_id = sname.scientific_name_id

and est.subnation_id = subnation.subnation_id

and ent.nation_id = nation.nation_id

and est.d_maintained_by_status_id = d_maint.d_maintained_by_status_id

and est.d_eo_track_status_id = d_eo_trk.d_eo_track_status_id (+)

and name_type_cd in ('A', 'P')

and sname.scientific_name_seq_uid <> gname.scientific_name_seq_uid

and sname.scientific_name_seq_uid not in

(select synname.scientific_name_seq_uid

from scientific_name synname,

synonym_global syn_g

where syn_g.scientific_name_id = synname.scientific_name_id

and syn_g.element_global_id = egt.element_global_id

)

ORDER BY name_cat.name_type_desc, informal_tax(egt.element_global_id),

sname.scientific_name