PROMPT Force delete Navigation Action Items that have an invalid 'mcn_reference'

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

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

  for nai in
  (
    select nai.id
    from ci_navigation_action_items@r212 nai
    where action_item_for = 'MCN'
    and not exists ( select 1
                     from ci_module_component_inclusions@r212 mcn
                     where mcn.id = nai.mcn_reference )          
    and nai.application_system_owned_by in ( select ci_appid
	    					         from ck_migration_objects
						         where ci_stage = 'APPSEL' )         
  )
  loop
    ciiutl.delete_element@r212(nai.id, 'ACI');
  
    delete from sdd_elements@r212
    where el_id = nai.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;
/





