PROMPT Force delete relation selections that do not have valid table or view reference
-- modified script to fix bug 1477871
-- for the condition when views are based on views

DECLARE
   bug_no varchar2(8) := 'CK927130';
BEGIN

  update ck_migration_objects set ci_created = 'N' where ci_stage = 'CKPREMIG' and ci_name = bug_no;

  for vwtab in 
  (
    select vwtab.id id
    from ci_relation_selections@r212 vwtab
    where (not exists ( select 1
                        from   ci_view_definitions@r212 vw
                        where  vw.id = vwtab.view_reference )
           and
           not exists ( select 1
                        from   ci_snapshot_definitions@r212 snp
                        where  snp.id = vwtab.view_reference )
           )
    or (not exists ( select 1
                    from ci_table_definitions@r212 tab
                    where tab.id = vwtab.table_reference )
        and
        not exists ( select 1
                    from ci_view_definitions@r212 vw
                    where vw.id = vwtab.table_reference )
       )
    and vwtab.application_system_owned_by in ( select ci_appid
	    					           from ck_migration_objects
						           where ci_stage = 'APPSEL' )
)
  loop
    ciiutl.delete_element@r212(vwtab.id, 'VWTAB');
  
    delete from sdd_structure_elements@r212
    where str_id = vwtab.id;
  
  end loop; 

  update ck_migration_objects set ci_created = 'Y' where ci_stage = 'CKPREMIG' and ci_name = bug_no;
   
  commit;
  
EXCEPTION 
  when others then
     dbms_output.put_line (' ** Exception Raised ** ');
     dbms_output.put_line ('...ERROR: '||substrb(sqlerrm, 1, 200));
     raise;
	 
end;
/
