PROMPT Force delete duplicate Module Arguments

DECLARE
   bug_no varchar2(9) := 'CK1283776';
BEGIN

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

  -- Validating denormalised property ARG.ATTRIBUTE_OR_DATAITEM
  update ci_module_arguments@r212 q1
  set q1.ATTRIBUTE_OR_DATAITEM = null
  where q1.ATTRIBUTE_OR_DATAITEM is not null
  and not exists ( select null from SDD_ELEMENTS@r212 q2
  	        	   where q2.EL_ID =  q1.ATTRIBUTE_OR_DATAITEM
  	               and q2.EL_TYPE_OF in ('ATT', 'DIT') )
  and q1.application_system_owned_by in ( select ci_appid
	    			          			  from ck_migration_objects
				          				  where ci_stage = 'APPSEL' );			
  
  
  -- Validating denormalised property TRGCOL.TABLE_REFERENCE
  update ci_trigger_column_usages@r212 q1
  set q1.TABLE_REFERENCE = null
  where q1.TABLE_REFERENCE is not null
  and not exists ( select null from CI_TABLE_DEFINITIONS@r212 q2
		           where q2.ID = q1.TABLE_REFERENCE)
  and q1.application_system_owned_by in ( select ci_appid
	    			          			  from ck_migration_objects
				          				  where ci_stage = 'APPSEL' );				   
  


  -- Force delete duplicate Module Arguments
  for arg in
  (
    select arg.id id, arg.name, arg.module_reference
    from ci_module_arguments@r212 arg
    where arg.id < ( select max(arg2.id)
                     from ci_module_arguments@r212 arg2
                     where arg.id != arg2.id
                     and arg.name = arg2.name
                     and arg.module_reference = arg2.module_reference
                   )
    and arg.application_system_owned_by in ( select ci_appid
	     					         	     from ck_migration_objects
						                     where ci_stage = 'APPSEL' )
  )
  loop
    ciiutl.delete_element@r212(arg.id, 'ARG');
  
    delete from sdd_elements@r212
    where el_id = arg.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;
/
