PROMPT Updating sequence_within_uid on unique identifier entries to ensure unique...
DECLARE
   bug_no varchar2(9) := 'CK1164644';
   cnt number;
BEGIN

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

  for rec in (
		   select count(*) cnt,  unique_identifier_reference ui_ref, sequence_within_uid
		   from ci_unique_identifier_entries@r212
		   where  application_system_owned_by in ( select ci_appid
	    	 	               	                   from ck_migration_objects
						                   where ci_stage = 'APPSEL' )
		   having count(*) > 1
		   group by unique_identifier_reference, sequence_within_uid
             )
    loop     
      if rec.cnt > 1
      then cnt := 10;
           for uie in ( select id id
                        from ci_unique_identifier_entries@r212 
                        where unique_identifier_reference = rec.ui_ref                    
                      )
           loop  	
             update ci_unique_identifier_entries@r212
             set    sequence_within_uid = cnt
             where  id = uie.id;
             cnt := cnt + 10;
           end loop;
      end if;
    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;
/
