WHY PERFORM THE QC: During the exchange of ELEMENT_SUBNATIONAL data from a local Network Program database to the central NatureServe database, records with a subnation value that does not match the exporting installation's subnation will be rejected.

WHAT THE QUERY DOES: This query finds ELEMENT_SUBNATIONAL records where the subnation (in ELEMENT_SUBNATIONAL.SUBNATION_ID) does not match your installation’s subnation.

ACTION: Check these records to see if the subnation entry is correct. If not, create a new ELEMENT_SUBNATIONAL record with the same parent ELEMENT_NATIONAL record but with the correct subnation, copy the data from the incorrect record and reparent any EOs, and then delete the incorrect record.

SELECT est.element_subnational_id,

egt.elcode_bcd elcode,

nation.iso_nation_cd nation,

subnation.subnation_code subnation,

name_cat.name_type_desc name_type,

sname.scientific_name sname,

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,

d_maint.maintained_by_status_cd est_maint,

egt.inactive_ind inactive

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 subnation.subnation_code <> (select substr(org_unit_code, 0, 2) from org_unit where org_unit_id = (select system_option_value from system_option where system_option_keyword = 'ORG_UNIT_ID'))

ORDER BY subnation.subnation_code, name_cat.name_type_desc, sname.scientific_name