Document-ID:        13446.1
Subject:            Upgrading Menu Roles
Author:             DWSMITH
Last Modified:      16 Aug 94 


 
                    Upgrading Roles from Menu 5 to Forms 4.0
                    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There currently exists several bugs which do do not allow the roles used
in sqlmenu 5 to be upgraded to roles in Forms 4.0.

The sql script below will perform the upgrade as desired.

/* 
** MENUUPG.SQL - Upgrade Menu 5 roles to Forms 4.0
**
** Created 18/4/94 Darren Smith 
**                 Oracle Tools WWS
**
** This script will upgrade role information used in SQLMENU 5 to 
** Forms 4.0 Role information.  The script needs to be run by the 
** owner of the Forms and SQLMenu database tables (SYSTEM).  Both
** sets of tables must be within the same database (as per normal 
** upgrade to Forms 4.0)
**
*/

/* Create a temporary sequence */
create sequence frm40_grp_seq
/  
/* Delete all current roles (the forms upgrade does this as well) */
delete from frm40_mnugrp
/  
/* Upgrade the roles */
insert into frm40_mnugrp 
select frm40_grp_seq.nextval            grpid,
       group_name                       grpname,
       decode(debug_allowed,'Y',1,0) +
       decode(os_comm_allowed,'Y',2,0) +
       decode(bgm_allowed,'Y',4,0)      grpflag,
       null                             grpcmt,
       0                                cmtlen 
from   menu_b_group
/  
/* Drop the sequence */
drop sequence frm40_grp_seq
/
/* Delete current users */ 
delete from frm40_mnuuser
/
/* Upgrade the user information */
insert into frm40_mnuuser 
select f.grpid                          grpid,
       m.user_name                      username
from   menu_b_user m,
       frm40_mnugrp f
where  m.group_name = f.grpname
/
declare
  comment       varchar2(32000);
  current_group varchar2(30);
  current_id    number(10);
  cursor c1 is select g.group_name, t.object_text, m.grpid
               from   menu_b_group g,
                      menu_b_obj_text t,
                      frm40_mnugrp m
               where  m.grpname          = g.group_name
               and    g.object_text_id   = t.object_text_id
               and    t.application_name = 'MNUGROUPAPPLICATION'
               order by
                      g.group_name,
                      t.object_text_order;
begin
  
  /* Upgrade Comments on roles */
  current_group := '';
  for rec in c1 loop

    /* Are we starting a new group */
    if current_group != rec.group_name
    or current_group is null
    then

      /* Update if the old group was not null */
      if current_group is not null
      then
        update frm40_mnugrp
        set    grpcmt  = comment||chr(0),
               cmtlen  = length(comment) + 1
        where  grpid   = current_id;
      end if;
      comment       := '';
      current_group := rec.group_name;
      current_id    := rec.grpid;
    end if;

    comment := comment||' '||rec.object_text;

  end loop;

  /* Update the last role */
  if current_group is not null
  then
    update frm40_mnugrp
    set    grpcmt  = comment||chr(0),
           cmtlen  = length(comment) + 1
    where  grpid   = current_id;
  end if;
  commit;
end;
/