PROMPT Correcting invalid GEM reference on MCO (based on MCNs)...

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

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

  update ci_module_components@r212 mco
  set general_module_reference = ( select modinc.general_module_reference
                                   from   ci_module_component_inclusions@r212 modinc,
                                          ci_general_modules@r212 md
                                   where  modinc.general_module_reference = md.id
                                   and    modinc.module_component_reference = mco.id
					             )
  where mco.module_component_type = 'SPECIFIC'
  and not exists
			  ( select 1
				from ci_module_component_inclusions@r212 modinc,
					 ci_general_modules@r212 md
				where modinc.general_module_reference = md.id
				and   modinc.module_component_reference = mco.id
				and   mco.general_module_reference = modinc.general_module_reference
			  )
  and mco.application_system_owned_by in ( select ci_appid
	      				       from ck_migration_objects
						       where ci_stage = 'APPSEL' ); 

  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;
/
