set heading off
set feedback off
set verify off
set pages 0
column nl newline
set lines 132
set scan off
PROMPT 
PROMPT    Generating script to migrate Oracle Designer sub-ordinate users...

set termout off

spool migrate_repos_privs.sql
PROMPT rem
PROMPT rem   PURPOSE : Migrates Repository sub-ordinate users from Oracle Designer 
PROMPT rem             Release 2.1.2/6.0 to a Designer 6.5 Repository
PROMPT rem
PROMPT rem
PROMPT rem   ****************************************************************************
PROMPT rem   * IMPORTANT : Please do not make changes to this script otherwise it will  *
PROMPT rem   *             not be supported.                                            *
PROMPT rem   *                                                                          *
PROMPT rem   * To execute this script on your Designer 6.5 Repository                   *
PROMPT rem   *                                                                          *
PROMPT rem   * Syntax, from within SQL*Plus connected as the Repository owner:          *
PROMPT rem   * SQL> @migrate_repos_privs.sql                                            *
PROMPT rem   *                                                                          *
PROMPT rem   ****************************************************************************
PROMPT
PROMPT 
PROMPT spool migrate_repos_privs.log
PROMPT
PROMPT prompt
PROMPT prompt
PROMPT prompt Migrating Repository sub-ordinate users to the Designer 6.5 Repository...
PROMPT
PROMPT   
PROMPT   -- Migrate sub-ordinate users for a Versioned Repository
select  ' ' nl,
	' ' nl,
	' prompt Creating sub-ordinate user ' || su.usr_oracle_username nl,
	'-- Decleration of local variables' nl,
	'declare rtnval number;' nl,  
	'begin ' nl,
	' ' nl,
	'if jr_sys_privs.IS_VERSIONED_REPOS() then ' nl,
	'   -- Insert sub-ordinate user ' || '''' || su.usr_oracle_username || '''' || ' of type ' || decode(su.usr_type,'M','Manager','U','User') nl,
	'   rtnval := jr_sys_privs.ins (' nl,
        '                                ''' || su.usr_oracle_username || '''' || ',' nl,
        '                                ''' || su.usr_full_user_name || '''' || ',' nl,
        '                                ''' || su.usr_desc || '''' || ',' nl,
	  '                                 sdd_sys_priv_list(' nl,
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Manage Workarea' nl,
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Manage Configuration' nl,
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Manage Container' nl,
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Manage User' nl,
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Manage Branch Label' nl,
        '                                    ''Y'' ' || ',' || '   -- Manage Dependency' nl,
	  '                                    ''Y'' ' || ',' || '   -- Manage VVC' nl,
	  '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Register Schema' nl,   
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Set Policy' nl,
        '                                    ''Y'' ' || ',' || '   -- Purge' nl,
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Force Delete' nl,
        '                                    ''Y'' ' || ',' || '   -- Global Purge 'nl,
        '                                    ''Y'' ' || ',' || '   -- Connect via Repository Object Navigator' nl,
        '                                    ''N'' ' || ',' || '   -- Connect via Repository Adminstration Utility' nl,
        '                                    ''Y'' ' || ',' || '   -- Connect via Matrix Diagrammer' nl,
        '                                    ''Y'' ' || ',' || '   -- Connect via Repository Reports' nl,
        '                                    ''Y'' ' || ',' || '   -- Connect via Command Line Interface' nl,
        '                                    ''Y'' ' || ',' || '   -- Connect via Version History Viewer' nl,
        '                                    ''Y'' ' || '));' ||' -- Connect via Version Event Viewer' nl,
	  'else ' nl,
	  ' ' nl,
        '   -- Migrate sub-ordinate users for a Non-Versioned Repository ' nl,
	  ' ' nl,
	  '   -- Insert sub-ordinate user ' || '''' || su.usr_oracle_username || '''' nl,
	  '   rtnval := jr_sys_privs.ins (' nl,
        '                                ''' || su.usr_oracle_username || '''' || ',' nl,
        '                                ''' || su.usr_full_user_name || '''' || ',' nl,
        '                                ''' || su.usr_desc || '''' || ',' nl,
	  '                                 sdd_sys_priv_list(' nl,
        '                                    ''N'' ' || ',' || '   -- Manage Workarea' nl,
        '                                    ''N'' ' || ',' || '   -- Manage Configuration' nl,
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Manage Container' nl,
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Manage User' nl,
        '                                    ''N'' ' || ',' || '   -- Manage Branch Label' nl,
        '                                    ''Y'' ' || ',' || '   -- Manage Dependency' nl,
        '                                    ''N'' ' || ',' || '   -- Manage VVC' nl,
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Register Schema' nl,   
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Set Policy' nl,
        '                                    ''N'' ' || ',' || '   -- Purge' nl,
        '                                    ''' || decode(su.usr_type,'M','Y', 'N') || ''' ' || ',' || '   -- Force Delete' nl,
        '                                    ''N'' ' || ',' || '   -- Global Purge 'nl,
        '                                    ''Y'' ' || ',' || '   -- Connect via Repository Object Navigator' nl,
        '                                    ''Y'' ' || ',' || '   -- Connect via Repository Adminstration Utility' nl,
        '                                    ''Y'' ' || ',' || '   -- Connect via Matrix Diagrammer' nl,
        '                                    ''Y'' ' || ',' || '   -- Connect via Repository Reports' nl,
        '                                    ''Y'' ' || ',' || '   -- Connect via Command Line Interface' nl,
        '                                    ''N'' ' || ',' || '   -- Connect via Version History Viewer' nl,
        '                                    ''N'' ' || '));' ||' -- Connect via Version Event Viewer' nl,
        'end if;' nl,
        'end;' nl,
        '/' nl
from sdw_users su
where nvl(su.usr_sdd_owner,'N') != 'Y';

prompt
prompt commit;;
prompt
PROMPT set termout on
PROMPT set verify on
PROMPT spool off
PROMPT prompt
PROMPT prompt
PROMPT prompt Completed migration of Repository sub-ordinate users.
PROMPT prompt 
PROMPT prompt  **************************************************************************************
PROMPT prompt  * IMPORTANT                                                                          *
PROMPT prompt  *                                                                                    *
PROMPT prompt  * Please check log file 'migrate_repos_privs.log' to ensure there were no errors     *
PROMPT prompt  * whilst creating the Repository sub-ordinate users on the Designer 6.5 Repository   *
PROMPT prompt  *                                                                                    *
PROMPT prompt  * If there are no errors, use the 'Maintain Users' option in the Repository          *
PROMPT prompt  * Administration Utility to check that the correct number of sub-ordinate users      *
PROMPT prompt  * and system privileges have been migrated. If this is satisfied run FULL RECONCILE. *
PROMPT prompt  *                                                                                    *
PROMPT prompt  **************************************************************************************
PROMPT prompt
PROMPT prompt

set termout on
set heading on
set feedback on
set verify on
set scan on
spool off
PROMPT
PROMPT    Completed generating script.
PROMPT 
PROMPT    ****************************************************************************
PROMPT    * Now execute the generated script on your Designer 6.5 Repository         *
PROMPT    *                                                                          *
PROMPT    * Syntax, from within SQL*Plus connected as the Repository Owner:          *
PROMPT    * SQL> @migrate_repos_privs.sql                                            *
PROMPT    *                                                                          *
PROMPT    ****************************************************************************
PROMPT
PROMPT

