Rem Copyright (c) 1996 by Oracle Corporation 
Rem
Rem FILE: ckazanal.sql
Rem 06/16/97 patch for applied 505924

DECLARE 
  ckaz_tbsp varchar2(30) := '&s';
  k_max_rows  integer := 50000; -- constant max# of rows in transaction
  brepowner   boolean := FALSE;
  repos_owner varchar2(30) := null;  
  utility_name   varchar2(29)  := 'Designer Repository Analyzer:';
  proc           varchar2(100) := '';
  
  Procedure   utl_check_db_objects Is
    -- Check that objects required by the anlayser
    -- exist and are valid.
    -- Procedure raises an application error if depended on 
    -- DB objects are not available
    cursor_name   integer;
    ignore        integer;
    exists_count  integer;

  BEGIN
    rmdbg.trace(utility_name|| 'utl_check_db_objects' ||' loc 10');
	   
	rmdbg.trace(utility_name|| 'utl_check_db_objects' ||' loc 11');	   
	   
	if (brepowner = TRUE) then	
	    select count(*)
	    into exists_count
	    from user_triggers
	    where trigger_name in (
	          'CDI_EL_CREATE_AUDIT'     
	         ,'CDI_EL_UPDATE_AUDIT'
	         ,'CDI_STR_CREATE_AUDIT'
	         ,'CDI_STR_UPDATE_AUDIT')
	     and status = 'ENABLED';
	 
	    IF exists_count != 4 Then 
	 	  rmdbg.trace(utility_name|| 'utl_check_db_objects' ||' loc 12');	  								
	      raise_application_error(-20001, 'ERROR: Missing|DISABLED triggers '
	                            ||' on SDD_ELEMENTS, SDD_STUCTURE_ELEMENTS');
	    End If;
	end if;	

	rmdbg.trace(utility_name|| 'utl_check_db_objects' ||' loc 13');

	if (brepowner = TRUE) then	
    	select count(*)
    	into exists_count
    	from user_objects
    	where object_name = 'CIIUTL'     
        and status = 'VALID'
	    and object_type = 'PACKAGE BODY';
	else
		select count(*)
    	into exists_count
    	from user_synonyms
    	where synonym_name = 'CIIUTL'
		and table_owner = repos_owner;		   
	end if;
	
    IF exists_count != 1 Then 
      -- Utility package body (force delete) missing or invalid
	  rmdbg.trace(utility_name|| 'utl_check_db_objects' ||' loc 14');	  		
      raise_application_error(-20001, 'ERROR: Package body CIIUTL '
                            ||'is missing or invalid');
    End If;

    EXCEPTION
      When Others Then
        Raise;

  END utl_check_db_objects;

  Procedure utl_del_unknown_types Is
    -- Delete rows of unknown element types from 
    -- SDD_STRUCTURE_ELEMENTS and SDD_ELEMENTS
    -- Unknown element types can only exist from 
    -- unsupported use of the repository or very old
    -- obsolete data.
    -- The repository analyser identifies such rows
    -- to the CKAZ_ACTIONS table, with a delete action 
    -- but with no referential integrity check (check_no is null) 
    cursor_name   integer;
    ignore        integer;
    exists_count  integer;
  BEGIN
    rmdbg.trace(utility_name|| 'utl_del_unknown_types' ||' loc 100');
	
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                  ' delete from SDD_ELEMENTS el'
                ||' where el.el_id in  ( '
                ||'    select a.reference_id '
                ||'    from ckaz_actions a '
                ||'    where '
                ||'      a.action = ''DELETE'' '
                ||'      and a.check_no is null '
                ||'    )'
                 ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);

    rmdbg.trace(utility_name|| 'utl_del_unknown_types' ||' loc 91');
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                  ' delete from SDD_STRUCTURE_ELEMENTS sel '
                ||' where sel.str_id in  ( '
                ||'    select a.reference_id '
                ||'    from ckaz_actions  a '
                ||'    where  '
                ||'      a.action = ''DELETE'' '
                ||'      and a.check_no is null '
                ||'    )'
                 ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);

    rmdbg.trace(utility_name|| 'utl_del_unknown_types' ||' loc 92');
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                  ' update ckaz_actions a '
                ||' set a.action = ''DELETED'' '
                ||' where a.reference_id is not null'
                ||'   and a.action = ''DELETE'' '
                ||'   and a.check_no is null '
                 ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);

    commit;

    EXCEPTION
      When Others Then
	    rmdbg.trace(utility_name|| 'utl_del_unknown_types' ||' loc 93');
        Raise;
  END utl_del_unknown_types;


  Procedure anal_create_ckaz_tables (ckaz_tbsp varchar2) Is
    -- Create all the tables required by the analyzer/purge
    -- utility: CKAZ_ACTIONS, CKAZ_CHECKS, CKAZ_TYPES_2_CHECK
    -- If these tables exist drop them first.
    -- even if not empty.
    cursor_name   integer;
    ignore        integer;
    exists_count  integer;
  BEGIN   
    rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 40');
  
    select count(*)
    into exists_count
    from user_tables
    where table_name = 'CKAZ_ACTIONS';
	
    IF exists_count = 1 Then
	  rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 41');
      cursor_name := dbms_sql.open_cursor;
      dbms_sql.parse(cursor_name,
                    ' drop table CKAZ_ACTIONS '
                     ,DBMS_SQL.NATIVE);
      ignore := dbms_sql.execute(cursor_name);
      dbms_sql.close_cursor(cursor_name);
    End If;
	
	rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 41.1');
	
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                  'create table CKAZ_ACTIONS ( '
                ||' reference_id    number (38) '
                ||' ,action          varchar2(15) '
                ||' ,table_name      varchar2(30) '
 	     	    ||' ,base_table_name varchar2(30) '
                ||' ,column_name     varchar2(30) '
                ||' ,value           number(38) '
                ||' ,elmt_type       varchar2(30) '
                ||' ,refd_elmt_type  varchar2(30) '
                ||' ,check_no        number(38)  '
                ||' ,date_created    date '
                ||' ,date_changed    date '
                ||' ,user_created_by varchar2(30) '
                ||' ,user_changed_by varchar2(30) ) '
                ||' tablespace '
                ||  ckaz_tbsp
                   ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
	
	rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 42');

    select count(*)
    into exists_count
    from user_tables
    where table_name = 'CKAZ_CHECKS';
    IF exists_count = 1 Then
	  rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 42.1');
      cursor_name := dbms_sql.open_cursor;
      dbms_sql.parse(cursor_name,
                    ' drop table CKAZ_CHECKS '
                     ,DBMS_SQL.NATIVE);
      ignore := dbms_sql.execute(cursor_name);
      dbms_sql.close_cursor(cursor_name);
    End If;
	
	rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 42.2');

    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                  'create table CKAZ_CHECKS ( '
                ||'  check_no          number(38)   '
                ||' ,enabled           varchar2(1) default ''Y'' '
                ||' ,check_mode        varchar2(15) '
                ||' ,type_check        varchar2(20) '
                ||' ,check_stmt        varchar2(2000) '
                ||' ,checked_date_time varchar2(20) '
                ||' ,rows_found        number(38)   '
                ||' ) tablespace '
                ||  ckaz_tbsp
                   ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);

	rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 43');

    select count(*)
    into exists_count
    from user_tables
    where table_name = 'CKAZ_TYPES_2_CHECK';
	
    IF exists_count = 1 Then
	  rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 43.1');
      cursor_name := dbms_sql.open_cursor;
      dbms_sql.parse(cursor_name,
                    ' drop table CKAZ_TYPES_2_CHECK '
                     ,DBMS_SQL.NATIVE);
      ignore := dbms_sql.execute(cursor_name);
      dbms_sql.close_cursor(cursor_name);
    End If;

    rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 43.2');

    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                    'create table CKAZ_TYPES_2_CHECK '
                  ||' ( ty_id , type_of , table_name  ) '
                  ||' tablespace '
                  ||  ckaz_tbsp
                  ||' as select distinct el_ty, el_type_of, '
                  ||' ''' || 'SDD_ELEMENTS' || ''' '
                  ||' from sdd_elements '
                  ||' where el_type_of != '
                  ||' ''' || 'DIAG' || ''' '
                  ||' union select distinct str_ty, str_type_of, '
                  ||' ''' || 'SDD_STRUCTURE_ELEMENTS' || ''' '
                  ||' from sdd_structure_elements '
                     ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
	
	rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 43.3');

    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                    'create index CKAZ_TYPES_2_CHECK1 on '
                    || 'CKAZ_TYPES_2_CHECK(TY_ID) '
                    || ' tablespace ' || ckaz_tbsp
                    ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
	
	
	rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 43.4');

    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                    'create index CKAZ_TYPES_2_CHECK2 on '
                    || 'CKAZ_TYPES_2_CHECK(TYPE_OF) '
                    || ' tablespace ' || ckaz_tbsp
                    ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
	
	rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 44');

    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                    'create unique index ckaz_chk_check_no   on '
                    || 'CKAZ_CHECKS (CHECK_NO) '
                    || ' tablespace ' || ckaz_tbsp
                    ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);


    EXCEPTION
      When Others Then
	  	rmdbg.trace(utility_name|| 'anal_create_ckaz_tables' ||' loc 45');
        If dbms_sql.is_open(cursor_name)
        Then dbms_sql.close_cursor(cursor_name);
        End If;
        Raise;
  END anal_create_ckaz_tables;

  Procedure anal_log_obs_rows Is
    -- dependent on CKAZ_TYPES_2_CHECK creation in previous PL/SQL block
    cursor_name   integer;
    ignore        integer;
 
  BEGIN  
    rmdbg.trace(utility_name|| 'anal_log_obs_rows' ||' loc 60');  

    -- Record elements which do not correlate to the Repository
    -- type model, to be deleted i.e.
    --   no corresponding SHORT_NAME in RM_ELEMENT_TYPES for 
    --   SDD_ELEMENTS.EL_TYPE_OF
    --   Similary with SDD_STRUCTURE_ELEMENTS.STR_TYPE_OF.

    -- don't use UNION of inserts to avoid large sort space
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                    'insert into CKAZ_ACTIONS ( '
                  ||'  reference_id  '
                  ||', table_name '
                  ||', base_table_name '
                  ||', elmt_type '
                  ||', action '
                  ||', date_created '
                  ||', date_changed '
                  ||', user_created_by '
                  ||', user_changed_by '
                  ||') '
                  ||'select e.el_id         '
                  ||',     ''SDD_ELEMENTS''  '
                  ||',     ''SDD_ELEMENTS''  '
                  ||',      e.el_type_of   '
                  ||',     ''DELETE'' '
                  ||',      e.el_date_created '
                  ||',      e.el_date_changed '
                  ||',      e.el_user_created_by '
                  ||',      e.el_user_changed_by '
                  ||'from CKAZ_TYPES_2_CHECK       c '
                  ||',    sdd_elements             e '
                  ||'where e.el_type_of = c.type_of '
                  ||'  and c.table_name = ''SDD_ELEMENTS'' '
                  ||'  and  '
                  ||'      not exists ( '
                  ||'       select null '
                  ||'       from rm_element_types  e '
                  ||'       where e.short_name = c.type_of) '
                     ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
	
	rmdbg.trace(utility_name|| 'anal_log_obs_rows' ||' loc 61');  
	
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                    'insert into CKAZ_ACTIONS ( '
                  ||'  reference_id '
                  ||', table_name '
                  ||', base_table_name '
                  ||', elmt_type '
                  ||', action '
                  ||', date_created '
                  ||', date_changed '
                  ||', user_created_by '
                  ||', user_changed_by '
                  ||') '
                  ||'select s.str_id  '
                  ||',      ''SDD_STRUCTURE_ELEMENTS'' '
                  ||',      ''SDD_STRUCTURE_ELEMENTS'' '
                  ||',      s.str_type_of '
                  ||',      ''DELETE'' '
                  ||',      s.str_date_created '
                  ||',      s.str_date_changed '
                  ||',      s.str_created_by '
                  ||',      s.str_changed_by '
                  ||'from CKAZ_TYPES_2_CHECK      c '
                  ||',    sdd_structure_elements   s '
                  ||'where s.str_type_of = c.type_of '
                  ||'  and c.table_name = ''SDD_STRUCTURE_ELEMENTS'' '
                  ||'  and  '
                  ||'      not exists ( '
                  ||'       select null '
                  ||'       from rm_element_types  e '
                  ||'       where e.short_name = c.type_of) '
                     ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
    commit;
	
	rmdbg.trace(utility_name|| 'anal_log_obs_rows' ||' loc 62');  

    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                    'insert into CKAZ_ACTIONS ( '
                  ||'  reference_id  '
                  ||', table_name '
                  ||', base_table_name '
                  ||', elmt_type '
                  ||', action '
                  ||', date_created '
                  ||', date_changed '
                  ||', user_created_by '
                  ||', user_changed_by '
                  ||') '
                  ||' select el.el_id         '
                  ||',     ''SDD_ELEMENTS''  '
                  ||',     ''SDD_ELEMENTS''  '
                  ||',      el.el_type_of   '
                  ||',     ''RESET TY ID'' '
                  ||',      el.el_date_created '
                  ||',      el.el_date_changed '
                  ||',      el.el_user_created_by '
                  ||',      el.el_user_changed_by '
                  ||'from CKAZ_TYPES_2_CHECK       c '
                  ||',    sdd_elements             el '
                  ||'where el.el_type_of = c.type_of '
                  ||'  and nvl(el.el_ty, 0) = nvl(c.ty_id, 0) '
                  ||'  and c.table_name = ''SDD_ELEMENTS'' '
                  ||'  and  '
                  ||'      not exists ( '
                  ||'       select null '
                  ||'       from rm_element_types  e '
                  ||'       where e.id = el.el_ty) '
                  ||'  and  '
                  ||'       exists ( '
                  ||'       select null '
                  ||'       from rm_element_types  e '
                  ||'       where e.short_name = el.el_type_of) '
                     ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);

    rmdbg.trace(utility_name|| 'anal_log_obs_rows' ||' loc 63');  

    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                    'insert into CKAZ_ACTIONS ( '
                  ||'  reference_id '
                  ||', table_name '
                  ||', base_table_name '
                  ||', elmt_type '
                  ||', action '
                  ||', date_created '
                  ||', date_changed '
                  ||', user_created_by '
                  ||', user_changed_by '
                  ||') '
                  ||' select st.str_id  '
                  ||',      ''SDD_STRUCTURE_ELEMENTS'' '
                  ||',      ''SDD_STRUCTURE_ELEMENTS'' '
                  ||',      st.str_type_of '
                  ||',      ''RESET TY ID'' '
                  ||',      st.str_date_created '
                  ||',      st.str_date_changed '
                  ||',      st.str_created_by '
                  ||',      st.str_changed_by '
                  ||'from CKAZ_TYPES_2_CHECK      c '
                  ||',    sdd_structure_elements   st '
                  ||'where st.str_type_of = c.type_of '
                  ||'  and nvl(st.str_ty, 0) = nvl(c.ty_id, 0) '
                  ||'  and c.table_name = ''SDD_STRUCTURE_ELEMENTS'' '
                  ||'  and  '
                  ||'      not exists ( '
                  ||'       select null '
                  ||'       from rm_element_types  e '
                  ||'       where e.id = st.str_ty) '
                  ||'  and  '
                  ||'      exists ( '
                  ||'       select null '
                  ||'       from rm_element_types  e '
                  ||'       where e.short_name = st.str_type_of) '
                     ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
	
	rmdbg.trace(utility_name|| 'anal_log_obs_rows' ||' loc 64');  

    -- Update any rows with wrong  'TY' IDs found in the previous 2 steps.
    -- Otherwise the analyser will not be able to check integrity of
    -- these rows.
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                    ' update sdd_elements '
                  ||' set el_ty = ( select e.id from rm_element_types e '
                  ||'                where e.short_name = el_type_of ) '
                  ||' where el_id in ( '
                  ||'     select reference_id from ckaz_actions '
                  ||'     where  action = ''RESET TY ID'' ) '
                     ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
	
	rmdbg.trace(utility_name|| 'anal_log_obs_rows' ||' loc 65');  

    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                    ' update sdd_structure_elements '
                  ||' set str_ty = ( select e.id from rm_element_types e '
                  ||'                where e.short_name = str_type_of ) '
                  ||' where str_id in ( '
                  ||'  select reference_id from ckaz_actions '
                  ||'  where  action = ''RESET TY ID'' ) '
                     ,DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
	
    commit;	

    EXCEPTION
      When Others Then
	    rmdbg.trace(utility_name|| 'anal_log_obs_rows' ||' loc 66');  
        Raise;

  END anal_log_obs_rows;

  Procedure anal_create_tmp_indexes(ckaz_tbsp varchar2) Is
    cursor_name   integer;
    ignore        integer;
    exists_count  integer;
  BEGIN
    rmdbg.trace(utility_name|| 'anal_create_tmp_indexes' ||' loc 50');
   
    select count(*)
    into   exists_count
    from user_indexes
    where index_name = 'CKAZ_EL_TYPE_OF';

    If exists_count = 0 Then
	 rmdbg.trace(utility_name|| 'anal_create_tmp_indexes' ||' loc 51');
      cursor_name := dbms_sql.open_cursor;
      dbms_sql.parse(cursor_name,
                    'create index ckaz_el_type_of on SDD_ELEMENTS(EL_TYPE_OF) '
                    || ' tablespace ' ||  ckaz_tbsp
                    ,DBMS_SQL.NATIVE);
     ignore := dbms_sql.execute(cursor_name);
      dbms_sql.close_cursor(cursor_name);
    End If;
	
	rmdbg.trace(utility_name|| 'anal_create_tmp_indexes' ||' loc 52');
	 
    cursor_name := dbms_sql.open_cursor;
    select count(*)
    into   exists_count
    from user_indexes
    where index_name = 'CKAZ_STR_TYPE_OF';

    If exists_count = 0 Then
	rmdbg.trace(utility_name|| 'anal_create_tmp_indexes' ||' loc 53');
    dbms_sql.parse(cursor_name,
                    'create index ckaz_str_type_of on '
                    || 'SDD_STRUCTURE_ELEMENTS(STR_TYPE_OF) '
                    || ' tablespace ' || ckaz_tbsp
                    ,DBMS_SQL.NATIVE);
      ignore := dbms_sql.execute(cursor_name);
      dbms_sql.close_cursor(cursor_name);
    End If;
    EXCEPTION
      When Others Then
	    rmdbg.trace(utility_name|| 'anal_create_tmp_indexes' ||' loc 54');
        If dbms_sql.is_open(cursor_name)
        Then dbms_sql.close_cursor(cursor_name);
        End If;
        Raise;
  END anal_create_tmp_indexes;

  Procedure anal_create_indexes(ckaz_tbsp varchar2) Is
    cursor_name   integer;
    ignore        integer;
    cnt_actions   integer;
    exists_count  integer;
  BEGIN
    rmdbg.trace(utility_name|| 'anal_create_indexes' ||' loc 120');
  
    -- If there are more than 1000 actions or more than 
    -- 20,000 elements then create indexes
    -- on the actions table to speed execution of actions
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
             'select count(*) '
           ||'from ckaz_actions'
               , DBMS_SQL.NATIVE);
    dbms_sql.define_column(cursor_name, 1, cnt_actions);
    ignore := dbms_sql.execute(cursor_name);
    If dbms_sql.fetch_rows(cursor_name) > 0 Then
      dbms_sql.column_value(cursor_name, 1, cnt_actions);
    End If;
    dbms_sql.close_cursor(cursor_name);
	
	rmdbg.trace(utility_name|| 'anal_create_indexes' ||' loc 121');

    select count(*)
    into   exists_count
    from   sdd_elements;

    If cnt_actions > 1000 or exists_count > 20000 Then
   
      rmdbg.trace(utility_name|| 'anal_create_indexes' ||' loc 122');
   
      select count(*)
      into   exists_count
      from user_indexes
      where index_name = 'CKAZ_I_ACT_1';

      If exists_count = 0 Then
        cursor_name := dbms_sql.open_cursor;
        dbms_sql.parse(cursor_name,
                    'create index ckaz_i_act_1 on CKAZ_ACTIONS('
                     || 'REFERENCE_ID, REFD_ELMT_TYPE)'
                     || ' tablespace ' ||  ckaz_tbsp
                     ,DBMS_SQL.NATIVE);
        ignore := dbms_sql.execute(cursor_name);
        dbms_sql.close_cursor(cursor_name);
      End If;
	  
	  rmdbg.trace(utility_name|| 'anal_create_indexes' ||' loc 123');
	  
      select count(*)
      into   exists_count
      from user_indexes
      where index_name = 'CKAZ_I_ACT_2'
      ;
      If exists_count = 0 Then
	    rmdbg.trace(utility_name|| 'anal_create_indexes' ||' loc 124');
	  
        cursor_name := dbms_sql.open_cursor;
        dbms_sql.parse(cursor_name,
                    'create index ckaz_i_act_2 on CKAZ_ACTIONS('
                     || 'REFD_ELMT_TYPE, ELMT_TYPE)'
                     || ' tablespace ' ||  ckaz_tbsp
                     ,DBMS_SQL.NATIVE);
        ignore := dbms_sql.execute(cursor_name);
        dbms_sql.close_cursor(cursor_name);
      End If;
	  
	  rmdbg.trace(utility_name|| 'anal_create_indexes' ||' loc 125');
	  
      select count(*)
      into   exists_count
      from user_indexes
      where index_name = 'CKAZ_I_ACT_3';

      If exists_count = 0 Then
	    rmdbg.trace(utility_name|| 'anal_create_indexes' ||' loc 126');	  
        cursor_name := dbms_sql.open_cursor;
        dbms_sql.parse(cursor_name,
                    'create index ckaz_i_act_3 on CKAZ_ACTIONS('
                     || 'CHECK_NO)'
                     || ' tablespace ' ||  ckaz_tbsp
                     ,DBMS_SQL.NATIVE);
        ignore := dbms_sql.execute(cursor_name);
        dbms_sql.close_cursor(cursor_name);
      End If;

    rmdbg.trace(utility_name|| 'anal_create_indexes' ||' loc 127');

    End If;
    EXCEPTION
      When Others Then
	    rmdbg.trace(utility_name|| 'anal_create_indexes' ||' loc 128');
        If dbms_sql.is_open(cursor_name) Then 
          dbms_sql.close_cursor(cursor_name);
        End If;
        Raise;
  END anal_create_indexes;


  Procedure anal_drop_tmp_indexes Is
    cursor_name   integer;
    ignore        integer;
  BEGIN
    rmdbg.trace(utility_name|| 'anal_drop_tmp_indexes' ||' loc 90');
	
    select count(*) 
    into   ignore
    from user_indexes
    where index_name in ('CKAZ_EL_TYPE_OF', 'CKAZ_STR_TYPE_OF');
  
    If ignore > 0 Then
	  rmdbg.trace(utility_name|| 'anal_drop_tmp_indexes' ||' loc 91');
	 
      cursor_name := dbms_sql.open_cursor;
      dbms_sql.parse(cursor_name, 'drop index ckaz_el_type_of ' 
                    ,DBMS_SQL.NATIVE);
      ignore := dbms_sql.execute(cursor_name);
      dbms_sql.close_cursor(cursor_name);
	  
	  rmdbg.trace(utility_name|| 'anal_drop_tmp_indexes' ||' loc 92');
	   
      cursor_name := dbms_sql.open_cursor;
      dbms_sql.parse(cursor_name, 'drop index ckaz_str_type_of ' 
                    ,DBMS_SQL.NATIVE);
      ignore := dbms_sql.execute(cursor_name);
      dbms_sql.close_cursor(cursor_name);
    End IF;

    EXCEPTION
      When Others Then
	    rmdbg.trace(utility_name|| 'anal_drop_tmp_indexes' ||' loc 93');
        If dbms_sql.is_open(cursor_name)
        Then dbms_sql.close_cursor(cursor_name);
        End If;
        raise;
  END anal_drop_tmp_indexes;


  Procedure ins_check_stmt (
      ref_tab       varchar2     -- base table for reference property
     ,ref_col       varchar2     -- base column for reference property
     ,type_of       varchar2     -- reference properties' element type
     ,id_type_of    varchar2     -- element type of referenced element
     ,slkt_clause   varchar2     -- 'select/from' clause
     ,exst_clause   varchar2     -- 'existence' condition clause
     ,mnd_clause    varchar2     -- extra condition to handle ARCs
     ,action        varchar2     -- action to take by 'exec_purge'
     ,check_no      integer
    ) Is

      cursor_name   integer;
      rows_inserted integer;
      rows_updated  integer;
      message       varchar2(160);

  BEGIN
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name,
                ' insert into CKAZ_CHECKS '
              ||' ( check_mode, type_check, check_stmt, check_no) '
              ||' values ( '
              ||' ''REFERENTIAL'', :type_of, :message, ' || check_no ||') '
                , DBMS_SQL.NATIVE);
    If mnd_clause != '0' Then
     dbms_sql.bind_variable(cursor_name, ':message'
              , slkt_clause
             || exst_clause || ' and ( '
             || mnd_clause || ' ) ' );
    Else 
     dbms_sql.bind_variable(cursor_name, ':message', slkt_clause||exst_clause);
    End If;

    dbms_sql.bind_variable(cursor_name, ':type_of', type_of);
    rows_inserted:= dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
    commit;

    EXCEPTION
      When Others Then
        If dbms_sql.is_open(cursor_name)
        Then dbms_sql.close_cursor(cursor_name);
        End If;
		if (brepowner = TRUE) then
          anal_drop_tmp_indexes;
		end if;
        Raise;

  END ins_check_stmt;


  Procedure anal_cons_ref_checks Is
      -- Procedure builds SQL select statement to drive insert 
      -- into CKAZ_ACTIONS.
      -- Check statements are of the form
      --    >>-- SELECT <list> -------------------->
      --    >>-- FROM   <API_viewname>------------->
      --                V------- AND -----------|
      --    >>-- WHERE ---- <not exists cond> ----->
      --    >>-------------------------------------><
      --          |- OR (<mandatory check cond>) -|
      --
      -- The SQL statement is complete when all the references
      -- (REF_COLUMN_NAME -> ID pairs)  for each type (i.TYPE_OF)
      -- have been retrieved by the cursor (the order by clause is
      -- essential!)
      -- All the check statements are stored in the CKAZ_CHECKS
      -- table, for subsequent execution.

      -- This cursor gets the reference property columns of API views 
      -- based on SDD_ELEMENTS and STRUCTURE_ELEMENTS, as defined in 
      -- the Repository type model, together with audit columns. 
      -- These are read from the Repository meta data tables. 
      Cursor reference_columns Is
      select  distinct
              uv.view_name                                     VIEWNAME	
		 ,upper(st.name)						   BASE_TABLE_NAME	
             ,upper(et.short_name)                             TYPE_OF
             ,lp.arc_no                                        ARC_NUMBER
             ,upper(lp.name)                                   REF_COLUMNNAME
             ,DECODE(upper(sc.name)
                    ,'EL_ELEM_OWNED_BY'
                    , 'ABS(q1.'|| upper(lp.name) ||') '
                    ,'q1.'|| upper(lp.name) )                  QREF_COLUMN
             ,upper(sc.name)                                   BASE_COLUMN
             ,nvl(uvo.view_name, upper(stid.name))             ID_VIEWNAME
             ,' not exists ( select null from ' ||
             --
               DECODE
                 (uvo.view_name
                 , NULL
                 ,DECODE
                    (upper(et.short_name)
                  , 'DEU'
                    ,DECODE
                      (upper(lp.name)
                      -- Element references not catered for correctly
                      -- in RM data, so this is a special case
                      , 'CIELEMENT_REFERENCE'
                      , ' sdd_structure_elements q3 '||
                         ' where q3.str_id = q1.CIELEMENT_REFERENCE'||
                         ' union select null from ' 
                      , NULL
                     ) || ' SDD_ELEMENTS q2 where q2.EL_ID= '
                  ,DECODE 
                     (upper(stid.name)
                     ,'SDD_ELEMENTS'
                     ,' SDD_ELEMENTS q2 where ' ||
					 DECODE
                                 (upper(et.short_name)
                                  , 'UPR', ''
                                  , '(q2.EL_TYPE_OF = ''APP'' OR q2.EL_ELEM_OWNED_BY in ( select id from upg_app)) AND '
                                 ) ||
					 ' q2.EL_ID= '
                    ,' SDD_STRUCTURE_ELEMENTS q2 ' ||
                     ' where q2.STR_ELEM_OWNED_BY in ( select id from upg_app) AND q2.STR_ID= '
                    )
                  ) 
               , uvo.view_name || ' q2 where ' || 
			   ' q2.' ||
			   				DECODE
                                            (upper(eto.short_name)
                                             ,'APP', 'ID'   																 																															 																			  					   										  
   		   					   ,DECODE
                                                (upper(et.short_name)
                                                 , 'APP', 'ID'
                                                 ,'APPLICATION_SYSTEM_OWNED_BY' 
                                                )
                                            ) ||
				' in ( select id from upg_app ) AND ' ||
			   'q2.ID= ' 
               )|| 
               -- for PACs use ABS on references to application systems
               -- as -ve id's mean the object is locked
			    DECODE(upper(sc.name)
			                   ,'EL_ELEM_OWNED_BY'
			                   , 'ABS(q1.'|| upper(lp.name) ||') '
			                   ,'q1.'||upper(lp.name) 
                ) ||       				              
                    -- Kludge to handle granting of access to PUBLIC
                    DECODE(uv.view_name
                     ,'CI_ORACLE_USER_MODULE_ACCESSES'
                      ,DECODE(upper(lp.name)
                       ,'ORACLE_DATABASE_USER_REFERENCE'
                      ,' or q1.ORACLE_DATABASE_USER_REFERENCE = 0 ' 
                      ,NULL
                     )
                     ,NULL
                    ) ||
                    -- end kludge					
                ' ) ' ||
				' AND q1.' ||	
				DECODE(upper(et.short_name), 'APP', 'ID', 'APPLICATION_SYSTEM_OWNED_BY') ||
				' in ( select id from upg_app ) '               				EXISTS_COND
             ,DECODE (upper(st.name)
                 ,'SDD_ELEMENTS','EL_ID'
                 ,'STR_ID')                                    ID
             ,'q2.ID'                                          ID_COLUMNNAME
             ,upper(eto.short_name)                            ID_TYPE_OF
             ,DECODE(upper(et.short_name)
                    , 'DEU' ,DECODE(eto.short_name,'DIA','DELETE','CONSOLIDATE')
                    ,DECODE(lp.min_cardinality
                           ,1 ,'DELETE', 'NULLIFY'))           ACTION
             ,et.id                                            ET_ID
      from rm_sql_tables       st
      ,    all_views           uv
      ,    rm_sql_columns      sc
      ,    rm_element_types    et
      ,    rm_link_properties  lp
      ,    rm_property_maps    pm 
      ,    rm_link_properties  lpo
      ,    rm_element_types    eto
      ,    rm_properties       pyid
      ,    rm_property_maps    pmid 
      ,    rm_sql_columns      scid
      ,    rm_sql_tables       stid
      ,    all_views           uvo
      where
            pm.context = et.id    
        and uv.view_name = upper('CI_'||et.plural_name)
        and pm.property = lp.id   
        and pm.in_column = sc.id 
        and st.id        = sc.in_table
        and lpo.link_type = lp.link_type
        and lpo.defined_against = eto.id
        and lpo.id != lp.id
        and pmid.context  = eto.id
        and pyid.name = 'ID'
        and pmid.property = pyid.id
        and pmid.in_column = scid.id
        and stid.id        = scid.in_table
        and uvo.view_name (+) = upper('CI_'||eto.plural_name)
        and scid.name  in ('EL_ID', 'STR_ID')
        and st.name    in ('SDD_ELEMENTS', 'SDD_STRUCTURE_ELEMENTS')
		and lp.min_cardinality in (1,0) 
	 	and uv.owner = repos_owner
		and uvo.owner(+) = repos_owner
      order by 1,2,3,4;


      -- initialise loop variables for detecting 'break in value'      
      iminus1tab    varchar2(30) := '*';
      iminus1col    varchar2(30) := '*';
      iminus1bas    varchar2(30) := '*';
      iminus1arc    varchar2(30) := '*';
      iminus1typ    varchar2(30) := '*';
      iminus1act    varchar2(12) := '*';
      iminus1itp    varchar2(30) := '*';
      -- dynamic SQL clause building variables
      stmt_no       integer := 0;
      num_clauses   integer := 0;  -- control counter used to limit sizeof check_stmt 
      MAX_VARCHAR2  integer := 1950;  -- maximum number of chararcters to limit sizeof 
                                      -- check_stmt
                                      -- CW Fix bug 1635490
                                      -- originally 2000 but reduced to allow for
                                      -- minor changes in size of actual sql generated
                                      -- (originally imposed due to VARCHAR2 max of 2000)                                
      mnd_clause    varchar2(2000) := '0';
      slkt_clause   varchar2(900);
      slkt1_clause   varchar2(900);
      exst_clause   varchar2(2000);
      audt_props    varchar2(60) :=
                                 ' DATE_CREATED, DATE_CHANGED, '
                               ||' CREATED_BY, CHANGED_BY ';

      -- flag for first time round loop
      notfirst      boolean := FALSE;

  BEGIN
    rmdbg.trace(utility_name|| 'anal_cons_ref_checks' ||' loc 70');

    For rec In reference_columns Loop
      -- Build SQL select statement to drive insert into CKAZ_ACTIONS.
      -- Check statements are of the form
      --    >>-- SELECT <list> -------------------->
      --    >>-- FROM   <API_viewname>------------->
      --                V------- AND -----------| 
      --    >>-- WHERE ---- <not exists cond> ----->
      --    >>-------------------------------------><  
      --          |- OR (<mandatory check cond>) -|
      --
      -- The SQL statement is complete when all the references 
      -- (REF_COLUMN_NAME -> ID pairs)  for each type (rec.TYPE_OF)
      -- have been retrieved by the cursor (the order by clause is
      -- essential!)
      -- All the check statements are stored in the CKAZ_CHECKS
      -- table, for subsequent execution.

      If iminus1tab = rec.VIEWNAME and iminus1arc = rec.ARC_NUMBER Then
        -- need multiple check on arc reference properties
        
        -- CW Fix bug 1635490
        -- Need to specify the slkt_clause BEFORE the length test as the clause
        -- being tested for length below needs to be the same as that passed to
        -- the ins_check_stmt procedure.

        -- If ARC checking statements are split due to size then
        -- need MANUAL resolution for now.

        -- LO Bug 2251035
        -- Code was resetting checks to MANUAL even when MAX length
        -- was not exceeded. Store MANUAL select statement for length
        -- check but only use if length exceeded.
        
        slkt1_clause := ' select '
            ||         ' ID '
            ||' , ''' || 'MANUAL'         || ''' '
            ||' , ''' || rec.VIEWNAME       || ''' '
  		      ||' , ''' || rec.BASE_TABLE_NAME|| ''' '
            ||' , ''' || 'REFER TO CHECKS' || ''' '
            ||' , '   || rec.REF_COLUMNNAME
            ||' , ''' || rec.TYPE_OF        || ''' '
            ||' , ''' || rec.ID_TYPE_OF     || ''' '
            ||' , '   || stmt_no
            ||' , '   || audt_props
            ||' from ' || rec.VIEWNAME || ' q1 where '
            ;

        -- CW Fix bug 1635490
        -- The length checking needs to check for some extra characters added
        -- by the ins_check_stmt procedure and possibly the
        -- 'if action = nullify' test below

        If length(slkt1_clause)
         + length(exst_clause)
         + length(' and ' || rec.EXISTS_COND)                         -- will be added next time round loop
         + length(' and ( ' || mnd_clause || ' ) ')
         + length(' or q1.' || rec.REF_COLUMNNAME || ' is not null ') -- may be added next time
         > MAX_VARCHAR2
        Then
          -- need to 'flush' otherwise check stmt may exceed MAX_VARCHAR2 chars
          ins_check_stmt(iminus1tab, iminus1col, iminus1typ, iminus1itp
                        ,slkt1_clause ,exst_clause, mnd_clause
                        ,iminus1act, stmt_no);
          exst_clause := rec.EXISTS_COND;
          num_clauses := 0;
          stmt_no := stmt_no + 1;
        Else
          exst_clause := exst_clause || ' and ' || rec.EXISTS_COND;
          num_clauses := num_clauses + 1;
        End If;
		 
        If rec.ACTION = 'NULLIFY' and iminus1bas != rec.BASE_COLUMN Then
          -- additional check for dangling NOT NULLS in optional Fkeys 
          mnd_clause  := mnd_clause || ' or q1.'||rec.REF_COLUMNNAME||' is not null ';
          -- and need modified SELECT list if check is for multiple references
          -- Has to be a MANUAL correction because there is more than one
          -- foreign key to consider for nullifying.
          slkt_clause := ' select '
                ||         ' ID '
                ||' , ''' || 'MANUAL'         || ''' '
                ||' , ''' || rec.VIEWNAME         || ''' '
  		    ||' , ''' || rec.BASE_TABLE_NAME|| ''' '
                ||' , ''' || 'REFER TO CHECKS' || ''' '
                ||' , NULL'
                ||' , ''' || rec.TYPE_OF          || ''' '
                ||' , NULL'
                ||' , '   || stmt_no
                ||' , '   || audt_props
                ||' from ' || rec.VIEWNAME || ' q1 where ' ;
        End If;

      Else -- on to a new reference TABLE/COLUMN/element-type pair to check
        -- and having finished building the conditions for the previous
        -- TABLE/COLUMN pair, store the resulting statement		
        If notfirst Then 
          ins_check_stmt(iminus1tab, iminus1col, iminus1typ, iminus1itp
                            ,slkt_clause ,exst_clause, mnd_clause
                            ,iminus1act, stmt_no);
          mnd_clause := '0';
        Else notfirst:=TRUE;
        End If;			

        stmt_no     := stmt_no + 1;
        num_clauses := 0;

        -- build <select list> and <from viewname> clause
        slkt_clause := ' select '
                ||         ' ID '
                ||' , ''' || rec.ACTION         || ''' '
                ||' , ''' || rec.VIEWNAME       || ''' '
  		    ||' , ''' || rec.BASE_TABLE_NAME|| ''' '
                ||' , ''' || rec.REF_COLUMNNAME || ''' '
                ||' , '   || rec.REF_COLUMNNAME
                ||' , ''' || rec.TYPE_OF        || ''' '
                ||' , ''' || rec.ID_TYPE_OF     || ''' '
                ||' , '   || stmt_no
                ||' , '   || audt_props
                ||' from ' || rec.VIEWNAME || ' q1 where ' ;

        iminus1tab  := rec.VIEWNAME;
        exst_clause := rec.EXISTS_COND;	

        If rec.ACTION = 'NULLIFY' Then  
          -- additional check for dangling NOT NULLS in optional Fkeys 
          mnd_clause:= ' q1.' ||rec.REF_COLUMNNAME|| ' is not null ';
        End If;

      End If;
      iminus1col  := rec.REF_COLUMNNAME;
      iminus1bas  := rec.BASE_COLUMN;
      iminus1arc  := rec.ARC_NUMBER;
      iminus1typ  := rec.TYPE_OF;
      iminus1act  := rec.ACTION;
      iminus1itp  := rec.ID_TYPE_OF;

    End Loop;

    -- store the SQL statement built up in the last cycle of the loop
    ins_check_stmt(iminus1tab, iminus1col, iminus1typ,iminus1itp
                      ,slkt_clause ,exst_clause, mnd_clause
                      , iminus1act, stmt_no);

    EXCEPTION
      When Others Then
        anal_drop_tmp_indexes;
        Raise;
  END anal_cons_ref_checks;  

  Procedure anal_exec_checks (mode_of_check varchar2) Is
      fet_cursor    integer;
      ins_cursor    integer;
      upd_cursor    integer;
      rows_inserted integer;
      rows_updated  integer;
      ignore        integer;
      check_stmt    varchar2(2000);
      check_no      integer;
  BEGIN
    rmdbg.trace(utility_name|| 'anal_exec_checks' ||' loc 80');
   
    fet_cursor := dbms_sql.open_cursor;
    -- Check each row in the given table to see if the value in the given
    -- 'reference column' is still that of a valid SDD_ELEMENT row.
    -- If not insert the id of the row in the 'actions' table together
    -- with the relevant audit information to allow further analysis.
    -- The 'ACTION' will be used later to determine what happens to
    -- the row or column value.
    dbms_sql.parse(fet_cursor,
             'select check_no,check_stmt '
           ||'from ckaz_checks '
           ||'where enabled = ''Y'' '
           ||'  and check_mode = ''' || mode_of_check || ''''
           ||'order by check_no ' 
               , DBMS_SQL.NATIVE);
    dbms_sql.define_column(fet_cursor, 1, check_no);
    dbms_sql.define_column(fet_cursor, 2, check_stmt, 2000);
    ignore := dbms_sql.execute(fet_cursor);
	
	rmdbg.trace(utility_name|| 'anal_exec_checks' ||' loc 81');
	   
    Loop
      If dbms_sql.fetch_rows(fet_cursor) > 0 Then	  
        dbms_sql.column_value(fet_cursor, 1, check_no);
        dbms_sql.column_value(fet_cursor, 2, check_stmt);
        ins_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(ins_cursor,
                 ' INSERT into ckaz_actions ( '
               ||'   reference_id '
               ||' , action '
               ||' , table_name '
               ||' , base_table_name '
               ||' , column_name '
               ||' , value '
               ||' , elmt_type '
               ||' , refd_elmt_type '
               ||' , check_no '
               ||' , date_created '
               ||' , date_changed '
               ||' , user_created_by '
               ||' , user_changed_by '
               ||' ) '
               || check_stmt
                   , DBMS_SQL.NATIVE);

        rows_inserted:= dbms_sql.execute(ins_cursor);
        dbms_sql.close_cursor(ins_cursor);
        -- timestamp each element type for monitoring progress
        -- and diagnosis if all checks not completed
				
        upd_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(upd_cursor,
                 ' UPDATE ckaz_checks '
               ||' set checked_date_time = to_char(SYSDATE, '
               ||  ' ''DD-MON-YY HH24:MI:SS'') '
               ||',   rows_found = ' || rows_inserted 
               ||' where check_no = ' || check_no
                   , DBMS_SQL.NATIVE);
        rows_updated:= dbms_sql.execute(upd_cursor);
        dbms_sql.close_cursor(upd_cursor);

        commit;
      Else Exit; -- no more checks to execute
      End If;
    End Loop;

    EXCEPTION
      When Others Then
	  	rmdbg.trace(utility_name|| 'anal_exec_checks' ||' loc 82');
        If dbms_sql.is_open(fet_cursor)
        Then dbms_sql.close_cursor(fet_cursor);
        End If;
        If dbms_sql.is_open(ins_cursor)
        Then dbms_sql.close_cursor(ins_cursor);
        End If;
        If dbms_sql.is_open(upd_cursor)
        Then dbms_sql.close_cursor(upd_cursor);
        End If;
        anal_drop_tmp_indexes;

        Raise;
  END anal_exec_checks;
  

  Procedure exec_del_actions Is
  -- Execute the ACTION for each row in the CKAZ_ACTIONS table.
  -- Currently, actions other than 'UPDATE' or 'DELETE' are ignored
  -- e.g. 'CONSOLIDATE' should be executed via the appropriate 
  -- diagramming tool.
  -- Note that the audit table triggers are left enabled, therefore 
  -- UPDATE actions will be recorded in the base row.
     ignore       integer;

     i_ref_id    integer :=0;
     i_value     integer;
     i_check_no  integer;
     i_view_type     varchar2(30);
     i_table_name    varchar2(30);
     i_column_name   varchar2(30);
     i_ref_view_type varchar2(30);

     v_min_ref   integer;         -- variables to span range of ids over 
     v_max_ref   integer;         -- 'k_max_rows'
     loop_count  integer :=0;
     
     fet_cursor  integer;
     del_cursor  integer;
     upd_cursor  integer;
     loop_cursor integer;

  BEGIN
    rmdbg.trace(utility_name|| 'exec_del_actions' ||' loc 140');
	  
    -- Delete rows from the SDD_ELEMENTS and SDD_STRUCTURE_ELEMENTS 
    -- tables which depend on other element objects which no longer exist. 
    -- Rows to be removed have been identified by the repository Integrity
    -- Check Analyser.

    fet_cursor := dbms_sql.open_cursor;  
    dbms_sql.parse(fet_cursor,
              ' select min(reference_id) '
            ||' from ckaz_actions '
            ||' where action = ''DELETE'' '
            ||'   and check_no is not NULL'  -- exclude invalid element types
               , DBMS_SQL.NATIVE);
    dbms_sql.define_column(fet_cursor, 1, v_min_ref);
    ignore := dbms_sql.execute(fet_cursor);

    ignore := dbms_sql.fetch_rows(fet_cursor); -- must/should! return 1 row
    dbms_sql.column_value(fet_cursor, 1, v_min_ref);
    dbms_sql.close_cursor(fet_cursor);
	
	rmdbg.trace(utility_name|| 'exec_del_actions' ||' loc 141');

    -- del_actions
    While v_min_ref is not NULL
    Loop		  
      loop_cursor := dbms_sql.open_cursor;
      -- start deleting from v_min_ref, which is recomputed before each parse
      -- to avoid an infinite loop if for some reason the rows are not deleted.
      dbms_sql.parse(loop_cursor,
                ' select  a.reference_id  '
              ||'  ,      a.elmt_type    '
              ||'  ,      a.base_table_name  '
              ||'  ,      nvl(a.value, a.reference_id)  '
              ||'  ,      a.refd_elmt_type              '
              ||'  from ckaz_actions       a '
              ||'  where a.action = ''DELETE'' '  
              ||'  and a.reference_id >= ' || v_min_ref  
              ||'  order by a.reference_id '
               , DBMS_SQL.NATIVE);
      dbms_sql.define_column(loop_cursor, 1, i_ref_id);
      dbms_sql.define_column(loop_cursor, 2, i_view_type , 30);
      dbms_sql.define_column(loop_cursor, 3, i_table_name, 30);
      dbms_sql.define_column(loop_cursor, 4, i_value);
      dbms_sql.define_column(loop_cursor, 5, i_ref_view_type , 30);
      ignore := dbms_sql.execute(loop_cursor);	

      -- There must always be at least 1 row to delete if this
      -- location is reached otherwise we have an unhandled
      -- program or data exception
      While dbms_sql.fetch_rows(loop_cursor) > 0 and loop_count < k_max_rows
      Loop
	  	
        dbms_sql.column_value(loop_cursor, 1, i_ref_id);
        dbms_sql.column_value(loop_cursor, 2, i_view_type);
        dbms_sql.column_value(loop_cursor, 3, i_table_name);
        dbms_sql.column_value(loop_cursor, 4, i_value);
        dbms_sql.column_value(loop_cursor, 5, i_ref_view_type);

        If i_table_name = 'SDD_ELEMENTS' Then
          -- delete the referenced element and all dependent rows 
          ciiutl.delete_element (i_ref_id, i_view_type
                                ,i_value, i_ref_view_type);
        End If;
        v_max_ref := i_ref_id;
        loop_count := loop_count + 1;
      End Loop;
      dbms_sql.close_cursor(loop_cursor);

      -- Delete structure elements whose referenced elements 
      -- no longer exist.
      del_cursor := dbms_sql.open_cursor;
      dbms_sql.parse(del_cursor,
                ' delete from sdd_structure_elements '
             || ' where str_id between ' || v_min_ref ||' and '|| v_max_ref
             || '   and exists ( '
             || '       select null '
             || '       from ckaz_actions '
             || '       where reference_id = str_id '
             || '       ) '
               , DBMS_SQL.NATIVE);
      ignore := dbms_sql.execute(del_cursor);
      dbms_sql.close_cursor(del_cursor);


      upd_cursor := dbms_sql.open_cursor;
      dbms_sql.parse(upd_cursor,
                ' update ckaz_actions '
             || ' set action = ''DELETED'' '
             || ' where action = ''DELETE'' '
             || '   and not exists ( '
             || '          select null '
             || '          from sdd_elements '
             || '          where reference_id = el_id ) '    
             || '   and not exists ( '
             || '          select null '
             || '          from sdd_structure_elements '
             || '          where reference_id = str_id ) '
               , DBMS_SQL.NATIVE);
      ignore := dbms_sql.execute(upd_cursor);
      dbms_sql.close_cursor(upd_cursor);    
	  
      Exit When loop_count < k_max_rows;
      v_min_ref := v_max_ref;
      loop_count := 0;
    End Loop del_actions;

    EXCEPTION
      When Others Then
	    rmdbg.trace(utility_name|| 'exec_del_actions' ||' loc 142');
        If dbms_sql.is_open(fet_cursor) Then
          dbms_sql.close_cursor(fet_cursor);
        End If;
        If dbms_sql.is_open(upd_cursor) Then
          dbms_sql.close_cursor(upd_cursor);
        End If;
        If dbms_sql.is_open(loop_cursor) Then
          dbms_sql.close_cursor(upd_cursor);
        End If;

      Raise;
  END exec_del_actions;


  Procedure exec_del_text Is
  -- Remove dangling text lines from the CDI_TEXT table.
  -- Note: Release 2 has an additional table CK_APPLICATION_LOGIC
  --       which may have text lines.

    exists_count Integer := 0;
    ignore       Integer;
    cursor_name  Integer;

    delete_stmt_4r1 Varchar2(400) :=
      ' delete from cdi_text'||
      ' where not exists ( ' ||
      '  select null ' ||
      '  from sdd_elements  ' ||
      '  where txt_ref = el_id ) '||
      ' and not exists ( ' ||
      '  select null ' ||
      '  from sdd_structure_elements '||  
      '  where txt_ref = str_id ) ' ;

    xtra_cond_4r2 Varchar2(400) :=
      ' and not exists ( '||
      '  select null '||
      '  from ck_application_logic '||
      '  where txt_ref = al_id )';

  BEGIN
    rmdbg.trace(utility_name|| 'exec_del_text' ||' loc 150');
	
    select count(*)
    into exists_count
    from all_tables
    where table_name = 'CK_APPLICATION_LOGIC'
	and owner = repos_owner;

    If exists_count = 1 Then -- release 2, add constraint 
                             -- so the A.L. text is not lost
      cursor_name := dbms_sql.open_cursor;
      dbms_sql.parse(cursor_name
                    ,delete_stmt_4r1 ||
                     xtra_cond_4r2
                    ,DBMS_SQL.NATIVE);
      ignore := dbms_sql.execute(cursor_name);
      dbms_sql.close_cursor(cursor_name);
    Else -- a release 1 repository with no A.L.
      cursor_name := dbms_sql.open_cursor;
      dbms_sql.parse(cursor_name
                    ,delete_stmt_4r1
                    ,DBMS_SQL.NATIVE);
      ignore := dbms_sql.execute(cursor_name);
      dbms_sql.close_cursor(cursor_name);
    End If;

    commit;
    EXCEPTION
      When Others Then
      Raise;

  END exec_del_text;

  Procedure exec_upd_actions Is
    -- Set foreign key column values to NULL as specified in ckaz_actions
    -- This  is done using dynamic SQL so that updates are only issued
    -- if required.
     ignore       integer;

     i_ref_id    integer :=0;
     i_value     integer;
     i_check_no  integer;
     i_view_type     varchar2(30);
     i_table_name    varchar2(30);
     i_column_name   varchar2(30);
     i_ref_view_type varchar2(30);

     k_max_rows  integer := 1000; -- constant max# of rows in transaction
     v_min_ref   integer;         -- variables to span range of ids over
     v_max_ref   integer;         -- 'k_max_rows'

     rows_updated integer:= 0;    -- total rows updated in loop (break if>k_max_rows)
     
     fet_cursor  integer;
     upd_cursor  integer;
     loop_cursor integer;

  BEGIN
    rmdbg.trace(utility_name|| 'exec_upd_actions' ||' loc 160');
   
    fet_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(fet_cursor,
              ' select min(reference_id) '
            ||' from ckaz_actions '
            ||' where action = ''NULLIFY'' '
               , DBMS_SQL.NATIVE);
    dbms_sql.define_column(fet_cursor, 1, v_min_ref);
    ignore := dbms_sql.execute(fet_cursor);
	
	rmdbg.trace(utility_name|| 'exec_upd_actions' ||' loc 161');
  
    ignore := dbms_sql.fetch_rows(fet_cursor);  -- must/should! return 1 row
    dbms_sql.column_value(fet_cursor, 1, v_min_ref); 
    dbms_sql.close_cursor(fet_cursor);
	
	rmdbg.trace(utility_name|| 'exec_upd_actions' ||' loc 162');

    fet_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(fet_cursor,
                ' select max(reference_id) '
              ||' from ckaz_actions '
              ||' where action = ''NULLIFY'' '
                 , DBMS_SQL.NATIVE);
    dbms_sql.define_column(fet_cursor, 1, v_max_ref);
    ignore := dbms_sql.execute(fet_cursor);
    ignore := dbms_sql.fetch_rows(fet_cursor);        -- MAX >= MIN
    dbms_sql.column_value(fet_cursor, 1, v_max_ref); 
    dbms_sql.close_cursor(fet_cursor);
	
	rmdbg.trace(utility_name|| 'exec_upd_actions' ||' loc 163');

    -- ref_range_lev1
    While v_min_ref is not NULL
    Loop	 	
      loop_cursor := dbms_sql.open_cursor;
      dbms_sql.parse(loop_cursor,
                  ' select  check_no '
                ||' ,       table_name '
                ||' ,       column_name '
                ||' from   ckaz_actions '
                ||' where  action     = ''NULLIFY'' '
                ||'  and reference_id between ' || v_min_ref
                ||'                       and ' || v_max_ref
                ||' group by check_no, table_name, column_name '
                 , DBMS_SQL.NATIVE);
      dbms_sql.define_column(loop_cursor, 1, i_check_no);
      dbms_sql.define_column(loop_cursor, 2, i_table_name, 30);
      dbms_sql.define_column(loop_cursor, 3, i_column_name, 30);
      ignore := dbms_sql.execute(loop_cursor);

      rows_updated := 0;
      -- nullify_actions_lev2
      While dbms_sql.fetch_rows(loop_cursor) > 0 and rows_updated < k_max_rows
      Loop	
        dbms_sql.column_value(loop_cursor, 1, i_check_no);
        dbms_sql.column_value(loop_cursor, 2, i_table_name);
        dbms_sql.column_value(loop_cursor, 3, i_column_name);

        upd_cursor := dbms_sql.open_cursor;
        dbms_sql.parse (upd_cursor,
                 'update '||i_table_name||' set '||i_column_name||'=null'
                 ||' where ID in (select reference_id from ckaz_actions '
                 ||' where check_no  = ' || i_check_no
                 || '  and reference_id between '
                 ||  v_min_ref || ' and ' || v_max_ref
                 || '   and action = ''NULLIFY'' )'
                 , DBMS_SQL.NATIVE);
        ignore := dbms_sql.execute(upd_cursor);
        dbms_sql.close_cursor(upd_cursor);			

        upd_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(upd_cursor,
                  ' update ckaz_actions '
               || ' set action = ''NULLIFIED'' '
               || ' where action = ''NULLIFY'' '
               || '   and reference_id between ' || v_min_ref  
               || '           and '              || v_max_ref 
               || '   and check_no = ' || i_check_no
                 , DBMS_SQL.NATIVE);
        ignore := dbms_sql.execute(upd_cursor);
        rows_updated := rows_updated + ignore;
        dbms_sql.close_cursor(upd_cursor);
      End Loop nullify_actions_lev2;
      commit;	

      fet_cursor := dbms_sql.open_cursor;
      dbms_sql.parse(fet_cursor,
                ' select min(reference_id) '
              ||' from ckaz_actions '
              ||' where action = ''NULLIFY'' '
                 , DBMS_SQL.NATIVE);
      dbms_sql.define_column(fet_cursor, 1, v_min_ref);
      ignore := dbms_sql.execute(fet_cursor);	  	

      ignore := dbms_sql.fetch_rows(fet_cursor);  -- must/should! return 1 row
      dbms_sql.column_value(fet_cursor, 1, v_min_ref);
      dbms_sql.close_cursor(fet_cursor);
	  
      fet_cursor := dbms_sql.open_cursor;
      dbms_sql.parse(fet_cursor,
                  ' select max(reference_id) '
                ||' from ckaz_actions '
                ||' where action = ''NULLIFY'' '
                   , DBMS_SQL.NATIVE);
      dbms_sql.define_column(fet_cursor, 1, v_max_ref);
      ignore := dbms_sql.execute(fet_cursor);
      ignore := dbms_sql.fetch_rows(fet_cursor);        -- MAX >= MIN
      dbms_sql.column_value(fet_cursor, 1, v_max_ref);
      dbms_sql.close_cursor(fet_cursor);

    End Loop ref_range_lev1; 

    EXCEPTION
      When Others Then
	    rmdbg.trace(utility_name|| 'exec_upd_actions' ||' loc 164');
		
        If dbms_sql.is_open(fet_cursor) Then 
          dbms_sql.close_cursor(fet_cursor);
        End If;
        If dbms_sql.is_open(upd_cursor) Then 
          dbms_sql.close_cursor(upd_cursor);
        End If;
        If dbms_sql.is_open(loop_cursor) Then 
          dbms_sql.close_cursor(upd_cursor);
        End If;

      Raise;
  END exec_upd_actions;

  Procedure exec_purge (ckaz_tbsp varchar2) is
  -- Rows which have lost their context are deleted e.g.
  -- 'ATTRIBUTE' definitions which depend on an 'ENTITY' definition
  -- which has been deleted. The utility uses the 'cascade delete'
  -- procedure in the API, so that other dependent rows are processed
  -- appropriately.
  -- Rows which still have a meaningful context, but contain references
  -- to other rows which no longer exist, are updated and the invalid
  -- reference is set to NULL.
  -- Rows in the CDI_TEXT table (multiline text) which reference rows
  -- in either the SDD_ELEMENTS or SDD_STRUCTURE_ELEMENTS which have
  -- been deleted, are also deleted.

  ins_cursor integer;
  ignore     integer;

  BEGIN   
    rmdbg.trace(utility_name|| 'exec_purge' ||' loc 110');
    
    anal_create_indexes(ckaz_tbsp); -- create indexes on CKAZ_ACTIONS for faster purge
	
	rmdbg.trace(utility_name|| 'exec_purge' ||' loc 111');   

    exec_del_actions;
    -- create a status flag in ACTIONS table to confirm progress
    ins_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(ins_cursor,
                 ' INSERT into ckaz_actions ( '
               ||'   reference_id '
               ||' , action '
               ||' , table_name '
               ||' , base_table_name '
               ||' , column_name '
               ||' , value '
               ||' , elmt_type '
               ||' , refd_elmt_type '
               ||' , check_no '
               ||' , date_created '
               ||' , date_changed '
               ||' , user_created_by '
               ||' , user_changed_by '
               ||' ) '
               ||' values ('
               ||'   null '
               ||' ,''DELETE COMPLETE'' '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' , -2 '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' ) '
                   , DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(ins_cursor);
    dbms_sql.close_cursor(ins_cursor);
    commit;
	
	rmdbg.trace(utility_name|| 'exec_purge' ||' loc 113');

    exec_del_text;
	
	rmdbg.trace(utility_name|| 'exec_purge' ||' loc 114');
	
    exec_upd_actions;
    -- create a status flag in ACTIONS table to confirm progress
    ins_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(ins_cursor,
                 ' INSERT into ckaz_actions ( '
               ||'   reference_id '
               ||' , action '
               ||' , table_name '
               ||' , base_table_name '
               ||' , column_name '
               ||' , value '
               ||' , elmt_type '
               ||' , refd_elmt_type '
               ||' , check_no '
               ||' , date_created '
               ||' , date_changed '
               ||' , user_created_by '
               ||' , user_changed_by '
               ||' ) '
               ||' values ('
               ||'   null '
               ||' ,''PURGE COMPLETE'' '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' , -3 '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' , null '
               ||' ) '
                   , DBMS_SQL.NATIVE);
    ignore := dbms_sql.execute(ins_cursor);
    dbms_sql.close_cursor(ins_cursor);
    commit;
	
	rmdbg.trace(utility_name|| 'exec_purge' ||' loc 115');

    EXCEPTION
      When Others Then
	    rmdbg.trace(utility_name|| 'exec_purge' ||' loc 116');
      Raise;
  END exec_purge;

  Procedure anal_references (ckaz_tbsp varchar2, indexes_reqd boolean) Is

  BEGIN
   rmdbg.trace(utility_name|| 'anal_references' ||' loc 30');
	 
   anal_create_ckaz_tables(ckaz_tbsp);
   If indexes_reqd and (brepowner = TRUE) Then
     anal_create_tmp_indexes(ckaz_tbsp);
   End If;
   anal_log_obs_rows;
   anal_cons_ref_checks;
   anal_exec_checks ('REFERENTIAL');
   commit;

   anal_drop_tmp_indexes;

  END anal_references;
  
  Procedure upgrd_purge (ckaz_tbsp varchar2) Is
  BEGIN   
     rmdbg.trace(utility_name|| 'upgrd_purge' || ' is started.');
	
     utl_check_db_objects; -- checks that required triggers/packages are available
  
     -- always create temporary indexes (on el_type_of and str_type_of)
     -- when upgrading i.e 2nd parameter TRUE
     anal_references(ckaz_tbsp, TRUE);
  
     -- delete unknown types identified in the pre-analysis of references
     utl_del_unknown_types;
  
     -- cleanup the repository by deleting dangling references
     -- ...creates indexes on ckaz_actions if>1000 rows or >20000 elements
     exec_purge(ckaz_tbsp);
     
     -- normal succesful completion can be verified by checking for 
     -- CKAZ_ACTIONS.ACTION = 'PURGE COMPLETE'
     -- and also from CK_INSTALLED_OBJECTS via the following update:
     update ck_installed_objects
     set ci_created = 'Y'
     where ci_stage = 'CKPURGE';
     commit;
	 
     rmdbg.trace(utility_name||proc||' loc 170:Finish');
     rmdbg.flush_trace;
  
     EXCEPTION
        When Others Then	
		rmdbg.trace(utility_name||proc || ' Finish');
		rmdbg.flush_trace;
        Raise;
  
  END upgrd_purge;

BEGIN	 	
 	 select usr_oracle_username
	 into repos_owner
	 from   sdw_users
	 where  usr_sdd_owner = 'Y';
	 
	 if (repos_owner = user) then
	   brepowner := TRUE;
	 end if;	
	 	
	 upgrd_purge(ckaz_tbsp);
	     
     EXCEPTION
        When Others Then
      	Raise;
END;
/


