rem NAME : ckexp70.sql
rem
rem       Supports export of Work Areas 
rem             - export of unresolved work area
rem             - export of resolved work area
rem             - includes option to export only checked out elements
rem       Supports export of configurations
rem       Supports export of folders
rem             - includes option to walk down folder hierarchy
rem
rem Pre-requisites: XTSYS schema object creation (support file CKXTSYS.SQL)
rem 
rem   This package exports all elements contained within a work area including 
rem   the work area itself and the configurations it uses and the configuration
rem   members.
rem 
rem   Objects to be exported should be stored in the table XTSYS_EXPORT_OBJECTS:
rem
rem  OBJECT_IRID Number NOT NULL
rem  OBJECT_IVID Number
rem  OBJECT_TYPE Varchar2(1) - W=Workarea, C=Configuration, F=Folder
rem  WA_MODE     Varchar2(1) - C=Checked out, R=Resolved, U=Unresolved
rem  FD_MODE     Varchar2(1) - T=Top level folder only, D= Decompose subfolders recursively
rem  STATUS      Varchar2(1) - Set to 'N' - after processing 'P' - Passed OK, 'F' - Failed
rem 
rem
rem  Change History
rem      SM   21-Nov-01 :  bug 2038874 : performance enhanvement of exporting folders.
rem                                      All folder processing is now done in one go.
rem                                      - Moved decomp_folder to inside loop
rem                                      - removed any parameters to ext_fd_data and get_fd_tabs
rem                                      - replaced irid reference with SQL to fetch all
rem                                        decomposed folders
rem                                      - removed NOT EXISTS clause from all SQLs (This is the
rem                                        biggest factor in improving performance).
rem                                      - I have not bothered with the lock functionality as it
rem                                        is commented out at lower level anyway.
rem                                      - added group by clause to irid/ivid mapping table SQLs
rem                                        to avoid uniqueness violation on SDD_ATV (this is 
rem                                        because NOT EXISTS clause has been removed and SDD_ATV
rem                                        has 4 rows in xtsys_tabs_exported as it is in an ARC.)
rem                                      - removed config_mapping cursor and related code from 
rem                                        from procedure ext_unr_wa_data to avoid clash between
rem                                        sdd_configurations and sdd_object_versions rows being
rem                                        mapped to irid/ivid mapping xtsys tables.
rem                                      Other bugs fixed:
rem                                      - folder export with no recurse option now works 
rem                                      
rem      TNS  08-Aug-00 :  bug 1336484 : also include objects with PUBLIC read access
rem      TNS  07-Aug-00 :  bug 1336488 : prevent Ora-955 error by pre-check on CREATE INDEX...
rem      DLG  02-Jun-00 :  Commented out duplicated bind variables because of problems
rem                        with Java RSJ binding - part fixes bug 1316312 (dlgoldsm)
rem      TNS  03-May-99 :  bug 1275707 PKey constraint violated on XTSYS_TABS_EXPORTED 
rem      TNS  17-Apr-00 :  bug 1162133 Performance : for extraction of dependent SAC rows, 
rem                          added code to make row processing more selective by filtering 
rem                          against OBJECT_VERSIONS of the appropriate type ('FAMILY_IRID')
rem      TNS  27-Mar-00 :  bug 1242363 Extract dependency data after initialising mapping tables
rem      TNS  14-Feb-00 :  bug 1192396 SAC elements not exported
rem      TNS  09-Feb-00 :  bug 1188126 Wastebasket entries not excluded from rseolved WA export
rem      TNS  09-Feb-00 :  bug 1169926 implement checked_in_obj_flag early warning
rem      TNS  17-Jan-00 :  bug 1133646 objects outside WA scope on CO/New mode
rem      TNS  21-Dec-99 :  Performance improvments/code optimization fro Folder Mode
rem      TNS  15-Dec-99 :  Performance improvments/code optimization Workarea Modes
rem      TNS  11-Nov-99 :  Extract entries in Wastebasket to use for synchronizing target
rem      TNS  01-Nov-99 :  Completed changes resulting from WA restructure (I$SDD_WA_CONTEXT)
rem      TNS  20-oct-99 :  Changes resulting from changes to WA structure in 4.0.12
rem      TNS  07-Sep-99 :  Fixed bugs 972414, 972358 Allow exclude of dependency data (default)
rem      TNS   2-Aug-99 :  Fixed bug 951902 SQL error (ora933) on 'C'hecked out obects mode
rem      TNS  30-Jul-99 :  Fixed bug 951537 folders not exported when recursion not selected.
rem      TNS  16-Jun-99 :  Fixed bug 911327 allow renaming of root folders in Import.
rem      TNS  15-Jun-99 :  Fixed bugs 909081 (text), 908161 (MUNs)
rem      TNS   1-Jun-99 :  Extract all relevant version associations for object versions
rem      TNS  25-May-99 :  Striped export set via XTSYS_IMPORT_IVID_MAPPING.IMPORT_SEQUENCE.
rem                        Extract branch data only for Unresolved and Configuration modes.
rem      TNS  24-May-99 :  Added code to extract Dependency data
rem      TNS  20-May-99 :  Added code to extract NO FK data (text, application logic etc)
rem      TNS   4-May-99 :  Include full folder path to root for Folder Mode i.e.
rem                        assume a non root folder may be given as input
rem      TNS  15-Apr-99 :  Added code to exclude specific objects from Export
rem      TNS  14-Apr-99 :  Removed condition " fm.element_type_name = 'APPCEL' "
rem                        for decomposing folders as this column name removed in 4.0.9
rem DESCRIPTION
rem
rem   export                This is the main procedure.
rem                          It controls the order of processing and the
rem                          error handling. If the user has specified a 
rem                          rollback segment this is set by this procedure.
rem                          * Performs necessary validation
rem                          * LOOP for each object on XTSYS_EXPORT_OBJECTS
rem                          		* Locks object
rem                          		* Set transaction if rollback segment specified
rem                          		* Extract table definitions for objects on XTSYS_EXPORT_OBJECTS
rem                          		* Creates and extracts data to table definitions
rem                          		* Update XTSYS_EXPORT_OBJECTS
rem                          		* Release object lock
rem                          * END LOOP
rem                               

rem
rem NOTES
rem   This package uses the following system tables :
rem 
rem            XTSYS_EXPORT_OBJECTS		(CRU)
rem            XTSYS_TABS_EXPORTED		(CRUD)
rem            XTSYS_IMPORT_IRID_MAPPING	(CRUD)
rem            XTSYS_IMPORT_IVID_MAPPING	(CRUD)
rem            XT_.......			(C)
rem            RM_SQL_TABLES                    (R)
rem            RM_SQL_CONSTRAINTS               (R)
rem            SDD_...				(R)
rem
rem
rem
DECLARE

----------------------GLOBAL declaration ------------------------------------------

--  Exceptions trapped in RON 

unlock_failed EXCEPTION;
lock_failed EXCEPTION;
invalid_parameter EXCEPTION;
validation_failed EXCEPTION;

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

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;
invalid_param_err constant number     := 6077;
get_schema_vn_fail constant number     := 6078;

unhdl_orcl_err    constant number := 2105;


-- bug 1879803 only need to check manage workarea if we have a non-versioned repos
l_versioned VARCHAR2(1); 

-- B2291498 - not all versions of text extracted in unresolved wa mode
l_all_versions VARCHAR2(1); 


-- Nested table to hold SQL statements
type sql_table is table of varchar2(1500);
---------------------CKEXP70 declaration ----------------------------------------------

PROCEDURE ckexp70(tablespace_name varchar2, prollback_segment varchar2 DEFAULT NULL) IS

-- Cursor to select objects to export
-- The 'import_order' value is used both to order the Export/Import of the given sets of
-- data and to 'stripe' the instance data to the given export object i.e. all the objects
-- for a Workarea, Configuration or Folder. The 'stripe' is implemented via  
-- XTSYS_IMPORT_IVID_MAPPING.
-- If no order is given then objects will be loaded in a default (random) order and
-- treated as one partition.
-- Note: ac_type_check used to check that USER has ADMIN/MANAGE privilege on given object
CURSOR obj_to_export IS
  SELECT eo.object_irid   irid
  ,      eo.object_ivid   ivid
  ,      eo.object_type   type
  ,      eo.wa_mode
  ,      eo.fd_mode
  ,      eo.mkmem_fldr_irid
  ,      eo.extract_reg_metad
  ,      eo.all_versions 
  ,      eo.include_deps
  ,      nvl(eo.import_order, 0) import_order
  ,      decode(eo.object_type, 'W', 'WA', 'C', 'CFG', 'F', 'CEL', null) ac_type_check
  FROM   xtsys_export_objects  eo
  WHERE  eo.status = 'N'
  ORDER BY nvl(eo.import_order, eo.object_irid);

--  Cursor to store the number of rows extracted 
-- !!!!!!!!!!!!!change count(*) to count(irid) when all views have this column!!!!!!!!
-- Group by is required to make sure that sql is fired only once for those tables which
-- are in an arc.

CURSOR obj_rows IS
  SELECT 'UPDATE xtsys_tabs_exported '||
         'SET    row_cnt = (SELECT COUNT(*) FROM '||te.exp_table_name||' ) '||
         'WHERE  exp_table_name = '''||te.exp_table_name||''''    stmt
  FROM   xtsys_tabs_exported  te
  GROUP BY te.exp_table_name;

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

next_pass Number; 
                 -- used to set xtsys_tabs_exported.pass for 'generic/system' tables

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

-- Flag to indicate if the folders have been processed
fd_processed   boolean := FALSE;

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

wa_private_cfg number;

inc_deps       Varchar2(1); 
                           -- is set to value of XTSYS_EXPORT_OBJECTS.INCLUDE_DEPS
                           -- and if 'Y' Export will extract dependency data in all modes

--------------------------------------------------------------------------------
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;

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 execute_sql_collection (sql_statements sql_table) IS
  proc           varchar2(100) := 'execute_sql_collection ';

BEGIN

    FOR i in  sql_statements.FIRST .. sql_statements.LAST LOOP
	--Set rollback segment...
      ck_util.do_commit (prollback_segment);
	--Execute the statement
	execute_sql ( sql_statements(i) );
    END LOOP;

END execute_sql_collection;

--------------------------------------------------------------------------------

PROCEDURE precreate_xt_tables IS
  proc           varchar2(100) := 'precreate_xt_tables ';
-- Precreate the Tables for registered object to avoid more complicated checking 
-- for Table existence during row extraction. This relieas on identical view and 
-- table definition for version resolved views (doesn't appear to be true for 
-- all tables/views in this release!).

CURSOR xt_tables_list IS
  SELECT DISTINCT exp_table_name, table_name
  FROM   xtsys_tabs_exported
  WHERE  pass >= 1
    AND  table_irid > 0
    AND  nvl(created,'N') != 'Y' ;

sql_statement Varchar2(1500);

BEGIN
    rmdbg.trace(proc || ' Loc 1');

    -- This may be the second cycle, Folder Mode, so filter out any tables already
    -- created
    execute_sql ('UPDATE xtsys_tabs_exported SET created = ''Y'' ' ||
                 ' WHERE exp_table_name IN ' ||
                 '   (SELECT table_name FROM user_tables WHERE table_name like ''XT%'') ');

    FOR i IN xt_tables_list LOOP
       sql_statement := 
         ' CREATE TABLE '     || i.exp_table_name ||' TABLESPACE '||tablespace_name ||
         ' AS SELECT * FROM ' || i.table_name || ' WHERE rownum < 1 ';
	--Execute the statement
	execute_sql (sql_statement);
    END LOOP;
    execute_sql ('UPDATE xtsys_tabs_exported SET created = ''Y'' ' ||
                 ' WHERE exp_table_name IN ' ||
                 '   (SELECT table_name FROM user_tables WHERE table_name like ''XT%'') ');

END precreate_xt_tables;

--------------------------------------------------------------------------------


PROCEDURE trunc_tabs IS
--  PURPOSE
--    This procedure truncates xtsys_tabs_exported and xtsys_import_irid_mapping,
--    xtsys_import_ivid_mapping.
--    Truncate will do an implicit commit
  proc           varchar2(100) := 'Truncate Tables ';
  csr number;
  exe number;

BEGIN

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

  -- TRUNCATE results in a Deadlock for this table. Will have to use Delete as a 
  -- workround until problem can be resolved
--  csr := DBMS_SQL.OPEN_CURSOR;
--  DBMS_SQL.PARSE(csr, 'TRUNCATE TABLE xtsys_tabs_exported', DBMS_SQL.NATIVE);
  execute_sql ( 'DELETE FROM xtsys_tabs_exported' );
--  DELETE FROM xtsys_tabs_exported;
  COMMIT;

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

  execute_sql ( 'TRUNCATE TABLE xtsys_import_irid_mapping' );

  execute_sql ( 'TRUNCATE TABLE xtsys_import_ivid_mapping' );

  --Set rollback segment...
  ck_util.do_commit (prollback_segment);

END trunc_tabs;
--------------------------------------------------------------------------------

PROCEDURE drop_tabs IS
--  PURPOSE
--    This procedure drops any tables where the name begins with XT_ from the user's schema.
--    It will only drop those tables which do not appear in XTSYS_TABS_EXPORTED.
--    The first time this procedure is called XTSYS_TABS_EXPORTED will be empty.
--    The second time this procedure is called XTSYS_TABS_EXPORTED will be populated with the
--    tables created by the extract process which contain data.
--    So, the second time it is called this procedure will drop all empty tables created by
--    the extract process.
--    Drop will do an implicit commit.
  proc   varchar2(100) := 'Drop Tables ';
  tmp    varchar2(255);
  csr    number;
  i      number;

  CURSOR drp_tabs IS
     SELECT 'DROP TABLE '||uo.object_name   stmt
     FROM   user_objects  uo
     WHERE  uo.object_name like 'XT\_%' ESCAPE'\'
     AND    uo.object_type = 'TABLE'
     AND NOT EXISTS ( SELECT NULL
                      FROM   xtsys_tabs_exported  te
                      WHERE  te.exp_table_name = uo.object_name )
     AND    uo.object_name not in ('XT_RM_SQL_SCHEMA_VERSIONS') ;

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

  FOR i in drp_tabs LOOP
    rmdbg.trace(utility_name||proc || 'loc 2.1 Statement: '||i.stmt);  
    tmp := i.stmt;
    rmdbg.trace(utility_name||proc || 'loc 2.2');  
    execute_sql(i.stmt);
  END LOOP;
  --Set rollback segment...
  ck_util.do_commit (prollback_segment);

  EXCEPTION
    WHEN OTHERS THEN
      rmdbg.trace(utility_name||proc || 'loc 2.4');
      rmmes.post(cda_error, drop_tab_fail, tmp);
      rmmes.post('ORA', SQLCODE);
      RAISE;

END drop_tabs;
--------------------------------------------------------------------------------
  
FUNCTION lock_obj(irid number, ivid number, obj_type VARCHAR2) RETURN number IS
--  PURPOSE
--    This procedure calls the lock_work_area and lock_folder functions to create a
--    user lock for the object (folder, work area) marked for export.
--    Each work area or folder is processed individually so it is locked,
--    processed and unlocked. This should minimise the lock window for an
--    object being exported.
  proc           varchar2(100) := 'Lock Object ';
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 3');

  IF (obj_type = 'W') THEN
    rmdbg.trace(utility_name||proc || 'loc 3.1 - Work Area irid: ' || irid);
--    return ck_util.lock_work_area(irid, 'E');
  ELSIF (obj_type = 'F') THEN
    rmdbg.trace(utility_name||proc || 'loc 3.1 - Folder irid: ' || irid || ' Folder ivid: ' || ivid);
--    return ck_util.lock_folder(ivid, 'E');
  END IF;

return 1;

END lock_obj;
--------------------------------------------------------------------------------

PROCEDURE unlock_obj (lock_handle IN number) IS
--  PURPOSE
--    This procedure calls the unlock_object function to remove user locks
--    for each locked object
  proc           varchar2(100) := 'Unlock Object ';
  err_cond boolean := FALSE;

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

--  ck_util.release_object_lock(lock_handle);

  EXCEPTION
    WHEN OTHERS THEN
      rmdbg.trace(utility_name||proc || 'loc 4.1');
      RAISE;

END unlock_obj;

--------------------------------------------------------------------------------
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;

--------------------------------------------------------------------------------
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;

--------------------------------------------------------------------------------

PROCEDURE get_repos_version IS
  proc   varchar2(100) := 'get_repos_version ';
  -- Take the details of the Repository instance so that the
  -- target Repository can be checked for compatibility.

BEGIN
  Begin
    execute_sql('DROP TABLE xtsys_rm$repositories ');
    EXCEPTION
      WHEN OTHERS THEN NULL;
  End;

  rmdbg.trace(utility_name||proc || 'loc 3.1');
  execute_sql('CREATE TABLE xtsys_rm$repositories TABLESPACE '||tablespace_name||
              ' AS SELECT * FROM rm$repositories');
  rmdbg.trace(utility_name||proc || 'loc 3.2');

  EXCEPTION
    WHEN OTHERS THEN
      rmdbg.trace(utility_name||proc || 'loc 3.3');
      rmmes.post(cda_error, get_repos_vn_fail, 'RM$REPOSITORIES');
      rmmes.post('ORA', SQLCODE);
      RAISE;

END get_repos_version ;
--------------------------------------------------------------------------------

PROCEDURE get_reg_schema_info IS
  proc   varchar2(100) := 'get_reg_schema_info ';
  -- Take the details of the registered schemas in a Repository.
  -- The target Repository needs to have the same schemas present
  -- before instance data can be loaded.
  -- Note: If necessary the schemas must be synchronized by a Repository
  --       administrator connected as the 'Repository owner'

  csr NUMBER;
  exe number;

BEGIN
  Begin
    execute_sql( ' DROP TABLE xt_rm_sql_schema_versions ');
    EXCEPTION
      WHEN OTHERS THEN NULL;
  End;

  rmdbg.trace(utility_name||proc || 'loc 4.1');
  execute_sql(' CREATE TABLE xt_rm_sql_schema_versions TABLESPACE '||tablespace_name||
              ' AS SELECT * FROM i$rm_sql_schema_versions ');
  rmdbg.trace(utility_name||proc || 'loc 4.2');

  EXCEPTION
    WHEN OTHERS THEN
      rmdbg.trace(utility_name||proc || 'loc 4.3');
      rmmes.post(cda_error, get_schema_vn_fail, 'RM_SQL_SCHEMA_VERSIONS');
      rmmes.post('ORA', SQLCODE);
      RAISE;

END get_reg_schema_info ;

--------------------------------------------------------------------------------

PROCEDURE get_cmp_wa_tabs(wa_irid in number) IS
--  PURPOSE
--    This procedure identifies all the tables which store objects accessible
--    from a compiled work area.
--  NOTES
--    There is no concept of sharing elements between work areas.
--    SDD_CONFIGURATION_MEMBERS should be retrieved as a dependency of SDD_CONFIGURATIONS.
--    SDD_FOLDER_MEMBERS will be retrieved as a dependency of SDD_FOLDERS.
--    SDD_OBJECT_VERSIONS will be needed as well.
  proc   varchar2(100) := 'Get_Comp_Work_Area_Tables ';
  i      number := 0;
  
  current_wa number;
 
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 5'|| ' Workarea irid: '||wa_irid);  

  -- Compile a list of tables containing the instance data to export 

  -- Extract definitions for tables outside of the model 
  -- i.e. Work area and work area to configuration mappings 

  IF (wa_base = FALSE) THEN
    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass
    )
    SELECT DISTINCT 
       rt.irid
    ,  rt.irid
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'N'
    ,  0
    FROM   rm_sql_tables         rt
    WHERE  name = 'SDD_OBJECT_VERSIONS';
	

    rmdbg.trace(utility_name||proc || 'loc 5.1: '||sql%rowcount);  
    wa_base := TRUE;
  END IF;

  -- Object versions table is pass 0
  -- Tables on pass = 1 contain versionable objects
  -- Any tables with pass > 1 contain dependant objects

  --Change context to the workarea we are exporting.
  --Keep record of current context.
  current_wa:=jr_context.workarea;
  rmdbg.trace(utility_name||proc || 'loc 5.1.a  CURRENT_WA = ' || current_wa);  
  jr_context.set_workarea(wa_irid);
  
  INSERT INTO xtsys_tabs_exported (
     table_irid
  ,  family_irid
  ,  table_name
  ,  exp_table_name
  ,  created
  ,  pass  )
  SELECT DISTINCT 
     ov.table_irid
  ,  ov.table_irid
  ,  rt.name
  ,  'XT_'||rt.name
  ,  'N'
  ,  1
  FROM   sdd_object_versions   ov,      rm_sql_tables         rt
  WHERE  rt.irid = ov.table_irid
  AND NOT EXISTS ( SELECT NULL FROM   xtsys_tabs_exported   ta1
          WHERE  ta1.table_irid = ov.table_irid AND ta1.family_irid = ov.table_irid);
  
  rmdbg.trace(utility_name||proc || 'loc 5.2: '||sql%rowcount);  

  LOOP
    i := i + 1;
    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass  )
    SELECT DISTINCT 
       rt.irid
    ,  ta.family_irid
    ,  rt.name
    , 'XT_' ||decode(sc2.is_owning_fk, 'O', rt.name||'_O', rt.name)
    , 'N'
    ,  i + 1
    FROM   rm_sql_constraints     sc1
    ,      rm_sql_constraints     sc2
    ,      xtsys_tabs_exported    ta
    ,      rm_sql_tables          rt
    WHERE  ta.table_irid = sc1.table_irid
    AND    sc2.r_constraint_name = sc1.constraint_name
    AND    sc2.is_owning_fk in ('Y','O') 
              -- 'O' picks up dual class elements eg SDD_MUN
    AND    rt.irid = sc2.table_irid
    AND    ta.pass = i          
             -- elements from previous pass 
    AND 
         NOT EXISTS ( 
            SELECT NULL 
            FROM   xtsys_tabs_exported   ta1
            WHERE  ta1.family_irid = ta.family_irid and ta1.exp_table_name = 
                     'XT_' ||decode(sc2.is_owning_fk, 'O', rt.name||'_O', rt.name) );

    EXIT WHEN SQL%NOTFOUND;
    rmdbg.trace(utility_name||proc || 'loc 5.3:'||to_char(i + 1)||':'||sql%rowcount );

  END LOOP;

  --Reset the workarea context
  rmdbg.trace(utility_name||proc || 'loc 5.4');
  jr_context.set_workarea(current_wa);
  

  ck_util.do_commit (prollback_segment);

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

END get_cmp_wa_tabs;
--------------------------------------------------------------------------------

PROCEDURE get_cf_tabs (cf_ivid IN number,checked_out in varchar2 default 'N') IS
--  PURPOSE
--    This procedure identifies all the tables which store objects accessible
--    from a configuration.
--  NOTES
--    There is no concept of sharing elements between configurations.
--    SDD_FOLDER_MEMBERS will be retrieved as a dependency of SDD_FOLDERS.
--    Configurations are exported outside of a work area so the code cannot 
--    use the version resolved views.
--    Need to take I$SDD_OBJECT_VERSIONS too.
  proc   varchar2(100) := 'Get_Configuration_Tables ';
  i      number := 0;
 
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 7');  

  -- Compile a list of tables containing the instance data to export 

  -- Object versions table is pass 0
  -- Tables on pass = 1 contain versionable objects
  -- Any tables with pass > 1 contain dependant objects
 
  IF (cf_base = FALSE) THEN
    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass  )
    SELECT DISTINCT 
       rt.irid
    ,  rt.irid
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'N'
    , 0
    FROM   rm_sql_tables         rt
    WHERE  name in ( 'SDD_CONFIGURATIONS', 'SDD_OBJECT_VERSIONS')
    UNION
    SELECT 
       rt.irid
    ,  rt.irid
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'N'
    ,  2
    FROM   rm_sql_tables         rt
    WHERE  name =  'SDD_CONFIGURATION_MEMBERS'
    UNION
    SELECT
        -3
    ,   -3
    ,  'SDD_VERSION_ASSOCIATIONS'
    ,  'XT_SDD_VERSION_ASSOCIATIONS'
    ,  'N'
    ,  2
    FROM   sys.dual;


    -- Note: exclude SDD_CONFIGURATIONS as this is picked up earlier as an Infrastructure table
    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass  )
    SELECT DISTINCT 
       ov.table_irid
    ,  ov.table_irid
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'N'
    ,  1
    FROM   I$sdd_object_versions ov
    ,      rm_sql_tables         rt
    WHERE  rt.name != 'SDD_CONFIGURATIONS'      
    AND    ov.ivid = cf_ivid
    AND    rt.irid = ov.table_irid
    AND NOT EXISTS (
          SELECT NULL
          FROM   xtsys_tabs_exported   ta
          WHERE  ta.table_irid = ov.table_irid AND ta.family_irid = ov.table_irid);

    dbms_output.put_line(utility_name||proc || 'loc 7.1: '||sql%rowcount);  

    rmdbg.trace(utility_name||proc || 'loc 7.1: '||sql%rowcount);  
    cf_base := TRUE;
  END IF;

  -- Add any tables containing configuration members into xtsys_tabs_exported 
  rmdbg.trace(utility_name||proc || 'loc 7.2');  

  INSERT INTO xtsys_tabs_exported (
     table_irid
  ,  family_irid
  ,  table_name
  ,  exp_table_name
  ,  created
  ,  pass
  )
  SELECT DISTINCT 
     ov.table_irid
  ,  ov.table_irid
  ,  rt.name
  ,  'XT_'||rt.name
  ,  'N'
  ,  1
  FROM   I$sdd_object_versions ov
  ,      rm_sql_tables         rt
  ,      I$sdd_configuration_members  cm
  WHERE  cm.config_ivid = cf_ivid
  AND    cm.object_ivid = ov.ivid
  AND    rt.irid = ov.table_irid
  AND    ((checked_out='N') 
       OR (ov.STATE != 'I')) 
          -- If checked out TRUE, don't get checked-in objects)
  AND NOT EXISTS (
          SELECT NULL
          FROM   xtsys_tabs_exported   ta
          WHERE  ta.table_irid = ov.table_irid AND ta.family_irid = ov.table_irid);
  
  rmdbg.trace(utility_name||proc || 'loc 7.3: '||sql%rowcount);  

  LOOP
    i := i + 1;
    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass  )
    SELECT DISTINCT 
       rt.irid
    ,  ta.family_irid
    ,  rt.name
    , 'XT_' ||decode(sc2.is_owning_fk, 'O', rt.name||'_O', rt.name)
    , 'N'
    ,  i + 1
    FROM   rm_sql_constraints     sc1
    ,      rm_sql_constraints     sc2
    ,      xtsys_tabs_exported    ta
    ,      rm_sql_tables          rt
    WHERE  ta.table_irid = sc1.table_irid
    AND    sc2.r_constraint_name = sc1.constraint_name
    AND    sc2.is_owning_fk in ('Y','O')
              -- 'O' picks up dual class elements eg SDD_MUN
    AND    rt.irid = sc2.table_irid
    AND    ta.pass = i         
              -- elements from previous pass 
    AND 
         NOT EXISTS ( 
            SELECT NULL 
            FROM   xtsys_tabs_exported   ta1
            WHERE  ta1.family_irid = ta.family_irid and ta1.exp_table_name = 
                     'XT_' ||decode(sc2.is_owning_fk, 'O', rt.name||'_O', rt.name) );

    EXIT WHEN SQL%NOTFOUND;
    rmdbg.trace(utility_name||proc || 'loc 7.4:'||to_char(i + 1)||':'||sql%rowcount );

  END LOOP;

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

  ck_util.do_commit (prollback_segment);

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

END get_cf_tabs;
--------------------------------------------------------------------------------

PROCEDURE get_unr_wa_tabs (wa_irid IN number) IS
--  PURPOSE
--    This procedure identifies all the tables which store objects forming the basis of an
--    unresolved work area.
--  CALLS
--    get_cf_tables
--  NOTES
--    There is no concept of sharing elements between work areas.
--    SDD_CONFIGURATION_MEMBERS should be retrieved as a dependency of SDD_CONFIGURATIONS.
--    SDD_FOLDER_MEMBERS will be retrieved as a dependency of SDD_FOLDERS.
--    SDD_OBJECT_VERSIONS will be needed as well.
  proc   varchar2(100) := 'Get_Unresolved_Work_Area_Tables ';
 
  i number := 0;
 
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 6');  
  dbms_output.put_line(utility_name||proc || 'loc 6');  

  -- Compile a list of tables containing the instance data to export 

  -- Extract definitions for tables outside of the model 
  -- i.e. Work area and work area to configuration mappings  
  --  pass = -1 for these tables

  IF (wa_base = FALSE) THEN
    -- all the Workarea definition and infrastructure tables
    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass
    )
    SELECT
       rt.irid
    ,  rt.irid
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'N'
    ,  -1
    FROM   rm_sql_tables         rt
    WHERE  name in ('SDD_WORKAREAS'
                   ,'SDD_WORKAREA_SPEC_ENTRIES'
                   ,'SDD_RULES')
    UNION
    SELECT
        -1
    ,   -1
    ,  'I$SDD_RULE_PARAMS'
    ,  'XT_I$SDD_RULE_PARAMS'
    ,  'N'
    ,  -1
    FROM   sys.dual
    UNION
    SELECT
        -2
    ,   -2
    ,  'I$SDD_RULE_PARAM_VALUES'
    ,  'XT_I$SDD_RULE_PARAM_VALUES'
    ,  'N'
    ,  -1
    FROM   sys.dual
    UNION
    SELECT
        -3
    ,   -3
    ,  'I$SDD_WA_CONTEXT'
    ,  'XT_I$SDD_WA_CONTEXT'
    ,  'N'
    ,  -1
    FROM   sys.dual;
    rmdbg.trace(utility_name||proc || 'loc 6.1: '||sql%rowcount);  
    dbms_output.put_line(utility_name||proc || 'loc 6.1: '||sql%rowcount);  
   wa_base := TRUE;
  END IF;

  IF (cf_base = FALSE) THEN
    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass  )
    SELECT DISTINCT 
       rt.irid
    ,  rt.irid
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'N'
    ,  0
    FROM   rm_sql_tables         rt
    WHERE  name = 'SDD_OBJECT_VERSIONS'
    UNION
    SELECT DISTINCT 
       rt.irid
    ,  rt.irid
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'N'
    ,  0
    FROM   rm_sql_tables         rt
    WHERE  name = 'SDD_CONFIGURATIONS'
    AND EXISTS (
          SELECT NULL 
          FROM i$sdd_workarea_spec_entries 
          WHERE workarea_irid = wa_irid 
            AND config_ivid IS NOT NULL
          )
    UNION
    SELECT 
       rt.irid
    ,  rt.irid
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'N'
    ,  2
    FROM   rm_sql_tables         rt
    WHERE  name =  'SDD_CONFIGURATION_MEMBERS'
    AND EXISTS (
          SELECT NULL 
          FROM i$sdd_workarea_spec_entries 
          WHERE workarea_irid = wa_irid 
            AND config_ivid IS NOT NULL
          )
    UNION
    SELECT
        -3
    ,   -3
    ,  'SDD_VERSION_ASSOCIATIONS'
    ,  'XT_SDD_VERSION_ASSOCIATIONS'
    ,  'N'
    ,  2
    FROM   sys.dual;

    rmdbg.trace(utility_name||proc || 'loc 7.1: '||sql%rowcount);  
    cf_base := TRUE;
  END IF;

  -- Object versions table is pass 0
  -- Tables on pass = 1 contain versionable objects
  -- Any tables with pass > 1 contain dependant objects
 
  -- Get hold of the tables for the  objects within the workarea.
  rmdbg.trace(utility_name||proc || 'loc 6.2');  
  INSERT INTO xtsys_tabs_exported (
     table_irid
  ,  family_irid
  ,  table_name
  ,  exp_table_name
  ,  created
  ,  pass
  )
  SELECT DISTINCT 
     ov.table_irid
  ,  ov.table_irid
  ,  rt.name
  ,  'XT_'||rt.name
  ,  'N'
  ,  1
  FROM   I$sdd_object_versions ov
  ,      rm_sql_tables         rt
  ,      I$sdd_wa_context      wx
  WHERE  wx.workarea_irid = wa_irid
  AND    rt.irid = ov.table_irid
  AND NOT EXISTS (
          SELECT NULL
          FROM   xtsys_tabs_exported   ta
          WHERE  ta.table_irid = ov.table_irid );
  
  rmdbg.trace(utility_name||proc || 'loc 6.3: '||sql%rowcount);  

  -- and dependendents of private objects
  LOOP
    i := i + 1;
    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass  )
    SELECT DISTINCT 
       rt.irid
    ,  ta.family_irid
    ,  rt.name
    , 'XT_' ||decode(sc2.is_owning_fk, 'O', rt.name||'_O', rt.name)
    , 'N'
    ,  i + 1
    FROM   rm_sql_constraints     sc1
    ,      rm_sql_constraints     sc2
    ,      xtsys_tabs_exported    ta
    ,      rm_sql_tables          rt
    WHERE  ta.table_irid = sc1.table_irid
    AND    sc2.r_constraint_name = sc1.constraint_name
    AND    sc2.is_owning_fk in ('Y','O')
              -- 'O' picks up dual class elements eg SDD_MUN
    AND    rt.irid = sc2.table_irid
    AND    ta.pass = i         
              -- elements from previous pass 
    AND 
         NOT EXISTS ( 
            SELECT NULL 
            FROM   xtsys_tabs_exported   ta1
            WHERE  ta1.family_irid = ta.family_irid and ta1.exp_table_name = 
                     'XT_' ||decode(sc2.is_owning_fk, 'O', rt.name||'_O', rt.name) );

    EXIT WHEN SQL%NOTFOUND;
    rmdbg.trace(utility_name||proc || 'loc 6.4:'||to_char(i + 1)||':'||sql%rowcount );

  END LOOP;

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

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

END get_unr_wa_tabs;
--------------------------------------------------------------------------------

PROCEDURE decomp_folder IS
--  PURPOSE
--    This procedure decomposes a folder hierarchy and inserts a row for each child folder
--    on to XTSYS_EXPORT_OBJECTS. Only child folders visible in the work area are extracted.
--    This procedure is called once at the start of processing and decomposes all marked folders
--    in one statement.
  proc   varchar2(100) := 'Decompose_Folder_Hierarchy ';
  i      number := 0;
 
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 9');  

  -- Initialise/mark folder(s) for decomposition
  UPDATE xtsys_export_objects
  SET status = 'X'
  WHERE object_type = 'F'
  AND fd_mode = 'D';

  LOOP
    FOR eo in (SELECT object_irid, import_order
               FROM xtsys_export_objects 
               WHERE object_type = 'F'
                 AND status = 'X'
              )
    LOOP

      INSERT INTO xtsys_export_objects (
         object_irid
      ,  object_ivid
      ,  object_type
      ,  default_import_name
      ,  wa_mode
      ,  fd_mode
      ,  status
      ,  import_order
      ,  notes
      )
      SELECT
         ov.irid
      ,  ov.ivid
      ,  'F'
      ,  ov.name
      ,  NULL
      ,  'S' 
         -- Sub-folder in hierarchy
      ,  'H'
      ,  nvl(eo.import_order,0) + rownum
      ,  'Folder ' || ov.name ||' included because recursive option was given'
      FROM   sdd_folders           ov
      ,      sdd_folder_members    fm
      WHERE  fm.folder_reference = eo.object_irid
      AND    fm.member_object = ov.irid;

      rmdbg.trace(utility_name||proc || 'loc 9.1:'||sql%rowcount );
    END LOOP;

    -- reset folders processed above so that they are not considered again
    UPDATE xtsys_export_objects
    SET status = 'N'
    WHERE status = 'X';

    -- Mark the newly inserted (on Hold) folders for decomposition
    UPDATE xtsys_export_objects
    SET status = 'X'
    WHERE status = 'H';
					 					 
    EXIT WHEN SQL%ROWCOUNT = 0;

  END LOOP;

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

  ck_util.do_commit (prollback_segment);

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

END decomp_folder;
--------------------------------------------------------------------------------

PROCEDURE get_fd_tabs IS
--  PURPOSE
--    This procedure identifies all the tables which store objects accessible
--    from a configuration.
--  NOTES
--    There is no concept of sharing elements between configurations.
--    Folders cannot contain configurations so SDD_CONFIGURATION_MEMBERS not relevant.
--    SDD_FOLDER_MEMBERS will be retrieved as a dependency of SDD_FOLDERS.
--    Folders can only be exported from within a work area so the code can use the
--    version resolved views.
--    SDD_OBJECT_VERSIONS will be required too.
  proc   varchar2(100) := 'Get_Folder_Tables ';
  i      number := 0;
 
BEGIN
  rmdbg.trace(utility_name||proc || 'loc 8');  

  -- Compile a list of tables containing the instance data to export 

  -- Object versions table is pass 0
  -- Tables on pass = 1 contain versionable objects
  -- This should retrieve sdd_folders
  -- Any tables with pass > 1 contain dependant objects

  IF (fd_base = FALSE) THEN
    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass
    )
    SELECT
       rt.irid
    ,  rt.irid
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'N'
    ,  0
    FROM   rm_sql_tables         rt
    WHERE  name = 'SDD_OBJECT_VERSIONS';

    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass
    )
    SELECT DISTINCT 
       ov.table_irid
    ,  ov.table_irid
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'N'
    ,  1
    FROM   sdd_object_versions   ov
    ,      rm_sql_tables         rt
    WHERE  ov.irid =   
           ( SELECT eo.object_irid   irid
             FROM   xtsys_export_objects  eo
             WHERE  eo.object_type = 'F' 
             AND    rownum = 1
           )
    AND    rt.irid = ov.table_irid;

    rmdbg.trace(utility_name||proc || 'loc 8.1: '||sql%rowcount);  
    fd_base := TRUE;
  END IF;

  -- Add any tables containing folder members into xtsys_tabs_exported 
  rmdbg.trace(utility_name||proc || 'loc 8.2');  

  INSERT INTO xtsys_tabs_exported (
     table_irid
  ,  family_irid
  ,  table_name
  ,  exp_table_name
  ,  created
  ,  pass
  )
  SELECT DISTINCT 
     ov.table_irid
  ,  ov.table_irid
  ,  rt.name
  ,  'XT_'||rt.name
  ,  'N'
  ,  1
  FROM   sdd_object_versions   ov
  ,      rm_sql_tables         rt
  ,      sdd_folder_members    fm
  WHERE  fm.folder_reference IN 
         ( SELECT eo.object_irid   irid
           FROM   xtsys_export_objects  eo
           WHERE  eo.object_type = 'F' 
         )
  AND    fm.member_object = ov.irid
  AND    rt.irid = ov.table_irid
  AND NOT EXISTS (
          SELECT NULL
          FROM   xtsys_tabs_exported   ta
          WHERE  ta.table_irid = ov.table_irid );

  rmdbg.trace(utility_name||proc || 'loc 8.3: '||sql%rowcount);  

  LOOP
    i := i + 1;
    INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass  )
    SELECT DISTINCT 
       rt.irid
    ,  ta.family_irid
    ,  rt.name
    , 'XT_' ||decode(sc2.is_owning_fk, 'O', rt.name||'_O', rt.name)
    , 'N'
    ,  i + 1
    FROM   rm_sql_constraints     sc1
    ,      rm_sql_constraints     sc2
    ,      xtsys_tabs_exported    ta
    ,      rm_sql_tables          rt
    WHERE  ta.table_irid = sc1.table_irid
    AND    sc2.r_constraint_name = sc1.constraint_name
    AND    sc2.is_owning_fk in ('Y','O') 
               -- 'O' picks up dual class elements eg SDD_MUN
    AND    rt.irid = sc2.table_irid
    AND    ta.pass = i          
              -- elements from previous pass 
    AND 
         NOT EXISTS ( 
            SELECT NULL 
            FROM   xtsys_tabs_exported   ta1
            WHERE  ta1.family_irid = ta.family_irid and ta1.exp_table_name = 
                     'XT_' ||decode(sc2.is_owning_fk, 'O', rt.name||'_O', rt.name) );

    EXIT WHEN SQL%NOTFOUND;
    rmdbg.trace(utility_name||proc || 'loc 8.4:'||to_char(i + 1)||':'||sql%rowcount );
  END LOOP;

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

  ck_util.do_commit (prollback_segment);

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

PROCEDURE ext_cmp_wa_data(wa_irid in number, checked_out in varchar2) IS
--  PURPOSE
--    This procedure uses xtsys_export_tabs to extract the instance data referenceable
--    from a compiled Work Area.
--    Tables and data are extracted in one CREATE TABLE table_name SELECT * from .....
--    statement.
--    For 'checked-out' (or new) objects only it is sufficient to drive off object_versions.
--  NOTES
--    There is no concept of sharing elements.
  proc   varchar2(100) := 'Extract Data based on Compiled Work Area ';
  current_wa number; 

  tmp VARCHAR2(2000);
  csr NUMBER;
  exe number;
  ind number;
  sql_statements sql_table;
  

  CURSOR get_object_versions IS
     -- Extract the rows from the Object Versions table.
     -- Use Workarea Context and apply an Access Rights filter.
     -- Once extracted, this table can be used to drive the remaining extractions 
     -- against the base tables rather than views.
     SELECT decode(ta1.created
	              ,'N','CREATE TABLE '||ta1.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta1.exp_table_name||' ') ||
            'SELECT * '||
            ' FROM I$'||ta1.table_name || ' xx ' ||
            ' WHERE xx.ivid IN ( SELECT wa.object_ivid '||
              '                    FROM i$sdd_wa_context wa'||
              '                    WHERE wa.workarea_irid = '||wa_irid ||
              '                      AND wa.wastebasket = ''N'' ) ' ||
              ' AND (EXISTS ( ' ||
              --     The folder/container
              '      SELECT NULL FROM sdw_access_rights ' ||
              '      WHERE xx.irid = object_reference ' ||
              '        AND grantee_reference IN (USER,''PUBLIC'') ) ' || 
              '   OR EXISTS ( ' ||
              --     Its contents
              '      SELECT NULL ' ||
              '      FROM   sdw_access_rights, i$sdd_folder_members ' ||
              '      WHERE  grantee_reference IN (USER,''PUBLIC'')  ' ||
              '        AND  object_reference  = folder_reference ' ||
              '        AND  xx.irid = member_object ) ) ' stmt,
          ta1.table_irid, ta1.created, rowid
     FROM   xtsys_tabs_exported  ta1
     WHERE  ta1.table_name = 'SDD_OBJECT_VERSIONS';

  CURSOR get_co_object_versions IS
     -- Alternatively extract Checked out or new objects but include all folders in the 
     -- Workarea so that paths are complete.
     -- Once extracted, this table can be used to drive the remaining extractions 
     -- against the base tables rather than views.
     SELECT decode(ta1.created
	              ,'N','CREATE TABLE '||ta1.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta1.exp_table_name||' ') ||
            'SELECT * '||
            ' FROM I$'||ta1.table_name || ' xx ' ||
            ' WHERE ( ' ||
            '       xx.state != ''I'' ' ||
            '   AND xx.ivid IN ' ||
            '       (SELECT wa.object_ivid '||
            '        FROM  i$sdd_wa_context wa'||
            '        WHERE wa.workarea_irid = '||wa_irid ||
            '          AND wa.wastebasket != ''Y'' )' ||
            '    OR xx.ivid IN ' ||
            '      (SELECT f.ivid ' ||
            '       FROM  i$sdd_folders f, i$sdd_wa_context wa '||
            '       WHERE f.ivid = wa.object_ivid AND wa.wastebasket = ''N'' ' ||
            '         AND wa.workarea_irid = '||wa_irid||') '||
            '       ) '  ||
              ' AND (EXISTS ( ' ||
              --     The folder/container
              '      SELECT NULL FROM sdw_access_rights ' ||
              '      WHERE xx.irid = object_reference ' ||
              '        AND grantee_reference IN (USER,''PUBLIC'') ) ' || 
              '   OR EXISTS ( ' ||
              --     Its contents
              '      SELECT NULL ' ||
              '      FROM   sdw_access_rights, i$sdd_folder_members ' ||
              '      WHERE  grantee_reference IN (USER,''PUBLIC'')   ' ||
              '        AND  object_reference  = folder_reference ' ||
              '        AND  xx.irid = member_object ) ) ' stmt,
          ta1.table_irid, ta1.created, rowid
     FROM   xtsys_tabs_exported  ta1
     WHERE  ta1.table_name = 'SDD_OBJECT_VERSIONS';

  -- Use the fact that XT_SDD_OBJECT_VERSIONS has just been created to speed up this step.
  -- Also use the IVID lookup to go direct to the base table ('I$').
  CURSOR get_versionable IS
     SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||' SELECT * FROM I$'||ta.table_name||' xx' 
          ||' WHERE xx.ivid IN ( SELECT ov.ivid FROM xt_sdd_object_versions ov '
          ||                'WHERE ov.table_irid = '|| ta.table_irid ||'  ) '  stmt,
            ta.table_irid, ta.created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass = 1;

  -- Assumes that all dependent data is only dependent on a versionable object
  -- Use the fact that XT_SDD_OBJECT_VERSIONS has just been created to speed up this step 
  CURSOR get_dependents IS
     SELECT  DISTINCT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM I$'||ta.table_name||' xx'         ||
            ' WHERE xx.parent_ivid in ( SELECT ov.ivid '     || 
                            'FROM xt_sdd_object_versions ov '|| 
                            ' ,   xtsys_tabs_exported    te '||
                            'WHERE te.family_irid = ov.table_irid '   ||
                            '  AND te.table_name = ''' || ta.table_name ||
                            ''' )' stmt,
            table_irid, created, table_name
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass > 1 and ta.table_name != 'SDD_FOLDER_MEMBERS'
     UNION
     SELECT  decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM I$'||ta.table_name||' xx'           ||
            ' WHERE xx.parent_ivid in ( SELECT ov.ivid '       || 
                            'FROM xt_sdd_object_versions ov ) '|| 
            '   AND xx.member_object IN (SELECT ov.irid '      ||
                            'FROM xt_sdd_object_versions ov ) 'stmt,
            table_irid, created, table_name
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass > 1 and ta.table_name = 'SDD_FOLDER_MEMBERS';

BEGIN
  rmdbg.trace(utility_name||proc || 'loc 30');  
  -- Set workarea context to workarea being exported
  -- Keep record of current workarea.
  current_wa:=jr_context.workarea;
  jr_context.set_workarea(wa_irid);
  
  -- Extract all versionable instance data 
  rmdbg.trace(utility_name||proc || 'loc 31');  
  -- Initialize nested table to hold SQL used to extract data
  sql_statements := sql_table();
  -- Get the SQL statements to generate the export data
  IF checked_out = 'Y' THEN
    FOR i in get_co_object_versions LOOP

      sql_statements.extend;
      sql_statements(sql_statements.last):=i.stmt;

	if i.created='N' then
        execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where rowid = ''' || i.rowid || '''' );
	end if;

      rmdbg.trace(utility_name||proc || 'loc 33');
    END LOOP;
  ELSE
    -- get object versions in either state
    FOR i in get_object_versions LOOP

      sql_statements.extend;
      sql_statements(sql_statements.last):=i.stmt;

	if i.created='N' then
        execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where rowid = ''' || i.rowid || '''' );
	end if;

      rmdbg.trace(utility_name||proc || 'loc 33');
    END LOOP;
  END IF;
  sql_statements.extend;
  sql_statements(sql_statements.last):=
              ' CREATE UNIQUE INDEX xt_sdd_objver1 ON xt_sdd_object_versions(table_irid,ivid)'||
              ' TABLESPACE ' ||tablespace_name;
  sql_statements.extend;
  sql_statements(sql_statements.last):= ' ANALYZE INDEX xt_sdd_objver1 COMPUTE STATISTICS ';

  FOR i in get_versionable LOOP
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;

	if i.created='N' then
        execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where rowid = ''' || i.rowid || '''' );
	end if;

    rmdbg.trace(utility_name||proc || 'loc 33.2');
  END LOOP;

  -- Extract all dependent instance data 
  rmdbg.trace(utility_name||proc || 'loc 35');  
  FOR i in get_dependents LOOP
    -- Store the statement in the nested table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;

    if i.created='N' then
      execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where table_name = ''' || i.table_name || '''' );
    end if;

    rmdbg.trace(utility_name||proc || 'loc 37');
  END LOOP;

  -- Execute each of the SQL statements in turn
  execute_sql_collection(sql_statements);

  
  -- Reset the workarea context
  jr_context.set_workarea(current_wa);

  rmdbg.trace(utility_name||proc || 'loc 38');
  ck_util.do_commit (prollback_segment);

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

END ext_cmp_wa_data;
--------------------------------------------------------------------------------

PROCEDURE ext_prvt_data (wa_irid      IN number
                        ,all_versions IN varchar2 default 'N') IS
--  PURPOSE
--    Extract the PRIVATE WA members in unresolved WA mode.
--    PRIVATE objects are identified by an entry in I$SDD_WA_CONTEXT where SPEC_ENTRY is null
--    i.e. there is no CONFIGURATION of PRIVATE entries.
  proc   varchar2(100) := 'Extract PRIVATE objects in Unresolved WA mode ';
 
  tmp VARCHAR2(2000);
  csr NUMBER;
  exe number;
  ind number;
  sql_statements sql_table;

  CURSOR get_object_versions IS
     SELECT 1, decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||'  ')
                     ||'SELECT * FROM I$'||ta.table_name||' xx'||
                       ' WHERE ' || decode(all_versions, 'Y', ' xx.irid ', ' xx.ivid ')|| 
                       '       IN ( SELECT  ' ||
                                    decode(all_versions, 'Y', ' ov.irid ',    ' ov.ivid ')|| 
                                       'FROM I$sdd_wa_context wx,I$SDD_OBJECT_VERSIONS ov ' ||
                                       'WHERE wx.workarea_irid = '||wa_irid ||
                                       ' AND   wx.object_ivid = ov.ivid ' ||
                                       ' AND  wx.wastebasket = ''N'' ' ||
                                       ' AND ( wx.spec_entry is null ' ||
                                       '      OR ' ||
                                       '       EXISTS (SELECT NULL ' ||
                                       '       FROM  i$sdd_workarea_spec_entries se ' ||
                                       '       WHERE se.seq = wx.spec_entry ' ||
                                       '        AND  se.rule_irid IS NOT NULL) ' ||
                                       '      ) ' || 
                            '      ) ' || 
                       decode(ta.created,'N', ' ',
                       ' AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
                                        ' WHERE y.IVID = xx.ivid ) ') ||
              ' AND (EXISTS ( ' ||
              --     The folder/container
              '      SELECT NULL FROM sdw_access_rights ' ||
              '      WHERE xx.irid = object_reference ' ||
              '        AND grantee_reference IN (USER,''PUBLIC'') ) ' || 
              '   OR EXISTS ( ' ||
              --     Its contents
              '      SELECT NULL ' ||
              '      FROM   sdw_access_rights, i$sdd_folder_members ' ||
              '      WHERE  grantee_reference IN (USER,''PUBLIC'') ' ||
              '        AND  object_reference  = folder_reference ' ||
              '        AND  xx.irid = member_object ) ) ' stmt,
             table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass = 0
     AND    ta.table_name != 'SDD_CONFIGURATIONS';

  CURSOR get_versionable IS
     -- Use the fact that XT_SDD_OBJECT_VERSIONS is created first, to 'drive' this step.
     -- Also use the IVID lookup to go direct to the base table ('I$').
     SELECT 2, decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||' SELECT * FROM I$'||ta.table_name||' xx' 
          ||' WHERE xx.ivid IN ( SELECT ov.ivid FROM xt_sdd_object_versions ov '
          ||                'WHERE ov.table_irid = '|| ta.table_irid ||'  ) '  stmt,
            ta.table_irid, ta.created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass = 1
     ORDER BY 1;
    
  -- Assumes that all dependent data is only dependent on a versionable object 
  -- When exporting checked_out objects only, both the folder
  -- and the member object will be checked out.
  -- Also extract version association rows for all extracted object versions.
  CURSOR get_dependents IS
     SELECT DISTINCT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ') ||
                       'SELECT * FROM I$'||ta.table_name||' xx'||
                       ' WHERE ' || decode(all_versions, 'Y', ' xx.pac_ref ', ' xx.parent_ivid ')|| 
                       '       IN ( SELECT  ' ||
                                    decode(all_versions, 'Y', ' xo.irid ',    ' xo.ivid ')|| 
                       '            FROM xt_sdd_object_versions  xo  ' ||
                       '            ,    xtsys_tabs_exported     te  ' ||
                       '            WHERE te.family_irid =  xo.table_irid  ' ||
                       '              AND te.table_name  = ''' ||ta.table_name||''') ' ||
                       decode(ta.created,'N', ' ',
                       ' AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
                                        ' WHERE y.IVID = xx.ivid ) ')  stmt,
              table_irid, created, ta.table_name
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass > 1
	 AND    ta.table_name not in (
                 'SDD_CONFIGURATION_MEMBERS'
                ,'SDD_VERSION_ASSOCIATIONS'
                ,'SDD_FOLDER_MEMBERS' )
     UNION
     SELECT  decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||' SELECT * FROM I$'||ta.table_name||' xx'           ||
            ' WHERE xx.parent_ivid in ( SELECT ov.ivid '       || 
            '                           FROM xt_sdd_object_versions ov ) '|| 
            '   AND xx.member_object IN (SELECT ov.irid '      ||
                            'FROM xt_sdd_object_versions ov ) 'stmt,
            table_irid, created, ta.table_name
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass > 1 and ta.table_name = 'SDD_FOLDER_MEMBERS'
	 UNION ALL
	 SELECT decode(ta.created
	              ,'N',' CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
		        ,'Y',' INSERT INTO '||ta.exp_table_name||' ')  ||
                         ' SELECT * FROM I$'||ta.table_name||' xx'||
                         ' WHERE EXISTS ( SELECT NULL FROM '|| 
                                 ' XT_SDD_OBJECT_VERSIONS xo'||
			               ' WHERE xo.ivid in (xx.from_ivid, xx.to_ivid) ) ' ||
                       decode(ta.created,'N', ' ',
                       ' AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
                                        ' WHERE y.to_ivid = xx.to_ivid ' || 
                                        '   AND y.from_ivid = xx.from_ivid)' ) stmt, 
                 table_irid, created, ta.table_name
       FROM   xtsys_tabs_exported  ta
       WHERE  ta.pass>1
	   AND  ta.table_name='SDD_VERSION_ASSOCIATIONS'; 

BEGIN
  rmdbg.trace(utility_name||proc || 'loc 50');  
  --Initialize nested table to hold SQL used to extract data
  sql_statements := sql_table();
  
  -- get object versions in either state
  FOR i in get_object_versions LOOP

    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;

    if i.created='N' then
        execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where rowid = ''' || i.rowid || '''' );
    end if;

    rmdbg.trace(utility_name||proc || 'loc 33');
  END LOOP;
  sql_statements.extend;
  IF all_versions = 'Y' THEN 
    sql_statements(sql_statements.last):=
              ' CREATE  INDEX xt_sdd_objver1 ON xt_sdd_object_versions(table_irid, irid) ' ||
              ' TABLESPACE ' ||tablespace_name;
  ELSE
    sql_statements(sql_statements.last):=
              ' CREATE UNIQUE INDEX xt_sdd_objver1 ON xt_sdd_object_versions(table_irid,ivid)'||
              ' TABLESPACE ' ||tablespace_name;
  END IF;
  sql_statements.extend;
  sql_statements(sql_statements.last):= ' ANALYZE INDEX xt_sdd_objver1 COMPUTE STATISTICS ';

  -- Extract all versionable instance data 
  rmdbg.trace(utility_name||proc || 'loc 51');  

  -- Get hold of the SQL statements to extract the data
  FOR i in get_versionable LOOP
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;

    if i.created='N' then
      execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where rowid = ''' || i.rowid || '''' );
    end if;

    rmdbg.trace(utility_name||proc || 'loc 53');
  END LOOP;

  -- Extract all dependent instance data
  rmdbg.trace(utility_name||proc || 'loc 55');  

  FOR i in get_dependents LOOP
    --Store the statement in the nested table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;

    if i.created='N' then
      execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where table_name = ''' || i.table_name || '''' );
    end if;
  END LOOP;
  --Set rollback segment...
  ck_util.do_commit (prollback_segment);

  --Execute each of the SQL statements in turn
  execute_sql_collection(sql_statements);
 
  rmdbg.trace(utility_name||proc || 'loc 58');
  ck_util.do_commit (prollback_segment);

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

END ext_prvt_data;
--------------------------------------------------------------------------------

PROCEDURE ext_cf_data (cf_ivid      IN number
                      ,all_versions IN varchar2 default 'N') IS
--  PURPOSE
--    This procedure uses xtsys_export_tabs to extract the instance data referenceable
--    from a Configuration (e.g. the configurations wholly or partly composing a Workarea). 
--    Tables and data are extracted in one CREATE TABLE table_name SELECT * from .....
--    statement.
  proc   varchar2(100) := 'Extract Data based on Configuration ';
 
  tmp VARCHAR2(2000);
  csr NUMBER;
  exe number;
  ind number;
  sql_statements sql_table;

  CURSOR get_config_members IS
    SELECT decode(ta.created
	           ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ') ||
                     ' SELECT * FROM I$'||ta.table_name||' xx'||
		         ' WHERE xx.config_ivid = '||cf_ivid  ||
              ' AND (EXISTS ( ' ||
              --     The folder/container
              '      SELECT NULL FROM sdw_access_rights ' ||
              '      WHERE xx.object_irid = object_reference ' ||
              '        AND grantee_reference IN (USER,''PUBLIC'') ) ' || 
              '   OR EXISTS ( ' ||
              --     Its contents
              '      SELECT NULL ' ||
              '      FROM   sdw_access_rights, i$sdd_folder_members ' ||
              '      WHERE  grantee_reference IN (USER,''PUBLIC'')  ' ||
              '        AND  object_reference  = folder_reference ' ||
              '        AND  xx.object_irid = member_object ) ) ' stmt,
                 table_irid, created, rowid
       FROM  xtsys_tabs_exported  ta
       WHERE  ta.pass>1
	    AND  ta.table_name='SDD_CONFIGURATION_MEMBERS';

  CURSOR get_versionable IS
     SELECT decode(ta.created
	    ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
          ,'Y','INSERT INTO '||ta.exp_table_name||'  ') ||
               ' SELECT * FROM I$'||ta.table_name||' xx'||
               ' WHERE '|| decode(all_versions, 'Y', ' xx.irid ', ' xx.ivid ')||
               '      IN ( SELECT '|| 
                           decode(all_versions, 'Y', ' cm.object_irid ', ' cm.object_ivid  ')||                                   
               '           FROM xt_sdd_configuration_members  cm '||
               '           WHERE cm.config_ivid = '||cf_ivid||')'||
                   decode(ta.created,'N', ' ',
               '   AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
               '                    WHERE y.IVID = xx.ivid ) ')  stmt, 
             table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass in (0, 1)
     AND    ta.table_name != 'SDD_CONFIGURATIONS'
     UNION  
       -- get the Congfiguration row itself
     SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||'  ')
                     ||'SELECT * FROM I$'||ta.table_name||' xx'||
                       ' WHERE ivid = '||cf_ivid||
                       ' AND  EXISTS ( ' ||
                         --     The folder/container/configuration
                       '      SELECT NULL FROM sdw_access_rights ' ||
                       '      WHERE xx.irid = object_reference ' ||
                       '        AND grantee_reference IN (USER,''PUBLIC'') ) ' || 
                         decode(ta.created,'N', ' ',
                       ' AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
                                        ' WHERE y.IVID = xx.ivid ) ')  stmt, 
             table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta 
     WHERE  ta.table_name = 'SDD_CONFIGURATIONS';

-- Need to get the Object Version row for the configuration itself
CURSOR get_config_objver IS
     SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||'  ')
                     ||'SELECT * FROM I$'||ta.table_name||' xx'||
                       ' WHERE ivid = '||cf_ivid||
                       ' AND  EXISTS ( ' ||
                         --     The folder/container/configuration
                       '      SELECT NULL FROM sdw_access_rights ' ||
                       '      WHERE xx.irid = object_reference ' ||
                       '        AND grantee_reference IN (USER,''PUBLIC'') ) ' || 
                         decode(ta.created,'N', ' ',
                       ' AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
                                        ' WHERE y.IVID = xx.ivid ) ')  stmt,
             table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_OBJECT_VERSIONS';

    
  -- Assumes that all dependent data is only dependent on a versionable object 
  CURSOR get_dependents IS
     SELECT DISTINCT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
                     ||'SELECT * FROM I$'||ta.table_name||' xx'||
               ' WHERE '|| decode(all_versions, 'Y', ' xx.pac_ref ', ' xx.parent_ivid ')||
               '      IN ( SELECT '|| 
                           decode(all_versions, 'Y', ' cm.object_irid ', ' cm.object_ivid  ')||                                   
               '           FROM xt_sdd_configuration_members  cm '||
               '           WHERE cm.config_ivid = '||cf_ivid||')'||
                       decode(ta.created,'N', ' ',
                       ' AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
                                        ' WHERE y.IVID = xx.ivid ) ')  stmt,
              table_irid, table_name, created
       FROM   xtsys_tabs_exported  ta
       WHERE  ta.pass > 1
	 AND    ta.table_name not in (
                 'SDD_CONFIGURATION_MEMBERS'
                ,'SDD_VERSION_ASSOCIATIONS')
	 UNION ALL
	 SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
		        ,'Y','INSERT INTO '||ta.exp_table_name||' ')  ||
              DECODE (all_versions, 'Y',
                         'SELECT * FROM I$'||ta.table_name||' xx'||
                          ' WHERE  xx.irid '||
                          '      IN ( SELECT cm.object_irid ' ||
                          '           FROM xt_sdd_configuration_members  cm '||
			        '           WHERE '||cf_ivid|| ' = cm.config_ivid ) ' ||
                          decode(ta.created,'N', ' ',
                          ' AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
                          '           WHERE y.to_ivid = xx.to_ivid ' || 
                          '             AND y.from_ivid = xx.from_ivid)' ),
                                  -- otherwise all_versions 'N'
                         'SELECT * FROM I$'||ta.table_name||' xx'||
                         ' WHERE xx.from_ivid '||
                         '      IN ( SELECT cm.object_ivid '||
                         '           FROM xt_sdd_configuration_members  cm '||
		 	       '           WHERE '||cf_ivid|| ' = cm.config_ivid ) ' ||
                         decode(ta.created,'N', ' ',
                         ' AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
                         '          WHERE y.to_ivid = xx.to_ivid ' || 
                         '            AND y.from_ivid = xx.from_ivid)' ) ||
                         ' UNION ' ||
                         'SELECT * FROM I$'||ta.table_name||' xx'||
                         ' WHERE xx.to_ivid '||
                         '      IN ( SELECT cm.object_ivid '||
                         '           FROM xt_sdd_configuration_members  cm '||
		 	       '           WHERE '||cf_ivid|| ' = cm.config_ivid ) ' ||
                         decode(ta.created,'N', ' ',
                         ' AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
                         '          WHERE y.to_ivid = xx.to_ivid ' || 
                         '            AND y.from_ivid = xx.from_ivid)' ) 
                    ) stmt, 
                 table_irid, table_name, created
       FROM  xtsys_tabs_exported  ta
      WHERE  ta.pass>1
	    AND  ta.table_name='SDD_VERSION_ASSOCIATIONS'; 

-- Need to get the Version Association rows for the configuration itself
CURSOR get_config_ver_ass IS
     SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||'  ')
                     ||' SELECT * FROM I$'||ta.table_name||' xx' ||
                       ' WHERE  '||cf_ivid|| ' IN (xx.from_ivid, xx.to_ivid) ' ||
                       ' AND  EXISTS ( ' ||
                         --     The folder/container/configuration
                       '      SELECT NULL FROM sdw_access_rights ' ||
                       '      WHERE xx.irid = object_reference ' ||
                       '        AND grantee_reference IN (USER,''PUBLIC'') ) ' ||
                         decode(ta.created,'N', ' ',
                       ' AND NOT EXISTS ( SELECT NULL FROM '||ta.exp_table_name|| ' y '||
                       ' WHERE y.from_ivid = xx.from_ivid ' ||
                       '   AND xx.to_ivid = y.to_ivid' ||
                       '   AND xx.edge_kind = y.edge_kind ) ')  stmt,
             table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_VERSION_ASSOCIATIONS';

BEGIN
  rmdbg.trace(utility_name||proc || 'loc 50');  
  --Initialize nested table to hold SQL used to extract data
  sql_statements := sql_table();
  
  -- Extract all versionable instance data 
  rmdbg.trace(utility_name||proc || 'loc 51');  

  -- Get hold of the SQL statements to extract the data
  FOR i in get_config_members LOOP
    --Store the statement in the table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;
    if i.created='N' then
      execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where rowid = ''' || i.rowid || '''' );
    end if;
  END LOOP;
  IF index_exists('XT_SDD_CFGMEM_CFG_IVID') THEN
    NULL;
  ELSIF all_versions = 'Y' THEN
    sql_statements.extend;
    sql_statements(sql_statements.last):=
      ' CREATE INDEX xt_sdd_cfgmem_cfg_ivid ON xt_sdd_configuration_members(config_ivid, object_irid)'||
      ' TABLESPACE ' ||tablespace_name;
  ELSE
    sql_statements.extend;
    sql_statements(sql_statements.last):=
      ' CREATE INDEX xt_sdd_cfgmem_cfg_ivid ON xt_sdd_configuration_members(config_ivid, object_ivid)'||
      ' TABLESPACE ' ||tablespace_name;
  END IF;
  sql_statements.extend;
  sql_statements(sql_statements.last):= ' ANALYZE INDEX xt_sdd_cfgmem_cfg_ivid COMPUTE STATISTICS ';

  FOR i in get_versionable LOOP
    --Store the statement in the table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;
    if i.created='N' then
      execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where rowid = ''' || i.rowid || '''' );
    end if;

  END LOOP;

  FOR i in get_config_objver LOOP
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;    	
    if i.created='N' then
      execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where rowid = ''' || i.rowid || '''' );
    end if;
  END LOOP;

  -- Extract all dependent instance data
  rmdbg.trace(utility_name||proc || 'loc 55');  

  FOR i in get_dependents LOOP
    --Store the statement in the nested table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;

    if i.table_name='SDD_VERSION_ASSOCIATIONS' then
      execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where table_name = ''' || i.table_name || '''' );
    end if;
    rmdbg.trace(utility_name||proc || 'loc 57');
  END LOOP;

  FOR i in get_config_ver_ass LOOP
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;
    	
    if i.created='N' then
      execute_sql(
        ' update xtsys_tabs_exported ' ||
        ' set created=''Y'' '          ||
        ' where rowid = ''' ||i.rowid ||'''');
    end if;
  END LOOP;

  --Execute each of the SQL statements in turn
  execute_sql_collection(sql_statements);
 
  rmdbg.trace(utility_name||proc || 'loc 58');
  ck_util.do_commit (prollback_segment);

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

END ext_cf_data;
--------------------------------------------------------------------------------

PROCEDURE ext_unr_wa_data (wa_irid IN number, all_versions IN varchar2 default 'N') IS
--  PURPOSE
--    This procedure uses xtsys_export_tabs to extract the instance data forming the basis of an
--    unresolved work area.
--    Tables and data are extracted in one CREATE TABLE table_name SELECT * from .....
--    statement.
  proc   varchar2(100) := 'Extract Data based on Unresolved Work Area ';
  
  csr number;
  exe number;
  tmp VARCHAR2(2000);   
  tmp2 VARCHAR2(2000);   
  ind number;
  sql_statements sql_table;  
  
  CURSOR get_configs IS
     SELECT config_ivid
     FROM   I$SDD_WORKAREA_SPEC_ENTRIES
     WHERE  workarea_irid = wa_irid
       AND  config_ivid IS NOT NULL;

  CURSOR get_base_tabs IS
     SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM I$'||ta.table_name||' xx' ||
            ' WHERE IRID = '||to_char(wa_irid) stmt
          , table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_WORKAREAS'
	 UNION ALL
     SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM '||ta.table_name||' xx' ||
            ' WHERE WORKAREA_IRID = '||to_char(wa_irid) ||
            ' AND   SPEC_ENTRY IS NULL ' stmt
          , table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'I$SDD_WA_CONTEXT'
	 UNION ALL
	 SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM '||ta.table_name||' xx' ||
          -- Watchout: view SDD_WORKAREA_SPEC_ENTRIES view column order different to
          -- table order in D22 !!
            ' WHERE WORKAREA_IRID = '||to_char(wa_irid) stmt
          , table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_WORKAREA_SPEC_ENTRIES'
	 UNION ALL
	 SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM I$'||ta.table_name||' xx ' ||
            ' WHERE EXISTS (SELECT NULL FROM I$SDD_WORKAREA_SPEC_ENTRIES E' ||
                           ' WHERE E.WORKAREA_IRID = '||to_char(wa_irid)  ||
                           ' AND   E.CONFIG_IVID = xx.IVID )' stmt
          , table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_CONFIGURATIONS'
	 UNION ALL
	 SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM I$'||ta.table_name||' xx ' ||
            ' WHERE EXISTS (SELECT NULL FROM I$SDD_WORKAREA_SPEC_ENTRIES E' ||
                           ' WHERE E.WORKAREA_IRID = '||to_char(wa_irid)  ||
                           ' AND   E.RULE_IRID = xx.IRID )' stmt
         , table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_RULES'
	 UNION ALL
	 SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM '||ta.table_name||' xx ' ||
            ' WHERE EXISTS (SELECT NULL FROM I$SDD_WORKAREA_SPEC_ENTRIES E ' ||
                           ' WHERE E.WORKAREA_IRID = '||to_char(wa_irid)   ||
                           ' AND  xx.ENTRY_SEQ  = E.SEQ )' stmt
          , table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'I$SDD_RULE_PARAM_VALUES'
	 UNION ALL
	 SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM '||ta.table_name||' xx ' ||
            ' WHERE EXISTS (SELECT NULL FROM I$SDD_WORKAREA_SPEC_ENTRIES E ' ||
                           '                ,I$SDD_RULES               R ' ||
                           ' WHERE E.WORKAREA_IRID = '||to_char(wa_irid)   ||
                           ' AND   E.RULE_IRID = R.IRID  '                 ||
                           ' AND  xx.RULE_IRID = R.IRID )' stmt
          , table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'I$SDD_RULE_PARAMS';


  CURSOR rule_mapping IS
     SELECT  ' INSERT INTO XTSYS_IMPORT_IRID_MAPPING '
          ||' SELECT IRID, NULL, NULL, NULL, '
          ||  ta.table_irid 
          ||' FROM I$'||ta.table_name||' xx ' ||
            ' WHERE EXISTS (SELECT NULL FROM I$SDD_WORKAREA_SPEC_ENTRIES E' ||
                           ' WHERE E.WORKAREA_IRID = '||to_char(wa_irid)  ||
                           ' AND   E.RULE_IRID = xx.IRID )' stmt
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_RULES';

  CURSOR workarea_spec_mapping IS
     SELECT  ' INSERT INTO XTSYS_IMPORT_IRID_MAPPING '
          ||' SELECT seq, NULL, NULL, NULL, '
          ||  ta.table_irid 
          ||' FROM I$SDD_WORKAREA_SPEC_ENTRIES E' 
          ||' WHERE E.WORKAREA_IRID = '||to_char(wa_irid) stmt
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_WORKAREA_SPEC_ENTRIES';

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

  --Extract workarea tables (I$SDD_WORKAREAS,I$SDD_WORKAREA_SPEC_ENTRIES)

  --Initialize nested table to hold SQL used to extract data
  sql_statements := sql_table();

  FOR i in get_base_tabs LOOP
   	--Store the statement in the nested table
	sql_statements.extend;
	sql_statements(sql_statements.last):=i.stmt;
	
	if i.created='N' then
      execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where rowid = ''' || i.rowid || '''' );
	end if;
    rmdbg.trace(utility_name||proc || 'loc 57');
  END LOOP;
  
  tmp := null;
  tmp2:= null;
  OPEN rule_mapping;
  FETCH rule_mapping into tmp;
  CLOSE rule_mapping;
  IF tmp is not null THEN 
    rmdbg.trace(utility_name||proc || 'loc 40c Statement:');  
    rmdbg.trace(substr(tmp,1,255));
    if (length(tmp) > 255 ) then  
      rmdbg.trace(substr(tmp,256,255));
    end if;  
    sql_statements.extend;
    sql_statements(sql_statements.last):= tmp;
  END IF;
  tmp := null;
  tmp2:= null;
  OPEN workarea_spec_mapping;
  FETCH workarea_spec_mapping into tmp;
  CLOSE workarea_spec_mapping;
  IF tmp is not null THEN 
    rmdbg.trace(utility_name||proc || 'loc 40d Statement:');  
    rmdbg.trace(substr(tmp,1,255));
    if (length(tmp) > 255 ) then  
      rmdbg.trace(substr(tmp,256,255));
    end if;  
    sql_statements.extend;
    sql_statements(sql_statements.last):= tmp;
  END IF;

  --Set rollback segment...
  ck_util.do_commit (prollback_segment);

  --Execute each of the SQL statements in turn
  rmdbg.trace('loc 40e executing SQL... ');
  execute_sql_collection(sql_statements);

  ext_prvt_data(wa_irid, all_versions);
	  
  -- Extract all versionable and dependent instance data from all configurations 
  -- on which the work area is based 
  FOR i in get_configs LOOP
    rmdbg.trace(utility_name||proc || 'loc 41 Config ivid: '||i.config_ivid);  
    ext_cf_data(i.config_ivid, all_versions);
  END LOOP;

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

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

PROCEDURE ext_fd_data IS
--  PURPOSE
--    This procedure uses xtsys_export_tabs to extract the instance data referenceable
--    from a Folder.
--    PAC Tables and data are extracted in one CREATE TABLE table_name SELECT * from .....
--    statement.
--    SAC (Dependent) Tables are created earlier, the data is extracted via INSERTs
--  This procedure will process the FOLDER arguments in the 
--  XTSYS_EXPORT_OBJECTS table in one go.
-- Unlike WA level Export the query extraction of SDD_FOLDERS has an inbuilt
-- access rights filter so there is no need for further access rights checking.


  proc   varchar2(100) := 'Extract Data based on Folder ';
 
  tmp VARCHAR2(2000);
  csr NUMBER;
  exe number;  
  ind number;
  sql_statements sql_table;
  fldr_tbl_irid Number;



  CURSOR get_versionable IS
     SELECT 0, 'CREATE TABLE '||ta1.exp_table_name
                       ||' TABLESPACE '||tablespace_name||' AS '
             ||'SELECT * FROM '||ta1.table_name||' xx WHERE rownum < 1 ' stmt, 
     table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta1
     WHERE  ta1.pass = 0
       AND   ta1.created = 'N'
     UNION 
     SELECT 1, 'INSERT INTO '||ta1.exp_table_name
          ||   ' SELECT  * FROM '||ta1.table_name||' xx'
          ||   ' WHERE  xx.irid  = '|| eo.object_irid  stmt, 
     ta1.table_irid, ta1.created, ta1.rowid
     FROM   xtsys_tabs_exported  ta1
     ,      xtsys_export_objects eo
     WHERE  ta1.pass = 0
     AND    eo.object_type = 'F'
     AND    eo.fd_mode in ('D', 'T')
     UNION 
     SELECT 2, 'INSERT INTO '||ta1.exp_table_name
          ||   ' SELECT  * FROM '||ta1.table_name||' xx'
          ||   ' WHERE '
          ||   ' xx.irid IN ( SELECT fm.member_object '
          ||   ' FROM   sdd_folder_members   fm '
          ||   ' WHERE  fm.folder_reference IN '
          ||   ' (SELECT eo.object_irid FROM xtsys_export_objects eo ' 
          ||   ' WHERE eo.object_type = ''F'' ))' stmt, 
     table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta1
     WHERE  ta1.pass = 0
     UNION 
     SELECT 3, ' CREATE UNIQUE INDEX xt_sdd_objver1 ON ' || 
            ' xt_sdd_object_versions(table_irid,ivid)'||
            ' TABLESPACE ' ||tablespace_name   stmt,
            table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta1
     WHERE  ta1.table_name = 'SDD_OBJECT_VERSIONS' AND ta1.created = 'N'
       AND  NOT EXISTS (SELECT NULL FROM user_indexes WHERE index_name = 'XT_SDD_OBJVER1')
     UNION
     SELECT 4, ' ANALYZE INDEX xt_sdd_objver1 COMPUTE STATISTICS ' || ' ' stmt,
            table_irid, created, rowid
     FROM   xtsys_tabs_exported  ta1
     WHERE  ta1.table_name = 'SDD_OBJECT_VERSIONS' AND ta1.created = 'N'
     UNION
     SELECT 5, decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM I$' ||ta.table_name||' xx'||
            ' WHERE xx.ivid IN ( SELECT ov.ivid '||
                            'FROM  xt_sdd_object_versions  ov '||
                            'WHERE   ov.table_irid = '||ta.table_irid||' ) ' stmt,
     table_irid, created, rowid
     FROM   xtsys_tabs_exported ta
     WHERE  ta.pass = 1 
     ORDER BY 1;

   -- Assumes that all dependent data is only dependent on a versionable object
  CURSOR get_dependents IS
     SELECT DISTINCT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM I$'||ta.table_name||' xx'||
            ' WHERE xx.parent_ivid IN (SELECT ov.ivid '||
                             'FROM xt_sdd_object_versions  ov '||
                             'WHERE ov.table_irid = '|| ta.family_irid ||' ) ' stmt,
        table_irid, created
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass > 1
	 AND    ta.table_name!='SDD_FOLDER_MEMBERS'
     UNION ALL
     --Folder members are a special case...
     SELECT decode(ta.created
	              ,'N','CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name||' AS '
				  ,'Y','INSERT INTO '||ta.exp_table_name||' ')
          ||'SELECT * FROM '||ta.table_name||' xx'||
            ' WHERE xx.folder_reference IN (SELECT eo.object_irid FROM xtsys_export_objects eo ' ||
            ' WHERE eo.object_type = ''F'') ' stmt, 
        table_irid, created
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass > 1
	 AND    ta.table_name='SDD_FOLDER_MEMBERS';

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

  -- Extract all versionable instance data 
  rmdbg.trace(utility_name||proc || 'loc 61');  
  
  --Initialize nested table to hold SQL used to extract data
  sql_statements := sql_table();



  FOR i in get_versionable LOOP
    --Store the statement in the nested table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;

    if i.created='N' then
      execute_sql(
        ' update xtsys_tabs_exported '||
        ' set created=''Y'' '         ||
        ' where rowid = ''' || i.rowid || '''' );
    end if;
  END LOOP;

  rmdbg.trace(utility_name||proc || 'loc 64');
  ck_util.do_commit (prollback_segment);

  -- Extract all dependent instance data 
  rmdbg.trace(utility_name||proc || 'loc 65');  
  FOR i in get_dependents LOOP

    --Store the statement in the nested table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;

    rmdbg.trace(utility_name||proc || 'loc 67');
  END LOOP;

  --Set rollback segment...
  ck_util.do_commit (prollback_segment);

  --Execute each of the SQL statements in turn
  execute_sql_collection(sql_statements);
 

  execute_sql(
          ' INSERT INTO XT_SDD_OBJECT_VERSIONS '
        ||' SELECT * FROM sdd_object_versions ov '
        ||' WHERE ov.ivid IN      ( SELECT f.ivid FROM xt_sdd_folders f  )      '
        ||'  AND  ov.ivid NOT IN  ( SELECT o.ivid FROM xt_sdd_object_versions o '
        ||'                         WHERE  o.table_irid = '
        ||'                                (select table_irid from xtsys_tabs_exported'
        ||'                                 where  table_name = ''SDD_FOLDERS'') '
        ||'                       )' 
        ) ;


  rmdbg.trace(utility_name||proc || 'loc 68');
  ck_util.do_commit (prollback_segment);

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

END ext_fd_data;
--------------------------------------------------------------------------------

PROCEDURE  extract_meta_data(workarea IN Number) IS
   proc   varchar2(100) := 'Extract meta data ';
-- Extract all the schemas/meta data associated with a given Workarea.
-- Before instance data can be loaded/imported at a target Repository,
-- it is necessary to ensure the target can accomodate that data.
-- Loading of meta data and registration may be restricted to the owner
-- (owning schema) of the Repository.
voidb Boolean;
schema_list jr_rm_xt.jr_schema_list;

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

  schema_list := jr_rm_xt.get_schemas(jr_context.workarea);
  voidb := jr_rm_xt.extract_rm_to_xt(schema_list);

END extract_meta_data;
--------------------------------------------------------------------------------

PROCEDURE ext_no_fk_data(mode_tab_prefix IN varchar2 default NULL) IS
  proc   varchar2(100) := 'Extract NO OFK data ';

  -- Extract rows from 
  --    CK_APPLICATION_LOGIC
  --    RM_TEXT_LINES
  --    SDD_ERU
  --    SDD_PRU
  --    SDD_UES
  --    SDD_UPR
  -- These tables are unusual in that they do not have foreign key meta data.
  -- Instead we determin the rows to extract by joining the generic reference
  -- properties from these tables to the IRID, IVID mapping tables populated
  -- earlier in the export i.e. these tables are dependent on and only on the
  -- data extracted thus far.

  tmp VARCHAR2(2000);
  csr NUMBER;
  exe number;  
  ind number;
  sql_statements sql_table;

  CURSOR get_no_fk_data IS
     SELECT 'CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name
          ||' AS '
          ||'SELECT * FROM '||mode_tab_prefix||ta.table_name||' xx'
          ||' WHERE xx.parent_ivid IN ( SELECT m.ivid ' 
          ||                'FROM xt_sdd_object_versions m )' stmt,
            table_irid, created
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass = next_pass ;

BEGIN
  rmdbg.trace(utility_name||proc || 'loc 600');  
  INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass
    )
  SELECT
       rt.irid
    ,  -4
    ,  rt.name
    ,  'XT_'||rt.name
    ,  'Y'
    ,  next_pass                
         -- these can all be the same load order
  FROM   rm_sql_tables         rt
  WHERE  owned_by = 'NO OFK';


  sql_statements := sql_table();
  --Get the SQL statements to generate the export data
  FOR i in get_no_fk_data LOOP
    rmdbg.trace(utility_name||proc || 'loc 1000 ');    
    --Store the statement in the table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;
  END LOOP;

  next_pass := next_pass + 1; 
	
  --Execute each of the SQL statements in turn
  execute_sql_collection(sql_statements);

END ext_no_fk_data;
--------------------------------------------------------------------------------

PROCEDURE ext_branches IS
  proc   varchar2(100) := 'Extract Branches ';

  tmp VARCHAR2(2000);
  csr NUMBER;
  exe number;  
  ind number;
  sql_statements sql_table;

  CURSOR get_branch_data IS
     SELECT 'CREATE TABLE xt_sdd_branches TABLESPACE '||tablespace_name||' AS '
          ||'SELECT * FROM sdd_branches   xx '
          ||'WHERE EXISTS  (SELECT NULL '
          ||'               FROM sdd_branches b'
          ||'               ,    xt_sdd_version_associations  xva'
          ||'               WHERE b.branch_id = xva.branch_id ) '  stmt
     ,     'SDD_BRANCHES'                                          table_name
     ,     'XT_SDD_BRANCHES'                                       exp_table_name
     FROM   sys.dual
     WHERE  exists (select null from xtsys_tabs_exported where table_name = 'SDD_VERSION_ASSOCIATIONS');

BEGIN
  rmdbg.trace(utility_name||proc || 'loc 60');  
  sql_statements := sql_table();
  --Get the SQL statements to generate the export data
  FOR i in get_branch_data LOOP
    rmdbg.trace(utility_name||proc || 'loc 32 Statement:');    
    rmdbg.trace(i.stmt);
    --Store the statement in the table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;

  END LOOP;
  INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass
    )
  SELECT DISTINCT 
       rt.irid
    ,  -5
    ,  rt.table_name
    ,  'XT_'||rt.table_name
    ,  'Y'
    ,  next_pass
  FROM   rm_sql_tables         rt
  WHERE  table_name in ('SDD_BRANCHES');

  next_pass := next_pass + 1;
	
  rmdbg.trace(utility_name||proc || 'loc 33 rowcount : ' ||sql%rowcount);

  --Execute each of the SQL statements in turn
  execute_sql_collection(sql_statements);

END ext_branches;
--------------------------------------------------------------------------------

PROCEDURE ext_dependency_data IS
  proc   varchar2(100) := 'Extract dependency data ';

  -- Extract rows from 
  --    SDD_DEPENDENCIES
  --    SDD_DEPENDENCY_TYPES
  --    SDD_GLOBAL_NAMES
  --    SDD_GLOBAL_NAME_DOMAINS
  -- These tables do not have foreign key meta data.
  -- There is an extra complexity in that these are not version resolved. 
  -- Therefore all global names are extracted then global name domains which
  -- are referenced by a global name.
  -- Dependencies are then extracted by joining the generic reference
  -- properties from these tables to the IRID, IVID mapping tables populated
  -- earlier in the export i.e. these tables are dependent on and only on the
  -- data extracted thus far.

  tmp VARCHAR2(2000);
  csr NUMBER;
  exe number;  
  ind number;
  sql_statements sql_table;

  CURSOR get_dependencies IS
     SELECT 'CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name
          ||' AS '
          ||'SELECT * FROM '||ta.table_name||' xx'
          ||' WHERE xx.client_ivid IN ( SELECT m.old_ivid  ' 
          ||                'FROM xtsys_import_ivid_mapping m ) '
          ||' UNION '
          ||'SELECT * FROM '||ta.table_name||' xx'
          ||' WHERE xx.supplier_ivid IN ( SELECT m.old_ivid  ' 
          ||                'FROM xtsys_import_ivid_mapping m )' stmt,
            table_irid, created
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_DEPENDENCIES';

  -- must execute after 'get_dependencies'
  CURSOR get_dependency_types IS
     SELECT 'CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name
          ||' AS '
          ||'SELECT * FROM '||ta.table_name||' xx'
          ||' WHERE xx.irid IN ( SELECT m.dependency_type_irid ' 
          ||                'FROM xt_sdd_dependencies m ) ' stmt,
            table_irid, created
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_DEPENDENCY_TYPES';

  -- must execute after 'get_dependencies'
  CURSOR get_global_names IS
     SELECT 'CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name
          ||' AS '
          ||'SELECT * FROM '||ta.table_name||' xx' 
          ||' WHERE xx.ivid IN ( SELECT m.client_ivid ' 
          ||                'FROM xt_sdd_dependencies m ) '
          ||' UNION '
          ||'SELECT * FROM '||ta.table_name||' xx' 
          ||' WHERE xx.ivid IN ( SELECT m.supplier_ivid ' 
          ||                'FROM xt_sdd_dependencies m ) ' stmt,
            table_irid, created
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_GLOBAL_NAMES';

  CURSOR get_global_name_domains IS
     SELECT 'CREATE TABLE '||ta.exp_table_name||' TABLESPACE '||tablespace_name
          ||' AS '
          ||'SELECT * FROM '||ta.table_name||' xx'
          ||' WHERE xx.irid IN ( SELECT g.domain_irid  ' 
          ||                'FROM xt_sdd_global_names g )' stmt,
            table_irid, created
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name = 'SDD_GLOBAL_NAME_DOMAINS';

  CURSOR get_irid_ivid_values IS
     SELECT 
          'INSERT INTO XTSYS_IMPORT_IRID_MAPPING '
          ||' SELECT DISTINCT IRID, NULL, NULL, NULL, '
          || ta.table_irid 
          ||' FROM '||ta.exp_table_name||' xx' stmt1,
          'INSERT INTO XTSYS_IMPORT_IVID_MAPPING '
          ||' SELECT DISTINCT IVID, NULL, NULL, '
          || ta.table_irid ||', NULL'
          ||' FROM '||ta.exp_table_name||' xx' stmt2
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.table_name IN (
              'SDD_DEPENDENCIES', 'SDD_DEPENDENCY_TYPES'
             ,'SDD_GLOBAL_NAMES', 'SDD_GLOBAL_NAME_DOMAINS'
              );


BEGIN
  rmdbg.trace(utility_name||proc || 'loc 600');   
  INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass
    )
  SELECT
       rt.irid
    ,  -6
    ,  rt.table_name
    ,  'XT_'||rt.table_name
    ,  'Y'
    ,  decode (table_name, 'SDD_GLOBAL_NAME_DOMAINS', next_pass + 1
                         , 'SDD_DEPENDENCY_TYPES' , next_pass + 2
                         , 'SDD_GLOBAL_NAMES', next_pass +  3
                         , 'SDD_DEPENDENCIES', next_pass + 4                      
                         , next_pass)
  FROM   rm_sql_tables         rt
  WHERE  table_name in ('SDD_DEPENDENCIES', 'SDD_DEPENDENCY_TYPES' 
                      , 'SDD_GLOBAL_NAMES', 'SDD_GLOBAL_NAME_DOMAINS');

  next_pass := next_pass + 4;

  sql_statements := sql_table();
  --Get the SQL statements to generate the export data
  FOR i in get_dependencies LOOP
    rmdbg.trace(utility_name||proc || 'loc 2002 ');    
    --Store the statement in the table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;
  END LOOP;
  FOR i in get_dependency_types LOOP
    rmdbg.trace(utility_name||proc || 'loc 2003 ');    
    --Store the statement in the table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;
  END LOOP;
  FOR i in get_global_names LOOP
    rmdbg.trace(utility_name||proc || 'loc 2000 ');    
    --Store the statement in the table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;
  END LOOP;
  FOR i in get_global_name_domains LOOP
    rmdbg.trace(utility_name||proc || 'loc 2001 ');    
    --Store the statement in the table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt;
  END LOOP;
  FOR i in get_irid_ivid_values LOOP
    rmdbg.trace(utility_name||proc || 'loc 2004 ');    
    --Store the statement in the table
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt1;
    sql_statements.extend;
    sql_statements(sql_statements.last):=i.stmt2;
  END LOOP;
	
  --Execute each of the SQL statements in turn
  execute_sql_collection(sql_statements);

END ext_dependency_data;
--------------------------------------------------------------------------------

PROCEDURE ext_wastebasket IS
  proc   varchar2(100) := 'Extract Wastbasket enties ';
-- The contents of the Wastebasket are extracted.
-- These entries may be used to delete objects at the target Repository
-- if 'R'eplicate or 'M'erge modes are requested.


  tmp VARCHAR2(2000);
  csr NUMBER;
  exe number;  
  ind number;
  sql_statements sql_table;


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

  -- No entry in RM tables for wastebasket
  INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass
    )
  VALUES (
       -1
    ,  -1
    ,  'SDD_WASTEBASKET'
    ,  'XT_SDD_WASTEBASKET'
    ,  'Y'
    ,  next_pass);

    next_pass := next_pass + 1;

    sql_statements := sql_table();
    --Get the SQL statements to generate the export data
    sql_statements.extend;
    sql_statements(sql_statements.last):=
        'CREATE TABLE xt_sdd_wastebasket TABLESPACE '||tablespace_name 
      ||' AS SELECT * FROM sdd_wastebasket ';

    --Execute each of the SQL statements in turn
      rmdbg.trace(utility_name||proc || 'loc w33 Statement:');  
    execute_sql_collection(sql_statements);

END ext_wastebasket;


--------------------------------------------------------------------------------

PROCEDURE ext_access_rights IS
  proc   varchar2(100) := 'Extract Access Rights for Folders ';

  tmp VARCHAR2(2000);
  csr NUMBER;
  exe number;  
  ind number;
  sql_statements sql_table;

  CURSOR get_ar_data IS
     SELECT ' INSERT INTO xt_sdw_users '
          ||' SELECT * FROM sdw_users   xx '
          ||' WHERE EXISTS  (SELECT NULL '
          ||'               FROM sdw_access_rights ar, '
          || '                   xt_sdd_folders    xz '
          ||'               WHERE ar.object_reference = xz.irid '
          ||'               AND   xx.username = ar.grantee_reference )' 
           stmt1,
              ' INSERT INTO xt_sdw_access_rights  '
            ||' SELECT * FROM sdw_access_rights xx'
            ||' WHERE EXISTS (SELECT NULL '
            ||'               FROM xt_sdd_folders       xz '
            ||'               WHERE xx.object_reference = xz.irid )' 
           stmt2
     FROM xtsys_tabs_exported
     WHERE  table_name = 'SDD_FOLDERS'
     UNION ALL
     SELECT ' INSERT INTO xt_sdw_users  '
          ||' SELECT * FROM sdw_users   xx '
          ||' WHERE EXISTS  (SELECT NULL '
          ||'               FROM sdw_access_rights     ar, '
          || '                   xt_sdd_configurations xz '
          ||'               WHERE ar.object_reference = xz.irid '
          ||'               AND   xx.username = ar.grantee_reference )' 
           stmt1,
              ' INSERT INTO xt_sdw_access_rights  '
            ||' SELECT * FROM sdw_access_rights xx'
            ||' WHERE EXISTS (SELECT NULL '
            ||'               FROM xt_sdd_configurations   xz '
            ||'               WHERE xx.object_reference = xz.irid )' 
           stmt2
     FROM xtsys_tabs_exported
     WHERE  table_name = 'SDD_CONFIGURATIONS';


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

  -- Execute privilige on the ADT sdd_acc_priv_list is required for 
  -- export/import of access rights
  INSERT INTO xtsys_tabs_exported (
       table_irid
    ,  family_irid
    ,  table_name
    ,  exp_table_name
    ,  created
    ,  pass
    )
  SELECT DISTINCT 
       rt.irid
    ,  -7
    ,  rt.table_name
    ,  'XT_'||rt.table_name
    ,  'Y'
    ,  decode (table_name, 'SDW_USERS', next_pass + 1
                         , 'SDW_ACCESS_RIGHTS', next_pass + 2
                         , next_pass)
  FROM   rm_sql_tables         rt
  WHERE  table_name in ('SDW_USERS', 'SDW_ACCESS_RIGHTS');

  -- Default access rights are created for the user doing the Import.
  -- Access rights extraction for repository owner is only for completeness.
  IF SQL%ROWCOUNT = 2 THEN 
    next_pass := next_pass + 2;

    sql_statements := sql_table();
    --Get the SQL statements to generate the export data
    sql_statements.extend;
    sql_statements(sql_statements.last):=
        'CREATE TABLE xt_sdw_users TABLESPACE '||tablespace_name 
      ||' AS SELECT * FROM sdw_users WHERE rownum < 1 ';
    sql_statements.extend;
    sql_statements(sql_statements.last):=
        'CREATE TABLE xt_sdw_access_rights TABLESPACE '||tablespace_name 
      ||' AS SELECT * FROM sdw_access_rights WHERE rownum < 1 ';

    FOR i in get_ar_data LOOP
      rmdbg.trace(utility_name||proc || 'loc 32 Statement:');    
      rmdbg.trace(i.stmt1);
      --Store the statement in the table
      sql_statements.extend;
      sql_statements(sql_statements.last):=i.stmt1;
      sql_statements.extend;
      sql_statements(sql_statements.last):=i.stmt2;

    END LOOP;
	
    rmdbg.trace(utility_name||proc || 'loc 33 rowcount : ' ||sql%rowcount);

    --Execute each of the SQL statements in turn
    execute_sql_collection(sql_statements);
  END IF;

END ext_access_rights;
----------------------------------------------------------------------------------------
PROCEDURE init_mapping_tabs(inc_deps Varchar2) IS
  proc   varchar2(100) := 'init_mapping_tabs ';
  -- This procedure creates an IRID/IVID entry for every instance data row extracted 
  -- to the XT tables.
  -- This is the basis for mapping primary key and foreign key columns when the
  -- data is loaded during import.
  -- If requested, the dependency 

  CURSOR get_extracted_tables IS
     -- Pass = 1 excludes all the PAC object tables which will be picked up
     -- via SDD_OBJECT_VERSIONS (pass=0)
     SELECT 
          'INSERT INTO XTSYS_IMPORT_IRID_MAPPING '
          ||' SELECT DISTINCT IRID, NULL, NULL, NULL, '
          || ta.table_irid 
          ||' FROM '||ta.exp_table_name||' xx' stmt1,
          'INSERT INTO XTSYS_IMPORT_IVID_MAPPING '
          ||' SELECT DISTINCT IVID, NULL, NULL, '
          || ta.table_irid ||', NULL'
          ||' FROM '||ta.exp_table_name||' xx' stmt2
     FROM   xtsys_tabs_exported  ta
     WHERE  ta.pass >= 0 AND ta.pass != 1
       AND  ta.table_name not in ('SDD_VERSION_ASSOCIATIONS', 'SDD_CONFIGURATION_MEMBERS'
                                  ,'SDW_ACCESS_RIGHTS', 'SDW_USERS', 'SDD_CONFIGURATIONS')
     GROUP BY table_irid, exp_table_name;
     -- SDD_ATV is an arc, hence group by is essential here. Else will get uniqueness violation
   

BEGIN
  rmdbg.trace(utility_name||proc ||' Initialising mapping tables from extracted data ');  
  FOR i in get_extracted_tables LOOP
    execute_sql(i.stmt1);
    execute_sql(i.stmt2);
  END LOOP;
  IF inc_deps = 'Y' THEN
    ext_dependency_data;
  END IF;

END init_mapping_tabs;

---------------------------CKEXP70 MAIN-------------------------------------------------

BEGIN
 
  
  rmdbg.trace(utility_name||proc || 'is started....');
  dbms_output.put_line(utility_name||proc || 'is started.');
  ck_util.register(utility_name,'Init');
  --Set rollback segment.
  dbms_output.put_line('RBS : '||prollback_segment);
  ck_util.do_commit (prollback_segment);

  rmdbg.trace(utility_name||proc || 'loc 80');  
  dbms_output.put_line(utility_name||proc || 'loc 80, Tablespace name : '||tablespace_name );  
  if (tablespace_name IS NULL) then
    raise validation_failed;
  end if;

  -- truncate XTSYS_TABS_EXPORTED, XTSYS_IMPORT_IRID_MAPPING, XTSYS_IMPORT_IVID_MAPPING
  trunc_tabs;

  -- Drop other tables prefixed XT_ 
  drop_tabs;

  -- Get the Signature/Version of the Repository
  get_repos_version;
  -- and the names of registered schemas for inter repository compatibility checking
  get_reg_schema_info;


  rmdbg.trace(utility_name||proc || 'loc 81');  
  dbms_output.put_line(utility_name||proc || 'loc 81'); 
  
  -- Find out if repository is versioned and therefore whether 
  -- MANAGE workarea privilege needs to be checked for each time in loop
  select supports_version into l_versioned
  from   rm$repositories;   


  FOR i in obj_to_export LOOP
    rmdbg.trace(utility_name||proc || 'loc 82 object irid:'||to_char(i.irid)||' object ivid:'||to_char(i.ivid)||
                        ' object type:'||i.type||' work area mode:'||i.wa_mode||
                        ' folder mode:'||i.fd_mode ||' All Versions : ' ||i.all_versions||
                        ' Include Dependencies : '||i.include_deps);  
    dbms_output.put_line(utility_name||proc || 'loc 82 object irid:'||to_char(i.irid)||' object ivid:'||to_char(i.ivid)||
                        ' object type:'||i.type||' work area mode:'||i.wa_mode||
                        ' folder mode:'||i.fd_mode ||' All Versions : ' ||i.all_versions||
                        ' Include Dependencies : '||i.include_deps);  
    
    -- Bug fix 1879803 and 2203897.  
    -- Since bug fix 1553880 users can no longer export form the GLOBAL SHARED WORKAREA 
    -- in a Non-versioned repository.
    -- Am therefore adding a check for a Non-Versioned repository so that the MANAGE system privilege
    -- is not checked anymore in a Non-Versioned world.  Then a user can export anything that is visible to them.    
    
    if l_versioned = 'Y' then
    
      -- Check user has system privilege MANAGE... for Workarea, Configuration or Container
      jr_sys_privs.check_manage(user, i.ac_type_check);
      
    end if;
    

    -- Save the value for Include Dependencies for use beyond the Loop.
    -- May have to bring the extraction of dependencies inside this loop
    -- at some future point when striped export/import is supported.
    inc_deps := i.include_deps;

    rmdbg.trace(utility_name||proc || 'loc 82a');  
    -- Set status to FAILED - reset later if everything OK 
    execute_sql( ' UPDATE xtsys_export_objects '
                      ||' SET status = ''F'' '
                      ||' WHERE object_irid = ' || i.irid );

    rmdbg.trace(utility_name||proc || 'loc 82b');  
    ck_util.do_commit (prollback_segment);

    -- Attempt to lock object 
    ck_util.register(utility_name,'Lock Object');  
    obj_handle := lock_obj(i.irid, i.ivid, i.type);
    rmdbg.trace(utility_name||proc || 'loc 84');  
    rmdbg.trace(utility_name||proc || 'loc 84');  

    -- Reset rollback segment 
    ck_util.do_commit (prollback_segment);
    rmdbg.trace(utility_name||proc || 'loc 85');  
     
    ck_util.register(utility_name,'Lock Object');  

    IF ( i.type = 'W' AND i.wa_mode = 'C') THEN    
                                                -- Checked out Work Area 
      ck_util.register(utility_name,'Checked Out Work Area: ' || to_char(i.irid));  
      rmdbg.trace(utility_name||proc || 'loc 86');  
      get_cmp_wa_tabs(i.irid);

      precreate_xt_tables;
      rmdbg.trace(utility_name||proc || 'loc 89');  
      ext_cmp_wa_data(i.irid, 'Y');

    ELSIF ( i.type = 'W' AND i.wa_mode = 'R') THEN   
                                                  -- Compiled Work Area 
      ck_util.register(utility_name,'Compiled Work Area: ' || to_char(i.irid));  
      rmdbg.trace(utility_name||proc || 'loc 88');  
      get_cmp_wa_tabs(i.irid);

      precreate_xt_tables;
      rmdbg.trace(utility_name||proc || 'loc 89');  
      ext_cmp_wa_data(i.irid, 'N');
      
    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');  
      get_unr_wa_tabs(i.irid);

      precreate_xt_tables;
      rmdbg.trace(utility_name||proc || 'loc 91');  
      ext_unr_wa_data(i.irid, i.all_versions);
      ext_branches;
      
      -- B2291498 - not all versions of text extracted in unresolved wa mode
      l_all_versions := i.all_versions;

      
    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');  
      get_cf_tabs(i.ivid);

      precreate_xt_tables;
      rmdbg.trace(utility_name||proc || 'loc 93');  
      ext_cf_data(i.ivid, 'N');
      -- Don't take branches for configurations because assocations not extracted
    
    ELSIF (i.type = 'F' AND fd_processed = FALSE) THEN                         
                              -- Folder 
      -- SM bug fix 2038874 - Moved decomp_folder inside the loop. 
      -- Decompose folders 
      ck_util.register(utility_name,'Decompose Folders');  
      ck_util.init(utility_name,'Start');
      dbms_output.put_line('DECOMP_FOLDER');
      decomp_folder;
      ck_util.do_commit (prollback_segment);
      rmdbg.trace(utility_name||proc || 'loc 93.1');  
      dbms_output.put_line(utility_name||proc || 'loc 93.1');  

      -- Set status to FAILED for all sub-folders - reset later if everything OK 
      execute_sql( ' UPDATE xtsys_export_objects '
                        ||' SET status = ''F'' '
                        ||' WHERE object_type = ''F'' '
                        ||' AND   fd_mode = ''S'' '
                        ||' AND   status = ''N'' ');

      rmdbg.trace(utility_name||proc || 'loc 93.2');  
      ck_util.do_commit (prollback_segment);

      -- Process
      ck_util.register(utility_name,'Folder: ' || to_char(i.irid)||' : '||to_char(i.ivid));
      rmdbg.trace(utility_name||proc || 'loc 94');  
      get_fd_tabs;
      precreate_xt_tables;
      rmdbg.trace(utility_name||proc || 'loc 95');  
      ext_fd_data;

      -- Set status to PASS for all sub-folders processed
      execute_sql( ' UPDATE xtsys_export_objects '
                        ||' SET status = ''P'' '
                        ||' WHERE object_type = ''F'' '
                        ||' AND   fd_mode = ''S'' '
                        ||' AND   status = ''F'' ');

      rmdbg.trace(utility_name||proc || 'loc 95.2');  
      ck_util.do_commit (prollback_segment);

      fd_processed := TRUE;  -- All folders have been processed in one go. So ignore any further processing.
      
    ELSE 
      raise invalid_parameter;      
    END IF;

    rmdbg.trace(utility_name||proc || 'loc 96');  
    unlock_obj(obj_handle);


    rmdbg.trace(utility_name||proc || 'loc 97');  
    execute_sql( ' UPDATE xtsys_export_objects '
                      ||' SET status = ''P'' '
                      ||' WHERE object_irid = ' || i.irid );

   
    rmdbg.trace(utility_name||proc || 'loc 98');  
    ck_util.do_commit (prollback_segment);


    rmdbg.trace(utility_name||proc || 'loc 99');  
    IF i.extract_reg_metad = 'Y' THEN
      rmdbg.trace(utility_name||proc || 'loc 100 extract meta data ');  
      -- extract all the 'registered' schemas associated with the context Workarea
      -- which may be a prerequisite to load/migrate/merge the extracted instance data
      extract_meta_data(jr_context.workarea);
    END IF;
  END LOOP;

  -- get the 'pass' value for controlling load order of xtsys_tabs_exported
  -- for the remaining table data being extracted
  SELECT max(pass) + 1
  INTO   next_pass
  FROM   xtsys_tabs_exported;

  IF next_pass IS NULL THEN 
                            -- No objects can have been extracted 
    RAISE invalid_parameter;
  ELSE
    -- B2291498 - not all versions of text extracted in unresolved wa mode
    IF l_all_versions = 'Y' THEN
      ext_no_fk_data('I$');  
                     -- i.e. the uncomforities such as Text, Application Logic etc
    ELSE
      ext_no_fk_data(NULL);  
    END IF;
    ext_wastebasket;
    ext_access_rights;
  END IF;

  -- Update exported objects with count of rows of instance data 
  rmdbg.trace(utility_name||proc || 'loc 99a');  
  FOR i in obj_rows LOOP
    rmdbg.trace(utility_name||proc || 'loc 99b Statement:');  
    execute_sql( i.stmt );
  END LOOP;

  -- Delete any rows containing empty tables 
  execute_sql (
    ' DELETE FROM xtsys_tabs_exported ' ||
    ' WHERE row_cnt = 0 ' ) ;
  rmdbg.trace(utility_name||proc || 'loc 99d:'||sql%rowcount);  

  -- Drop any empty tables created by the export process
  drop_tabs;

  -- Initialise the IRID and IVID mapping tables with the extracted instance data.
  -- The mapping tables are used by Import to reset all 'GUID' columns.
  -- This procedure will also extract dependency data, if required, as extraction
  -- relies on IRID/IVID values for all extracted element types.
  init_mapping_tabs(inc_deps);

  -- set flag to indicate the presence of checked in objects to alert Import Wizard
  -- if importing to a non versioned Repository
  execute_sql (
    ' UPDATE xtsys_export_objects ' ||
    ' SET checked_in_obj_flag = ''Y'' ' ||
    ' WHERE EXISTS ( ' ||
    '   SELECT null ' ||
    '   FROM   xtsys_import_ivid_mapping, i$sdd_object_versions ' ||
    '   WHERE  ivid = old_ivid AND state = ''I'' ) ' );

  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 invalid_parameter THEN
      rmdbg.trace(utility_name||proc || 
         'loc 101: Invalid or missing parameter in XTSYS_EXPORT_OBJECTS');  
      rmmes.post(cda_error, invalid_param_err, 'XTSYS_EXPORT_OBJECTS');
      rmmes.post('ORA', SQLCODE);
      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
      rmdbg.trace(utility_name||proc || 'loc 102: Tablespace name is NULL');  
-- !!!!!!!!!!!!!!!!Handle this error properly !!!!!!!!!!!!!!!!!
      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;
    WHEN lock_failed THEN
      rmdbg.trace(utility_name||proc || 'loc 103');  
      -- attempt to unlock object 
      unlock_obj(obj_handle);
      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;
    WHEN unlock_failed THEN
      rmdbg.trace(utility_name||proc || 'loc 104');  
      rollback;
      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;
    WHEN jr_sys_privs.SYSTEM_PRIV_ERROR THEN
      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 OTHERS THEN
      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');  
      -- attempt to unlock object 
      unlock_obj(obj_handle);
      ck_util.init(utility_name,'Finish');
      rmdbg.trace(utility_name||proc || 'Finish');
      ck_util.register(utility_name,'Finish');
      rmdbg.flush_trace;
      raise;

END ckexp70;
------------------------------------MAIN----------------------------------------

BEGIN

  rmmes.clear;
  ckexp70(:tablespace_name, :prollback_segment);
--  :iserror := 0;
--  :sqlcode := 0;
  exception when others then
  :iserror := 1;
  :sqlcode := sqlcode;
--  dbms_output.put_line('Error return code ' || :sqlcode);
  :sqlerrm := SQLERRM(sqlcode);
--  dbms_output.put_line(:sqlerrm);

END;
