PROMPT Remove duplicate Tablesace Quotas for a Database User..

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

  update ck_migration_objects set ci_created = 'N' where ci_stage = 'CKPREMIG' and ci_name = bug_no;
  
  -- Validating denormalised property COL.SOURCE_ENTITY_DERIVED_FROM
  update ci_columns@r212 q1
  set q1.SOURCE_ENTITY_DERIVED_FROM = null
  where q1.SOURCE_ENTITY_DERIVED_FROM is not null
  and not exists ( select null from CI_ENTITIES@r212 q2
	     		   where q2.ID = q1.SOURCE_ENTITY_DERIVED_FROM )
  and q1.application_system_owned_by in ( select ci_appid
	    			          			  from ck_migration_objects
				          				  where ci_stage = 'APPSEL' );	
										  										  
  -- Validating denormalised property COL.SOURCE_RELATIONSHIP_END
  update ci_columns@r212 q1
  set q1.SOURCE_RELATIONSHIP_END = null
  where q1.SOURCE_RELATIONSHIP_END is not null
  and not exists ( select null from CI_RELATIONSHIP_ENDS@r212 q2
	        where q2.ID = q1.SOURCE_RELATIONSHIP_END )
  and q1.application_system_owned_by in ( select ci_appid
  	    			          			  from ck_migration_objects
				          				  where ci_stage = 'APPSEL' );	
  
  -- Validating denormalised property COL.SOURCE_REL_ENTITY 
  update ci_columns@r212 q1
  set q1.SOURCE_REL_ENTITY = null
  where q1.SOURCE_REL_ENTITY is not null
  and not exists ( select null from CI_ENTITIES@r212 q2
		           where q2.ID = q1.SOURCE_REL_ENTITY) 
  and q1.application_system_owned_by in ( select ci_appid
  	    			          			  from ck_migration_objects
				          				  where ci_stage = 'APPSEL' );		
										  										  
  
  -- Validating denormalised property COL.SOURCE_REL_ENTITY_ATTRIBUTE
  update ci_columns@r212 q1
  set q1.SOURCE_REL_ENTITY_ATTRIBUTE = null
  where  q1.SOURCE_REL_ENTITY_ATTRIBUTE is not null
  and not exists ( select null from CI_ATTRIBUTES@r212 q2
	        	   where q2.ID = q1.SOURCE_REL_ENTITY_ATTRIBUTE)
  and q1.application_system_owned_by in ( select ci_appid
  	    			          			  from ck_migration_objects
				          				  where ci_stage = 'APPSEL' );				
			
  
  -- Remove duplicate Tablesace Quotas for a Database User..
  for tsq in
    (
      select tsq.id id
      from ci_tablespace_quotas@r212 tsq
      where tsq.id < ( select max(tsq2.id)
   			           from ci_tablespace_quotas@r212 tsq2
                       where tsq.id != tsq2.id
                       and tsq.database_user_reference = tsq2.database_user_reference                       
					   and tsq.tablespace_reference = tsq2.tablespace_reference      
                      )
      and tsq.application_system_owned_by in ( select ci_appid
  	     					                   from ck_migration_objects
           						               where ci_stage = 'APPSEL' )
    )
    loop
      ciiutl.delete_element@r212(tsq.id, 'TSQ');
    
      delete from sdd_elements@r212
      where el_id = tsq.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;
/
