Prompt Populate missing ALIAS on MTI(s)..

DECLARE
   bug_no varchar2(8) := 'CK965805';
   new_name varchar2(28) := '<Detected null Migration>';
   rec_name varchar2(30);
   cnt number;
BEGIN

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

  update ci_module_detail_table_usages@r212
  set alias = new_name
  where alias is null;

  for rec in (
                 select alias alias,count(*) cnt, module_component_reference mco_ref
                 from   ci_module_detail_table_usages@r212
		     where  application_system_owned_by in ( select ci_appid
	    			              		         from ck_migration_objects
								         where ci_stage = 'APPSEL' )
                 having count(*) > 1
                 group by alias, module_component_reference
             )
    loop
      rec_name := substrb(rec.alias,1,  29 - lengthb(rec.cnt));
  
      if rec.cnt > 1
      then cnt := 1;
           for mti in ( select id id
                        from ci_module_detail_table_usages@r212 mdtu
                        where alias like rec_name
                        and module_component_reference = rec.mco_ref
                       )
           loop  	
             update ci_module_detail_table_usages@r212
             set    alias = substrb(alias,1, lengthb(rec_name)) || '_' || cnt
             where  id = mti.id;
             cnt := cnt + 1;
           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;
/
