Description:

Returns animal and plant eo_spec records / data from both a direct EGT-eo_specs link and a EGT-Element_Group-Eo_specs link.
Note: Though it is not common, when an EO_Spec exists for an individual species and for a species group in which the species is a member, query will return only the row(s) from the individual species relationship. Query will sometimes return duplicates (on EGT_id) where a when there are EO_Specs for more than 1 location use class (e.g. breeding, nonbreeding, migratory stopover)
AUTHORS: Marcos Robles and Tyler Christensen
DATE: 4/1/2004

MODIFIED BY: Whitney Weber on 5/3/2019

Query:
SELECT egt.element_global_id,
eo_specs.element_group_id,
egt.elcode_bcd,
luc.location_use_class_desc,
animal_eo_specs.inferred_extent_distance a_inferred_extent_dist,
animal_eo_specs.inferred_extent_notes a_inferred_extent_notes,
animal_eo_specs.sep_dist_suitable_habitat a_sep_dist_suitable,
animal_eo_specs.sep_dist_unsuitable_habitat a_sep_dist_unsuitable,
plant_eo_specs.sep_dist_suitable_habitat p_sep_dist_suitable,
plant_eo_specs.sep_dist_unsuitable_habitat p_sep_dist_unsuitable,
esd.alt_separation_procedure,
esd.mapping_guidance,
esd.minimum_eo_criteria,
esd.separation_barriers,
esd.separation_justification,
DelimList('SELECT esfd.feature_descriptor FROM
eo_specs_feature_desc esfd' || ' WHERE esfd.eo_specs_detail_id =' ||
esd.eo_specs_detail_id, '/ ') AS feat_desc

FROM (
select egt.element_global_id,
(case when eo_specs.eo_specs_id is not null
then eo_specs.eo_specs_id
else eo_specs2.eo_specs_id
end) eospecs_id,
egp.element_group_id,
d_element_group_type.element_group_type_desc
from element_global egt,
eo_specs,
element_group egp,
d_element_group_type,
element_group_membership egm,
eo_specs eo_specs2
where egt.element_global_id = eo_specs.element_global_id (+)
and egt.element_global_id = egm.element_global_id (+)
and egm.element_group_id = egp.element_group_id (+)
and egp.element_group_id = eo_specs2.element_group_id (+)
) egt_eospecs,
element_global egt,
eo_specs,
eo_specs_detail esd,
d_location_use_class luc,
animal_eo_specs,
plant_eo_specs

WHERE egt.element_global_id = egt_eospecs.element_global_id
and egt_eospecs.eospecs_id = eo_specs.eo_specs_id
and eo_specs.eo_specs_id = esd.eo_specs_id
and esd.d_location_use_class_id = luc.d_location_use_class_id (+)
and esd.eo_specs_detail_id = animal_eo_specs.eo_specs_detail_id (+)
and esd.eo_specs_detail_id = plant_eo_specs.eo_specs_detail_id (+)

ORDER BY egt.elcode_bcd