/* Do Not Exchange AND RELATED EOs */
 
SELECT getnametypecd(sname_id) type
  , est_elcode(es.element_subnational_id) elcode, getsciname(es.sname_id) sname
  , case when upper(eo.do_not_exchange_ind) = 'Y' then 'DNE'
       when eo.principal_eo_shape_id is null then 'child is DNE'
       when upper(eoP.do_not_exchange_ind) = 'Y' then 'parent is DNE'
      else 'sibling is DNE' end reason_excluded
  , eo.eo_id, eo.eo_num, eo.do_not_exchange_ind  --  , eo.shape_id, eo.principal_eo_shape_id
  , eoP.do_not_exchange_ind principal_DNE, eoP.eo_id principal_eo_id, eoP.eo_num principal_eo_num
FROM eo, eo eoP, element_subnational es
WHERE eo.principal_eo_shape_id = eoP.shape_id(+)
  and eo.element_subnational_id = es.element_subnational_id
  and (    upper(eo.do_not_exchange_ind) = 'Y'  --direct DNE
        or upper(eoP.do_not_exchange_ind) = 'Y' --child of DNE EO
        or exists(select 1 from eo eoC where eoC.principal_eo_shape_id = eo.shape_id and upper(eoC.do_not_exchange_ind) = 'Y') --parent of DNE EO
        or exists(select 1 from eo eoC where eoC.principal_eo_shape_id = eo.principal_eo_shape_id and upper(eoC.do_not_exchange_ind) = 'Y') --sibling of DNE EO   
      )
ORDER BY type, elcode, sname, eoP.eo_id nulls first, eo.eo_id