SELECT
egt.element_global_id egt_id,
est.element_subnational_id est_id,
egt.elcode_bcd,
nation.iso_nation_cd natn,
subnation.subnation_code subn,
(select clstat.classification_status_desc from d_classification_status clstat
where clstat.d_classification_status_id = egt.d_classification_status_id) class,
(select categ.name_category_desc from d_name_category categ where categ.d_name_category_id = sn.d_name_category_id) category,
sn.d_name_category_id,
gname.scientific_name gname,
sn.scientific_name sname,
est.s_primary_common_name scomname,
egt.g_rank,
egt.rounded_g_rank rnd,
est.s_rank srank,
d_dist_confidence.dist_confidence_desc dist_confidence,
(SELECT d_eo_track_status.eo_track_status_cd FROM d_eo_track_status
WHERE est.d_eo_track_status_id = d_eo_track_status.d_eo_track_status_id ) eo_track,
(SELECT COUNT (*)
FROM EO
WHERE EST.ELEMENT_SUBNATIONAL_ID = EO.ELEMENT_SUBNATIONAL_ID ) AS tot_EOS,
(SELECT COUNT (*)
FROM EO
WHERE EST.ELEMENT_SUBNATIONAL_ID = EO.ELEMENT_SUBNATIONAL_ID
and (eo.d_id_confirmed_id is null or eo.d_id_confirmed_id != 1) ) AS eos_w_ident_conf_y_ques_null,
(SELECT COUNT (*)
FROM EO
WHERE EST.ELEMENT_SUBNATIONAL_ID = EO.ELEMENT_SUBNATIONAL_ID
and eo.d_id_confirmed_id = 1) AS eos_w_ident_conf_n,
(SELECT COUNT (*)
FROM EO
WHERE EST.ELEMENT_SUBNATIONAL_ID = EO.ELEMENT_SUBNATIONAL_ID
and eo.d_id_confirmed_id = 2) AS eos_w_ident_conf_y,
(SELECT COUNT (*)
FROM EO
WHERE EST.ELEMENT_SUBNATIONAL_ID = EO.ELEMENT_SUBNATIONAL_ID
and (eo.d_id_confirmed_id = 3) ) AS eos_w_ident_conf_ques,
(SELECT COUNT (*)
FROM EO
WHERE EST.ELEMENT_SUBNATIONAL_ID = EO.ELEMENT_SUBNATIONAL_ID
and eo.d_id_confirmed_id is null) AS eos_w_ident_conf_null,

DelimList ('SELECT DISTINCT d_county.county_name AS counties '
|| ' FROM eo_county, d_county, eo
WHERE eo_county.d_county_id = d_county.d_county_id
and eo_county.eo_id = eo.eo_id
and eo.eo_id not in
(select eofalsetc.eo_id from eo eofalsetc, taxon_subnatl_dist tsdfalsetc
where tsdfalsetc.element_subnational_id = eofalsetc.element_subnational_id
and tsdfalsetc.d_dist_confidence_id in (2, 3, 4, 5, 6, 7) /**A TSD row has Distribution Confidence OTHER than Confident***/
and eofalsetc.d_id_confirmed_id = 1 /**EO Identification Confirmed N**/
and not exists /***No TSD row with Distribution Confidence of Confident***/
(select * from taxon_subnatl_dist tsd where eofalsetc.element_subnational_id = tsd.element_subnational_id and tsd.d_dist_confidence_id = 1))
and eo.element_subnational_id = ' || est.element_subnational_id || 'ORDER BY d_county.county_name ', ', ') AS counties_this_subn,
DelimList('SELECT ''ORG: '' || substr(d_origin.origin_desc,1,3) ||
''; REG: '' || substr(d_reg.regularity_desc,1,3) ||
''; CNF: '' || substr(d_conf.display_value,1,2) ||
''; PRS: '' || substr(d_pres.curr_presence_absence_desc,1,3) || (case when s_dist.d_population_id is not null then
''; POP: '' || substr(d_pop.population_desc,1,3) 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, '; ') AS taxon_subnatl_dist,
to_char(est.s_rank_change_date,'yyyy-mm-dd') srank_ch,
to_char(est.s_rank_review_date,'yyyy-mm-dd') srank_rev,
egt.element_global_seq_uid egt_seq_uid,
egt.element_global_ou_uid egt_ou_uid,
est.element_subnational_seq_uid est_seq_uid,
est.element_subnational_ou_uid est_ou_uid
FROM element_global egt,
element_national ent,
element_subnational est,
nation,
subnation,
scientific_name gname,
scientific_name sn,
taxon_subnatl_dist tsd,
d_dist_confidence
WHERE
egt.element_global_id = ent.element_global_id
and ent.element_national_id = est.element_national_id
and egt.gname_id = gname.scientific_name_id
and est.sname_id = sn.scientific_name_id
and ent.nation_id = nation.nation_id
and est.subnation_id = subnation.subnation_id
and est.element_subnational_id = tsd.element_subnational_id
and tsd.d_dist_confidence_id = d_dist_confidence.d_dist_confidence_id
and sn.d_name_category_id in (1, 2, 3, 4, 5, 6, 7, 19) /**zoological or botanical**/
and tsd.d_dist_confidence_id != 1 /***a tsd row has distribution confidence other than confident***/
and /****no tsd row has distribution confidence of confident***/
not exists (select * from taxon_subnatl_dist
where taxon_subnatl_dist.element_subnational_id = est.element_subnational_id
and taxon_subnatl_dist.d_dist_confidence_id = 1 /**confident**/)
and /***there is an eo with id confirmed null, Y, or ?***/
exists ( select * from eo
where est.element_subnational_id = eo.element_subnational_id
and (eo.d_id_confirmed_id is null or eo.d_id_confirmed_id != 1) )

ORDER BY
sn.d_name_category_id,
sn.scientific_name