rem NAME : ckimp70.sql
rem
rem   Version 4.0.12
rem
rem   This release of the Import utility supports:
rem     o Import of 'R'esolved and 'C'ompiled Workareas
rem       with three additional modes
rem       1) 'D'iverge  :- Imports with new IRID, IVID values
rem       2) 'M'erge    :- Imports with original IRID, new IVID values.
rem                        Existing objects are automatically checked-in to preseve
rem                        history.
rem       3) 'R'eplicate:- Imports with original IRID and IVID values
rem                        (currently will fail if version conflicts occur)
rem                        This mode is not appropriate for importing objects into
rem                        the source Repository or if the object already exists
rem                        at the target.
rem
rem  Importing by 'stripe' is not currently supported, therefore the driving cursor
rem  for loading objects obj_to_import is restricted to a single row fetch (rownum=1)
rem
rem
rem  Change History
rem    05-Apr-04 : Bug 3544821: ensure access right checks added by bug 3069020 only done when container already exits.
rem    24-Sep-03 : Bug 3069020: add access rights checking for workareas and conainers
rem    12-Aug-03 : Bug 3082111: add a new column to xt_sdd_tab and upgrade the instance data
rem		   This code runs only if the 4th digit in the source repository version is < 94
rem    12-Jun-03 : Bug 3001015; added procedure XT_UPGRADE which is called just before
rem                procedure ckimp70; moved function table_exits and procedures
rem                execute_sql out of procedure ckimp70 so that the new procedure
rem                XT_UPGRADE can also use them
rem    14-Apr-03 : Bug 1332218 Catered for null TYPE_ID when checking whether row
rem                already exists in SDD_GLOBAL_NAMES
rem    10-Apr-02 : Bug 2300092 Move delete from rm_text_lines to insert stack 
rem    03-Oct-01 : Bug 1987869 Remap branch_id to main in configuration mode
rem    26-Sep-01 : Bug 1866595 Use sdd_object_versions since it is versioned resolved
rem		   Force a full workarea compilation in 'R'eplica and 'M'erge Modes
rem    17-May-00 : Bug 1297884 Invalid column name (904) loading Dependencies (U mode)
rem    16-May-00 : Bug  970590 Modified process so tha Workarea always refreshed after
rem                 data and access rights loaded. Eliminates any side effects such as
rem                 multiple versions of same object in the Workarea.
rem    09-May-00 : Bug 1286740 unique constraint on Root 'Folders' not enforced when
rem                importing an 'Unresolved' Workarea
rem    05-May-00 : Bug 1288219 objects missing after importing an 'unresolved' Workarea
rem              : Bug 1266769 dependency data not imported correctly (fkeys not mapped)
rem    03-May-00 : bug 1275707 handle multiple secondary type entries for different PAC objects
rem    19-Apr-00 : Bugs 1206434,1206445 : Reset Owner of Folder to Importing User
rem                                     : Error if no Folder Members
rem    06-Apr-00 : Bug 1247895 Only check for FOLDER name conflict within the context workarea
rem              : Bug 1259898 SDD_GLOBAL_NAMES.TYPE_SHORT_NAME replaced with TYPE_ID
rem    31-Mar-00 : Bug 1161988 Don't do 'External Refs' check if not required
rem    30-Mar-00 : Bug 918182 SYSTEM FOLDER Language members exluded via static Object Id's
rem                from CDI_LANGUAGE_DEFINITIONS. New languages, of SYSTEM FOLDER will be imported
rem    24-Mar-00 : Bug 1236222 Don't create indexes if they already exist
rem    09-Feb-00 : Bug 1169926 support WA to WA import for non versioned repository
rem    08-Feb-00 : Fix bug  1170269 version checking issue
rem    21-Jan-00 : Fix bugs 1161618 and 1159990 access rights problems
rem    14-Dec-99 : Fix bug 1106558 (also needs the fix to jrchkref.jps)
rem    11-Nov-99 : Added support to delete version of an object from entries in source Wastebasket
rem    13-Oct-99 : Changes for 4.0.12 schema system tables
rem    11-Oct-99 : Added call to check_refs to identify and remove dangling references
rem    06-Oct-99 : Added support for frequent commits (short transactions)
rem    22-Sep-99 : Fix bugs 1000633 and 998944
rem    16-Sep-99 : Fix bug 994911 Ora-1704 during conflict resolution on config names
rem    13-Sep-99 : Fix bug 988246 Generic columns (ELEMENT_REF, CIELEMENT_REF) not mapped
rem    09-Sep-99 : Fix bug 973641 Import of existing objects acts Weird ('M'erge)
rem    06-Sep-99 : Fix bug 985751 Exclude row in XT_SDD_OBJECT_VERSIONS for PRIVATE config
rem    01-Sep-99 : Fix bug 972478   Access rights not re-enabled after import
rem    25-Jun-99 : Fixed bugs 917697, 917683, 917160
rem    21-Jun-99 : Fixed bug 913497 Checkin ex SYSTEM configurations when in 'U' mode 
rem                (03-May-00: This was later backed out due to changes in internal schema tables)
rem    17-Jun-99 : Fixed bug 911939
rem    16-Jun-99 : Fixed bug 911327 allow renaming of root folders in Import.
rem    16-Jun-99 : Fixed bug 910905
rem    15-Jun-99 : Fixed bugs 909081(Text), 908161 (MUNs)
rem     2-Jun-99 : Handle GUID mappings for SYSTEM FOLDER and contents.
rem     1-Jun-99 : Create root and tip version association nodes.
rem    27-May-99 : Create a common procedure for inserting rows for 'R' and 'U' WA modes.
rem  
DECLARE

-- Exceptions trapped in RON 

missing_reg_schema        EXCEPTION;
mismatched_repositories   EXCEPTION;
validation_failed         EXCEPTION;
parameters_invalid        EXCEPTION;
no_version_access_rights  EXCEPTION;
context_workarea_expected EXCEPTION;
duplicate_config          EXCEPTION;
root_folder_conflict      EXCEPTION;
configuration_conflict    EXCEPTION;
non_mergeable             EXCEPTION;

-- Error conditions placed on to the PL/SQL error stack 

NON_MERGEABLE_ERR    constant number     := 23001;
cda_error            constant varchar2(3):= 'CDA'; 
drop_tab_fail        constant number     := 6072;
no_apps_exist        constant number     := 6073;
inv_trans_ref        constant number     := 6074;
get_repos_vn_fail    constant number     := 6075;
chk_repos_vn_fail    constant number     := 6076;
missing_schema_error constant number     := 6077;

no_access_rights     constant number     := 2606;

unhdl_orcl_err    constant number := 2105;

-- Nested table to hold dynamic SQL statements prior to execution
type sql_table is table of varchar2(300);
type large_sql_table is table of varchar2(4000);
type irid_table is table of Number;
type ivid_table is table of Number;

-- Bug 1082783 (front end / backend conflict on input parameter name.
--             Backend changed to agree with front end
-- RBS size can be kept small if transaction sizes are kept small.
-- If Import fails then partial imports may occur.
short_trxns Boolean := FALSE;
-- Dangling references will be processed if this is set TRUE.
-- Mandatory dangling refs are fixed by cascade delet,
-- optional dangling refs by update setting to null
fix_dnglrefs Boolean := FALSE;

-- new or empty target Workarea simplifies import process
empty_target_wa boolean := FALSE; 
                                 -- set true if new WA created or existing WA is empty
new_workarea_irid number;

-- store the value for name of Repository owner used in queries against online dict
repos_owner Varchar2(30) := jr_sys_privs.get_repos_owner;


-- moved here from PROCEDURE ckimp70 so that PROCEDURE XT_UPGRADE can also use it
FUNCTION table_exists (Tname IN Varchar2) RETURN Boolean IS
  CURSOR tab_exists  IS
   SELECT 'Y'
   FROM  user_tables
   WHERE table_name = Tname
   ;
  exst Varchar2(1) := 'N';

BEGIN
  OPEN tab_exists;
  FETCH tab_exists INTO exst;
  IF tab_exists%found THEN
    CLOSE tab_exists;
    RETURN TRUE;
  ELSE 
    CLOSE tab_exists;
    RETURN FALSE;
  END IF;
END table_exists;

-- moved here from PROCEDURE ckimp70 so that PROCEDURE XT_UPGRADE can also use it
Procedure execute_sql(sql_statement Varchar2) IS
  proc           varchar2(100) := 'execute_sql ';

  csr Number;
  exe Number;
BEGIN

  rmdbg.trace(sql_statement);	  

  csr := dbms_sql.open_cursor;
  dbms_sql.parse(csr, sql_statement, DBMS_SQL.NATIVE);
  exe := dbms_sql.execute(csr);
  rmdbg.trace('Rows processed = '||exe);	  
  dbms_sql.close_cursor(csr);

END execute_sql;

-- moved here from PROCEDURE ckimp70 so that PROCEDURE XT_UPGRADE can also use it
Procedure execute_sql(sql_statement dbms_sql.Varchar2s, array_size Number) IS
  proc           varchar2(100) := 'execute_sql ';

  csr Number;
  exe Number;
BEGIN

  FOR i IN 1..array_size LOOP
    rmdbg.trace(sql_statement(i));
  END LOOP;

  csr := dbms_sql.open_cursor;
  dbms_sql.parse(csr, sql_statement, 1, array_size, true, DBMS_SQL.NATIVE);
  exe := dbms_sql.execute(csr);
  rmdbg.trace('Rows processed = '||exe);	  
  dbms_sql.close_cursor(csr);

END execute_sql;

PROCEDURE ckimp70( tablespace_name    Varchar2
                 , prollback_segment  Varchar2 Default NULL
                 , guid_mode          Varchar2 Default 'D'
                 , short_transactions Boolean  Default FALSE 
                 , fix_dangling_refs  Boolean  Default FALSE ) IS
 -- guid_mode set to 'R'eplica  will allow the IMPORT utility
 -- to use the IRID, IVID values currently set in the exported data i.e.
 -- the objects are imported as Replicas of the source.
 -- guid_mode 'D'iverge will allow the Import utility to reassign IRID and IVID
 -- i.e. creating objects with no obvious link to their source (useful for
 -- creating multiple independent copies)
 -- guid_mode 'M'erge will allow Import to reassign just the IVID values i.e. the
 -- objects keep their orignal identity and can be grafted/merged onto an existing
 -- object family line.

-- Select objects to import via the export table list 
CURSOR obj_to_import IS
  SELECT 
         eo.object_irid          irid
  ,      eo.object_ivid          ivid
  ,      eo.object_type          type
  ,      eo.default_import_name 
  ,      eo.wa_mode
  ,      eo.fd_mode
  ,      eo.root_flag
  ,      eo.mkmem_fldr_irid
  ,      eo.extract_reg_metad
  FROM   xtsys_export_objects  eo
  WHERE  nvl(eo.fd_mode, 'X') != 'S'
  AND    rownum = 1       
           -- restrict to loading in one hit untill striping implemented
  ;


utility_name   varchar2(27)  := 'Repository 6.5 Import:';
proc           varchar2(100) := 'Import ';

-- Flags to indicate if base table details have been stored 
wa_base        boolean := FALSE;
cf_base        boolean := FALSE;
fd_base        boolean := FALSE;

obj_handle     number;
mcsr           number;
mtmp           VARCHAR2(2000);
ret            number;

dummy          varchar2(100);

-- moved FUNCTION table_exists (Tname IN Varchar2) RETURN Boolean
-- out of PROCEDURE ckimp70 so that PROCEDURE XT_UPGRADE can also use it

FUNCTION index_exists (Iname IN Varchar2) RETURN Boolean IS
  CURSOR ind_exists  IS
   SELECT 'Y'
   FROM  user_indexes
   WHERE index_name = Iname
   ;
  exst Varchar2(1) := 'N';

BEGIN
  OPEN ind_exists;
  FETCH ind_exists INTO exst;
  IF ind_exists%found THEN
    CLOSE ind_exists;
    RETURN TRUE;
  ELSE 
    CLOSE ind_exists;
    RETURN FALSE;
  END IF;
END index_exists;

FUNCTION non_mergeable_objects RETURN Boolean IS
  -- This function checks that there are no objects in
  -- the Reposiory which conflict with objects in the import
  -- data but are not accessible to the context Workarea.
  -- In 'R'eplicate and 'M'erge modes such objects cannot
  -- be updated or versioned.
  proc           varchar2(100) := 'non_mergeable_objects ';
  csr Number;

  -- Bug 1716824, workaround for RDBMS Bug 1612147
  -- Replace explicit cursor with dynamic cursor
  
  -- Bug 1866595 user sdd_object_versions(version resolved) instead of i$sdd_object_versions 
BEGIN
  
  csr := dbms_sql.open_cursor;

--Check if the object is in the repository
  dbms_sql.parse(csr, ' SELECT ''Y'' FROM i$sdd_object_versions o '
                 ||   ',    xtsys_import_irid_mapping   m '
                 ||   'WHERE o.irid = m.old_irid AND m.conflict = ''Y'' '
                 ||   'AND m.exclude  IS NULL ' 
                 ||   'AND ROWNUM = 1 '
		      , DBMS_SQL.NATIVE );

  IF dbms_sql.execute_and_fetch(csr, false) > 0 THEN 
        --Now see if the object version is visible in the target
        --workarea. If not, raise an error.
     dbms_sql.parse(csr, ' SELECT ''Y'' FROM sdd_object_versions       o '
                 ||   ',    xtsys_import_irid_mapping   m '
                 ||   'WHERE o.irid = m.old_irid AND m.conflict = ''Y'' '
                 ||   'AND m.exclude  IS NULL AND exists ( '
                 ||   'SELECT null FROM i$sdd_wa_context w '
                 ||   'WHERE w.workarea_irid = jr_context.workarea '
                 ||   'AND o.ivid = w.object_ivid ) '
                 ||   'AND ROWNUM = 1 '
	      , DBMS_SQL.NATIVE );

	  IF dbms_sql.execute_and_fetch(csr, false) > 0 THEN 
		  rmdbg.trace(utility_name||proc || 'loc 86b : Objects can be merged ');  
		  dbms_sql.close_cursor(csr);
	     	  RETURN FALSE;
	  ELSE
		  rmdbg.trace(utility_name||proc || 'loc 86c : Objects cannot be merged ');  
		  dbms_sql.close_cursor(csr);
		  RETURN TRUE;
	  END IF;
  ELSE 
     rmdbg.trace(utility_name||proc || 'loc 86d : Objects can be merged ');  
     dbms_sql.close_cursor(csr);
     RETURN FALSE;
  END IF;

  EXCEPTION
    WHEN OTHERS THEN
      rmdbg.trace(utility_name||proc || 'loc 86d');
      rmmes.post('ORA', SQLCODE);
      -- close cursor after post otherwise sqlcode lost
      dbms_sql.close_cursor(csr);
      RAISE;
END non_mergeable_objects;

-- moved Procedure execute_sql(sql_statement Varchar2)
-- out of PROCEDURE ckimp70 so that PROCEDURE XT_UPGRADE can also use it

-- moved Procedure execute_sql(sql_statement dbms_sql.Varchar2s, array_size Number)
-- out of PROCEDURE ckimp70 so that PROCEDURE XT_UPGRADE can also use it

Procedure create_support_functions IS
  proc varchar2(100) := 'create_support_functions ';
BEGIN
  execute_sql (
      'CREATE OR REPLACE '
    ||'FUNCTION  xtimp_fn_get_mapirid(irid_col Number) '
    ||' RETURN Number IS '
    ||' irid Number;   '
    ||'BEGIN '
    ||'   SELECT  DISTINCT NEW_IRID  '
    ||'    INTO irid '
    ||'   FROM XTSYS_IMPORT_IRID_MAPPING  '
    ||'   WHERE OLD_IRID = irid_col; '
    ||' Return irid; '
    ||' EXCEPTION '
    ||' WHEN NO_DATA_FOUND THEN '
    ||'   Return irid_col; '
    ||' WHEN OTHERS THEN RAISE; '
    ||' END; ' 
    );

  execute_sql (
      'CREATE OR REPLACE '
    ||'FUNCTION  xtimp_fn_get_mapivid(ivid_col Number) '
    ||' RETURN Number IS '
    ||' ivid Number;   '
    ||'BEGIN '
    ||'   SELECT  DISTINCT NEW_IVID  '
    ||'    INTO ivid '
    ||'   FROM XTSYS_IMPORT_IVID_MAPPING  '
    ||'   WHERE OLD_IVID = ivid_col; '
    ||' Return ivid; '
    ||' EXCEPTION '
    ||' WHEN NO_DATA_FOUND THEN '
    ||'   Return ivid_col; '
    ||' WHEN OTHERS THEN RAISE; '
    ||' END; ' 
    );

  execute_sql (
      'CREATE OR REPLACE '
    ||'FUNCTION  xtimp_fn_get_map_name(ivid_col Number, old_name Varchar2) '
    ||' RETURN Varchar2 IS '
    ||' obj_name Varchar2(256);   '
    ||'BEGIN '
    ||'   SELECT  DISTINCT new_name '
    ||'    INTO obj_name '
    ||'   FROM XTSYS_IMPORT_IVID_MAPPING  '
    ||'   WHERE OLD_IVID = ivid_col '
    ||'     AND new_name IS NOT NULL; '
    ||' Return obj_name; '
    ||' EXCEPTION '
    ||' WHEN NO_DATA_FOUND THEN '
    ||'   Return old_name; '
    ||' WHEN OTHERS THEN RAISE; '
    ||' END; ' 
    );

END create_support_functions;

Procedure create_support_indexes (tbsp_clause Varchar2) IS
  proc varchar2(100) := 'create_support_indexes ';
BEGIN
  IF NOT index_exists('XTSYS_IMP_IRID_1') THEN
    execute_sql (
      'CREATE INDEX XTSYS_IMP_IRID_1 '
    ||' ON xtsys_import_irid_mapping (exclude) '
    ||tbsp_clause
    );
  END IF;
  -- Bug 2620284 try and improve performance
  IF NOT index_exists('XTSYS_IMP_IRID_2') THEN
    execute_sql (
      'CREATE UNIQUE INDEX XTSYS_IMP_IRID_2 '
    ||' ON xtsys_import_irid_mapping (old_irid,new_irid) '
    ||tbsp_clause
    );
  END IF;
  IF NOT index_exists('XTSYS_IMP_IVID_1') THEN
    execute_sql (
      'CREATE UNIQUE INDEX XTSYS_IMP_IVID_1 '
    ||' ON xtsys_import_ivid_mapping (old_ivid,new_ivid) '
    ||tbsp_clause
    );
  END IF;
  IF NOT index_exists('XT_SDDOBJ_1') THEN
    execute_sql (
      'CREATE INDEX XT_SDDOBJ_1 '
    ||' ON xt_sdd_object_versions(irid, name) '
    ||tbsp_clause
    );
  END IF;
  IF NOT index_exists('XT_SDDOBJ_2') THEN
    execute_sql (
      'CREATE INDEX XT_SDDOBJ_2 '
    ||' ON xt_sdd_object_versions(ivid) '
    ||tbsp_clause
    );
  END IF;
  IF table_exists('XT_SDD_FOLDER_MEMBERS') THEN
    IF NOT index_exists('XT_SDDFOLMEM_1') THEN
      execute_sql (
        'CREATE INDEX XT_SDDFOLMEM_1 '
      ||' ON xt_sdd_folder_members(folder_reference) '
      ||tbsp_clause
      );
    END IF;
    IF NOT index_exists('XT_SDDFOLMEM_2') THEN
      execute_sql (
        'CREATE INDEX XT_SDDFOLMEM_2 '
      ||' ON xt_sdd_folder_members(member_object) '
      ||tbsp_clause
      );
    END IF;
  END IF;
  IF table_exists('XT_SDD_FOLDERS') THEN
    IF NOT index_exists('XT_SDDFOL_1') THEN
      execute_sql (
        'CREATE INDEX XT_SDDFOL_1 '
      ||' ON xt_sdd_folders(irid, name) '
      ||tbsp_clause
      );
    END IF;
    IF NOT index_exists('XT_SDDFOL_2') THEN
      execute_sql (
        'CREATE INDEX XT_SDDFOL_2 '
      ||' ON xt_sdd_folders(ivid, name) '
      ||tbsp_clause
      );
    END IF;
  END IF;
  IF table_exists('XT_SDD_GLOBAL_NAMES') THEN
      IF NOT index_exists('XT_SDDGLOBNAME_1') THEN
        execute_sql (
          'CREATE INDEX XT_SDDGLOBNAME_1 '
        ||' ON xt_sdd_global_names(irid, name, type_id) '
        ||tbsp_clause
        );
      END IF;
      IF NOT index_exists('XT_SDDGLOBNAME_2') THEN
        execute_sql (
          'CREATE INDEX XT_SDDGLOBNAME_2 '
        ||' ON xt_sdd_global_names(name) '
        ||tbsp_clause
        );
      END IF;
  END IF;

END create_support_indexes;

PROCEDURE check_repos_version IS
  proc   varchar2(100) := 'check_repos_version ';
  -- Check the details of the Repository instance.
  -- The sourc and target must be compatible versions.

  csr NUMBER;
  exe number;

BEGIN
  csr := DBMS_SQL.OPEN_CURSOR;
  rmdbg.trace(utility_name||proc || 'loc 3.1');
  DBMS_SQL.PARSE(csr,' SELECT null FROM rm$repositories t, xtsys_rm$repositories s '||
                     ' WHERE substr(s.repository_version,1,instr(s.repository_version,''.'',1, 3)) '||
                     '     = substr(t.repository_version,1,instr(t.repository_version,''.'',1, 3)) '||
                     '   AND s.code_version       = t.code_version ' ||
                     '   AND s.schema_version     = t.schema_version', DBMS_SQL.NATIVE);
  
  IF DBMS_SQL.EXECUTE_AND_FETCH(csr) = 1 THEN 
                                             -- one and only one row should be returned
    DBMS_SQL.CLOSE_CURSOR(csr);
  ELSE 
    RAISE mismatched_repositories;
  END IF;
  rmdbg.trace(utility_name||proc || 'loc 3.2');

  EXCEPTION
    WHEN mismatched_repositories THEN
      DBMS_SQL.CLOSE_CURSOR(csr);
      rmdbg.trace(utility_name||proc || 'loc 3.3');
      rmmes.post(cda_error, chk_repos_vn_fail, 'RM$REPOSITORIES');
      rmmes.post('ORA', SQLCODE);
      RAISE ;

    WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(csr);
      rmdbg.trace(utility_name||proc || 'loc 3.4');
      rmmes.post('ORA', SQLCODE);
      RAISE;

END check_repos_version ;

PROCEDURE compute_stats IS
  proc   varchar2(100) := 'compute_stats ';
  -- Computes statistics on XTSYS tables which may be used by the optimizer

  csr NUMBER;
  exe number;

BEGIN
  rmdbg.trace(utility_name||proc || 'loc 3000.1');
  execute_sql(' ANALYZE TABLE xtsys_tabs_exported COMPUTE STATISTICS');  
  rmdbg.trace(utility_name||proc || 'loc 3000.2');

  execute_sql(' ANALYZE TABLE xtsys_import_irid_mapping COMPUTE STATISTICS');

  execute_sql(' ANALYZE TABLE xtsys_import_ivid_mapping COMPUTE STATISTICS');

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(csr);
      rmdbg.trace(utility_name||proc || 'loc 3000.4');
      rmmes.post('ORA', SQLCODE);
      RAISE;

END compute_stats ;

PROCEDURE enable_all IS
  proc   varchar2(100) := 'enable_all ';
  -- re-enable all constraints, access rights checks etc which
  -- are diabled during execution of Import
  -- Procedure called at end of proecessing or on Exception.

BEGIN
  rmdbg.trace(utility_name||proc || 'loc 6050');

  jr_context.enable(jr_context.ACCESS_RIGHTS);
  jr_context.enable(jr_context.OBJECT_VERSIONS);
  jr_context.enable(jr_context.FOLDER_MEMBERS);
  jr_context.enable(jr_context.POPULATE_PARENT_INFO);
  jr_context.enable(jr_context.REPOS_CONSTRAINTS);
  jr_context.enable(jr_context.NO_CHECKIN_UPDATE);

END enable_all ;

FUNCTION wa_set_and_check(wa_name Varchar2) RETURN Boolean IS
  proc           varchar2(100) := 'wa_set_and_check ';
  -- Set the context Workarea or
  -- create a new Workarea as requested

dummy i$sdd_workareas.name%Type;
CURSOR check_wa Is
 SELECT name
 FROM   i$sdd_workareas
 WHERE  name = wa_name;
 
scratch_irid number;
CURSOR is_wa_empty IS
 SELECT irid
 FROM   sdd_object_versions
 WHERE  rownum = 1;

is_empty Boolean := TRUE;

BEGIN
    rmdbg.trace(utility_name||proc || 'loc 81b : Checking WA: ' || wa_name);  
    OPEN check_wa;
    FETCH check_wa INTO dummy;
    IF check_wa%notfound THEN
      rmdbg.trace(utility_name||proc || 'loc 82 : creating WA: ' || wa_name);  
      new_workarea_irid := jr_workarea.create_workarea(USER, wa_name, 'Created by IMPORT utility'); 
      is_empty  := TRUE;
    ELSE
      OPEN is_wa_empty;
      FETCH is_wa_empty INTO scratch_irid;
      IF is_wa_empty%found THEN
        rmdbg.trace(utility_name||proc || 'loc 82d : Existing WA is not empty ' || wa_name);  
        is_empty  := FALSE;
      END IF;
      CLOSE is_wa_empty;
    END IF;
    CLOSE check_wa;

  rmdbg.trace(utility_name||proc || 'loc 82b : Setting WA: ' || wa_name);  
  jr_context.set_workarea(wa_name);
  
  RETURN is_empty;
END wa_set_and_check;

PROCEDURE process_meta_data  IS
  proc   varchar2(100) := 'process_meta_data ';

  xt_schemas jr_rm_xt.jr_schema_list;
  rm_schemas jr_rm_xt.jr_schema_list;
  voidb Boolean;

  j Number;



BEGIN
  rmdbg.trace(utility_name||proc || 'loc 10 ');	
  IF User = repos_owner THEN
    xt_schemas := jr_rm_xt.get_schemas('XT');
    -- The user is the Repository owner so load any schema meta data not already present
    voidb := jr_rm_xt.load_xt_to_rm (xt_schemas, TRUE);  
    FOR i in xt_schemas.first .. xt_schemas.last LOOP
      -- generate/create the actual database objects in the Repository owner
      -- notes: 1) we could move this step into the load procedure
      --        2) this step will automatically handle/skip any objects which already
      --           exist in the database
      jr_registration.generate(xt_schemas(i).name);
    END LOOP;    
  ELSE
    -- For subordinate users if the schema meta data is not loaded raise an error.
    -- Import cannot proceed without the prerequisite metadata or objects but these
    -- can't be loaded/created by a subordinate.
    FOR i IN (SELECT table_name FROM user_tables WHERE table_name LIKE 'XT_RM%') LOOP
      -- grant read access on the XT_RM... tables to enable the JR_XT package to see them
      execute_sql('GRANT SELECT ON ' || i.table_name || ' TO ' || repos_owner);
    END LOOP;
    xt_schemas := jr_rm_xt.get_schemas('XT');
    rm_schemas := jr_rm_xt.get_schemas('RM');
    FOR i in xt_schemas.first .. xt_schemas.last LOOP
      j := rm_schemas.first;
      WHILE j is NOT NULL AND xt_schemas(i).irid != rm_schemas(j).irid LOOP
         j := rm_schemas.next(j);
      END LOOP;

      IF xt_schemas(i).irid != rm_schemas(j).irid  THEN 
        -- the schema in the XT set is not found in RM 
        RAISE missing_reg_schema;
      END IF;
     
    END LOOP;
  END IF;

  EXCEPTION
    WHEN missing_reg_schema THEN
      rmmes.post(cda_error, missing_schema_error, 'RM$REPOSITORIES');
      rmmes.post('ORA', SQLCODE);
      raise;
    WHEN OTHERS THEN
      rmmes.post('ORA', SQLCODE);
      raise;

END process_meta_data;

--  check all objects and folders in exclude rules exist in the target repository
PROCEDURE validate_rules IS
  proc   varchar2(100) := 'validate_rules ';
 
  object_list       jr_name.objver_list;
  new_entry_seq     number; 
  n_ref_successor   number; 
  n_ref_predecessor number; 
  csr               integer;
  exe               integer;
  value             varchar2(300);
  entry_seq         number;

BEGIN
  
  rmdbg.trace(utility_name||proc || 'loc  5000');	  


  -- Bug 1727083 check all objects/folders in imported exclude rules exist
  csr := dbms_sql.open_cursor;
  DBMS_SQL.PARSE(csr,
        'SELECT xrp.value, xrp.entry_seq, xm.new_irid '
      ||'FROM   xt_i$sdd_rule_param_values   xrp '
      ||',      xt_sdd_rules                 xr '
      ||',      xtsys_import_irid_mapping    xm '
      ||'WHERE  xm.old_irid   = xrp.entry_seq '
      ||'AND    xrp.rule_irid = xr.irid '
      ||'AND    xr.name like ''EXCLUDE%'' '
         , DBMS_SQL.NATIVE);

  DBMS_SQL.DEFINE_COLUMN(csr, 1, value, 300);
  DBMS_SQL.DEFINE_COLUMN(csr, 2, entry_seq);
  DBMS_SQL.DEFINE_COLUMN(csr, 3, new_entry_seq);
  rmdbg.trace(utility_name||proc || 'loc 5001  : Parse csr');  
  exe := DBMS_SQL.EXECUTE(csr);

  LOOP
    if DBMS_SQL.FETCH_ROWS(csr)>0 then
      DBMS_SQL.COLUMN_VALUE(csr, 1, value);
      DBMS_SQL.COLUMN_VALUE(csr, 2, entry_seq);
      DBMS_SQL.COLUMN_VALUE(csr, 3, new_entry_seq);
      
      object_list := jr_name.get_objects(value);
      if object_list.last is NULL then
         -- object referenced in rule does not exist in the target repository

         rmdbg.trace(utility_name||proc || 'loc 5002: '
         || 'Object referenced in exclude rule does not exist - '|| value);

         -- 
         -- Update the successor (if any) to point to the predecessor 
         -- of the current row. 
         -- 
         n_ref_successor := jr_workarea.find_ref_successor(jr_context.workarea, new_entry_seq); 
 
         rmdbg.trace(utility_name||proc || 'loc 5003: '
                     || 'Successor is :'|| to_char(n_ref_successor));

         if n_ref_successor IS NOT NULL then
            SELECT wse.predecessor 
            INTO   n_ref_predecessor 
            FROM   i$sdd_workarea_spec_entries wse 
            WHERE  wse.seq = new_entry_seq; 
   
            UPDATE i$sdd_workarea_spec_entries wse 
            SET    wse.predecessor = n_ref_predecessor 
            WHERE  wse.seq = n_ref_successor; 
         end if; 
 
     
         -- now only need to remove workarea spec entry as this will cascade delete 
         -- from i$sdd_rule_param_values
         
         rmdbg.trace(utility_name||proc || 'loc 5004: '
                     || 'Deleting workarea spec entry :'|| to_char(new_entry_seq));
         DELETE from i$sdd_workarea_spec_entries  wse
         WHERE  wse.seq = new_entry_seq; 
      end if;
      
    else
      EXIT;
    end if;
    
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(csr);
      
  EXCEPTION  
    WHEN OTHERS THEN
      rmdbg.trace(utility_name||proc || 'loc 5010: '
                  || 'In When Others :'|| sqlerrm);
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END validate_rules;

PROCEDURE cmp_unr_wa IS
  proc   varchar2(100) := 'cmp_unr_wa ';
  --  recompile after loading workarea

  CURSOR is_versioning_allowed IS
   SELECT supports_version
   FROM   rm$repositories
   ;

BEGIN
  
  FOR i IN is_versioning_allowed LOOP
    rmdbg.trace(proc || ' Loc 0:If versioning repository then allow Workarea compilation ');
    IF i.supports_version = 'Y' THEN
      IF guid_mode in ('R', 'M') THEN
	 -- Bug No 1866595 : Need to reset the compile flag on Workarea so that a	
         -- full recompilation occurs. Only required in 'R'eplica or 'M' Merge mode
         jr_workarea.set_compile_flag(jr_context.workarea, 'N');
      END IF;

      -- Bug 1727083 check all objects/folders in exclude rules exist
      -- delete the rules if the object does not exist because otherwise
      -- recompile of the workarea will fail
      IF (table_exists('XT_I$SDD_RULE_PARAM_VALUES')  AND
          table_exists('XT_SDD_RULES') )THEN
        validate_rules;      
      END IF;
      -- now recompile workarea
      jr_workarea.compile(jr_context.workarea);
    END IF;
  END LOOP;

  EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END cmp_unr_wa;

PROCEDURE conflict_resolution (object_type Varchar2) IS
  proc   varchar2(100) := 'conflict_resolution ';

  -- This appends a suffix (date timestamp) onto names to avoid
  -- UID conflicts.
  -- Currently handles Root Folders and Configurations.

  wa_name Varchar2(100);

  csr Number;
  exe Number;
BEGIN
  rmdbg.trace(utility_name||proc || 'loc  3000');	  


  IF object_type = 'FOLDER' AND table_exists ('XT_SDD_FOLDERS') THEN
    -- This may also update rows in the XT tables which have been flagged for exclusion
    -- e.g. SYSTEM FOLDER
    -- but it isn't worth making this update statement more complex and slower to avoid this 
    -- because these rows won't get loaded anyway.
    -- However don't update rows which are conflicting as this resolves with a new version
    -- Note check for name conflicts only in the current workarea context.
    -- If allowed the user can change the name to something else after restore.
    execute_sql( ' UPDATE xt_sdd_object_versions x '
        ||' SET x.name = substr(x.name ||''_''|| to_char(sysdate, ''DDMONYY''),1,256) '
        ||' WHERE exists ( '
        ||'        select null from sdd_folders lf, xt_sdd_folders xf, xtsys_import_irid_mapping xm '
        ||'        where lf.name = xf.name and xm.old_irid = xf.irid and xm.conflict is NULL '
        ||'        and    x.ivid = xf.ivid and lf.root_flag = ''Y'' and xf.root_flag = ''Y'' ) ' );

    execute_sql(' UPDATE xt_sdd_folders xf '
        ||' SET xf.name=(select xo.name from xt_sdd_object_versions xo where xo.ivid = xf.ivid ) '
        ||' WHERE exists ( '
        ||'        select null from sdd_folders lf , xtsys_import_irid_mapping xm '
        ||'        where xm.old_irid = xf.irid and xm.conflict is NULL '
        ||'        and lf.name = xf.name '
        ||'        and lf.root_flag = ''Y'' and xf.root_flag = ''Y'' ) '  );

    -- If thats not unique try including the time
    csr := dbms_sql.open_cursor;
    dbms_sql.parse(csr, ' select null from sdd_folders lf, xt_sdd_folders xf, xtsys_import_irid_mapping xm '
        ||'        where xm.old_irid = xf.irid and xm.conflict is NULL '
        ||'        and lf.name = xf.name '
        ||'        and lf.root_flag = ''Y'' and xf.root_flag = ''Y'' ' 
        , DBMS_SQL.NATIVE );
    IF dbms_sql.execute_and_fetch(csr, false) > 0 THEN 
      execute_sql( ' UPDATE xt_sdd_object_versions x '
        ||' SET x.name = substr(x.name || to_char(sysdate, ''DDMMYYYYHH24MISS''),1,256) '
        ||' WHERE exists ( '
        ||'        select null from sdd_folders lf, xt_sdd_folders xf, xtsys_import_irid_mapping xm '
        ||'        where lf.name = xf.name and xm.old_irid = xf.irid and xm.conflict is NULL '
        ||'        and    x.ivid = xf.ivid and lf.root_flag = ''Y'' and xf.root_flag = ''Y'' ) ' );

      execute_sql(' UPDATE xt_sdd_folders xf '
        ||' SET xf.name=(select xo.name from xt_sdd_object_versions xo where xo.ivid = xf.ivid ) '
        ||' WHERE exists ( '
        ||'        select null from sdd_folders lf , xtsys_import_irid_mapping xm '
        ||'        where xm.old_irid = xf.irid and xm.conflict is NULL '
        ||'        and lf.name = xf.name '
        ||'        and lf.root_flag = ''Y'' and xf.root_flag = ''Y'' ) '  );

      -- Raise an exception if renaming conflicting root folders does not result in unique names
     csr := dbms_sql.open_cursor;
     dbms_sql.parse(csr, ' select null from sdd_folders lf, xt_sdd_folders xf, xtsys_import_irid_mapping xm '
        ||'        where xm.old_irid = xf.irid and xm.conflict is NULL '
        ||'        and lf.name = xf.name '
        ||'        and lf.root_flag = ''Y'' and xf.root_flag = ''Y'' ' 
        , DBMS_SQL.NATIVE );
      IF dbms_sql.execute_and_fetch(csr, false) > 0 THEN 
         dbms_sql.close_cursor(csr);
         RAISE ROOT_FOLDER_CONFLICT;
      END IF;
      dbms_sql.close_cursor(csr);
    END IF;

  ELSIF object_type = 'CONFIGURATION' AND table_exists('XT_SDD_CONFIGURATIONS') THEN
    -- If a target IVID matches a 'new' ivid mapping then guid_mode = 'R'
    -- and in this case the second predicate 'not exists' will block the update
    execute_sql(
          ' UPDATE xt_sdd_object_versions x '
        ||' SET x.name = substr(x.name || to_char(sysdate, ''DDMMYYYYHH24MISS''),1,256) '
        ||' WHERE exists ( '
        ||'        select null from i$sdd_configurations lc, xt_sdd_configurations xc '
        ||'        where lc.name = xc.name and x.ivid = xc.ivid ) '  
        ||' AND   not exists ( '
        ||'        select null from i$sdd_configurations lc, xtsys_import_ivid_mapping m '
        ||'        where  lc.ivid = m.new_ivid ) '
                );

    execute_sql(
          ' UPDATE xt_sdd_configurations xc '
        ||' SET xc.name=(select xo.name from xt_sdd_object_versions xo where xo.ivid = xc.ivid ) '
        ||' WHERE exists ( '
        ||'        select null from i$sdd_configurations lc where lc.name = xc.name  ) '
        ||'   AND  exists ( '
        ||'        select null from xt_sdd_object_versions xo where xo.ivid = xc.ivid  ) '
        ||' AND   not exists ( '
        ||'        select null from i$sdd_configurations lc, xtsys_import_ivid_mapping m '
        ||'        where  lc.ivid = m.new_ivid ) '
                );

    -- Raise an exception if renaming conflicting configurations does not result in unique names
    csr := dbms_sql.open_cursor;
    dbms_sql.parse(csr,
          ' select null from i$sdd_configurations lc, xt_sdd_configurations xc '
        ||' where lc.name = xc.name '  
        ||' AND   not exists ( '
        ||'        select null from xtsys_import_ivid_mapping m '
        ||'        where  lc.ivid = m.new_ivid ) '
                  , DBMS_SQL.NATIVE);
    IF dbms_sql.execute_and_fetch(csr, false) > 0 THEN 
       dbms_sql.close_cursor(csr);
       RAISE CONFIGURATION_CONFLICT;
    END IF;
    dbms_sql.close_cursor(csr);
  END IF;

EXCEPTION  
    WHEN ROOT_FOLDER_CONFLICT THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;
    WHEN CONFIGURATION_CONFLICT THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END conflict_resolution;


PROCEDURE set_guid_ivid_mappings(guid_mode Varchar2) IS
  proc   varchar2(100) := 'set_guid_ivid_mappings ';
  -- Set the value of NEW_IRID, NEW_IVID according to the Guid Mode.
  -- We also need to do some special mapping settings for Seeded data 
  -- e.g. the contents of the 'SYSTEM FOLDER'
  -- Most SF content seed data (languages) can be excluded by filter via
  -- the CDI_LANGUAGE_DEFINITIONS table, however the preference set CG$GENERAL 
  -- does not have a static IRID/IVID or a seed table (hard coding!)

 csr Number;
 exe Number;

BEGIN
  rmdbg.trace(utility_name||proc || 'loc  4000 : GUID Mode: '|| guid_mode);	  
  IF guid_mode = 'D' THEN 
    -- Divergent mode selected
    -- IRID and IVID are given new values, objects cannot be traced back to source
    -- using internal identifies.
    execute_sql(
       'UPDATE xtsys_import_irid_mapping '
     ||'SET new_irid = jr_util.get_new_irid '
     );

    execute_sql(
       'UPDATE xtsys_import_ivid_mapping '
     ||'SET new_ivid = jr_system_util.get_new_ivid '
     );

  ELSIF guid_mode = 'M' THEN 
    -- Merge mode selected
    -- IRID columns keep their original values, objects can be matched with source
    -- Subsequent object versions exported can be merged/grafted at tip of object.
    -- mark conflicting objects for special processing(i.e. there is an existing 
    -- object in target Repository) 
    execute_sql (
       'UPDATE xtsys_import_irid_mapping '
     ||' SET new_irid = old_irid '
     ||' ,   conflict =(select distinct ''Y'' from i$sdd_object_versions where irid = old_irid) '
     );

    execute_sql (
       'UPDATE xtsys_import_ivid_mapping '
     ||'SET new_ivid = jr_system_util.get_new_ivid '
     );

  ELSIF guid_mode = 'R' THEN  
    -- Replica mode selected
    -- Both IRID and IVID keep their original values. 
    -- This may be used to synchronize across Repositories, objects which are not 
    -- under source control or checkout-out objects.
    execute_sql (
       'UPDATE xtsys_import_irid_mapping '
     ||' SET new_irid = old_irid '
     ||' ,   conflict =(select distinct ''Y'' from i$sdd_object_versions where irid = old_irid) '
     );

    execute_sql (
       'UPDATE xtsys_import_ivid_mapping '
     ||'SET new_ivid = old_ivid '
     );
  END IF;
 
  -- Map xtsys_export_objects.default_import_name  to xtsys_import_ivid_mapping.new_name
  execute_sql (
     'UPDATE xtsys_import_ivid_mapping m '
   ||'SET m.new_name = (SELECT eo.default_import_name '
   ||'                  FROM   xtsys_export_objects     eo '
   ||'                  WHERE eo.object_ivid = m.old_ivid) '
   ||'WHERE EXISTS ( '
   ||'       SELECT NULL '
   ||'       FROM xtsys_export_objects     eo '
   ||'       WHERE eo.object_ivid = m.old_ivid) '
   );


  -- only do the next bit if objects within folders (FOLDER MEMBERS) were extracted
  IF table_exists('XT_SDD_FOLDER_MEMBERS') THEN
    -- Identify the IRID and IVID values for SYSTEM FOLDER and contents 
    -- and set NEW_IVID, NEW_IRID to the values in the target (inbound objects
    -- must reference the SYSTEM folder and contents at the target).
    -- Also signal that pre-seeded system folder object should not be 
    -- loaded (exclude='Y').
    execute_sql (
       'UPDATE xtsys_import_irid_mapping x '
     ||'SET    new_irid = (select max(f.irid) from i$sdd_folders f where f.name = ''SYSTEM FOLDER'' ) '
     ||',      exclude  = ''Y'' '             
     ||'WHERE  x.old_irid  IN (  '
     ||'        SELECT xf.irid  '
     ||'        FROM   xt_sdd_folders  xf '
     ||'        WHERE  xf.name = ''SYSTEM FOLDER'' '
     ||'      ) '
     );

    -- exclude the SYSTEM_FOLDER seed data preference set 'CG$GENERAL'
    -- unfortunately the IRID/IVID is not seeded/predefined as for Languages
    execute_sql (
       'UPDATE xtsys_import_irid_mapping x '
     ||'SET    new_irid = ( select max(o.irid) from i$sdd_object_versions o '
     ||'        WHERE   o.name IN ( '
     ||'        SELECT xo.name '
     ||'        FROM   xt_sdd_folders        xf '
     ||'        ,      xt_sdd_folder_members xm '
     ||'        ,      xt_sdd_object_versions xo '
     ||'        WHERE  xo.name = ''CG$GENERAL'' '
     ||'          AND  xf.irid = xm.folder_reference  '
     ||'          AND  xf.name = ''SYSTEM FOLDER''  '
     ||'          AND  xm.member_object = x.old_irid  '
     ||'          AND  xm.member_object = xo.irid '
     ||'      ) ) '
     ||',      exclude  = ''Y'' '
     ||'WHERE  x.old_irid  IN ( '
     ||'        SELECT xo.irid '
     ||'        FROM   xt_sdd_folders        xf '
     ||'        ,      xt_sdd_folder_members xm '
     ||'        ,      xt_sdd_object_versions xo '
     ||'        WHERE  xf.irid = xm.folder_reference  '
     ||'          AND  xf.name = ''SYSTEM FOLDER''  '
     ||'          AND  xm.member_object = xo.irid  '
     ||'          AND  xo.name = ''CG$GENERAL'' '
     ||'      ) '
     );

    -- exclude the SYSTEM_FOLDER seed data for Languages
    execute_sql (
       'UPDATE xtsys_import_irid_mapping x '
     ||'SET    new_irid = ( select lan_irid from cdi_language_definitions '
     ||'                    WHERE  old_irid = lan_irid ) '
     ||',      exclude  = ''Y'' '
     ||'WHERE  EXISTS ( '
     ||'        SELECT NULL '
     ||'        FROM   cdi_language_definitions  '
     ||'        WHERE  old_irid = lan_irid  '
     ||'      ) '
     );

  END IF;


  -- Dependency Types, Global Name Domains are not versioned and may be seeded
  -- therefore inbound Dependencies may need to reference existing DTs and GNDs
  FOR i IN (SELECT table_name FROM xtsys_tabs_exported 
            WHERE table_name IN ('SDD_DEPENDENCY_TYPES'
                               , 'SDD_DEPENDENCIES'
                               , 'SDD_GLOBAL_NAMES'
                               , 'SDD_GLOBAL_NAME_DOMAINS') ) LOOP 
    IF i.table_name  = 'SDD_DEPENDENCY_TYPES' THEN
      rmdbg.trace(utility_name||proc || 'loc  4010');	  

      execute_sql(' UPDATE xtsys_import_irid_mapping m '
                 || ' SET    new_irid = (SELECT l.irid ' 
                 || '    FROM sdd_dependency_types l, xt_sdd_dependency_types x ' 
                 || '    WHERE m.old_irid = x.irid AND l.type = x.type '
                 || '      AND ( l.subtype = x.subtype OR '
                 || '          ( l.subtype IS NULL AND x.subtype IS NULL)))'
                 || ' ,      exclude  = ''Y'' '
                 || ' WHERE EXISTS (SELECT null ' 
                 || '    FROM sdd_dependency_types l, xt_sdd_dependency_types x ' 
                 || '    WHERE m.old_irid = x.irid AND l.type = x.type '
                 || '      AND ( l.subtype = x.subtype OR '
                 || '          ( l.subtype IS NULL AND x.subtype IS NULL)))');

      rmdbg.trace(utility_name||proc || 'loc  4020');	  
      execute_sql(' UPDATE xtsys_import_ivid_mapping m '
                 || ' SET    new_ivid = (SELECT l.ivid ' 
                 || '    FROM sdd_dependency_types l, xt_sdd_dependency_types x ' 
                 || '    WHERE m.old_ivid = x.ivid AND l.type = x.type '
                 || '      AND ( l.subtype = x.subtype OR '
                 || '          ( l.subtype IS NULL AND x.subtype IS NULL)))'
                 || ' WHERE EXISTS (SELECT null ' 
                 || '    FROM sdd_dependency_types l, xt_sdd_dependency_types x ' 
                 || '    WHERE m.old_ivid = x.ivid AND l.type = x.type '
                 || '      AND ( l.subtype = x.subtype OR '
                 || '          ( l.subtype IS NULL AND x.subtype IS NULL)))');
    ELSIF i.table_name  = 'SDD_DEPENDENCIES' THEN
      -- Dependencies are not versioned so when Importing in Merge mode need to 
      -- assign a new IRID to avoid Pkey constraint violation
      rmdbg.trace(utility_name||proc || 'loc  4025');	  

      execute_sql(' UPDATE xtsys_import_irid_mapping m '
                 || ' SET    new_irid = jr_util.get_new_irid ' 
                 || ' WHERE  old_irid IN ' 
                 || '    (SELECT irid FROM xt_sdd_dependencies  ) ');
    ELSIF i.table_name  = 'SDD_GLOBAL_NAME_DOMAINS' THEN
      rmdbg.trace(utility_name||proc || 'loc  4030');	  

      execute_sql(' UPDATE xtsys_import_irid_mapping m '
                 || ' SET    new_irid = (SELECT l.irid ' 
                 || '    FROM sdd_global_name_domains l, xt_sdd_global_name_domains x ' 
                 || '    WHERE m.old_irid = x.irid AND l.name = x.name) '
                 || ' ,      exclude  = ''Y'' '
                 || ' WHERE EXISTS (SELECT null ' 
                 || '    FROM sdd_global_name_domains l, xt_sdd_global_name_domains x ' 
                 || '    WHERE m.old_irid = x.irid AND l.name = x.name ) ');
    ELSIF i.table_name = 'SDD_GLOBAL_NAMES' THEN
      rmdbg.trace(utility_name||proc || 'loc  4040');	  

      execute_sql(' UPDATE xtsys_import_irid_mapping m '
                 || ' SET    new_irid = (SELECT l.irid ' 
                 || '    FROM sdd_global_names l, xt_sdd_global_names x ' 
                 || '    WHERE m.old_irid = x.irid AND l.name = x.name '
                 || '    AND ( l.type_id = x.type_id OR '
                 || '        (l.type_id IS NULL AND x.type_id IS NULL))'
                 || '    AND ( l.domain_irid = x.domain_irid OR '
                 || '        (l.domain_irid IS NULL AND x.domain_irid IS NULL)))'
                 || ' ,      exclude  = ''Y'' '
                 || ' WHERE EXISTS (SELECT null ' 
                 || '    FROM sdd_global_names l, xt_sdd_global_names x ' 
                 || '    WHERE m.old_irid = x.irid AND l.name = x.name '
                 || '    AND ( l.type_id = x.type_id OR '
                 || '        (l.type_id IS NULL AND x.type_id IS NULL))'
                 || '    AND ( l.domain_irid = x.domain_irid OR '
                 || '        (l.domain_irid IS NULL AND x.domain_irid IS NULL)))');
    END IF;
  END LOOP;

 
END set_guid_ivid_mappings;

PROCEDURE set_default_properties IS
  proc   varchar2(100) := 'set_default_properties ';
  -- reset default properties on CORE schema tables which would normally be set 
  -- by API/Trigger. Triggers are disabled during import so can't rely on API to do this.
  -- Currently limited to the SDD_FOLDERS.OWNING_USER

BEGIN
  rmdbg.trace(utility_name||proc || 'loc  4500 : resetting default properties in XT tables');	  

  IF table_exists('XT_SDD_FOLDERS') THEN
    execute_sql(
       'UPDATE xt_sdd_folders '
     ||'SET owning_user = user  '
     );
  END IF;

END set_default_properties;

PROCEDURE set_main_branch IS
  proc   varchar2(100) := 'set_main_branch ';
  -- Bug 1987869
  -- When a configuration is exported all objects are created as new on the main branch
  -- in the target repository and branch information is not extracted from the source
  -- repository.
  -- Any version associations referencing branches not extracted from the source 
  -- repository need to be mapped to the main branch in the target repository.
  -- This code is currently only executed for configurations when guid_mode is 'D'
  
BEGIN
  rmdbg.trace(utility_name||proc || 'loc  4600 : remapping branches to the main branch ');	  

  IF (table_exists('XT_SDD_VERSION_ASSOCIATIONS') AND
      NOT table_exists('XT_SDD_BRANCHES')) THEN
    execute_sql(
       'UPDATE xt_sdd_version_associations '
     ||'SET branch_id = (SELECT branch_id from sdd_branches WHERE  upper(name) = ''MAIN'') '
     );
  END IF;

END set_main_branch;

PROCEDURE checkin_status_i_objs(wa_mode Varchar2) IS
  proc   varchar2(100) := 'checkin_status_i_objs ';
  -- This procedure will check-in any objects which were in the
  -- checked-in state prior to an EXPORT. 
  -- Note that when executing in 'R'esolved Workarea mode any other configuration
  -- structure/information will not be restored.
 csr integer;
 exe integer;
 irid number;
 ivid number;
 void_num number;
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 50');  
  IF wa_mode = 'R' THEN
    rmdbg.trace(utility_name||proc || 'loc 50');  

    csr := dbms_sql.open_cursor;
    -- Select the IRIDs of objects which were in a 
    -- check-in state prior to EXPORT.
    DBMS_SQL.PARSE(csr,
        ' SELECT distinct new.irid, new.ivid '
      ||' FROM   xt_sdd_object_versions     old '
      ||' ,      xtsys_import_ivid_mapping '
      ||' ,      sdd_object_versions        new '  
      ||' WHERE  old.ivid = old_ivid '
      ||' AND    new.ivid = new_ivid '
      ||' AND    old.state =  ''I'' '
      ||' AND  NOT EXISTS ( '
      ||'      SELECT NULL '
      ||'      FROM   xtsys_import_irid_mapping xir '
      ||'      WHERE  new.irid = xir.new_irid '
      ||'        AND  xir.exclude = ''Y'' ) '
         , DBMS_SQL.NATIVE);

    DBMS_SQL.DEFINE_COLUMN(csr, 1, irid);
    DBMS_SQL.DEFINE_COLUMN(csr, 2, ivid);
    rmdbg.trace(utility_name||proc || 'loc 51  : Parse csr');  
    exe := DBMS_SQL.EXECUTE(csr);

    LOOP
      IF DBMS_SQL.FETCH_ROWS(csr)>0 THEN

        DBMS_SQL.COLUMN_VALUE(csr, 1, irid);
        DBMS_SQL.COLUMN_VALUE(csr, 2, ivid);
 
        void_num:= jr_version.check_in(irid, ivid
                  , 'Object was checked-in at source before it was Exported ' ||
                    'and was therefore checked-in at target by Import', null, null);

      ELSE  
        EXIT;
      END IF;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(csr);

  ELSIF wa_mode = 'U' THEN
    rmdbg.trace(utility_name||proc || 'loc 52');  

    IF guid_mode = 'R' THEN 
      -- nothing to do for this mode but we'll do the test this way 
      -- in case we need to put some code in here in future
      NULL;
    ELSE
      -- exporting a single version of an object may result in rootless and tipless
      -- object versions but these must be fixed up
      -- Bug 1987869, also patch up ROOTS where edge_kind is 'B' (branch point)
      rmdbg.trace(utility_name||proc || 'loc 50.1:  fixing ROOTS');  
      execute_sql (
        ' UPDATE i$sdd_version_associations   a '
      ||' SET    a.from_ivid = a.to_ivid '
      ||' ,      a.edge_kind = ''R'' '
      ||' ,      a.edge_sequence = 1 '
      ||' WHERE  not exists ( '
      ||'         SELECT null '
      ||'         FROM   xtsys_import_ivid_mapping xm '
      ||'         WHERE  a.from_ivid = xm.new_ivid ) '
      ||' AND    exists ( '
      ||'         SELECT null '
      ||'         FROM   xtsys_import_ivid_mapping n '
      ||'         WHERE  n.new_ivid = a.to_ivid ) '
      ||' AND    (a.edge_kind = ''N'' OR a.edge_kind = ''B'') '
         );

      rmdbg.trace(utility_name||proc || 'loc 50.2:  fixing TIPS');  
      execute_sql (
        ' UPDATE i$sdd_version_associations   a '
      ||' SET    a.to_ivid = a.from_ivid '
      ||' ,      a.edge_kind = ''T'' '
      ||' ,      a.edge_sequence = 1 '
      ||' WHERE  not exists ( '
      ||'         SELECT null '
      ||'         FROM   xtsys_import_ivid_mapping xm '
      ||'         WHERE  a.to_ivid = xm.new_ivid ) '
      ||' AND    exists ( '
      ||'         SELECT null '
      ||'         FROM   xtsys_import_ivid_mapping n '
      ||'         WHERE  n.new_ivid = a.from_ivid ) '
      ||' AND    a.edge_kind = ''N'' '
         );


      -- remove any dangling 'Merge' edges
      execute_sql (
        ' DELETE from i$sdd_version_associations a '
      ||' WHERE  not exists ( '
      ||'         SELECT null '
      ||'         FROM   xtsys_import_ivid_mapping xm '
      ||'         WHERE  a.to_ivid = xm.new_ivid ) '
      ||' AND    exists ( '
      ||'         SELECT null '
      ||'         FROM   xtsys_import_ivid_mapping n '
      ||'         WHERE  n.new_ivid = a.from_ivid ) '
      ||' AND    a.edge_kind = ''M'' '
         );

      execute_sql (
        ' DELETE from i$sdd_version_associations a '
      ||' WHERE  not exists ( '
      ||'         SELECT null '
      ||'         FROM   xtsys_import_ivid_mapping xm '
      ||'         WHERE  a.from_ivid = xm.new_ivid ) '
      ||' AND    exists ( '
      ||'         SELECT null '
      ||'         FROM   xtsys_import_ivid_mapping n '
      ||'         WHERE  n.new_ivid = a.to_ivid ) '
      ||' AND    a.edge_kind = ''M'' '
         );

      -- reset to 'N'ew any object versions which were 'O'ut but 
      -- after Import have only one object version
      execute_sql (
        ' UPDATE i$sdd_object_versions  t '
      ||' SET    t.state = ''N''  '
      ||' WHERE  t.state  = ''O'' '
      ||'   AND  EXISTS ( '
      ||'       SELECT COUNT(s.irid), s.irid '
      ||'       FROM   i$sdd_object_versions s '
      ||'       ,      xtsys_import_irid_mapping m '
      ||'       WHERE  t.irid = s.irid '
      ||'         AND  s.irid = m.new_irid '
      ||'       GROUP BY s.irid '
      ||'       HAVING  count(s.irid) = 1) '
         );
      -- and delete any inconsistent version association rows for above
      execute_sql (
        ' DELETE FROM i$sdd_version_associations a '
      ||' WHERE a.irid IN ( '
      ||'       SELECT s.irid '
      ||'       FROM   i$sdd_object_versions s '
      ||'       ,      xtsys_import_irid_mapping m '
      ||'       WHERE  s.irid = m.new_irid '
      ||'         AND  s.state = ''N'') '
         );

    END IF;
  END IF;

  EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END  checkin_status_i_objs;

PROCEDURE preins_cmp_wa IS
  proc   varchar2(100) := 'preins_cmp_wa ';      

BEGIN
  -- All that is required for now is to ensure that there are no root
  -- folder name conflicts anywhere in the Repository.
  -- This will support importing verion resolved or objects from a
  -- non versioned repository being loaded into an existing Workarea
  -- (with or without versioneing enabled).
  conflict_resolution('FOLDER');

EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END preins_cmp_wa;

PROCEDURE ins_tab_rows (wa_mode Varchar2, mode_tab_prefix Varchar2) IS
  proc   varchar2(100) := 'ins_tab_rows ';
--    This procedure generates and executes a direct SQL INSERT statement for each of
--    the exported tables. 
--    In 'R'esolved and 'C'heched out WA modes, rows are inserted in the 
--    context Workarea passed in as an argument to the Import utility.
--    When importing into a non empty Workarea in 'M'erge mode existing objects
--    with the same IRID value as an object in the import tables will be checked-in
--    and the inbound object will be appended to the objects branch.
--    If the inbound object was in a checked-in state at source then it will 
--    automatically be checked-in.
--    Although 'CONFIGURATIONS' can be versioned, they must be loaded before executing
--    this procedure as they are referenced by foreign keys in the Workarea structure tables.
--  NOTES
--   1) Referential and Check constraint validation is disabled (data is assumed valid).
--   2) It is assumed the SQL statements may be very large and are therefore assembled in
--    fixed size chunks which are then executed using the VARCHAR2S datatype of 
--    dbms_sql.parse.

-- A complete set of INSERT statements is built up in 'ins_statements'.
-- Each row corresponds to a limited size chunk of the statement.
-- Statements are delimited/seperated by the row value '/* NEXT ....*/'
ins_statements sql_table; 
insrt_list     sql_table; 
                         -- temporary assembly list for the 'inserting into columns'
slect_list     sql_table;  
                         -- temporary assembly list for the 'selecting from columns'
-- For very large SQL statements use the VARCHAR2S type of 
-- the SQL_PARSE procedure
ins_stmt       dbms_sql.varchar2s;  

cflct_irid irid_table;    
                         -- used to process objects with same irid source/target
cflct_old_ivid ivid_table;    
                         -- used to process objects with same ivid source/target
cflct_new_ivid ivid_table;    
                         -- used to process objects with same ivid source/target

csr integer;
ind integer;
ix  integer;
exe integer;

void_ivid number;

-- Table data must be loaded in the correct order.
-- Note the excluded tables data is managed elsewhere.
CURSOR get_tabs IS
SELECT   MAX(decode(table_name
         , 'SDD_OBJECT_VERSIONS',       2
         , 'I$SDD_WA_CONTEXT',          3
         , 'SDD_CONFIGURATION_MEMBERS', 4
         , 'SDD_VERSION_ASSOCIATIONS',  5
         , decode (ta.pass, 1, 1, ta.pass + 5)
         ) )             ord
,  ta.table_name         table_name
,  ta.exp_table_name     xt_table_name
,  ta.table_irid         table_irid
FROM  xtsys_tabs_exported  ta
WHERE table_name not in (
                          'SDD_BRANCHES'
                        , 'SDD_CONFIGURATIONS', 'SDD_RULES', 'I$SDD_RULE_PARAMS'
                        , 'I$SDD_RULE_PARAM_VALUES', 'SDD_WORKAREA_SPEC_ENTRIES'
                        , 'SDD_WORKAREAS'
                        , 'SDW_USERS', 'SDW_ACCESS_RIGHTS'
                        , 'SDD_WASTEBASKET'
                        )
GROUP BY ta.table_irid, ta.table_name, ta.exp_table_name
ORDER BY 1;

-- Note that rows for the Mapped IRID and IVID values are stored 
-- during Export and held in the XTSYS_IMPORT_IRID_MAPPING 
-- and XTSYS_IMPORT_IVID_MAPPING tables.
-- Use this cursor when guid IDs and reference properties are reassigned
CURSOR get_cols(irid_of_import_tab Number, import_tab Varchar2) IS
SELECT decode(rownum, 1, null, ',') || rc1.column_name          ins_col
,      decode(rownum, 1, null, ',') || 
         decode (rc1.column_name
                ,'IRID', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                ,'PAC_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                ,'MEMBER_OBJECT', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                ,'IVID', 'xtimp_fn_get_mapivid('||rc1.column_name||')'
                ,'PARENT_IVID', 'xtimp_fn_get_mapivid('||rc1.column_name||')'
                , rc2.column_name, 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                --Bug 3443907 : Assign the new ID to SUB_DIAGRAM_ID column. skhwaja
                , decode(import_tab,
                   'SDD_DIA', decode(rc1.column_name
                                     ,'SUB_DIAGRAM_ID', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_FOLDERS', decode(rc1.column_name
                                     ,'NAME', 'xtimp_fn_get_map_name(ivid, name)'
                                     ,rc1.column_name
                                     ),
                   'SDD_DFLATT', decode(rc1.column_name
                                     ,'ENTITY_REFERENCE', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),                                     
                   'SDD_DSTATT', decode(rc1.column_name
                                     ,'ENTITY_REFERENCE', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),                                                                          
                   'SDD_FUNATT', decode(rc1.column_name
                                     ,'ENTITY_REFERENCE', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),                                                                          
                   'RM_TEXT_LINES', decode(rc1.column_name
                                     ,'TXT_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'CK_APPLICATION_LOGIC', decode(rc1.column_name
                                     ,'AL_ID', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,'AL_REPOS_ELEMENT', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_SMI', decode(rc1.column_name
                                     ,'ELEMENT_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_DOCXXX', decode(rc1.column_name
                                     ,'ELEMENT_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_DEU', decode(rc1.column_name
                                     ,'CIELEMENT_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_SOU', decode(rc1.column_name
                                     ,'CIELEMENT_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_UPR', decode(rc1.column_name
                                     ,'CIELEMENT_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_ERU', decode(rc1.column_name
                                     ,'CIELEMENT_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,'UML_RULE_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_PRU', decode(rc1.column_name
                                     ,'CIELEMENT_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,'PREFERENCE_SET_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_UES', decode(rc1.column_name
                                     ,'PART_OF_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,'USE_OF_REF', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_DEPENDENCIES', decode(rc1.column_name
                                     ,'SUPPLIER_IRID', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,'SUPPLIER_IVID', 'xtimp_fn_get_mapivid('||rc1.column_name||')'
                                     ,'SUPPLIER_PARENT_IVID', 'xtimp_fn_get_mapivid('||rc1.column_name||')'
                                     ,'CLIENT_IRID', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,'CLIENT_IVID', 'xtimp_fn_get_mapivid('||rc1.column_name||')'
                                     ,'CLIENT_PARENT_IVID', 'xtimp_fn_get_mapivid('||rc1.column_name||')'
                                     ,'DEPENDENCY_TYPE_IRID', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,'PARENT_DEPENDENCY_IRID', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                   'SDD_GLOBAL_NAMES', decode(rc1.column_name
                                     ,'DOMAIN_IRID', 'xtimp_fn_get_mapirid('||rc1.column_name||')'
                                     ,rc1.column_name
                                     ),
                    rc1.column_name)
                 )                                  sel_col
FROM   all_tab_columns        rc1
,      
      (SELECT DISTINCT             
                    -- some columns in more than one constraint
               cc1.column_name    
       FROM                                 
          rm_sql_constraints     sc1
       ,  rm_sql_constraints     sc2
       ,  xtsys_tabs_exported    ta2
       ,
          rm_sql_cons_columns    cc1
       WHERE  
           sc1.table_irid  = irid_of_import_tab
       AND sc2.constraint_name = sc1.r_constraint_name
       AND ta2.table_irid = sc2.table_irid
       AND
           cc1.constraint_irid  = sc1.irid
      )                       rc2
WHERE  rc1.table_name = import_tab
AND    rc1.owner = repos_owner
AND  
       rc2.column_name (+) = rc1.column_name;

-- This cursor is used only in 'U'nresolved WA mode.
-- For SDD_OBJECT_VERSIONS, SDD_CONFIGURATION_MEMBERS and SDD_VERSION_ASSOCIATIONS
-- use the online dictionary because not supported by RM meta data
CURSOR get_cols_onlineDict(infra_tname Varchar2) IS
SELECT decode(rownum, 1, null, ',') || uc.column_name          ins_col
,      decode(rownum, 1, null, ',') || 
         decode (uc.column_name
                ,'IRID',         'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'IVID',         'xtimp_fn_get_mapivid('||uc.column_name||')'
                ,'SEQ',          'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'ENTRY_SEQ',    'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'SPEC_ENTRY',    'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'PREDECESSOR',  'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'WORKAREA_IRID','xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'RULE_IRID',    'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'BRANCH_ID',    'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'PAC_REF',      'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'MEMBER_OBJECT','xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'CONFIG_IVID',  'xtimp_fn_get_mapivid('||uc.column_name||')'
                ,'CONFIG_IRID',  'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'OBJECT_IVID',  'xtimp_fn_get_mapivid('||uc.column_name||')'
                ,'OBJECT_IRID',  'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'FROM_IVID',    'xtimp_fn_get_mapivid('||uc.column_name||')'
                ,'TO_IVID',      'xtimp_fn_get_mapivid('||uc.column_name||')'
                ,'PARENT_IVID',  'xtimp_fn_get_mapivid('||uc.column_name||')'
                ,uc.column_name)                               sel_col
FROM  all_tab_columns     uc
WHERE uc.table_name = infra_tname   
  AND owner = repos_owner
;

i_irid     Number;
i_new_ivid Number;
i_ivid     Number;
prev_pass  Number := 0;

BEGIN
  rmdbg.trace(utility_name||proc || 'loc 5');  
  IF jr_context.is_enabled(JR_CONTEXT.ACCESS_RIGHTS) THEN 
    rmdbg.trace(' JR_CONTEXT.ACCESS_RIGHTS   access rights checking is enabled');
  END IF;
  IF jr_context.is_enabled(JR_CONTEXT.OBJECT_VERSIONS   ) THEN 
    rmdbg.trace(' JR_CONTEXT.OBJECT_VERSIONS  Object Version creation/update on PAC create');
  END IF;
  IF jr_context.is_enabled(JR_CONTEXT.FOLDER_MEMBERS    ) THEN 
    rmdbg.trace(' JR_CONTEXT.FOLDER_MEMBERS    Folder Member creation when a PAC is created ');
  END IF;
  IF jr_context.is_enabled(JR_CONTEXT.REPOS_CONSTRAINTS) THEN 
    rmdbg.trace(' JR_CONTEXT.REPOS_CONSTRAINTS Primary, Unique and Referential enabled');
  END IF;
  IF jr_context.is_enabled(JR_CONTEXT.NO_CHECKIN_UPDATE) THEN 
    rmdbg.trace(' JR_CONTEXT.NO_CHECKIN_UPDATE No update of checked in object versions ');
  END IF;

  ins_statements := sql_table();
  FOR i in get_tabs LOOP
    -- In 'R'esolved workarea mode the DB triggers will create the appropriate
    -- data for the 'versioning' tables so exclude these tables.
    IF wa_mode = 'U' OR  (wa_mode != 'U' and i.table_name not in (
                           'SDD_OBJECT_VERSIONS', 
                           'I$SDD_WA_CONTEXT',
                           'SDD_CONFIGURATION_MEMBERS', 
                           'SDD_VERSION_ASSOCIATIONS')) THEN 
      rmdbg.trace(utility_name||proc ||' loc 32 : '|| i.table_name);
 
      insrt_list := sql_table();
      slect_list := sql_table();

      -- Construct ordered lists for the INSERT columns list and Selected values list
      IF i.table_name in ('SDD_OBJECT_VERSIONS', 
                             'SDD_CONFIGURATION_MEMBERS', 
                             'SDD_VERSION_ASSOCIATIONS',
                             'I$SDD_WA_CONTEXT') THEN
        FOR j in get_cols_onlineDict(i.table_name) LOOP
          insrt_list.extend;
          insrt_list(insrt_list.last) := j.ins_col;
          slect_list.extend;
          slect_list(slect_list.last) := j.sel_col;
        END LOOP;
      ELSE
        FOR j in get_cols(i.table_irid, i.table_name) LOOP
          insrt_list.extend;
          insrt_list(insrt_list.last) := j.ins_col;
          slect_list.extend;
          slect_list(slect_list.last) := j.sel_col;
        END LOOP;
      END IF;

      rmdbg.trace(utility_name||proc || 'loc 32.0 : retrieved Table Columns ');

      --Next block of code assembles the complete INSERT statement 
      --prior to dynamic SQL execution
      ins_statements.extend;
      IF i.table_name = 'I$SDD_WA_CONTEXT' THEN
        ins_statements(ins_statements.last):= 'INSERT INTO '  
                                            || i.table_name ||' (';
      ELSIF i.table_name in (
                          'SDD_DEPENDENCIES', 'SDD_DEPENDENCY_TYPES', 
                          'SDD_GLOBAL_NAMES', 'SDD_GLOBAL_NAME_DOMAINS') THEN
        ins_statements(ins_statements.last):= 'INSERT INTO '  
                                            || i.table_name ||' (';
      ELSE
        ins_statements(ins_statements.last):= 'INSERT INTO '  
                                            || mode_tab_prefix 
                                                             -- 'I$' if 'U'  
                                            || i.table_name ||' (';
      END IF;
      FOR k IN insrt_list.first .. insrt_list.last LOOP
        ins_statements.extend;
        ins_statements(ins_statements.last):= insrt_list(k);
      END LOOP;
      ins_statements.extend;
      ins_statements(ins_statements.last):=' ) SELECT ';
      FOR k IN insrt_list.first .. insrt_list.last LOOP
        ins_statements.extend;
        ins_statements(ins_statements.last):= slect_list(k);
      END LOOP;
      ins_statements.extend;
      ins_statements(ins_statements.last):= 'FROM ' || i.xt_table_name;
      rmdbg.trace(utility_name||proc || 'loc 33');
        
        -- Now append conditions for special cases and handling exclusions
        IF i.table_name = 'SDD_DEPENDENCY_TYPES' THEN 
          ins_statements.extend;
          rmdbg.trace(utility_name||proc || 'loc 33a');
          ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null ' 
                                           || '    FROM sdd_dependency_types l ' 
                                           || '    WHERE l.type = x.type '
                                           || '      AND ( l.subtype = x.subtype OR '
                                           || '          ( l.subtype IS NULL AND x.subtype IS NULL)))';
        ELSIF i.table_name = 'SDD_GLOBAL_NAME_DOMAINS' THEN
          ins_statements.extend;
          rmdbg.trace(utility_name||proc || 'loc 33b');
          ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null ' 
                                           || ' FROM sdd_global_name_domains l ' 
                                           || ' WHERE l.name = x.name  ) ';
        ELSIF i.table_name = 'SDD_GLOBAL_NAMES' THEN
          ins_statements.extend;
          rmdbg.trace(utility_name||proc || 'loc 33c');
          ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null ' 
                                           || ' FROM sdd_global_names l ' 
                                           || ' WHERE l.name = x.name ';
          ins_statements.extend;
          ins_statements(ins_statements.last):= 
                   ' AND ( l.type_id = x.type_id OR ' 
                      ||'(l.type_id is NULL and x.type_id is NULL))';
          ins_statements.extend;
          ins_statements(ins_statements.last):= 
                   ' AND ( l.domain_irid = xtimp_fn_get_mapirid(x.domain_irid) OR '
                || ' (l.domain_irid IS NULL AND x.domain_irid IS NULL)))';
        ELSIF i.table_name = 'SDD_DEPENDENCIES' THEN 
          IF wa_mode = 'R' THEN
            rmdbg.trace(utility_name||proc || 'loc 33d');
            -- To avoid the Dependency manager reporting that imported dependencies are
            -- not built or out of date, need to...
            ins_statements.extend;
            ins_statements(ins_statements.last):= '/* NEXT BLOCK */';
            ins_statements.extend;
            ins_statements(ins_statements.last):= 
                   ' UPDATE i$sdd_object_versions t '
                 ||' SET (obj_notm, obj_notm_when_analyzed) = ';
            ins_statements.extend;
            ins_statements(ins_statements.last):= 
                   '   (SELECT obj_notm, obj_notm_when_analyzed '
                 ||'    FROM xt_sdd_object_versions s    '
                 ||'    WHERE t.ivid = xtimp_fn_get_mapivid(s.ivid)'
                 ||'    AND s.obj_notm_when_analyzed IS NOT NULL ) ';
            ins_statements.extend;
            ins_statements(ins_statements.last):= 
                   ' WHERE t.ivid IN ( SELECT xtimp_fn_get_mapivid(s.ivid) '
                 ||'                   FROM   xt_sdd_object_versions s '
                 ||'                   WHERE  s.obj_notm_when_analyzed IS NOT NULL ) ';
          ELSE 
            -- note that in Unresolved WA mode, Import loads the Object Version data itself and
            -- not via triggers, so the above extension is not required.
            NULL;
          END IF;
        ELSIF i.table_name in ( 'SDD_OBJECT_VERSIONS', 'SDD_VERSION_ASSOCIATIONS' ) THEN 
          -- note must also be in 'U'nresolved workarea mode if loading object versions and associations
          rmdbg.trace(utility_name||proc || 'loc 35a.0 ');
          ins_statements.extend;
          ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null ' 
                                           || '  FROM xtsys_import_irid_mapping xm '
                                           || '  WHERE x.irid = xm.old_irid '
                                           || '    AND exclude = ''Y'' ) ';
        ELSIF i.table_name = 'SDD_CONFIGURATION_MEMBERS' THEN 
          -- note must also be in 'U'nresolved workarea mode if loading configuration members
          ins_statements.extend;
          ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null ' 
                                           || '  FROM xtsys_import_irid_mapping xm '
                                           || '  WHERE x.object_irid = xm.old_irid '
                                           || '    AND exclude = ''Y'' ) ';
        ELSIF i.ord <= 2 THEN 
                                 -- Versionable PAC objects
                                 -- See cursor 'get_tabs'  to understand '<= 2'
          -- add predicate to filter out excluded objects
          ins_statements.extend;
          ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null ' 
                                           || '  FROM xtsys_import_irid_mapping xm '
                                           || '  WHERE x.irid = xm.old_irid '
                                           || '    AND exclude = ''Y'' ) ';
        ELSIF i.ord > 5 THEN 
                           -- Dependendent data/elements
                           -- See cursor 'get_tabs' to understand '> 5'
          -- add predicate to filter out data/elements where parent has been excluded
          ins_statements.extend;
          ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null ' 
                                           || '  FROM xtsys_import_irid_mapping xm '
                                           || '  WHERE x.pac_ref = xm.old_irid '
                                           || '    AND exclude = ''Y'' ) ';
        END IF;
        -- end of special cases

      ins_statements.extend;
      IF short_transactions AND i.ord > 5 AND i.ord > prev_pass THEN
        -- commit after each level (if required) otherwise deferred constraints
        -- may fail
        -- Start comitting when SAC registered types being loaded i.e. ord > 5
        ins_statements(ins_statements.last):= '/* NEXT COMMIT */';
      ELSE
        ins_statements(ins_statements.last):= '/* NEXT BLOCK */';
      END IF;
      prev_pass := i.ord;

    END IF;

  END LOOP;

  rmdbg.trace(utility_name||proc || 'loc 35');

  cflct_irid := irid_table();
  cflct_old_ivid := ivid_table();
  cflct_new_ivid := ivid_table();
  IF not empty_target_wa THEN
    -- need to process conflicting objects at the target before executing the INSERT statements
    -- i.e. the object already exists at target
    csr := dbms_sql.open_cursor;
      DBMS_SQL.PARSE(csr,
        ' SELECT r.old_irid  '
      ||',       v.new_ivid   '
      ||',      ot.ivid      '
      ||'FROM xtsys_import_irid_mapping   r  '
      ||',    xtsys_import_ivid_mapping   v  '
      ||',    xt_sdd_object_versions      os '
      ||',    sdd_object_versions         ot '
      ||'WHERE ot.irid = r.old_irid '
      ||'AND   os.irid = r.old_irid '
      ||'AND   os.ivid = v.old_ivid '
      ||'AND   r.conflict = ''Y'' ' 
      ||'AND   r.exclude is null '
         , DBMS_SQL.NATIVE);

    DBMS_SQL.DEFINE_COLUMN(csr, 1, i_irid);
    DBMS_SQL.DEFINE_COLUMN(csr, 2, i_new_ivid);
    DBMS_SQL.DEFINE_COLUMN(csr, 3, i_ivid);
    rmdbg.trace(utility_name||proc || 'loc 52  : Parse csr');  
    exe := DBMS_SQL.EXECUTE(csr);

    LOOP
      IF DBMS_SQL.FETCH_ROWS(csr)>0 THEN

        DBMS_SQL.COLUMN_VALUE(csr, 1, i_irid);
        DBMS_SQL.COLUMN_VALUE(csr, 2, i_new_ivid);
        DBMS_SQL.COLUMN_VALUE(csr, 3, i_ivid);
        -- If the conflicting object is new or not checked-in then check it in
       -- so that no history/information is lost
       IF not jr_version.is_checked_in(i_irid, i_ivid) THEN
          void_ivid := jr_version.check_in(i_irid,i_ivid                                                
                        ,'existing object checked-in automatically to allow '||
                         ' another version to be Imported'
                        , NULL, NULL);

       END IF;

       rmdbg.trace(utility_name||proc || 'loc 35.1 ');
       rmdbg.trace(' irid ' || i_irid);
       rmdbg.trace(' ivid ' || i_ivid);
       rmdbg.trace(' new_ivid ' || i_new_ivid);
       -- Store the results of the current cursor because we will need them
       -- again to complete the 'M'erge after we've inserted the inbound objects
       cflct_irid.extend;
       cflct_old_ivid.extend;
       cflct_new_ivid.extend;
       cflct_irid(cflct_irid.last) := i_irid;
       cflct_old_ivid(cflct_old_ivid.last) := i_ivid;
       cflct_new_ivid(cflct_new_ivid.last) := i_new_ivid;

       -- If extending the branch of an object which is currently a PRIVATE object
       -- then the context setting row for that object must be removed (replaced).
       -- The new entry will be imported from XT_I$SDD_WA_CONTEXT
       execute_sql (
         ' DELETE i$sdd_wa_context '
       ||' WHERE workarea_irid = ' || jr_context.workarea
       ||' AND   object_ivid   = ' || i_ivid
       ||' AND   spec_entry is null '
         );


      ELSE  
        EXIT;
      END IF;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(csr);

  END IF;

  rmdbg.trace(utility_name||proc || 'loc 40 Starting insert of Instance data...');

  IF short_transactions THEN
    ck_util.do_commit (prollback_segment);
  END IF;
  -- Execute each of the SQL statements in turn, note that constraints have
  -- already been switched off earlier in this procedure
  IF jr_context.is_enabled(JR_CONTEXT.ACCESS_RIGHTS) THEN 
    rmdbg.trace(' JR_CONTEXT.ACCESS_RIGHTS   access rights checking is enabled');
  END IF;
  IF jr_context.is_enabled(JR_CONTEXT.OBJECT_VERSIONS   ) THEN 
    rmdbg.trace(' JR_CONTEXT.OBJECT_VERSIONS  Object Version creation/update on PAC create');
  END IF;
  IF jr_context.is_enabled(JR_CONTEXT.FOLDER_MEMBERS    ) THEN 
    rmdbg.trace(' JR_CONTEXT.FOLDER_MEMBERS    Folder Member creation when a PAC is created ');
  END IF;
  IF jr_context.is_enabled(JR_CONTEXT.REPOS_CONSTRAINTS) THEN 
    rmdbg.trace(' JR_CONTEXT.REPOS_CONSTRAINTS Primary, Unique and Referential enabled');
  END IF;
  IF jr_context.is_enabled(JR_CONTEXT.NO_CHECKIN_UPDATE) THEN 
    rmdbg.trace(' JR_CONTEXT.NO_CHECKIN_UPDATE No update of checked in object versions ');
  END IF;
  -- again disable constraints which may be re-enabled by previous versioning op
  jr_context.disable(jr_context.FOLDER_MEMBERS);

  ind := ins_statements.first;
  WHILE ind IS NOT NULL LOOP
      ix := 1;
      rmdbg.trace(utility_name||proc || 'loc 45ins');
      WHILE ins_statements(ind) NOT IN ( '/* NEXT COMMIT */', '/* NEXT BLOCK */')  LOOP 
         ins_stmt(ix) := ins_statements(ind);
         ix := ix + 1;
         ind:=ins_statements.next(ind);
      END LOOP;
      rmdbg.trace(utility_name||proc || 'loc 47ins');
    	--Set rollback segment...

      IF ins_statements(ind) = '/* NEXT COMMIT */' THEN
        ck_util.do_commit (prollback_segment);
      END IF;

      execute_sql(ins_stmt, ix - 1);
	--Get the next statement
	ind:=ins_statements.next(ind);
  END LOOP;  

  IF short_transactions THEN
    ck_util.do_commit (prollback_segment);
  END IF;
  -- complete the 'M'erge operation by creating the version assocation between
  -- an imported object and its predecessor, if one exists.
  IF not empty_target_wa THEN
     ind := cflct_irid.first;
     WHILE ind is NOT NULL LOOP
       rmdbg.trace(utility_name||proc || 'loc 48a : irid ' || cflct_irid(ind));
       execute_sql (
         'insert into i$sdd_version_associations    '
       ||' (irid ,from_ivid ,to_ivid ,edge_kind     '
       ||',created_by ,date_created ,branch_id ,notes) '
       ||' values ('
       ||  cflct_irid(ind)     ||' , '
       ||  cflct_old_ivid(ind) ||' , '
       ||  cflct_new_ivid(ind) ||' , '
       ||  ' ''C'', '
       ||  ' USER,SYSDATE, '
       ||   jr_version.get_current_branch_id(cflct_irid(ind),cflct_old_ivid(ind)) || ' , '
       ||  ' ''Created by IMPORT utility'') '
         );

      execute_sql (
        ' update i$sdd_object_versions '
      ||' set state = ''O'' '
      ||' where ivid = ' || cflct_new_ivid(ind)
        );
      ind := cflct_irid.next(ind);
     END LOOP;
  END IF;
  IF short_transactions THEN
    ck_util.do_commit (prollback_segment);
  END IF;
  
  -- check in objects which were checked in prior to export
  rmdbg.trace(utility_name||proc || 'loc 49');
  checkin_status_i_objs(wa_mode);

  rmdbg.trace(utility_name||proc || 'loc 50');
  IF short_transactions THEN
    ck_util.do_commit (prollback_segment);
  END IF;

  EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END ins_tab_rows;

-------------------------------------------------------------------------------------------
PROCEDURE r_mode_ins_tab_rows (wa_mode Varchar2, mode_tab_prefix Varchar2) IS
  proc   varchar2(100) := 'r_mode_ins_tab_rows ';

-- Same purpose as 'ins_tab_rows' except that UPDATE statements are generated
-- to update properties in the case of an IVID match.
-- If the object version doesn't exist then it will be inserted.

ins_statements sql_table; 
upd_statements sql_table; 
insrt_list     sql_table; 
                         -- temporary assembly list for the 'inserting into columns'
slect_list     sql_table;  
                         -- temporary assembly list for the 'selecting from columns'
-- For very large SQL statements use the VARCHAR2S type of 
-- the SQL_PARSE procedure
ins_stmt       dbms_sql.varchar2s;  

cflct_irid irid_table;    
                         -- used to process objects with same irid source/target
cflct_old_ivid ivid_table;    
                         -- used to process objects with same ivid source/target
cflct_new_ivid ivid_table;    
                         -- used to process objects with same ivid source/target

csr integer;
ind integer;
ix  integer;
exe integer;

void_ivid number;

-- Table data must be loaded in the correct order.
-- Note the excluded tables data is managed elsewhere.
CURSOR get_tabs IS
SELECT  MAX (decode(table_name
         , 'SDD_OBJECT_VERSIONS',       2
         , 'I$SDD_WA_CONTEXT',          3
         , 'SDD_CONFIGURATION_MEMBERS', 4
         , 'SDD_VERSION_ASSOCIATIONS',  5
         , decode (ta.pass, 1, 1, ta.pass + 5)
         ) )             ord
,  ta.table_name         table_name
,  ta.exp_table_name     xt_table_name
,  ta.table_irid         table_irid
FROM  xtsys_tabs_exported  ta
WHERE table_name not in (
                          'SDD_BRANCHES'
                        , 'SDD_CONFIGURATIONS', 'SDD_RULES', 'I$SDD_RULE_PARAMS'
                        , 'I$SDD_RULE_PARAM_VALUES', 'SDD_WORKAREA_SPEC_ENTRIES'
                        , 'SDD_WORKAREAS'
                        , 'SDW_USERS', 'SDW_ACCESS_RIGHTS'
                        , 'SDD_WASTEBASKET'
                        )
GROUP BY ta.table_irid, ta.table_name, ta.exp_table_name
ORDER BY 1;


-- This simple cursor is used when guid values are kept equal to source values
CURSOR get_cols_R_mode(tname Varchar2) IS
SELECT decode(rownum, 1, null, ',') || uc.column_name          col
FROM  all_tab_columns     uc
WHERE uc.table_name = tname   
  AND owner = repos_owner
;


i_irid     Number;
i_new_ivid Number;
i_ivid     Number;
prev_pass  Number := 0;
no_update  boolean := FALSE;

BEGIN
  rmdbg.trace(utility_name||proc || 'loc r5');  

  ins_statements := sql_table();
  upd_statements := sql_table();
  FOR i in get_tabs LOOP
    -- Bug 2620284 Update statements not required
    if i.table_name in ('RM_TEXT_LINES', 'SDD_DIAGRAM_SEGMENTS','SDD_DEU') then
      no_update := TRUE;
    else
      no_update := FALSE;
    end if;
  
    -- In 'R'esolved workarea mode the DB triggers will create the appropriate
    -- data for the 'versioning' tables so exclude these tables.
    IF wa_mode = 'U' OR  (wa_mode != 'U' and i.table_name not in (
                           'SDD_OBJECT_VERSIONS', 
                           'I$SDD_WA_CONTEXT',
                           'SDD_CONFIGURATION_MEMBERS', 
                           'SDD_VERSION_ASSOCIATIONS')) THEN 
      rmdbg.trace(utility_name||proc ||' loc r32 : '|| i.table_name);
 
      insrt_list := sql_table();
      slect_list := sql_table();

      -- Construct ordered lists for the INSERT columns list and Selected values list
        -- in Replica object mode, a simple column select cursor is used
        FOR j in get_cols_R_mode(i.table_name) LOOP
          insrt_list.extend;
          insrt_list(insrt_list.last) := j.col;
          slect_list.extend;
          slect_list(slect_list.last) := j.col;
        END LOOP;


      rmdbg.trace(utility_name||proc || 'loc r32.0 : retrieved Table Columns ');

      --Next block of code assembles the complete INSERT and UPDATE statement 
      --prior to dynamic SQL execution
      ins_statements.extend;

      IF i.table_name = 'I$SDD_WA_CONTEXT' THEN
        ins_statements(ins_statements.last):= 'INSERT INTO '  
                                            || i.table_name ||' (';
        upd_statements.extend;
        upd_statements(upd_statements.last):= ' UPDATE '  
                                            || i.table_name ||' target set (';
        rmdbg.trace(utility_name||proc || 'loc r32.1 : no prefix '|| i.table_name);

      ELSIF i.table_name in (
                          'SDD_DEPENDENCIES', 'SDD_DEPENDENCY_TYPES', 
                          'SDD_GLOBAL_NAMES', 'SDD_GLOBAL_NAME_DOMAINS') THEN
        ins_statements(ins_statements.last):= 'INSERT INTO '  
                                            || i.table_name ||' (';
        upd_statements.extend;
        upd_statements(upd_statements.last):= ' UPDATE '  
                                            || i.table_name ||' target set (';
        rmdbg.trace(utility_name||proc || 'loc r32.1 : no prefix '|| i.table_name);

      -- Data in the following tables must be replaced not updated
      -- Bug 2620284 Updates are time consuming and redundant for these tables
      -- RM_TEXT_LINES, SDD_DIAGRAM_SEGMENTS, SDD_DEU
      ELSIF no_update THEN
        rmdbg.trace(utility_name||proc || 'loc r32.2 R mode : '|| i.table_name);

        -- Bug 2300092 Move DELETE code from UPDATE stack to INSERT stack
        IF i.table_name  = 'RM_TEXT_LINES' THEN
          -- To update text blocks, need to replace text blocks by removing 
          -- all the existing text lines and reinserting the new.
          ins_statements(ins_statements.last):= 'DELETE FROM i$rm_text_lines '  
                                            ||' WHERE parent_ivid IN '
                                            ||' ( SELECT parent_ivid '
                                            ||'   FROM   xt_rm_text_lines ) ';
          ins_statements.extend;
          ins_statements(ins_statements.last):= '/* NEXT BLOCK */';
          ins_statements.extend;
        ELSIF i.table_name  = 'SDD_DIAGRAM_SEGMENTS' THEN
          -- Bug 2189506 diagram segments need to be replaced not updated
          -- Delete from base table to ensure triggers fire
          ins_statements(ins_statements.last):= 'DELETE FROM i$sdd_diagram_segments '  
                                            ||' WHERE parent_ivid IN '
                                            ||' ( SELECT parent_ivid '
                                            ||'   FROM   xt_sdd_diagram_segments ) ';
          ins_statements.extend;
          ins_statements(ins_statements.last):= '/* NEXT BLOCK */';
          ins_statements.extend;
        ELSIF i.table_name  = 'SDD_DEU' THEN
          -- Bug 2189506 diagram element usages need to be replaced not updated
          -- Delete from base table to ensure triggers fire
          ins_statements(ins_statements.last):= 'DELETE FROM i$sdd_deu '  
                                            ||' WHERE parent_ivid IN '
                                            ||' ( SELECT parent_ivid '
                                            ||'   FROM   xt_sdd_deu ) ';
          ins_statements.extend;
          ins_statements(ins_statements.last):= '/* NEXT BLOCK */';
          ins_statements.extend;
        END IF;
      
        ins_statements(ins_statements.last):= 'INSERT INTO '  
                                            || mode_tab_prefix 
                                                             -- 'I$' if 'U'  
                                            || i.table_name ||' (';
      ELSE
        ins_statements(ins_statements.last):= 'INSERT INTO '  
                                            || mode_tab_prefix 
                                                             -- 'I$' if 'U'  
                                            || i.table_name ||' (';
        upd_statements.extend;
        upd_statements(upd_statements.last):= 'UPDATE '  
                                            || mode_tab_prefix 
                                                             -- 'I$' if 'U'  
                                            || i.table_name ||' target set (';
      END IF;

      -- RM_TEXT_LINES, SDD_DIAGRAM_SEGMENTS, SDD_DEU
      -- Bug 2620284 Update statements not required
      IF no_update THEN

        FOR k IN insrt_list.first .. insrt_list.last LOOP
          ins_statements.extend;
          ins_statements(ins_statements.last):= insrt_list(k);
        END LOOP;

        ins_statements.extend;
        ins_statements(ins_statements.last):=' ) SELECT ';
 
        FOR k IN insrt_list.first .. insrt_list.last LOOP
          ins_statements.extend;
          ins_statements(ins_statements.last):= slect_list(k);
        END LOOP;

      ELSE

        FOR k IN insrt_list.first .. insrt_list.last LOOP
          ins_statements.extend;
          ins_statements(ins_statements.last):= insrt_list(k);
          upd_statements.extend;
          upd_statements(upd_statements.last):= insrt_list(k);
        END LOOP;

        ins_statements.extend;
        ins_statements(ins_statements.last):=' ) SELECT ';
        upd_statements.extend;
        upd_statements(upd_statements.last):=' ) = ( SELECT ';
 
        FOR k IN insrt_list.first .. insrt_list.last LOOP
          ins_statements.extend;
          ins_statements(ins_statements.last):= slect_list(k);
          upd_statements.extend;
          upd_statements(upd_statements.last):= slect_list(k);
        END LOOP;
      END IF;

      ins_statements.extend;
      ins_statements(ins_statements.last):= 'FROM ' || i.xt_table_name;
      rmdbg.trace(utility_name||proc || 'loc r33');

      IF i.table_name = 'I$SDD_WA_CONTEXT' THEN
         rmdbg.trace(utility_name||proc || 'loc r33.1 R mode : I$SDD_WA_CONTEXT');
         upd_statements.extend;
         upd_statements(upd_statements.last):= '   FROM ' || i.xt_table_name || ' XT ' ||
                                              '   WHERE target.object_ivid = xt.object_ivid ) '     ||
                                              ' WHERE target.object_ivid '||
                                              '    IN (SELECT xt.object_ivid '||
                                              '          FROM '||i.xt_table_name || ' XT) ';
                                             
          -- and if the row doesn't exist at the target need to complete the insert statement
          ins_statements.extend;
          ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null '     ||
                                              ' FROM  '||i.table_name || ' T '    ||
                                              ' WHERE x.object_ivid = t.object_ivid ) ';
      ELSIF i.table_name in (
          -- primary key of these tables is IRID as opposed to IVID
                          'SDD_DEPENDENCIES', 'SDD_DEPENDENCY_TYPES', 
                          'SDD_GLOBAL_NAMES', 'SDD_GLOBAL_NAME_DOMAINS') THEN
          rmdbg.trace(utility_name||proc || 'loc r33.2 R mode : '|| i.table_name);
          upd_statements.extend;
          upd_statements(upd_statements.last):= '   FROM ' || i.xt_table_name || ' XT ' ||
                                              '   WHERE target.irid = xt.irid ) '     ||
                                              ' WHERE target.irid '||
                                              '    IN (SELECT xt.irid'||
                                              '          FROM '||i.xt_table_name || ' XT) ';
                                              
                                      
          -- and if the row doesn't exist at the target need to complete the insert statement
          IF    i.table_name = 'SDD_DEPENDENCY_TYPES'    THEN
            ins_statements.extend;
            ins_statements(ins_statements.last):= 
                                              ' x WHERE NOT EXISTS ( ' ||
                                              '         SELECT null '     ||
                                              '         FROM  '||i.table_name || ' T '    ||
                                              '         WHERE x.irid = t.irid ) '
                                              ;
            ins_statements.extend;
            ins_statements(ins_statements.last):= 
                                              '     AND NOT EXISTS ( ' ||
                                              '         SELECT null '     ||
                                              '         FROM  '||i.table_name || ' T '    ||
                                              '         WHERE t.type    = x.type ' ||
                                              '           AND t.subtype = x.subtype )'
                                              ;
          ELSIF i.table_name = 'SDD_GLOBAL_NAMES'        THEN
            ins_statements.extend;
            ins_statements(ins_statements.last):= 
                                              ' x WHERE NOT EXISTS ( ' ||
                                              '         SELECT null '     ||
                                              '         FROM  '||i.table_name || ' T '    ||
                                              '         WHERE x.irid = t.irid ) ' 
                                              ;
            ins_statements.extend;
            ins_statements(ins_statements.last):= 
                                              '     AND NOT EXISTS ( ' ||
                                              '         SELECT null '     ||
                                              '         FROM  '||i.table_name || ' T '    ||
                                              '         WHERE t.name        = x.name ' ||
                                              '         AND t.type_id     = x.type_id ' ||
                                              '         AND NVL(t.domain_irid,1)=NVL(x.domain_irid,1)) '
                                              ;
          ELSIF i.table_name = 'SDD_GLOBAL_NAME_DOMAINS' THEN
            ins_statements.extend;
            ins_statements(ins_statements.last):= 
                                              ' x WHERE NOT EXISTS ( ' ||
                                              '         SELECT null '     ||
                                              '         FROM  '||i.table_name || ' T '    ||
                                              '         WHERE x.irid = t.irid ) '
                                              ;
            ins_statements.extend;
            ins_statements(ins_statements.last):= 
                                              '     AND NOT EXISTS ( ' ||
                                              '         SELECT null '     ||
                                              '         FROM  '||i.table_name || ' T '    ||
                                              '         WHERE t.name        = x.name ) '
                                              ;
          ELSE
            ins_statements.extend;
            ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null '     ||
                                              ' FROM  '||i.table_name || ' T '    ||
                                              ' WHERE x.irid = t.irid ) ';
          END IF;
      ELSIF i.table_name = 'SDD_CONFIGURATION_MEMBERS' THEN
         rmdbg.trace(utility_name||proc || 'loc r33.3 R mode : SDD_CONFIGURATION_MEMBERS');
         upd_statements.extend;
         upd_statements(upd_statements.last):= '   FROM ' || i.xt_table_name || ' XT ' ||
                                              '     WHERE target.object_ivid = xt.object_ivid  ' ||
                                              '     AND   target.config_ivid = xt.config_ivid )' ;
         upd_statements.extend;
         upd_statements(upd_statements.last):=' WHERE (target.object_ivid '||
                                              '       ,target.config_ivid) '||
                                              '    IN (select xt.object_ivid '||
                                              '              ,xt.config_ivid '|| 
                                              ' FROM  '||i.xt_table_name || ' XT) ';
                                              
          -- and if the row doesn't exist at the target need to complete the insert statement
          ins_statements.extend;
          ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null '     ||
                                              ' FROM  '||i.table_name || ' T '    ||
                                              ' WHERE x.object_ivid = t.object_ivid  ' ||
                                              ' AND   x.config_ivid = t.config_ivid )';
      ELSIF i.table_name = 'SDD_VERSION_ASSOCIATIONS' THEN
          rmdbg.trace(utility_name||proc || 'loc r33.4 R mode : SDD_VERSION_ASSOCIATIONS');
          upd_statements.extend;
          upd_statements(upd_statements.last):= '   FROM ' || i.xt_table_name || ' XT ' ||
                                              '   WHERE target.from_ivid = xt.from_ivid  '     ||
                                              '   AND   target.to_ivid = xt.to_ivid  '  ||
                                              '   AND   target.edge_kind = xt.edge_kind ) ';
          upd_statements.extend;
          upd_statements(upd_statements.last):= ' WHERE (target.from_ivid'||
                                                '       ,target.to_ivid'||
                                                '       ,target.edge_kind)'||
                                                '    IN (SELECT xt.from_ivid'||
                                                '              ,xt.to_ivid'||
                                                '              ,xt.edge_kind'||
                                                '   FROM  '||i.xt_table_name ||' XT) ';
          
          -- and if the row doesn't exist at the target need to complete the insert statement
          ins_statements.extend;
          ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null '     ||
                                              ' FROM  '||i.table_name || ' T '    ||
                                              ' WHERE x.from_ivid = t.from_ivid  '    ||
                                              ' AND   x.to_ivid   = t.to_ivid '      ||
                                              ' AND   x.edge_kind = t.edge_kind ) ';
      ELSE
          rmdbg.trace(utility_name||proc || 'loc r33.6 R mode : '|| i.table_name);
          IF i.ord <= 2 THEN 
                                 -- Versionable PAC objects
                                 -- See cursor 'get_tabs'  to understand '<= 2'
            -- add predicate to filter out excluded objects
            ins_statements.extend;
            ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null '     ||
                                              ' FROM  I$'||i.table_name || ' T '    ||
                                              ' WHERE x.ivid = t.ivid ) ' ||
                                              '   AND NOT EXISTS (SELECT null ' || 
                                              '  FROM xtsys_import_irid_mapping xm ' ||
                                              '  WHERE x.irid = xm.old_irid ' ||
                                              '    AND xm.exclude = ''Y'' ) ';
            IF (no_update = FALSE) THEN
              upd_statements.extend;
              upd_statements(upd_statements.last):= '   FROM ' || i.xt_table_name || ' XT ' ||
                                                    '   WHERE target.ivid = xt.ivid ) '||
                                              '  WHERE EXISTS (SELECT null ' || 
                                              '  FROM  xtsys_import_irid_mapping xm ' ||
                                              '  WHERE target.irid = xm.old_irid ' ||
                                              '    AND xm.exclude IS NULL AND xm.conflict = ''Y'' ) ';
            END IF;
          ELSIF i.ord > 5 THEN 
                           -- Dependendent data/elements
                           -- See cursor 'get_tabs' to understand '> 5'
            -- add predicate to filter out data/elements where parent has been excluded
            ins_statements.extend;
            ins_statements(ins_statements.last):= ' x WHERE NOT EXISTS (SELECT null '     ||
                                              ' FROM  I$'||i.table_name || ' T '    ||
                                              ' WHERE x.ivid = t.ivid ) ' ||
                                              '   AND NOT EXISTS (SELECT null ' ||  
                                              '  FROM xtsys_import_irid_mapping xm ' ||
                                              '  WHERE x.pac_ref = xm.old_irid ' ||
                                              '    AND exclude = ''Y'' ) ';
            IF (no_update = FALSE) THEN
              upd_statements.extend;
              upd_statements(upd_statements.last):= '   FROM ' || i.xt_table_name || ' XT ' ||
                                                    '   WHERE target.ivid = xt.ivid ) '||
                                              '  WHERE EXISTS (SELECT null ' || 
                                              '  FROM  xtsys_import_irid_mapping xm ' ||
                                              '  WHERE target.pac_ref = xm.old_irid ' ||
                                              '    AND xm.exclude IS NULL AND xm.conflict = ''Y'' ) ';
            END IF;
          END IF;

      END IF;
        
      -- end of special cases

      ins_statements.extend;
      IF short_transactions AND i.ord > 5 AND  i.ord > prev_pass THEN
        -- commit after each level (if required) otherwise deferred constraints
        -- may fail
        -- Begin commits when SAC registered types reached i.e. ord > 5
        ins_statements(ins_statements.last):= '/* NEXT COMMIT */';

        IF (no_update = FALSE) THEN
          upd_statements.extend;
          upd_statements(upd_statements.last):= '/* NEXT COMMIT */';
        END IF;
      ELSE
        ins_statements(ins_statements.last):= '/* NEXT BLOCK */';
        IF (no_update = FALSE) THEN
          upd_statements.extend;
          upd_statements(upd_statements.last):= '/* NEXT BLOCK */';
        END IF;
      END IF;
      prev_pass := i.ord;

    END IF;

  END LOOP;

  rmdbg.trace(utility_name||proc || 'loc r35');

  cflct_irid := irid_table();
  cflct_old_ivid := ivid_table();
  cflct_new_ivid := ivid_table();
  IF not empty_target_wa THEN
    -- need to process conflicting objects i.e. the object already exists at target
    csr := dbms_sql.open_cursor;
      -- Identify inbound new version of existing objects (conflict) for extending branch.
      -- The existing version in the context wa (sdd_object_verions) will be autocheckedin
      DBMS_SQL.PARSE(csr,
        ' SELECT r.old_irid  '
      ||',       v.new_ivid   '
      ||',       t.ivid      '
      ||'FROM xtsys_import_irid_mapping   r  '
      ||',    xtsys_import_ivid_mapping   v  '
      ||',    xt_sdd_object_versions      os '
      ||',    sdd_object_versions         t '
      ||'WHERE os.irid = r.old_irid '
      ||'AND   os.ivid = v.old_ivid '
      ||'AND   t.irid = r.old_irid '
      ||'AND   r.conflict = ''Y'' ' 
      ||'AND   r.exclude is null '
      ||'AND  not exists ( SELECT NULL '
      ||'      FROM i$sdd_object_versions ot '
      ||'      WHERE ot.ivid = os.ivid ) '
         , DBMS_SQL.NATIVE);

    DBMS_SQL.DEFINE_COLUMN(csr, 1, i_irid);
    DBMS_SQL.DEFINE_COLUMN(csr, 2, i_new_ivid);
    DBMS_SQL.DEFINE_COLUMN(csr, 3, i_ivid);
    rmdbg.trace(utility_name||proc || 'loc r52  : Parse csr');  
    exe := DBMS_SQL.EXECUTE(csr);

    LOOP
      IF DBMS_SQL.FETCH_ROWS(csr)>0 THEN

        DBMS_SQL.COLUMN_VALUE(csr, 1, i_irid);
        DBMS_SQL.COLUMN_VALUE(csr, 2, i_new_ivid);
        DBMS_SQL.COLUMN_VALUE(csr, 3, i_ivid);
        rmdbg.trace(utility_name||proc || 'loc r52 : LOOP : irid =' || i_irid);  
        -- If the conflicting object is new or not checked-in then check it in
       -- so that no history/information is lost
       IF not jr_version.is_checked_in(i_irid, i_ivid) THEN
          rmdbg.trace(utility_name||proc || 'loc r52 : LOOP : Checkin : irid =' || i_irid);  
          void_ivid := jr_version.check_in(i_irid,i_ivid                                                
                        ,'existing object checked-in automatically to allow '||
                         ' another version to be Imported'
                        , NULL, NULL);
       END IF;

       rmdbg.trace(utility_name||proc || 'loc r35.1 ');
       rmdbg.trace(' irid ' || i_irid);
       rmdbg.trace(' ivid ' || i_ivid);
       rmdbg.trace(' new_ivid ' || i_new_ivid);
       -- Store the results of the current cursor because we will need them
       -- again to complete the 'M'erge after we've inserted the inbound objects
       cflct_irid.extend;
       cflct_old_ivid.extend;
       cflct_new_ivid.extend;
       cflct_irid(cflct_irid.last) := i_irid;
       cflct_old_ivid(cflct_old_ivid.last) := i_ivid;
       cflct_new_ivid(cflct_new_ivid.last) := i_new_ivid;

       -- If extending the branch of an object which is currently a PRIVATE object (CO or new)
       -- then the context setting row for that object must be removed (replaced).
       -- The new entry will be created by DB trigger when the object is inserted.
        execute_sql (
         ' DELETE i$sdd_wa_context '
       ||' WHERE workarea_irid = ' || jr_context.workarea
       ||' AND   object_ivid   = ' || i_ivid
       ||' AND   spec_entry is null '
         );


      ELSE  
        EXIT;
      END IF;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(csr);

  END IF;

  rmdbg.trace(utility_name||proc || 'loc r40 Starting insert of Instance data...');

  IF short_transactions THEN
    ck_util.do_commit (prollback_segment);
  END IF;
  -- Execute each of the SQL statements in turn, note that constraints have
  -- already been switched off earlier in this procedure (switch of FLODER MEMBERS
  -- again, as some API code seems to be re-enabling it!).
  -- Note that this only disables constraint checking, not secondary processing
  jr_context.disable(jr_context.FOLDER_MEMBERS);

  -- Do the inserts before the updates. 
  -- Some updates may depend on new object versions e.g. new Folder versions,
  -- as a result of moving a folder or object
  ind := ins_statements.first;
  WHILE ind IS NOT NULL LOOP
      ix := 1;
      rmdbg.trace(utility_name||proc || 'loc r45ins');
      WHILE ins_statements(ind) NOT IN ( '/* NEXT COMMIT */', '/* NEXT BLOCK */')  LOOP 
         ins_stmt(ix) := ins_statements(ind);
         ix := ix + 1;
         ind:=ins_statements.next(ind);
      END LOOP;
      rmdbg.trace(utility_name||proc || 'loc r47ins');
    	--Set rollback segment...

      IF ins_statements(ind) = '/* NEXT COMMIT */' THEN
        ck_util.do_commit (prollback_segment);
      END IF;

      execute_sql(ins_stmt, ix - 1);
	--Get the next statement
	ind:=ins_statements.next(ind);
  END LOOP;  

  ind := upd_statements.first;
  WHILE ind IS NOT NULL LOOP
      rmdbg.trace('Ind is :'||to_char(ind)||' Statement :'||upd_statements(ind));
      ix := 1;
      rmdbg.trace(utility_name||proc || 'loc r45upd');
      WHILE upd_statements(ind) NOT IN ( '/* NEXT COMMIT */', '/* NEXT BLOCK */')  LOOP 
         ins_stmt(ix) := upd_statements(ind);
         ix := ix + 1;
         ind:=upd_statements.next(ind);
      END LOOP;
      rmdbg.trace(utility_name||proc || 'loc r47upd');
    	--Set rollback segment...

      rmdbg.trace('Ind is :'||to_char(ind));
      IF upd_statements(ind) = '/* NEXT COMMIT */' THEN
        ck_util.do_commit (prollback_segment);
      END IF;

      execute_sql(ins_stmt, ix - 1);
	--Get the next statement
	ind:=upd_statements.next(ind);
  END LOOP;  



  IF short_transactions THEN
    ck_util.do_commit (prollback_segment);
  END IF;
  -- complete  'M'erge operations by creating the version assocation between
  -- an imported object and its predecessor, if one exists.
  IF not empty_target_wa THEN
     ind := cflct_irid.first;
     WHILE ind is NOT NULL LOOP
       rmdbg.trace(utility_name||proc || 'loc r48a : irid ' || cflct_irid(ind));
       execute_sql (
         'insert into i$sdd_version_associations    '
       ||' (irid ,from_ivid ,to_ivid ,edge_kind     '
       ||',created_by ,date_created ,branch_id ,notes) '
       ||' values ('
       ||  cflct_irid(ind)     ||' , '
       ||  cflct_old_ivid(ind) ||' , '
       ||  cflct_new_ivid(ind) ||' , '
       ||  ' ''C'', '
       ||  ' USER,SYSDATE, '
       ||   jr_version.get_current_branch_id(cflct_irid(ind),cflct_old_ivid(ind)) || ' , '
       ||  ' ''Created by IMPORT utility'') '
         );

      execute_sql (
        ' update i$sdd_object_versions '
      ||' set state = ''O'' '
      ||' where ivid = ' || cflct_new_ivid(ind)
        );
      ind := cflct_irid.next(ind);
     END LOOP;
  END IF;
  IF short_transactions THEN
    ck_util.do_commit (prollback_segment);
  END IF;
  

  rmdbg.trace(utility_name||proc || 'loc r50');
  IF short_transactions THEN
    ck_util.do_commit (prollback_segment);
  END IF;

  EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END r_mode_ins_tab_rows;

-------------------------------------------------------------------------------------------
PROCEDURE ins_cmp_wa_tabs (wa_mode Varchar2) IS
  proc   varchar2(100) := 'ins_cmp_wa_tabs ';
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 5');  

  jr_context.disable(jr_context.REPOS_CONSTRAINTS);
  jr_context.disable(jr_context.ACCESS_RIGHTS);
  jr_context.disable(jr_context.FOLDER_MEMBERS);
  jr_context.disable(jr_context.NO_CHECKIN_UPDATE);

  IF guid_mode = 'R' THEN
    r_mode_ins_tab_rows(wa_mode, null);
  ELSE
    ins_tab_rows(wa_mode, null);
  END IF;

  EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END ins_cmp_wa_tabs;

PROCEDURE ins_infrastructure_unr_wa(table_name varchar2) IS
  proc   varchar2(100) := 'ins_infrastructure_unr_wa ';
--    This procedure inserts the rows corresponding to the given Workarea infrastructure
--    table
--    e.g. SDD_CONFIGURATIONS, SDD_RULES (if user defined), I$SDD_RULE_PARAMS,
--         I$SDD_RULE_PARAM_VALUES and SDD_WORKAREA_SPEC_ENTRIES

-- Each statement is assembled in 'ins_stmt' for execution via DBMS_SQL.PARSE
insrt_list     sql_table;  
                         -- temporary assembly list for the 'inserting into columns'
slect_list     sql_table; 
                         -- temporary assembly list for the 'selecting from columns'
-- For very large SQL statements use the VARCHAR2S type of 
-- the SQL_PARSE procedure
ins_stmt       dbms_sql.varchar2s;  

stmt Varchar2(2000);

cflct_irid irid_table;        
                          -- used to process objects with same irid source/target
cflct_old_ivid ivid_table;     
                          -- used to process objects with same ivid source/target
cflct_new_ivid ivid_table;     
                          -- used to process objects with same ivid source/target

csr integer;
ind integer;
ix  integer;
exe integer;

void_ivid number;

-- Not all infrastructure tables have corresponding meta data
-- so get definition from the online dictionary
-- Apply IRID, IVID mapping function to appropriate key and reference columns.
-- The KIND value for CONFIGURATIONS is set to NULL. This is done to convert 
-- 'SYSTEM' (rule based) configurations into ordinary configurations as otherwise
-- rule evaluation at the target may have unpredicatable results.
CURSOR get_cols_onlineDict(infra_tname Varchar2) IS
SELECT decode(rownum, 1, null, ',') || uc.column_name          ins_col
,      decode(rownum, 1, null, ',') || 
         decode (uc.column_name
                ,'KIND', decode(infra_tname
                               , 'SDD_CONFIGURATIONS', 'NULL',uc.column_name)
                ,'IRID', 'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'SEQ',  'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'ENTRY_SEQ', 'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'PREDECESSOR',  'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'WORKAREA_IRID', 'jr_context.workarea'
                ,'RULE_IRID', 'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'BRANCH_ID', 'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'PAC_REF', 'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'MEMBER_OBJECT', 'xtimp_fn_get_mapirid('||uc.column_name||')'
                ,'IVID', 'xtimp_fn_get_mapivid('||uc.column_name||')'
                ,'CONFIG_IVID', 'xtimp_fn_get_mapivid('||uc.column_name||')'
                ,'PARENT_IVID', 'xtimp_fn_get_mapivid('||uc.column_name||')'
                ,uc.column_name)                               sel_col
FROM  all_tab_columns     uc
WHERE uc.table_name = infra_tname   
  AND owner = repos_owner
;
 
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 300 : '||table_name);  
 
  insrt_list := sql_table();
  slect_list := sql_table();
  -- If XT table data exists for the table_name passed in, then insert it into Repository.
  -- Construct ordered lists for the INSERT columns list and Selected values list
  -- Use the online dictionary column view with hard coding for reference properties
  -- for now, due to incomplete definitions for infrastructure tables in RM meta data
  FOR j in get_cols_onlineDict(table_name) LOOP
      insrt_list.extend;
      insrt_list(insrt_list.last) := j.ins_col;
      slect_list.extend;
      slect_list(slect_list.last) := j.sel_col;
  END LOOP;

  IF insrt_list.FIRST IS NOT NULL THEN
    ix := 1;
    --Next block of code assembles the complete INSERT statement 
    --prior to dynamic SQL execution
    IF table_name in ('I$SDD_RULE_PARAMS', 'I$SDD_RULE_PARAM_VALUES') THEN
      ins_stmt(ix) := 'INSERT INTO '  || table_name ||' (';
    ELSE
      ins_stmt(ix) := 'INSERT INTO I$'|| table_name ||' (';
    END IF;
    ix := ix+1;

    FOR k IN insrt_list.first .. insrt_list.last LOOP
      ins_stmt(ix) := insrt_list(k);
      ix := ix+1;
    END LOOP;
    ins_stmt(ix) := ' ) SELECT ';
    ix := ix+1;
    FOR k IN insrt_list.first .. insrt_list.last LOOP
      ins_stmt(ix) := slect_list(k);
      ix := ix+1;
    END LOOP;
    ins_stmt(ix) := 'FROM XT_' ||table_name;

    IF table_name= 'SDD_WORKAREA_SPEC_ENTRIES' AND guid_mode = 'R' THEN
      ix := ix+1;
      ins_stmt(ix) := 
        ' xse WHERE NOT EXISTS (SELECT NULL '
      ||' FROM sdd_workarea_spec_entries tse'
      ||' WHERE tse.seq = xse.seq ) ';
    ELSIF table_name= 'SDD_CONFIGURATIONS' AND guid_mode = 'R' THEN
      ix := ix+1;
      ins_stmt(ix) := 
        ' x WHERE NOT EXISTS (SELECT null '     ||
        ' FROM  i$sdd_configurations t '    ||
        ' WHERE x.ivid = t.ivid ) ';
    ELSIF table_name = 'SDD_RULES' THEN
      rmdbg.trace(utility_name||proc || 'loc 330');
      ix := ix+1;
      ins_stmt(ix) := 
        ' xr WHERE NOT EXISTS (SELECT NULL FROM sdd_rules lr WHERE lr.name=xr.name) ';
    ELSIF table_name = 'I$SDD_RULE_PARAMS' THEN
      rmdbg.trace(utility_name||proc || 'loc 331');
      ix := ix+1;
      ins_stmt(ix) := 
        ' xp WHERE NOT EXISTS (SELECT NULL '
      ||' FROM i$sdd_rules lr, xt_sdd_rules xr, i$sdd_rule_params lp '
      ||' WHERE lr.name=xr.name and lp.name=xp.name) ';
    ELSIF table_name = 'I$SDD_RULE_PARAM_VALUES' AND guid_mode = 'R' THEN
      rmdbg.trace(utility_name||proc || 'loc 331');
      ix := ix+1;
      ins_stmt(ix) := 
        ' xp WHERE NOT EXISTS (SELECT NULL '
      ||' FROM  xt_i$sdd_rule_param_values xr '
      ||' WHERE xr.entry_seq = xp.entry_seq AND xr.rule_irid = xp.rule_irid ) ';
    ELSIF table_name = 'SDD_BRANCHES' THEN
      rmdbg.trace(utility_name||proc || 'loc 330');
      ix := ix+1;
      ins_stmt(ix) := 
        ' xb WHERE NOT EXISTS (SELECT NULL FROM sdd_branches lb WHERE lb.name=xb.name) ';
 
      -- set the correct value for IRID mapping based on equivalent branch irid in the target
      Begin
        SELECT
          ' UPDATE XTSYS_IMPORT_IRID_MAPPING '
        ||' SET new_irid = ( SELECT lb.branch_id FROM sdd_branches lb , xt_sdd_branches xb '
        ||'                  WHERE  lb.name = xb.name AND old_irid = xb.branch_id) '
        ||' WHERE EXISTS ( '
        ||'    SELECT null FROM sdd_branches lb , xt_sdd_branches xb '
        ||'    WHERE   lb.name = xb.name AND old_irid = xb.branch_id) '  
         stmt1
        INTO stmt
        FROM   xtsys_tabs_exported 
        WHERE  table_name = 'SDD_BRANCHES';

        csr := DBMS_SQL.OPEN_CURSOR;
        execute_sql(stmt);
        Exception
          WHEN NO_DATA_FOUND THEN NULL;
      End;
    END IF;       
    rmdbg.trace(utility_name||proc || 'loc 335');

    rmdbg.trace(utility_name||proc || 'loc 400');

    --Set rollback segment...
    IF short_transactions THEN
       ck_util.do_commit (prollback_segment);
    END IF;
    --Execute the statement
    execute_sql(ins_stmt,ix);
  
    rmdbg.trace(utility_name||proc || 'loc 480');
    IF short_transactions THEN
      ck_util.do_commit (prollback_segment);
    END IF;
  END IF;

  EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END ins_infrastructure_unr_wa;


PROCEDURE preins_unr_wa(source_wa_irid Number) IS
  proc   varchar2(100) := 'preins_unr_wa ';
         -- 1) Need to insert XT_SDD_CONFIGURATIONS and any additional RULES in 
         --    XT_SDD_RULES, before XT_SDD_WORKAREA_SPEC_ENTRIES
         --    updated and loaded.
         -- 2) Need to update specific References in XT_SDD_WORKAREA_SPEC_ENTRIES 
         --         CONFIG_IVID
         --         RULE_IRID
         --         WORKAREA_IRID
         -- 3) Need to update specific References in XT_I$SDD_RULE_PARAM_VALUES
         --         CONFIG_IVID
         --         RULE_IRID

 table_name sql_table := sql_table('SDD_BRANCHES', 'SDD_CONFIGURATIONS', 'SDD_RULES', 
                     'I$SDD_RULE_PARAMS', 'I$SDD_RULE_PARAM_VALUES', 'SDD_WORKAREA_SPEC_ENTRIES');


         stmt1 VARCHAR2(2000);
         stmt2 VARCHAR2(2000);
         stmt3 VARCHAR2(2000);
         csr NUMBER;
         exe number;
         ind number;
         sql_statements large_sql_table;

rules_to_process Boolean := false;

BEGIN

  -- Need to check that none of the inbound configurations have name conflicts with target
  conflict_resolution('CONFIGURATION');

  -- Need to update IRID  mappings for the new Workarea.
  Begin
   select ws.irid
     into new_workarea_irid
   from I$sdd_workareas ws
   where ws.irid = jr_context.workarea;

   -- If Import is rerun with existing XT tables may need to remove an
   -- entry for WA's IRID from previous execution before entering the 
   -- new IRID. 
   execute_sql(
            'DELETE FROM XTSYS_IMPORT_IRID_MAPPING '
          ||' WHERE old_irid IN (SELECT old.irid '
          ||' FROM xt_sdd_workareas old ) ');
   execute_sql(
            'INSERT INTO XTSYS_IMPORT_IRID_MAPPING '
          ||' SELECT old.irid, '||new_workarea_irid||', NULL, NULL, NULL'
          ||' FROM xt_sdd_workareas old ');
  End;

  jr_context.disable(jr_context.ACCESS_RIGHTS);
  jr_context.disable(jr_context.OBJECT_VERSIONS);
  jr_context.disable(jr_context.FOLDER_MEMBERS);
  jr_context.disable(jr_context.REPOS_CONSTRAINTS);
  jr_context.disable(jr_context.NO_CHECKIN_UPDATE);

  -- Load CONFIGURATIONS 
  IF table_exists('XT_SDD_CONFIGURATIONS') THEN
    ins_infrastructure_unr_wa ('SDD_CONFIGURATIONS');
  END IF;

  -- set the correct value for IRID mapping  based on the RULE irid in the target
  Begin
    SELECT
       ' UPDATE XTSYS_IMPORT_IRID_MAPPING '
     ||' SET new_irid = ( SELECT lt.irid FROM i$sdd_rules lt , xt_sdd_rules xt'
     ||'                  WHERE  lt.name = xt.name AND old_irid = xt.irid) '
     ||' WHERE EXISTS ( '
     ||'    SELECT null FROM i$sdd_rules lt , xt_sdd_rules xt '
     ||'    WHERE  lt.name = xt.name AND old_irid = xt.irid ) '
  
     stmt1
    INTO stmt1
    FROM   xtsys_tabs_exported 
    WHERE  table_name = 'SDD_RULES';

    execute_sql(stmt1);
    -- remember this for later
    rules_to_process := true;
    Exception
     WHEN NO_DATA_FOUND THEN NULL;
  End;
  IF table_exists ('XT_SDD_WORKAREA_SPEC_ENTRIES') THEN
    ins_infrastructure_unr_wa ('SDD_WORKAREA_SPEC_ENTRIES');
  END IF;
  IF rules_to_process THEN 
    -- Load additional User defined Rules
    ins_infrastructure_unr_wa ('SDD_RULES');
    -- Load additional User defined rule parameters
    ins_infrastructure_unr_wa ('I$SDD_RULE_PARAMS');
    -- Load RULE parameter values
    ins_infrastructure_unr_wa ('I$SDD_RULE_PARAM_VALUES');

  END IF;

  -- Load BRANCHES
  IF table_exists('XT_SDD_BRANCHES') THEN
    ins_infrastructure_unr_wa ('SDD_BRANCHES');
  END IF;

  -- Root folder conflict resolution should ideally occur against a recompiled
  -- Workarea, however recompilation would produce errors if inclusion rules
  -- referred to objects which had not yet been loaded.
  conflict_resolution('FOLDER');

   EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END preins_unr_wa;


PROCEDURE ins_unr_wa_tabs IS
--    Load data resulting from an Export of unresolved WA or specific configurations.
--
--    Workarea context is not a pre-requisite for this mode as rows are inserted via
--    base 'I$' tables not version resolved views. Therefore this procedure may also
--    be used to import specific Configurations.
--  NOTES
--    Referential and Check constraint validation is disabled (data is assumed valid).
  proc   varchar2(100) := 'ins_unr_wa_tabs ';
 
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 5');  

  IF guid_mode = 'R' THEN
    r_mode_ins_tab_rows('U', 'I$');
  ELSE
    ins_tab_rows('U', 'I$');
  END IF;


  EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END ins_unr_wa_tabs;


FUNCTION cfg_set_and_check(cfg_name Varchar2, cfg_ivid Number) RETURN BOOLEAN IS
  proc           varchar2(100) := 'cfg_set_and_check ';

dummy i$sdd_configurations.name%Type;
CURSOR check_cfg Is
 SELECT name
 FROM   i$sdd_configurations
 WHERE  name = cfg_name
 AND    ROWNUM = 1;

csr  Number;
exe  Number;

BEGIN
  rmdbg.trace(utility_name||proc || 'loc 810b : Checking configuration: ' || cfg_name);  
  OPEN check_cfg;
  FETCH check_cfg INTO dummy;
  CLOSE check_cfg;
  IF dummy IS NOT NULL THEN
    conflict_resolution('CONFIGURATION');
  ELSE 
    execute_sql('UPDATE xt_sdd_configurations  SET name = ''' || cfg_name||''''||
                ' WHERE ivid = '||cfg_ivid);
    execute_sql('UPDATE xt_sdd_object_versions SET name = ''' || cfg_name||''''||
                ' WHERE ivid = '||cfg_ivid);
  END IF;

  -- If there is a name conflict an exception would have been raised by conflict_resolution
  RETURN TRUE;

END cfg_set_and_check;

PROCEDURE preins_cfg IS
  proc   varchar2(100) := 'preins_cfg ';

 wa_name sdd_workareas.name%Type;

BEGIN
  rmdbg.trace(utility_name||proc || ' loc 1');	  

  SELECT name
  INTO   wa_name
  FROM   i$sdd_workareas   w
  ,      sdw_access_rights r
  WHERE  r.grantor_reference = USER
    AND  r.object_reference  = w.irid
    AND rownum = 1;
  jr_context.set_workarea(wa_name); 

  jr_context.disable(jr_context.ACCESS_RIGHTS);
  jr_context.disable(jr_context.OBJECT_VERSIONS);
  jr_context.disable(jr_context.FOLDER_MEMBERS);
  jr_context.disable(jr_context.REPOS_CONSTRAINTS);
  jr_context.disable(jr_context.NO_CHECKIN_UPDATE);

  -- Load CONFIGURATIONS 
  ins_infrastructure_unr_wa ('SDD_CONFIGURATIONS');


  EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END preins_cfg;


PROCEDURE ins_cfg_tabs IS
--   Import the data associated with the export of a Configuration.
--  NOTES
--   Currently there is no additional or special processing different to
--   that of importing unresolved workarea data.
  proc   varchar2(100) := 'ins_cfg_tabs ';

dummy number;
BEGIN

  ins_unr_wa_tabs;

  EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END ins_cfg_tabs;

PROCEDURE preins_fldr(fldr_irid Number, root_flag Varchar2, tgt_fldr Number) IS
  proc   varchar2(100) := 'preins_fldr ';      

BEGIN
  IF root_flag != 'Y' THEN
    -- a non root folder may become rooted during restore and
    -- this needs to be set before access rights are created 
    -- near the end of restore processing
    
    -- Bug 2241347 don't set root flag if folder exists in target
    -- only in merge and replicate modes
    -- If folder exists in diverge mode then we do want to set root_flag
    -- because we will be creating a totally new folder.
    IF guid_mode in ('R', 'M') THEN
      rmdbg.trace(utility_name||proc || ' loc 1 : Folder irid = ' || to_char(fldr_irid));	  
      execute_sql (
        'UPDATE xt_sdd_folders '        ||
        'SET    root_flag = ''Y'' '     ||
        'WHERE  irid = ' ||fldr_irid    ||
        ' AND NOT EXISTS (select NULL ' ||
        ' from i$sdd_folders f'         ||
        ' where f.irid = ' || fldr_irid ||')');
    ELSE    
      rmdbg.trace(utility_name||proc || ' loc 2 : Folder irid = ' || to_char(fldr_irid));	  
      execute_sql (
        'UPDATE xt_sdd_folders '        ||
        'SET    root_flag = ''Y'' '     ||
        'WHERE  irid = ' ||fldr_irid );
    END IF;
  END IF;
  IF tgt_fldr IS NULL THEN
    -- only need to do conflict resolution for root folders
    -- so its not necessary if reparenting/moving
    conflict_resolution('FOLDER');
  END IF;

EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END preins_fldr;


PROCEDURE ins_fldr_tabs (wa_mode Varchar2) IS
--  PURPOSE
--    Insert FOLDER data and folder contents into the context WA.
--    In 'FOLDER' mode, the data was exported in the context of a
--    Workarea, therefore the data is imported with Workarea context set.
  proc   varchar2(100) := 'ins_fldr_tabs ';

BEGIN
  -- load the Workarea for which the context has just been set : 
  ins_cmp_wa_tabs(wa_mode);

  rmdbg.trace(utility_name||proc || 'loc 89');	  

  EXCEPTION  
    WHEN OTHERS THEN
      ck_util.ora_error (utility_name,proc,sql%rowcount);
      rmmes.post ('RME',unhdl_orcl_err,sqlerrm);
      raise;

END ins_fldr_tabs;

PROCEDURE ins_access_rights(object_type Varchar2, wa_mode Varchar2) IS
  proc   varchar2(100) := 'Create Access Rights ';

  csr integer;
  exe integer;

  i_irid          Number;
  i_member_object Number;

  stmt_chunk      dbms_sql.varchar2s;  

  dummy Number;
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 60'); 
  csr := dbms_sql.open_cursor;

   stmt_chunk(1) := 
      ' insert into SDW_ACCESS_RIGHTS ( '
    ||'      IRID '
    ||'     ,IVID '
    ||'     ,GRANTEE_REFERENCE '
    ||'     ,GRANTOR_REFERENCE '
    ||'     ,OBJECT_REFERENCE  '
    ||'     ,OBJECT_TYPE       '
    ||'     ,ACCESS_PRIVS      '
    ||'     ) ';

   stmt_chunk(2) :=
      '  SELECT jr_util.get_new_irid '
    ||'    ,1        '
    ||'    ,USER '
    ||'    ,USER     '
    ||'    ,new_irid '
    ||'    ,''CEL''  '
    ||'    ,'||repos_owner||'.';

   IF jr_sys_privs.is_versioned_repos THEN
     stmt_chunk(3) := 'sdd_acc_priv_list(''Y'' ,''Y'' ,''Y'',''Y'',''Y'',''Y'',''Y'' ,''Y'') ';
   ELSE
     --In non-versioned repositories, only a 
     --sub-set of the full access rights can be granted
     stmt_chunk(3) := 'sdd_acc_priv_list(''Y'' ,''Y'' ,''Y'',''Y'',''Y'',''N'',''N'' ,''N'') ';
   END IF;

   stmt_chunk(4) :=
      ' FROM xtsys_import_irid_mapping '
    ||' WHERE new_irid IN ( '
    ||'  SELECT irid              '
    ||'  FROM  i$sdd_folders f            '
    ||'  ,     xtsys_import_irid_mapping  '
    ||'  WHERE new_irid = f.irid  ';
   stmt_chunk(5) :=
      '    AND f.irid NOT IN  '
    ||'        (SELECT xir.new_irid FROM   xtsys_import_irid_mapping xir '
    ||'         WHERE xir.exclude = ''Y'' ) '
    ||'    AND f.irid NOT IN '
    ||'       ( SELECT object_reference FROM sdw_access_rights ) ) ';

  execute_sql(stmt_chunk, 5);

  rmdbg.trace(utility_name||proc || 'loc 61'); 
  -- amend bits 2,4 and 5 of the insert statement to create access rights for existing FOLDERS
  -- i.e. use the existing grantor_reference
  stmt_chunk(2) :=
      ' SELECT jr_util.get_new_irid '
    ||' ,1        '
    ||' ,ar.grantor_reference '
    ||' ,USER     '
    ||' ,xm.new_irid '
    ||' ,''CEL''  '
    ||' ,'||repos_owner||'.';
  stmt_chunk(4) :=
      ' FROM xtsys_import_irid_mapping xm '
    ||' ,  (SELECT DISTINCT grantor_reference, object_reference '
    ||'     FROM   sdw_access_rights ) ar '         
    ||' WHERE xm.new_irid = ar.object_reference ';
  stmt_chunk(5) :=
      ' AND xm.new_irid NOT IN  '
    ||'      (SELECT xir.new_irid FROM   xtsys_import_irid_mapping xir '
    ||'       WHERE xir.exclude = ''Y'' ) '
    ||' AND xm.new_irid NOT IN '
    ||'      (SELECT object_reference FROM sdw_access_rights  '
    ||'       WHERE grantee_reference = USER ) ';

  execute_sql(stmt_chunk, 5);

  rmdbg.trace(utility_name||proc || 'loc 62'); 
  -- amend bits 2 and 5 (spliting 5 due to new size) 
  --to create select access rights for workarea owner on folders
  stmt_chunk(2) :=
      ' SELECT jr_util.get_new_irid '
    ||' ,1        '
    ||' ,(select grantee_reference from sdw_access_rights '
    ||'   where grantor_reference = grantee_reference '
    ||'   and object_reference = jr_context.workarea()) '
    ||' ,USER     '
    ||' ,xm.new_irid '
    ||' ,''CEL''  '
    ||' ,'||repos_owner||'.';
    
  stmt_chunk(3) := 'sdd_acc_priv_list(''N'' ,''N'' ,''N'',''Y'',''N'',''N'',''N'' ,''N'') ';
    
  stmt_chunk(5) :=
      ' AND xm.new_irid NOT IN  '
    ||'      (SELECT xir.new_irid FROM   xtsys_import_irid_mapping xir '
    ||'       WHERE xir.exclude = ''Y'' ) ';
   
  -- Bug 2943856 Query didn't consider that access rights may already exist on container
  -- for wa owner but granted by someone else.
  stmt_chunk(6) :=  
      ' AND xm.new_irid NOT IN '
          ||'(SELECT object_reference FROM sdw_access_rights'
          ||' WHERE grantee_reference = (select grantee_reference'
                                     ||' from sdw_access_rights'
                                     ||' where grantor_reference = grantee_reference'
                                     ||' and object_reference = jr_context.workarea()) )';
    

  execute_sql(stmt_chunk, 6);   
  
  rmdbg.trace(utility_name||proc || 'loc 63'); 
  csr := dbms_sql.open_cursor;
  DBMS_SQL.PARSE(csr,
      ' SELECT irid  '
    ||' FROM i$sdd_configurations  c  '
    ||' WHERE EXISTS  '
    ||'       (SELECT null FROM xtsys_import_irid_mapping  '
    ||'        WHERE new_irid = c.irid ) '
    ||'   AND NOT EXISTS  '
    ||'       (SELECT NULL FROM   xtsys_import_irid_mapping xir '
    ||'        WHERE c.irid = xir.new_irid  '
    ||'          AND  xir.exclude = ''Y'' ) '
    ||'   AND NOT EXISTS '
    ||'       (SELECT null FROM sdw_access_rights '
    ||'        WHERE grantee_reference = USER and object_reference = c.irid) '
         , DBMS_SQL.NATIVE);

  DBMS_SQL.DEFINE_COLUMN(csr, 1, i_irid);
  rmdbg.trace(utility_name||proc || 'loc 51  : Parse csr');  
  exe := DBMS_SQL.EXECUTE(csr);

  LOOP
    IF DBMS_SQL.FETCH_ROWS(csr)>0 THEN

      DBMS_SQL.COLUMN_VALUE(csr, 1, i_irid);
    rmdbg.trace(utility_name||proc || 'loc 63, CFG Irid :'||i_irid );  

    dummy := jr_acc_rights.ins(i_irid, 'CFG', jr_acc_rights.full_access_rights);

    ELSE  
      EXIT;
    END IF;
  END LOOP;

END ins_access_rights;

Procedure apply_wastebasket_entries IS
  proc varchar2(100) := 'Apply wastebasket entries';
  csr integer;
  exe integer;

  repos_table_name Varchar2(32);

BEGIN
  rmdbg.trace(utility_name||proc || 'loc w60'); 
  IF table_exists('XT_SDD_WASTEBASKET') THEN
    csr := dbms_sql.open_cursor;
    DBMS_SQL.PARSE(csr,
      ' SELECT DISTINCT t.repos_table_name     '
    ||'  FROM  xt_sdd_wastebasket  w '
    ||'  ,     rm_sql_tables        t '
    ||'  WHERE w.table_irid = t.irid '
         , DBMS_SQL.NATIVE);

    DBMS_SQL.DEFINE_COLUMN(csr, 1, repos_table_name, 32);
    rmdbg.trace(utility_name||proc || 'loc w51  : Parse csr');  
    exe := DBMS_SQL.EXECUTE(csr);

    LOOP
      IF DBMS_SQL.FETCH_ROWS(csr)>0 THEN

        DBMS_SQL.COLUMN_VALUE(csr, 1, repos_table_name);
        rmdbg.trace(utility_name||proc || 'loc w61, Deleting from '||repos_table_name);  

        execute_sql (
          ' DELETE FROM ' ||repos_table_name ||' t '
        ||' WHERE EXISTS ( SELECT NULL '
        ||'   FROM xt_sdd_wastebasket w '
        ||'   WHERE w.ivid = t.ivid ) ' );

      ELSE  
        EXIT;
      END IF;
    END LOOP;
  END IF;
  
  EXCEPTION
  WHEN OTHERS THEN
    rmdbg.trace(' Deletes using source Wastebasket entries failed ');
    rmdbg.trace(sql%rowcount || ' '||sqlerrm );

END apply_wastebasket_entries ;

Procedure set_ownership IS
  proc varchar2(100) := 'Set Folder Ownership ';
  csr integer;
  exe integer;

  object_irid number;
  folder_irid number;

BEGIN
  rmdbg.trace(utility_name||proc || 'loc 901'); 

  csr := dbms_sql.open_cursor;
  DBMS_SQL.PARSE(csr,
      ' SELECT o.new_irid  '
    ||' ,      f.new_irid  '
    ||' FROM xtsys_import_irid_mapping   o   '
    ||' ,    xtsys_import_irid_mapping   f   '
    ||' ,    xt_sdd_folder_members       xfm '
    ||' WHERE xfm.member_object = o.old_irid '
    ||' AND   xfm.folder_reference = f.old_irid '
    ||' AND NOT EXISTS (select null from xt_sdd_folder_members xfm1 '
    ||'                 where xfm1.member_object = xfm.member_object '
    ||'                 and xfm1.ownership_flag = ''Y'') '
       , DBMS_SQL.NATIVE);


  DBMS_SQL.DEFINE_COLUMN(csr, 1, object_irid);
  DBMS_SQL.DEFINE_COLUMN(csr, 2, folder_irid);
  rmdbg.trace(utility_name||proc || 'loc 902  : Parse csr:'||csr);  
  exe := DBMS_SQL.EXECUTE(csr);

  LOOP
    IF DBMS_SQL.FETCH_ROWS(csr)>0 THEN

      DBMS_SQL.COLUMN_VALUE(csr, 1, object_irid);
      DBMS_SQL.COLUMN_VALUE(csr, 2, folder_irid);

      rmdbg.trace(utility_name||proc || 'loc 903, Setting ownership of object '||
                  to_char(object_irid)||' to folder '||to_char(folder_irid));  

      execute_sql (
          ' UPDATE i$sdd_folder_members  fm '
        ||' SET    fm.ownership_flag = ''Y'' '
        ||' WHERE  fm.folder_reference = '|| folder_irid
        ||' AND    fm.member_object    = '|| object_irid
        ||' AND NOT EXISTS ( select null from i$sdd_folder_members   fm1 '
        ||'                  where fm.member_object = fm1.member_object  '
        ||'                  and fm1.ownership_flag = ''Y'') ' );


      rmdbg.trace(utility_name||proc || 'loc 904 ');
    ELSE  
      rmdbg.trace(utility_name||proc || 'loc 905 ');
      EXIT;
    END IF;
  END LOOP;
  
  dbms_sql.close_cursor(csr);
  
  EXCEPTION
    WHEN OTHERS THEN
      rmdbg.trace(utility_name||proc || 'loc 906 ');
      rmdbg.trace(' Setting ownership flag failed ');
      rmdbg.trace(sql%rowcount || ' '||sqlerrm );
      dbms_sql.close_cursor(csr);

END set_ownership ;

Procedure dangling_refs IS
  proc   varchar2(100) := 'Fix dangling refs ';
  -- Once all the instance data has been loaded, a check is made for dangling
  -- references in the context workarea. These are removed if the directive 
  -- jr_context.is_enabled(jr_context.NO_CHECKIN_UPDATE) is FALSE
  -- The following are the return variables for 
  --   jr_chk_refs.check_wa_refs:
  el_ivid      jr_check_refs.num_list_typ%type;
  ref_irid     jr_check_refs.num_list_typ%type;
  el_par_ivid  jr_check_refs.num_list_typ%type;
  ref_par_irid jr_check_refs.num_list_typ%type;
  el_type      jr_check_refs.str_list_typ%type;
  ref_type     jr_check_refs.str_list_typ%type;
  ref_prop     jr_check_refs.str_list_typ%type;
  mandatory    jr_check_refs.str_list_typ%type;
  reason       jr_check_refs.str_list_typ%type;
  elivid  number;
  eltype  varchar2(30);
  mand    varchar2(10);  
  fixes   number := 0;

BEGIN
  rmdbg.trace(utility_name||proc || ' loc 6060 ');
  -- check for and resolve dangling references in the context Workarea
  jr_check_refs.check_wa_refs(jr_context.workarea
                       ,el_ivid      
                       ,ref_irid     
                       ,el_par_ivid  
                       ,ref_par_irid 
                       ,el_type      
                       ,ref_type     
                       ,ref_prop     
                       ,mandatory    
                       ,reason);

  -- list all the invalid dangling refs to the diagnostic trace file
  -- i.e. reason = 'REPOSITORY'
  rmdbg.trace(utility_name||proc || ' loc 6061 ');
  IF not jr_context.is_enabled(jr_context.NO_CHECKIN_UPDATE) THEN
    rmdbg.trace ('jr_context.NO_CHECKIN_UPDATE is FALSE, any invalid dangling refs are fixed');
  ELSE
    rmdbg.trace ('jr_context.NO_CHECKIN_UPDATE is TRUE, invalid dangling refs are not fixed');
  END IF;

  IF el_ivid.FIRST IS NOT NULL THEN
    rmdbg.trace ('.......................................');
    rmdbg.trace ('Report of dangling references fixed');
    rmdbg.trace (rpad('Object Ivid',40) || rpad(' Object Type',30)||' Mandatory');
    FOR ind in el_ivid.FIRST .. el_ivid.LAST LOOP
     IF reason(ind) = 'REPOSITORY' THEN
        fixes := fixes + 1;
        rmdbg.trace (rpad(el_ivid(ind),40) ||' ' ||rpad(el_type(ind),30) || ' ' ||mandatory(ind));
      END IF;
    END LOOP;
    rmdbg.trace ('Total dangling references fixed = '|| fixes);
    rmdbg.trace ('.......................................');
  ELSE
    rmdbg.trace ('No external or dangling references detected');
  END IF;
 
END dangling_refs;

BEGIN 
     -- ckimp70 processing

  rmdbg.trace(utility_name||proc || 'is started.');
  ck_util.register(utility_name,'Init');
  rmdbg.trace(proc || ' Argument list is :');
  rmdbg.trace('o RBS : '||prollback_segment);
  rmdbg.trace('o Tablespace name : '||tablespace_name ); 
  rmdbg.trace('o Guid Mode : '||guid_mode ); 
  IF short_transactions THEN  
    rmdbg.trace('o Execute with short transactons / frequent commits');  
  END IF;
  IF fix_dangling_refs THEN
    -- output validation mode to trace file, the actual business is done later
    rmdbg.trace('o Automatically fix dangling refs ' );  
  END IF;

  check_repos_version;

  IF guid_mode NOT IN ('D', 'M', 'R') THEN 
    raise parameters_invalid ;
  END IF;

  -- for mapping functions
  create_support_functions ;
  -- and indexes for XT.. tables 
  IF tablespace_name IS NOT NULL THEN
    create_support_indexes (' TABLESPACE ' || tablespace_name);
  ELSE
    create_support_indexes (NULL);
  END IF;

  -- unless short_transactions are requested this will be the final COMMIT and
  -- set transaction operation by the Import utility
  ck_util.do_commit (prollback_segment);

  -- The parser takes out DDL locks which means that ANALYZE, TRUNCATE etc.
  -- result in DEADLOCK error. Need to make all SQL statements dynamic.  
  --  compute_stats; 
  -- compute stats for CBO on XTSYS tables


  rmdbg.trace(utility_name||proc || 'loc 81');  

  set_guid_ivid_mappings(guid_mode);
  set_default_properties;

  FOR i IN obj_to_import LOOP
    -- 20-May-99 : This loop is set to execute one cycle (ROWNUM = 1)
    --             A future enhancement will allow partitioned loads.

    -- Bug 1987869 set all branches to MAIN
    IF (guid_mode = 'D' AND i.type = 'C') THEN
      set_main_branch;
    END IF;

    -- Reset rollback segment 
    IF short_transactions THEN
      ck_util.do_commit (prollback_segment);
    END IF;

    IF i.extract_reg_metad = 'Y' THEN
      -- Before loading any instance data need to ensure that the Repository
      -- contains all the meta data and schema objects to support it.
      -- For export/import between Designer/Java++ Repositories this directive
      -- should not normally be set.
      process_meta_data;
    END IF;

    rmdbg.trace(utility_name||proc || 'loc 85');  
     
    IF ( i.type = 'W' AND i.wa_mode in ('C', 'R')) THEN  
      
      -- B1469456 - importing a wa allows duplication of app systems
      preins_cmp_wa; 
          
      -- source (export) was 'C'hecked out objects or a version 'R'esolved view 
      -- of a Work Area 
      empty_target_wa := wa_set_and_check(i.default_import_name);
      IF empty_target_wa THEN
         jr_workarea.define_spec(jr_context.workarea, 'LATEST(MAIN)');
      END IF;
      
      ck_util.register(utility_name,'C or R mode Workarea: ' || to_char(i.irid));  
      rmdbg.trace(utility_name||proc || 'loc 86');  

      -- Bug 3069020, check user has version access rights on the workarea
      IF guid_mode in ('R', 'M') AND NOT jr_acc_rights.has_access(jr_context.workarea, USER,'VER') then
        rmdbg.trace(utility_name||proc || 'loc 86a : No access rights to version workarea ');  
        RAISE no_version_access_rights;
      end if;
     
      rmdbg.trace(utility_name||proc || 'loc 86.1');  
      IF guid_mode in ('R', 'M') AND non_mergeable_objects THEN
        rmdbg.trace(utility_name||proc || 'loc 86b : Objects cannot be merged ');  
        RAISE non_mergeable;
      END IF;

	-- load the Workarea for which the context has just been set : 
      ins_cmp_wa_tabs(i.wa_mode);

      
    ELSIF ( i.type = 'W' AND i.wa_mode = 'U') THEN   
                                                  -- Unresolved Work Area 
      ck_util.register(utility_name,'Unresolved Work Area: ' || to_char(i.irid));  
      rmdbg.trace(utility_name||proc || 'loc 90');  
      empty_target_wa := wa_set_and_check(i.default_import_name);
      -- check for conflicting configuration names and rules
      preins_unr_wa(i.irid); 
      -- now insert all the instance data
      ins_unr_wa_tabs;
      rmdbg.trace(utility_name||proc || 'loc 91');  
      
    ELSIF ( i.type = 'C' ) THEN    
                               -- Configuration 
      ck_util.register(utility_name,'Configuration: ' || to_char(i.irid)||' : '||to_char(i.ivid));  
      rmdbg.trace(utility_name||proc || 'loc 92 :'||i.default_import_name||' '||i.ivid); 
      -- Import is not allowed into an existing Configuration
      IF cfg_set_and_check(i.default_import_name, i.ivid) THEN
        rmdbg.trace(utility_name||proc || 'loc 92a'); 
        preins_cfg;
        rmdbg.trace(utility_name||proc || 'loc 92b'); 
        ins_cfg_tabs;
        rmdbg.trace(utility_name||proc || 'loc 92c'); 
      ELSE
        raise duplicate_config;
      END IF;
    ELSE                          
      -- i.Type is Folder 
      ck_util.register(utility_name,'Folder: ' || to_char(i.irid)||' : '||to_char(i.ivid));
      rmdbg.trace(utility_name||proc || 'loc 94'); 
      
      IF jr_context.workarea IS NOT NULL THEN
      
        SELECT name INTO dummy FROM sdd_workareas WHERE irid = jr_context.workarea;
        empty_target_wa := wa_set_and_check(dummy);
        
        -- B3544821 : Only do access rights check when container already exists in target database
        DECLARE
          folderExists number;
        BEGIN
          select count(*) into folderExists from i$sdd_folders where irid = i.irid;
          
          if ( folderExists > 0 ) then
            -- Bug 3069020, check user has version access rights on the folder
            if (guid_mode in ('R', 'M') AND NOT jr_acc_rights.has_access(i.irid, USER,'VER')) then
              rmdbg.trace(utility_name||proc || 'loc 94a : No access rights to version folder ');  
              RAISE no_version_access_rights;
            end if;
          end if;
        END;
  
        -- initialize and check for conflicts
        preins_fldr(i.irid, i.root_flag, i.mkmem_fldr_irid); 
        
        IF guid_mode in ('R', 'M') AND non_mergeable_objects THEN
          RAISE non_mergeable;
        END IF;

        -- now insert all the instance data
        ins_fldr_tabs(i.wa_mode);
        rmdbg.trace(utility_name||proc || 'loc 95');
      ELSE
        RAISE context_workarea_expected;
      END IF;
      
    END IF;
    rmdbg.trace(utility_name||proc || 'loc 98'); 
	
    IF table_exists('XT_SDD_FOLDER_MEMBERS') THEN 
      IF guid_mode IN ('M', 'R') THEN
         execute_sql (
           ' delete '
  	 ||' from  i$sdd_folder_members mt '
	 ||' where mt.ownership_flag = ''Y''	'
	 ||'  and mt.folder_reference not in ( '
	 ||'   select xvm.folder_reference '
	 ||'   from    '
	 ||'          xt_sdd_folder_members xvm )'
	 ||'  and mt.member_object in (	  '
	 ||'   select xo.new_irid			   '
	 ||'   from   xt_sdd_folder_members xm '
         ||'   ,      xtsys_import_irid_mapping xo '
         ||'   where  xm.member_object = xo.old_irid '
         ||'     and  xm.ownership_flag = ''Y'') '
	 );
      END IF;

      -- Bug 2061473
      -- If a referenced object is imported without the owning folder and the owning
      -- folder does not already exist in the target repository then an object ownership
      -- must be created. If only one reference is imported then that becomes the owner.
      -- If more than one reference is imported then an arbitrary folder is chosen.

      set_ownership;

    END IF;


    -- Full Access Rights to all imported containers are given to the executing USER.
    ins_access_rights(i.type, i.wa_mode);

    -- Ensure Import doesn't result in any version associations for 'N'ew objects.
	-- On a clean repository this should not happen so there shouldn't be any contention
	execute_sql (
       ' DELETE FROM i$sdd_version_associations '
     ||' WHERE  irid IN ( '
     ||' SELECT irid '
     ||' FROM  i$sdd_object_versions '
     ||' WHERE state = ''N'' ) '
    );
	
    -- WA refresh/compilation MUST occur after access rights have been created.
    -- For simplicity the Workarea is always refreshed, excpet in Configuration mode,
    --  as otherwise there may be undesirable side effects, such as the WA showing 
    -- multiple versions of the same object, when importing into WAs with different 
    -- checkin Branches.

    IF i.type != 'C' THEN 
      cmp_unr_wa; 
    END IF;

    IF i.mkmem_fldr_irid IS NOT NULL THEN
      -- This code block only applies to FOLDER mode but is 
      -- placed here because it has to be done after access rights.
      -- Relocate the Imported folder(fldr_irid) to the given Folder(mkmem_fldr_irid)
      declare 
          fldr_irid Number;
          csr       Number;
          exe       Number;
      begin
        rmdbg.trace('loc 95a moving context folder to : '||i.mkmem_fldr_irid);
        csr := dbms_sql.open_cursor;

        DBMS_SQL.PARSE(csr,
              ' SELECT new_irid FROM xtsys_import_irid_mapping WHERE old_irid = ' ||i.irid 
              , DBMS_SQL.NATIVE);
        DBMS_SQL.DEFINE_COLUMN(csr, 1, fldr_irid);
        exe := DBMS_SQL.EXECUTE_AND_FETCH(csr);

        DBMS_SQL.COLUMN_VALUE(csr, 1, fldr_irid);
        DBMS_SQL.CLOSE_CURSOR(csr);

        rmdbg.trace('loc 95b moving context folder : '|| fldr_irid);

        cdapi.move_pac(fldr_irid, i.mkmem_fldr_irid);
      end;
    END IF;

    IF guid_mode in ('R', 'M') THEN
      -- In replicate/merge mode deletes at the source should be
      -- applied at the target.
      apply_wastebasket_entries;
    END IF;
    rmdbg.trace(utility_name||proc || 'loc 99'); 
  END LOOP;

  -- re-enable all constraints, access rights etc
  enable_all;   

  -- invalid dangling refs maybe fixed 
  -- and logged if diagnostic trace output is enabled
  IF fix_dangling_refs THEN
    jr_context.disable(jr_context.NO_CHECKIN_UPDATE);
    dangling_refs;
    jr_context.enable(jr_context.NO_CHECKIN_UPDATE);
  ELSE
    -- don't fix but log if diagnostics enabled
    IF rmdbg.enabled THEN
      dangling_refs;
    END IF;
  END IF;

  ck_util.init(utility_name,'Finish');
  ck_util.register(utility_name,'Finish');
  rmdbg.trace(utility_name||proc||'loc 100:Finish');
  rmdbg.flush_trace;

  EXCEPTION
    WHEN parameters_invalid THEN
      -- re-enable all constraints, access rights etc
      enable_all;   
      rmdbg.trace(utility_name||proc || 'loc 101');  
      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;
    WHEN validation_failed THEN
      -- re-enable all constraints, access rights etc
      enable_all;   
      rmdbg.trace(utility_name||proc || 'loc 102: Tablespace name is NULL');  
      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;
    WHEN non_mergeable THEN
      -- re-enable all constraints, access rights etc
      enable_all;   
      rmdbg.trace(utility_name||proc || 'loc 103: Non mergeable objects ');  
      rmmes.post('CDR', NON_MERGEABLE_ERR);
      rollback;
      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;
    WHEN context_workarea_expected THEN 
      -- re-enable all constraints, access rights etc
      enable_all;
      rmdbg.trace(utility_name||proc || 'loc 105');  
      rollback;
      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;

    WHEN no_version_access_rights THEN
      -- re-enable all constraints, access rights etc
      enable_all;   
      rmdbg.trace(utility_name||proc || 'loc 105a: No version access rights ');  
      rmmes.post('CDR', no_access_rights, 'workarea or container');
      rollback;
      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;

    WHEN OTHERS THEN
      -- re-enable all constraints, access rights etc
      enable_all;
      rmdbg.trace(utility_name||proc || 'loc 106');  
      rmmes.post('RME', unhdl_orcl_err, SQLERRM);
      ck_util.ora_error(utility_name,proc,sql%rowcount);
      rollback;

      rmdbg.trace(utility_name||proc || 'loc 107');  
      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;

END ckimp70;


-- upgrade of instance data in the XT tables
PROCEDURE XT_UPGRADE IS

l_source_repos_version rm$repositories.repository_version%type;
l_patch_number number;
col_exist         EXCEPTION;
tab_not_exist     EXCEPTION;
PRAGMA EXCEPTION_INIT(col_exist, -01430);
PRAGMA EXCEPTION_INIT(tab_not_exist, -00942);
l_utility_name   varchar2(27)  := 'Repository 6.5 Import:';
l_proc           varchar2(30) := 'xt_upgrade:';

BEGIN
  
  select repository_version 
   into  l_source_repos_version 	
   from  xtsys_rm$repositories;	
  
  l_patch_number := to_number(substr(l_source_repos_version,instr(l_source_repos_version,'.',1,3)+1,
  		    (instr(l_source_repos_version,'.',1,4))-(instr(l_source_repos_version,'.',1,3)+1)));

  ---If the patch version of the source repository < 93 (ie < 4.0.12.93) then apply this patch
  IF(l_patch_number < 93) THEN

    -- bugs 3001015/2874421 (patch CI04012.M93.005)
    IF table_exists('XT_SDD_SPV') THEN
	execute_sql('update XT_SDD_SPV ' ||
		'set    GRANT_OPTION_TO = ''DBU'' ' ||
		'where  DATABASE_USER_REF IS NOT NULL ' ||
		'and    ROLE_REF IS NULL ' ||
		'and    GRANT_OPTION_TO = ''GRP'' ');
    END IF;
  
  END IF;

  ---If the patch version of the source repository < 94 (ie < 4.0.12.94) then apply this patch
  IF(l_patch_number < 94) THEN
	
	begin
	
	   --Add the column to the table first as non-mandatory
	   execute_sql('ALTER TABLE XT_SDD_TAB ADD DATA_PERSISTENCE VARCHAR2(11) DEFAULT ''PERMANENT'' ');
	
	   --Update the value to 'PERMANENT' for all the existing rows
	   execute_sql('update XT_SDD_TAB set DATA_PERSISTENCE = ''PERMANENT'' ');
	
	   --Now make the column as mandatory
	   execute_sql('ALTER TABLE XT_SDD_TAB MODIFY DATA_PERSISTENCE VARCHAR2(11) DEFAULT ''PERMANENT'' NOT NULL ');

        exception
        
           when col_exist then
      	      rmdbg.trace(l_utility_name || l_proc || 'Column DATA_PERSISTENCE already exists');
	   when tab_not_exist then
      	      rmdbg.trace(l_utility_name || l_proc || 'Table XT_SDD_TAB does not exists');
	   when others then
	      rmdbg.trace(l_utility_name || l_proc || sqlerrm);
	end;

	
  END IF;	


END XT_UPGRADE;


BEGIN

  short_trxns  := :short_trans ='Y';
  fix_dnglrefs := :fix_dnglref ='Y';
  rmmes.clear;
  -- upgrade of instance data in the XT tables
  XT_UPGRADE; 
  ckimp70(:tablespace_name, :prollback_segment, :guid_mode, short_trxns, fix_dnglrefs);

  :iserror := 0;
  exception when others then
   :iserror := 1;
   :sqlcode := sqlcode;

END;
