Description:

Returns el_mgmt records/data for EGTs on a working list, from both a directEGT-el_mgmt link and a EGT-Element_Group-El_mgmt link.
Author: Marcos Robles and Tyler Christensen
Date: 4/1/04

Query:

SELECT egt.element_global_id,
em.element_group_id,
em.mgmt_methods,
em.mgmt_pgms_contacts,
em.mgmt_research_pgms,
em.mgmt_sum,
em.monitoring_methods,
em.monitoring_pgms_contacts,
em.restoration_potential,
em.site_cons_planning_consid

FROM (
select distinct egt.element_global_id,
(case when em.element_management_id is not null
then em.element_management_id
else em2.element_management_id
end) em_id
from element_global egt,
element_management em,
element_group egp,
element_group_membership egm,
element_management em2
where egt.element_global_id = em.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 = em2.element_group_id (+)
and (em.element_management_id is not null or em2.element_management_id is not null)
) egt_em,
element_global egt,
element_management em

WHERE egt.element_global_id = egt_em.element_global_id and
egt_em.em_id = em.element_management_id

ORDER BY egt.element_global_id