/* Finds SOURCE_FEATURE records that are not related to an EO record
*/

select getnametypecd(es.sname_id) nametype, GETNAMECATDESC(es.sname_id) namecat, est_elcode(es.element_subnational_id) elcode, getsciname(es.sname_id) sname
  , sf.source_feature_id, sf.source_feature_descriptor, sf.source_feature_locator, location_use_class_desc, independent_source_feature_ind
  , ds.data_sensitive_desc, dsc.data_sensitive_category_desc, sf.data_sensitive_reasons  
  , delimlist('select visit_date from visit where source_feature_id=' || sf.source_feature_id || ' order by visit_date desc', ', ') visits 
  , sf.rec_last_mod_user, sf.rec_last_mod_date
  , sf.source_feature_ou_uid, sf.source_feature_seq_uid, element_subnational_ou_uid, element_subnational_seq_uid, sf.shape_id, s.shape_ou_uid, s.shape_seq_uid
from source_feature sf, shape s, element_subnational es, eo_source_feature eosf, d_location_use_class dl, d_data_sensitive ds, d_data_sensitive_category dsc
where sf.shape_id = s.shape_id 
  and sf.element_subnational_id = es.element_subnational_id 
  and sf.source_feature_id = eosf.source_feature_id(+) 
  and sf.d_location_use_class_id = dl.d_location_use_class_id(+)
  and sf.d_data_sensitive_id = ds.d_data_sensitive_id(+)
  and sf.d_data_sensitive_category_id = dsc.d_data_sensitive_category_id(+)
  and eosf.source_feature_id is null 
order by nametype, elcode, shape_seq_uid