PROMPT Force delete specific module components that are not included in a general module

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

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

  -- Validating denormalised property DFL.EXPORT_ID
  update ci_dataflows@r212 q1
  set q1.EXPORT_ID = null
  where q1.EXPORT_ID is not null
  and not exists ( select null from CI_DATAFLOWS@r212 q2
			       where q2.ID = q1.EXPORT_ID )
  and q1.application_system_owned_by in ( select ci_appid
	    			          			  from ck_migration_objects
				          				  where ci_stage = 'APPSEL' );			
			
  -- Validating denormalised property DSTATT.ENTITY_REFERENCE_ID 
  update ci_datastore_attribute_usages@r212 q1
  set q1.ENTITY_REFERENCE = null
  where q1.ENTITY_REFERENCE is not null
  and not exists ( select null from CI_ENTITIES@r212 q2
  				   where q2.ID = q1.ENTITY_REFERENCE)
  and q1.application_system_owned_by in ( select ci_appid
  	    			          			  from ck_migration_objects
				          				  where ci_stage = 'APPSEL' );	
			
  -- Validating denormalised property EVT.EXPORT_ID
  update ci_events@r212 q1
  set q1.EXPORT_ID = null
  where q1.EXPORT_ID is not null
  and not exists ( select null from CI_EVENTS@r212 q2
		           where q2.ID = q1.EXPORT_ID )
  and q1.application_system_owned_by in ( select ci_appid
  	    			          			  from ck_migration_objects
				          				  where ci_stage = 'APPSEL' );				
			
  -- Validating denormalised property FUNATT.ENTITY_REFERENCE
  update ci_function_attribute_usages@r212 q1
  set q1.ENTITY_REFERENCE = null
  where q1.ENTITY_REFERENCE is not null
  and not exists ( select null from CI_ENTITIES@r212 q2
	        where q2.ID = q1.ENTITY_REFERENCE)
  and q1.application_system_owned_by in ( select ci_appid
  	    			          			  from ck_migration_objects
				          				  where ci_stage = 'APPSEL' );			


  -- Force delete specific module components that are not included in a general module
  for mco in 
  (
    select mco.id
    from ci_module_components@r212 mco
    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 
			)     
  )
  loop
    ciiutl.delete_element@r212(mco.id, 'MCO');
  
    delete from sdd_elements@r212
    where el_id = mco.id;
	
  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;
/

