WHY PERFORM THE QC: During the exchange of ELEMENT_SUBNATIONAL data from a local Network Program database to the central NatureServe database, only locally maintained, active records will be exported from the local database. Records selected by this query in a local database will not be exported and will not be updated in the central NatureServe database.

WHAT THE QUERY DOES: This query finds ELEMENT_SUBNATIONAL records that are not locally maintained (D_MAINTAINED_BY_STATUS.MAINTAINED_BY_STATUS_CD <> 'L') or that are attached to an ELEMENT_GLOBAL record that is Inactive (ELEMENT_GLOBAL.INACTIVE_IND = ‘Y’).

ACTION: Review inactive records to see if they should be active (in Locally Maintained ELEMENT_GLOBAL records uncheck the Inactive box). Review ELEMENT_SUBNATIONAL records that are not locally maintained, including those that are ‘TL’ which will not be exported, to see if they should be changed to ‘L’. If you need assistance with any record that has Maintained By Status = ‘C’ or ‘TC’ that you think should be ‘L’ or is incorrectly Inactive, contact Central Science Staff by creating a support ticket (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 egt.inactive_ind inactive,

d_maint.maintained_by_status_cd est_maint,

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_track.eo_track_status_cd eo_track,

(Select Count (*) From eo

 Where est.element_subnational_id = eo.element_subnational_id ) eos

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_track

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.d_eo_track_status_id = d_eo_track.d_eo_track_status_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 (d_maint.maintained_by_status_cd <> 'L' or egt.inactive_ind ='Y')

ORDER BY egt.inactive_ind, name_cat.name_type_desc, sname.scientific_name