PROMPT Correcting invalid MCO type...

DECLARE
   bug_no varchar2(8) := 'CK985636';
   rec_name varchar2(30);
   cnt number;
   appcon_id number;
   appcon_types rm.reference;
   app_sys_name varchar2(100);

BEGIN

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

  for rec in (               
		   select mco.id id, count(*) cnt, mco.name name, mco.application_system_owned_by app_ref
               from   ci_module_component_inclusions@r212 modinc,
                      ci_general_modules@r212 md,
			    ci_module_components@r212 mco
               where  modinc.general_module_reference = md.id
               and    modinc.module_component_reference = mco.id
		   and    mco.module_component_type = 'SPECIFIC'
               and    md.application_system_owned_by in ( select ci_appid
		    					                from ck_migration_objects
						           		    where ci_stage = 'APPSEL' )
		   having count(*) > 1
		   group by mco.id, mco.name, mco.application_system_owned_by
             )
    loop        
      if rec.cnt > 1
      then 
           update ci_module_components@r212
           set general_module_reference = null, module_component_type = 'REUSABLE'
 	     where id = rec.id;

   	     appcon_id := ciilok.idgen@r212;
           appcon_types := ciityp.str_ty@r212('APPMCO');
           app_sys_name := ciiutl.get_element_name@r212(rec.app_ref,'APP');

           insert into sdd_structure_elements@r212
           ( str_id, 
             str_ty,
             str_type_of,
             str_part_of,
             str_part_of_name,
             str_use_of,str_elem_owned_by
            )
            values
            (  appcon_id,
               appcon_types,
               'APPMCO',
               rec.app_ref,
               app_sys_name,
               rec.id,
               rec.app_ref);	    

           for rec2 in (               
	                   select mco.name name, count(*) cnt
                         from   ci_module_components@r212 mco
                         where mco.module_component_type = 'REUSABLE'
				 and mco.application_system_owned_by = rec.app_ref
           	             having count(*) > 1
              	       group by mco.name
                       )
           loop
             rec_name := substrb(rec2.name,1,  29 - lengthb(rec.cnt));

             if rec2.cnt > 1
             then 
               cnt := 1;
               for rmco in ( select id id
                             from ci_module_components@r212 
                             where name like rec_name			
	  		  	     and module_component_type = 'REUSABLE'
                            )
               loop  	
                 update ci_module_components@r212
                 set    name = substrb(name,1, lengthb(rec_name)) || '_' || cnt
                 where  id = rmco.id;
                 cnt := cnt + 1;
               end loop;
             end if;
           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;
/
