PROMPT Updating sequence number on index entries to comply with new uniqueness criteria...

DECLARE
   bug_no varchar2(8) := 'CK927148';
   counter number;
BEGIN

  update ck_migration_objects set ci_created = 'N' where ci_stage = 'CKPREMIG' and ci_name = bug_no;
 
  for ix in
  (
  select ix.id id
  from   ci_indexes@r212 ix
  where ix.application_system_owned_by in ( select ci_appid
	    	 				              from ck_migration_objects
						              where ci_stage = 'APPSEL' )
    and ix.id in (
       select ie.relation_index_reference
       from   ci_index_entries@r212   ie
       where  ie.application_system_owned_by in ( select ci_appid
	    	 				              from ck_migration_objects
						              where ci_stage = 'APPSEL' )
       group  by ie.relation_index_reference, ie.sequence_number
       having count(*) > 1)
  )
  loop
    counter := 10;
    for ie in
      (
      select rowid
      from   ci_index_entries@r212
      where  relation_index_reference = ix.id
      order  by sequence_number
      )
      loop
        update ci_index_entries@r212 ind
        set ind.sequence_number = counter
        where rowid = ie.rowid;
 
        counter := counter + 10;
   
    end loop;
  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;
/
