Liren Large Software Subsidy 5/05.iso/a/a523/43.ddi/CONVMENU.SQL

Text File | 1991-02-04 | 11.2 KB | 342 lines

Rem Copyright (c) 1989 by Oracle Corporation
Rem NAME
Rem    CONVMENU.SQL - convert 4.1 applications into 5.0 tables
Rem  FUNCTION
Rem  NOTES
Rem  MODIFIED
Rem   Giljum     05/29/90 - New table -- Menu_b_appl_grp
Rem   Giljum     05/15/90 - Bug39811 - remove set scan off call
Rem   Giljum     09/01/89 - Creation
Rem   Giljum     05/10/89 - Remove sequences
Rem   Giljum     03/28/89 - Creation
Rem

Set termout off
Set verify off
Set heading off
Set echo off
Spool convmenu.lis

connect system/manager;
Create sequence menu_sequence;



REM
REM SQL*Menu conversion from V4 to V5
REM
REM
REM         APPLICATION INFORMATION
REM

INSERT INTO MENU_B_APPL
          SELECT UPPER(APPLICATION_NAME), UPPER(APPLICATION_NAME),
                 UPPER(APPLICATION_NAME), CREATION_DATE, CREATOR,
                 VERSION_RELEASE_NR, LAST_RELEASE_DATE, MENU_DIRECTORY,
                 IDENTIFICATION
          FROM   MENU_APPLICATION
          WHERE  APPLICATION_NAME LIKE UPPER('&1');



REM
REM          APPLICATION GROUP INFORMATION
REM

INSERT INTO MENU_B_APPL_GRP
          SELECT UPPER(MENU_WORK_CLASS.APPLICATION_NAME),
                 UPPER(MENU_WORK_CLASS.APPLICATION_NAME) || '_' ||
                 MENU_WORK_CLASS.WORK_CLASS
          FROM   MENU_WORK_CLASS
          WHERE  APPLICATION_NAME LIKE UPPER('&1');



REM
REM          MENU INFORMATION
REM

INSERT INTO MENU_B_INFO
          SELECT UPPER(MENU_NAME), UPPER(APPLICATION_NAME),
                 TITLE, SUB_TITLE, BOTTOM_TITLE,
                 MENU_SEQUENCE.NEXTVAL
          FROM   MENU_INFO
          WHERE  APPLICATION_NAME LIKE UPPER('&1');



REM
REM          MENU OPTION INFORMATION
REM

INSERT INTO MENU_B_OPTION
          SELECT UPPER(MENU_NAME), UPPER(APPLICATION_NAME), OPTION_NUMBER,
                 UPPER(MENU_NAME) || '_' || OPTION_NUMBER, 'N', OPTION_TEXT,
                 MENU_SEQUENCE.NEXTVAL, COMMAND_TYPE, COMMAND_LINE
          FROM   MENU_OPTION
          WHERE  APPLICATION_NAME LIKE UPPER('&1');



REM
REM          PARAMETER INFORMATION
REM

INSERT INTO MENU_B_PARAM
          SELECT SUBSTITUTION_STRING, UPPER(APPLICATION_NAME), PAR_SIZE,
                 PAR_DEF, ECHO, MUST_FILL, RESPONSE_REQUIRED, UPPER_CASE,
                 MENU_SEQUENCE.NEXTVAL, PROMPT
          FROM   MENU_PARAM
          WHERE  APPLICATION_NAME LIKE UPPER('&1');



REM
REM          PARAMETER MENU CROSS REFERENCE
REM

INSERT INTO MENU_B_PARM_XREF
          SELECT UPPER(MENU_NAME), UPPER(APPLICATION_NAME),
                 SUBSTITUTION_STRING
          FROM   MENU_PARAM_XREF
          WHERE  APPLICATION_NAME LIKE UPPER('&1');



REM
REM          GROUP INFORMATION
REM

INSERT INTO MENU_B_GROUP
          SELECT UPPER(MENU_WORK_CLASS.APPLICATION_NAME) || '_' ||
                 MENU_WORK_CLASS.WORK_CLASS,
                 'Y','Y','Y',
                 MENU_SEQUENCE.NEXTVAL
          FROM   MENU_WORK_CLASS
          WHERE  APPLICATION_NAME LIKE UPPER('&1');



UPDATE MENU_B_GROUP
          SET    DEBUG_ALLOWED = 'N'
          WHERE  GROUP_NAME IN
                   (SELECT GROUP_NAME
                    FROM   MENU_B_GROUP, MENU_USER
                    WHERE  GROUP_NAME =
                           UPPER(APPLICATION_NAME) || '_' || WORK_CLASS
                    AND    UPPER(APPLICATION_NAME) LIKE UPPER('&1')
                    AND    MENU_USER.DEBUG_ALLOWED = 'N');



UPDATE MENU_B_GROUP
          SET    OS_COMM_ALLOWED = 'N'
          WHERE  GROUP_NAME IN
                   (SELECT GROUP_NAME
                    FROM   MENU_B_GROUP, MENU_USER
                    WHERE  GROUP_NAME =
                           UPPER(APPLICATION_NAME) || '_' || WORK_CLASS
                    AND    UPPER(APPLICATION_NAME) LIKE UPPER('&1')
                    AND    MENU_USER.OS_COMM_ALLOWED = 'N');



UPDATE MENU_B_GROUP
          SET    BGM_ALLOWED = 'N'
          WHERE  GROUP_NAME IN
                   (SELECT GROUP_NAME
                    FROM   MENU_B_GROUP, MENU_USER
                    WHERE  GROUP_NAME =
                           UPPER(APPLICATION_NAME) || '_' || WORK_CLASS
                    AND    UPPER(APPLICATION_NAME) LIKE UPPER('&1')
                    AND    MENU_USER.BGM_ALLOWED = 'N');





REM
REM          PRIVILEGES FOR EACH OPTION IN A MENU
REM

INSERT INTO MENU_B_PRIV
          SELECT MENU_SEQUENCE.NEXTVAL, 'MNU', UPPER(APPLICATION_NAME),
                 UPPER(MENU_NAME), OPTION_NUMBER
          FROM   MENU_OPTION
          WHERE  APPLICATION_NAME LIKE UPPER('&1');




REM
REM          GROUP PRIVILEGES
REM

INSERT INTO MENU_B_GRP_PRIV
          SELECT UPPER(W4.APPLICATION_NAME),
                 UPPER(W4.APPLICATION_NAME) || '_' || WORK_CLASS,
                 PRIVILEGE_ID
          FROM   MENU_B_PRIV V5, MENU_OPTION O4, MENU_WORK_CLASS W4
          WHERE  UPPER(O4.APPLICATION_NAME) LIKE UPPER('&1')
          AND    UPPER(V5.APPLICATION_NAME) = UPPER(O4.APPLICATION_NAME)
          AND    UPPER(V5.MENU_NAME) = UPPER(O4.MENU_NAME)
          AND    V5.OPTION_NUMBER = O4.OPTION_NUMBER
          AND    UPPER(O4.APPLICATION_NAME) = UPPER(W4.APPLICATION_NAME)
          AND    WORK_CLASS BETWEEN LOW_CLASS AND HIGH_CLASS ;



REM
REM          SQLMENU USERS
REM

INSERT INTO MENU_B_USER
          SELECT UPPER(APPLICATION_NAME) || '_' || WORK_CLASS, USER_NAME
          FROM   MENU_USER
          WHERE  APPLICATION_NAME LIKE UPPER('&1');




REM
REM          MENU OBJECT TEXT
REM

INSERT INTO MENU_B_OBJ_TEXT
          SELECT V5.APPLICATION_NAME, OBJECT_TEXT_ID, 1, DESCRIP_1 || '
'
          FROM   MENU_B_INFO V5, MENU_INFO V4
          WHERE  UPPER(V4.APPLICATION_NAME) LIKE UPPER('&1')
          AND    UPPER(V5.APPLICATION_NAME) = UPPER(V4.APPLICATION_NAME)
          AND    UPPER(V5.MENU_NAME) = UPPER(V4.MENU_NAME) ;

INSERT INTO MENU_B_OBJ_TEXT
          SELECT V5.APPLICATION_NAME, OBJECT_TEXT_ID, 2, DESCRIP_2 || '
'
          FROM   MENU_B_INFO V5, MENU_INFO V4
          WHERE  UPPER(V4.APPLICATION_NAME) LIKE UPPER('&1')
          AND    UPPER(V5.APPLICATION_NAME) = UPPER(V4.APPLICATION_NAME)
          AND    UPPER(V5.MENU_NAME) = UPPER(V4.MENU_NAME) ;

INSERT INTO MENU_B_OBJ_TEXT
          SELECT V5.APPLICATION_NAME, OBJECT_TEXT_ID, 3, DESCRIP_3 || '
'
          FROM   MENU_B_INFO V5, MENU_INFO V4
          WHERE  UPPER(V4.APPLICATION_NAME) LIKE UPPER('&1')
          AND    UPPER(V5.APPLICATION_NAME) = UPPER(V4.APPLICATION_NAME)
          AND    UPPER(V5.MENU_NAME) = UPPER(V4.MENU_NAME) ;

INSERT INTO MENU_B_OBJ_TEXT
          SELECT V5.APPLICATION_NAME, OBJECT_TEXT_ID, 4, DESCRIP_4 || '
'
          FROM   MENU_B_INFO V5, MENU_INFO V4
          WHERE  UPPER(V4.APPLICATION_NAME) LIKE UPPER('&1')
          AND    UPPER(V5.APPLICATION_NAME) = UPPER(V4.APPLICATION_NAME)
          AND    UPPER(V5.MENU_NAME) = UPPER(V4.MENU_NAME) ;

INSERT INTO MENU_B_OBJ_TEXT
          SELECT V5.APPLICATION_NAME, OBJECT_TEXT_ID,
                 HELP_SEQ_NR, HELP_TEXT || '
'
          FROM   MENU_B_OPTION V5, MENU_HELP V4
          WHERE  UPPER(V4.APPLICATION_NAME) LIKE UPPER('&1')
          AND    UPPER(V5.APPLICATION_NAME) = UPPER(V4.APPLICATION_NAME)
          AND    UPPER(V5.MENU_NAME) = UPPER(V4.MENU_NAME)
          AND    V5.OPTION_NUMBER = V4.OPTION_NUMBER ;

INSERT INTO MENU_B_OBJ_TEXT
          SELECT V5.APPLICATION_NAME, OBJECT_TEXT_ID, 1, HELP_TEXT || '
'
          FROM   MENU_B_PARAM V5, MENU_PARAM V4
          WHERE  UPPER(V4.APPLICATION_NAME) LIKE UPPER('&1')
          AND    UPPER(V5.APPLICATION_NAME) = UPPER(V4.APPLICATION_NAME)
          AND    V5.SUBSTITUTION_STRING = V4.SUBSTITUTION_STRING ;

INSERT INTO MENU_B_OBJ_TEXT
          SELECT 'MNUGROUPAPPLICATION', OBJECT_TEXT_ID, 1, DESCRIP_1 || '
'
          FROM   MENU_B_GROUP V5, MENU_WORK_CLASS V4
          WHERE  V5.GROUP_NAME =
                 UPPER(V4.APPLICATION_NAME) || '_' || V4.WORK_CLASS
          AND    UPPER(V4.APPLICATION_NAME) LIKE UPPER('&1');

INSERT INTO MENU_B_OBJ_TEXT
          SELECT 'MNUGROUPAPPLICATION', OBJECT_TEXT_ID, 2, DESCRIP_2 || '
'
          FROM   MENU_B_GROUP V5, MENU_WORK_CLASS V4
          WHERE  V5.GROUP_NAME =
                 UPPER(V4.APPLICATION_NAME) || '_' || V4.WORK_CLASS
           AND    UPPER(V4.APPLICATION_NAME) LIKE UPPER('&1');

INSERT INTO MENU_B_OBJ_TEXT
          SELECT 'MNUGROUPAPPLICATION', OBJECT_TEXT_ID, 3, DESCRIP_3 || '
'
          FROM   MENU_B_GROUP V5, MENU_WORK_CLASS V4
          WHERE  V5.GROUP_NAME =
                 UPPER(V4.APPLICATION_NAME) || '_' || V4.WORK_CLASS
          AND    UPPER(V4.APPLICATION_NAME) LIKE UPPER('&1');

INSERT INTO MENU_B_OBJ_TEXT
          SELECT 'MNUGROUPAPPLICATION', OBJECT_TEXT_ID, 4, DESCRIP_4 || '
'
          FROM   MENU_B_GROUP V5, MENU_WORK_CLASS V4
          WHERE  V5.GROUP_NAME =
                 UPPER(V4.APPLICATION_NAME) || '_' || V4.WORK_CLASS
          AND    UPPER(V4.APPLICATION_NAME) LIKE UPPER('&1');




REM  BACKGROUND
REM  ~~~~~~~~~~
REM  It is frequently the case in SQL*Menu 4.1 that all options in a
REM  particular application (root menu, and submenus) are available to
REM  everyone. Access to the application is controlled by the work class
REM  enrollment, but any authorized user can access every item on the
REM  menu.
REM
REM  Previously, in this special case, no Group Privilege information
REM  was converted and SQL*Menu 4.1 designers, expecting ALL users for
REM  that application to have access, received the 'No Active Items in
REM  Root Menu' message upon attempting to run the application.
REM
REM  METHOD
REM  ~~~~~~
REM  For each converted Menu 4.1 application matching the
REM  APPLICATION_NAME criteria and qualifying for the special case
REM  explained above, we assign access privileges on the first item of
REM  the root menu to all of the converted workclasses from the 4.1
REM  application.  Because of the privilege inheritance in SQL*Menu 5.0,
REM  it is enough to grant privilege to only one item on the root menu
REM  to all converted roles for a given application to regain the
REM  SQL*Menu 4.1 behaviour.

INSERT INTO MENU_B_GRP_PRIV
    SELECT V5_MBP.APPLICATION_NAME,
           UPPER(V4_MWC.APPLICATION_NAME) || '_' || V4_MWC.WORK_CLASS,
           MIN(V5_MBP.PRIVILEGE_ID)
      FROM MENU_B_PRIV     V5_MBP,
           MENU_WORK_CLASS V4_MWC
      WHERE V5_MBP.APPLICATION_NAME = V4_MWC.APPLICATION_NAME
        AND V5_MBP.MENU_NAME        = V5_MBP.APPLICATION_NAME
        AND V5_MBP.APPLICATION_NAME IN
          ( SELECT V4_MA.APPLICATION_NAME
               FROM MENU_APPLICATION V4_MA,
                    MENU_B_APPL      V5_MBA
               WHERE V4_MA.APPLICATION_NAME = V5_MBA.APPLICATION_NAME
               AND NOT EXISTS (SELECT V5_MBGP.PRIVILEGE_ID
                                  FROM MENU_B_GRP_PRIV V5_MBGP
                                  WHERE V5_MBGP.APPLICATION_NAME =
                                        V4_MA.APPLICATION_NAME)
                                  AND V4_MA.APPLICATION_NAME LIKE UPPER('&1') )
    GROUP BY V5_MBP.APPLICATION_NAME,
             UPPER(V4_MWC.APPLICATION_NAME)|| '_'||V4_MWC.WORK_CLASS;


commit;

Set verify on
Set heading on
Spool off
Set termout on