/* Step 1: In the Query Builder, run this query to see if you have any empty eo_specs records.
If you get any rows back, save the results as a working list called 'EMPTY EO SPECS' (all caps, please).  
If you need to change the name of this working list because you already have a working list of that name, be sure and change
the delete queries below!  If you don't get any results, you can close this file and go about your business. */

SELECT eo_specs.EO_SPECS_ID, eo_specs.element_global_id
FROM EO_SPECS, EO_SPECS_DETAIL, PLANT_EO_SPECS PES, eo_specs_feature_desc esfd
WHERE EO_SPECS.EO_SPECS_BCD is null and  
EO_SPECS_DETAIL.EO_SPECS_ID = EO_SPECS.EO_SPECS_ID and 
EO_SPECS_DETAIL.MAPPING_GUIDANCE is null and 
EO_SPECS_DETAIL.SEPARATION_BARRIERS is null and 
EO_SPECS_DETAIL.ALT_SEPARATION_PROCEDURE is null and 
EO_SPECS_DETAIL.SEPARATION_JUSTIFICATION is null and 
EO_SPECS_DETAIL.MINIMUM_EO_CRITERIA is null and 
EO_SPECS_DETAIL.VERSION_AUTHOR_NAME is null and  
EO_SPECS_DETAIL.VERSION_DATE is null and 
EO_SPECS_DETAIL.VERSION_NOTES is null and 
EO_SPECS.REC_LAST_MOD_USER is null and 
EO_SPECS.REC_LAST_MOD_DATE is null and 
EO_SPECS_DETAIL.EO_SPECS_DETAIL_ID = PES.EO_SPECS_DETAIL_ID AND
PES.SEP_DIST_UNSUITABLE_HABITAT IS NULL AND
PES.SEP_DIST_SUITABLE_HABITAT IS NULL and
eo_specs_detail.eo_specs_detail_id = esfd.eo_specs_detail_id(+) and
esfd.feature_descriptor is null
UNION
SELECT eo_specs.EO_SPECS_ID, eo_specs.element_global_id
FROM EO_SPECS, EO_SPECS_DETAIL, ANIMAL_EO_SPECS AES, eo_specs_feature_desc esfd 
WHERE EO_SPECS.EO_SPECS_BCD is null and  
EO_SPECS_DETAIL.EO_SPECS_ID = EO_SPECS.EO_SPECS_ID and 
EO_SPECS_DETAIL.MAPPING_GUIDANCE is null and 
EO_SPECS_DETAIL.SEPARATION_BARRIERS is null and 
EO_SPECS_DETAIL.ALT_SEPARATION_PROCEDURE is null and 
EO_SPECS_DETAIL.SEPARATION_JUSTIFICATION is null and 
EO_SPECS_DETAIL.MINIMUM_EO_CRITERIA is null and 
EO_SPECS_DETAIL.VERSION_AUTHOR_NAME is null and  
EO_SPECS_DETAIL.VERSION_DATE is null and 
EO_SPECS_DETAIL.VERSION_NOTES is null and 
EO_SPECS.REC_LAST_MOD_USER is null and 
EO_SPECS.REC_LAST_MOD_DATE is null and 
EO_SPECS_DETAIL.EO_SPECS_DETAIL_ID = AES.EO_SPECS_DETAIL_ID AND
AES.SEP_DIST_UNSUITABLE_HABITAT IS NULL AND
AES.SEP_DIST_SUITABLE_HABITAT IS NULL AND
AES.INFERRED_EXTENT_DISTANCE IS NULL AND
AES.INFERRED_EXTENT_NOTES IS NULL and
eo_specs_detail.eo_specs_detail_id = esfd.eo_specs_detail_id(+) and
esfd.feature_descriptor is null


/* Step 2: if you find any empty records, delete them with the next set of updates.  You must
do these instructions in SQL+ logged in as biotics_user. You must also do these in the order 
in which they appear below.*/


delete from eo_specs_feature_desc esfd
where esfd.eo_specs_detail_id in
  (select esd.eo_specs_detail_id
    from eo_specs_detail esd, eo_specs es,
    working_list_element_data wled,
    working_list wl
    where wl.working_list_name = 'EMPTY EO SPECS'
    and wl.working_list_id = wled.working_list_id
    and wled.data_id = es.element_global_id
    and es.eo_specs_id = esd.eo_specs_id);
commit;

delete from plant_eo_specs pes
where pes.eo_specs_detail_id in 
   (select esd.eo_specs_detail_id
    from eo_specs_detail esd,
    eo_specs es,
    working_list_element_data wled,
    working_list wl
    where wl.working_list_name = 'EMPTY EO SPECS' and
    wl.working_list_id = wled.working_list_id and
    wled.data_id = es.element_global_id and
    es.eo_specs_id = esd.eo_specs_id);
commit;

delete from animal_eo_specs aes
where aes.eo_specs_detail_id in 
(select esd.eo_specs_detail_id
 from eo_specs_detail esd,
 eo_specs es,
 working_list_element_data wled,
 working_list wl
 where wl.working_list_name = 'EMPTY EO SPECS' and
 wl.working_list_id = wled.working_list_id and
 wled.data_id = es.element_global_id and
 es.eo_specs_id = esd.eo_specs_id);
commit;

delete from eo_specs_detail
where eo_specs_detail_id in 	
  (select esd.eo_specs_detail_id
   from eo_specs_detail esd,
   eo_specs es,
   working_list_element_data wled,
   working_list wl
   where wl.working_list_name = 'EMPTY EO SPECS' and
   wl.working_list_id = wled.working_list_id and
   wled.data_id = es.element_global_id and
   es.eo_specs_id = esd.eo_specs_id);
commit;

delete from eo_specs
where eo_specs_id in
(select es.eo_specs_id
 from eo_specs es,
 working_list_element_data wled,
 working_list wl
 where wl.working_list_name = 'EMPTY EO SPECS' and
 wl.working_list_id = wled.working_list_id and
 wled.data_id = es.element_global_id);
commit;