This query selects all ELEMENT_SUBNATIONAL records that have a NATIONAL origin of “exotic” in your nation, the SUBNATIONAL DISTRIBUTION CONFIDENCE is “Confident,” and the SUBNATIONAL ORIGIN for your jurisdiction is either “Native” or “Unknown/Undetermined.”

ACTION: Review the results and make updates as needed to the ORIGIN value in the TAXON_SUBNATL_DIST distribution grid.

SELECT est.element_subnational_id est_id,

egt.element_global_id egt_id,

egt.elcode_bcd elcode,

name_cat.name_category_desc name_type,

sciname.scientific_name sname,

est.s_primary_common_name scomname,

subnation.subnation_code,

d_origin.origin_desc,

tsd.d_origin_id,

est.s_rank,

/* DISTRIBUTION DATA */

DelimList('SELECT ''ORG: '' || substr(d_origin.origin_desc,1,1) ||

''; REG: '' || substr(d_reg.regularity_desc,1,1) ||

''; CNF: '' || substr(d_conf.dist_confidence_cd,1,3) ||

''; PRS: '' || substr(d_pres.curr_presence_absence_desc,1,1) || (case when

s_dist.d_population_id is not null then

''; POP: '' || substr(d_pop.population_desc,1,1) else null end)

FROM taxon_subnatl_dist s_dist, d_curr_presence_absence d_pres,

d_dist_confidence d_conf, d_origin, d_population d_pop, d_regularity d_reg

WHERE s_dist.d_curr_presence_absence_id = d_pres.d_curr_presence_absence_id (+)

and s_dist.d_dist_confidence_id = d_conf.d_dist_confidence_id (+)

and s_dist.d_origin_id = d_origin.d_origin_id (+)

and s_dist.d_population_id = d_pop.d_population_id (+)

and s_dist.d_regularity_id = d_reg.d_regularity_id (+)

and s_dist.element_subnational_id = ' || est.element_subnational_id, ' xx ') AS

distribution_data

FROM element_global egt,

element_national ent,

element_subnational est,

scientific_name sciname,

d_name_category name_cat,

taxon_subnatl_dist tsd,

taxon_natl_dist tnd,

d_origin,

subnation

WHERE est.sname_id = sciname.scientific_name_id

and sciname.d_name_category_id = name_cat.d_name_category_id

and egt.element_global_id = ent.element_global_id

and ent.element_national_id = est.element_national_id

and est.element_subnational_id = tsd.element_subnational_id

and est.subnation_id = subnation.subnation_id

and tsd.d_origin_id = d_origin.d_origin_id

and tsd.d_origin_id <> 2

and tsd.d_dist_confidence_id = 1

and ent.element_national_id = tnd.element_national_id

and (ent.element_national_id in (select tnd.element_national_id

from taxon_natl_dist tnd where tnd.d_origin_id = 2)

and ent.element_national_id not in (select tnd.element_national_id

from taxon_natl_dist tnd where tnd.d_origin_id <> 2))

and egt.inactive_ind = 'N'