PROMPT Deleting diagram segments that have an invalid diagram reference...

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

  update ck_migration_objects set ci_created = 'N' where ci_stage = 'CKPREMIG' and ci_name = bug_no;
  
  -- Validating denormalised property DFLATTT.ENTITY_REFERENCE
  update ci_dataflow_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' );			
  

  -- Deleting diagram segments that have an invalid diagram reference...
  delete from sdd_diagram_segments@r212 ds
  where not exists ( select 1
                     from ci_diagrams@r212 dg
                     where dg.id = ds.diagram_reference );					 
					 
  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;
/
