SELECT
nation.iso_nation_cd nat,
subnation.subnation_code sub,
element_global.elcode_bcd,
higher_class_unit_family.higher_class_unit_name family,
d_classification_status.classification_status_desc clas_stat,
gname.scientific_name gname,
sname.scientific_name sname,
element_global.g_rank,
element_subnational.s_rank,

DelimList('SELECT d_conf.dist_confidence_cd conf
FROM taxon_subnatl_dist s_dist, d_dist_confidence d_conf
WHERE s_dist.d_dist_confidence_id = d_conf.d_dist_confidence_id (+)
and s_dist.element_subnational_id = ' || element_subnational.element_subnational_id, '; ') AS confid,

(Select Count (*)
From EO
Where element_subnational.element_subnational_id = eo.element_subnational_id ) as EOs,

d_eo_track_status.eo_track_status_cd trk,
to_char (element_subnational.s_rank_change_date, 'yyyy-mm-dd') srank_ch,
to_char (element_subnational.s_rank_review_date, 'yyyy-mm-dd') srank_rv,
element_global.g_classification_com,

DelimList('SELECT eg_nonstd.element_global_id || '' ('' || rel_type.element_rel_type_cd || '') '' || gname_nonstd.scientific_name || ''''
FROM element_std_nonstd_rel el_rel,
element_global eg_nonstd,
d_element_rel_type rel_type,
scientific_name gname_nonstd
WHERE eg_nonstd.gname_id = gname_nonstd.scientific_name_id
and el_rel.nonstd_element_id = eg_nonstd.element_global_id (+)
and el_rel.d_element_rel_type_id = rel_type.d_element_rel_type_id (+)
and el_rel.std_element_id IN '
|| '(SELECT element_global_id FROM element_global WHERE element_global_id='
|| element_global.element_global_id || ') ORDER BY gname_nonstd.scientific_name', ', ')
AS related_nonstds,

DelimList('SELECT eg_std.element_global_id || '' ('' || rel_type.element_rel_type_cd || '') '' || gname_std.scientific_name || ''''
FROM element_std_nonstd_rel el_rel,
element_global eg_std,
d_element_rel_type rel_type,
scientific_name gname_std
WHERE eg_std.gname_id = gname_std.scientific_name_id
and el_rel.std_element_id = eg_std.element_global_id (+)
and el_rel.d_element_rel_type_id = rel_type.d_element_rel_type_id (+)
and el_rel.nonstd_element_id IN '
|| '(SELECT element_global_id FROM element_global WHERE element_global_id='
|| element_global.element_global_id || ') ORDER BY gname_std.scientific_name', ', ')
AS related_stds,

(case when gname.scientific_name = concept_name.scientific_name then 'same as gname' else concept_name.scientific_name end) name_in_concept_rf, lower (reference.reference_code) concept_rf,

DelimList('SELECT dlookup(''D_USESA'', d_usesa_id)
AS usesa FROM taxon_global WHERE element_global_id='
|| element_global.element_global_id || ' ORDER BY usesa ', ', ')
AS usesa,

Delimlist ('select tg.interpreted_usesa from taxon_global tg
where tg.element_global_id = '|| element_global.element_global_id, ', ') as interp_usesa,

DelimList('SELECT dlookup(''D_COSEWIC'', d_cosewic_id)
AS cosewic FROM taxon_national tn
WHERE tn.d_cosewic_id is not null
and tn.element_national_id=' || element_subnational.element_national_id || ' ORDER BY cosewic ', ', ')
AS cosewic,

Delimlist ('select tn.interpreted_cosewic from taxon_national tn
where tn.interpreted_cosewic is not null
and tn.element_national_id = '|| element_subnational.element_national_id, ', ') as interp_cosewic,

element_global.rounded_g_rank round_g,
element_subnational.rounded_s_rank round_s,
d_name_category.name_category_desc name_cat,
element_global.element_global_id,
element_subnational.element_subnational_id

FROM
element_global,
scientific_name gname,
scientific_name sname,
scientific_name concept_name,
element_national,
element_subnational,
nation, subnation,
d_classification_status,
d_name_category,
d_eo_track_status,
higher_class_unit higher_class_unit_genus,
higher_class_unit higher_class_unit_family,
d_classification_level d_class_level_genus,
d_classification_level d_class_level_family,
reference

WHERE
element_global.element_global_id = element_national.element_global_id
and element_national.element_national_id = element_subnational.element_national_id
and element_global.d_classification_status_id = d_classification_status.d_classification_status_id
and element_global.gname_id = gname.scientific_name_id
and element_global.concept_name_id = concept_name.scientific_name_id
and element_subnational.sname_id = sname.scientific_name_id
and element_national.nation_id = nation.nation_id
and element_subnational.subnation_id = subnation.subnation_id
and element_subnational.d_eo_track_status_id = d_eo_track_status.d_eo_track_status_id (+)
and gname.d_name_category_id = d_name_category.d_name_category_id
and element_global.concept_reference_id= reference.reference_id
and element_global.higher_class_unit_id = higher_class_unit_genus.higher_class_unit_id
and higher_class_unit_genus.d_classification_level_id = d_class_level_genus.d_classification_level_id
and higher_class_unit_family.higher_class_unit_id = higher_class_unit_genus.parent_unit_id
and d_class_level_family.d_classification_level_id = d_class_level_genus.parent_level_id
and d_class_level_genus.classification_level_name = 'Genus'
and d_class_level_family.classification_level_name = 'Family'
and element_global.inactive_ind = 'N'
and d_name_category.name_type_cd = 'P'
AND sname.scientific_name in

(SELECT scientific_name.scientific_name
FROM scientific_name,
element_subnational est,
element_national ent,
element_global egt
WHERE
scientific_name.scientific_name_id = est.sname_id
and est.element_national_id = ent.element_national_id
and ent.element_global_id = egt.element_global_id
and egt.inactive_ind = 'N'
GROUP BY scientific_name.scientific_name
HAVING COUNT(*) > 1 )

ORDER BY
gname.d_name_category_id,
higher_class_unit_family.higher_class_unit_name,
gname.scientific_name,
sname.scientific_name,
element_global.d_classification_status_id