create or replace package body odwapred
is
/*****************************************************************************************
Purpose         To implement the privilege palettes for odwapred

Usage

Remarks

Revision history
When        Who                                Construct
  Revision  What
------------------------------------------------------------------------------------------
$REVISION_HISTORY$
27-Mar-2003   Kannan Parthasarathy
              -B713248: Fixed.
10-Dec-2002   Kannan Parthasarathy
              -B2663635: Fixed Javascript function.
15-oct-2002   Kannan Parthasarathy
              - Hide Role management features that aren't possible in Designer
26-feb-2001   Lucas Jellema                     
  1.6         - issue 213: cannot edit Role Grant [odwa project/213%] - added a close_palette to procedure editRoleGrant 
23-feb-2001   Lucas Jellema                     
  1.4         - issue 210 - revoke privs under All Containers from PUBLIC - hard to reproduce, hard coded delete from sdw_access_rights as a last resort. 
14-feb-2001   Lucas Jellema                     
  1.3         - when you select folder or application system in All Containers node, no property palette is shown 
07-feb-2001   Lucas Jellema                     
  1.2         - slightly improved logging 
29-jan-2001   Lucas Jellema                     
  1.1         - role cannot be granted to itself 
19-jan-2001   Lucas Jellema                     
  1.0          
*****************************************************************************************/

--
-- private constants
--
REVISION_LABEL        constant varchar2(30) := '$x.y::1.6       $';
PACKAGE_NAME          constant varchar2(30) := 'ODWAPRED';

check_gif             constant varchar2(200):= cdwp.add_images
                                               ('{check_mark.gif}');
g_action_report varchar2(32000);

procedure reset_report
is 
begin
  g_action_report:= '';
end; -- reset_report

procedure add_line
( p_text in varchar2
) is
begin
  if length( p_text) > 0
  then
    g_action_report:= g_action_report||p_text||chr(13)||chr(10);
  end if;
end; -- add_line

procedure add
( p_text in varchar2
) is
begin
  g_action_report:= g_action_report||p_text;
end; -- add

function handle_exception
return varchar2
is
   l_oracle_error_message varchar2(4000);
   l_message              varchar2(100);
   l_stack                varchar2(16000);
   arg0                   varchar2(5000);
   arg1                   varchar2(5000);
   arg2                   varchar2(5000);
   arg3                   varchar2(5000);
   arg4                   varchar2(5000);
   arg5                   varchar2(5000);
   arg6                   varchar2(5000);
   arg7                   varchar2(5000);
   arg8                   varchar2(5000);
   msg_fac varchar2(100);
   msg_code varchar2(100);
   
   procedure write( p_text in varchar2)
   is
   begin
     l_stack:= l_stack
     ||chr(10)||chr(13)||p_text
     ;
   end; -- write
begin
   if cdapi.stacksize > 0
   then
      while cdapi.stacksize > 0 loop
         cdapi.get_message
         ( cdapi.stacksize
         , msg_fac
         , msg_code 
         ,arg1 ,arg2,arg3,arg4,arg5,arg6,arg7,arg8
         );
         if instr( arg8, 'UDM') > 0 -- User Defined Message
         then
           -- now pop the message to remove it from the message stack!
           cdapi.pop_message
           ( msg_fac
           , msg_code 
           ,arg1 ,arg2,arg3,arg4,arg5,arg6,arg7,arg8
           );
           write( msg_fac||'-'||msg_code||':'||arg7||arg6||arg5
           ||cdwpbase.ifThenElse( arg6 <> '', ', '||arg6)
           ||cdwpbase.ifThenElse( arg5 <> '', ', '||arg5)
           ||cdwpbase.ifThenElse( arg4 <> '', ', '||arg6)
           ||cdwpbase.ifThenElse( arg3 <> '', ', '||arg7)
           );         
         else
           write(cdapi.pop_instantiated_message);
         end if;
      end loop;
      if cdapi.activity is not null then
         cdapi.abort_activity;
         write('Activity aborted with API errors.');
      else
         write('API Session aborted with API errors.');
      end if;
   else
      l_oracle_error_message := sqlcode||' '||sqlerrm;
      if cdapi.activity is not null
      then
         cdapi.abort_activity;
         l_message := 'Activity aborted';
      else
         l_message := 'API Session aborted';
      end if;
      if l_oracle_error_message = '1 User-Defined Exception'
      then
         write(l_message||'.');
      else
         write(l_message||' with ORACLE internal errors.');
         write(l_oracle_error_message
         );
      end if;
   end if;
   return l_stack;
end handle_exception;

                                               
  procedure open_palette
  ( p_title in varchar2
  , p_him_id in number default null
  ) is
  begin
     htp.tableopen( cattributes => 'WIDTH="100%" BORDER="0" CELLSPACING=0 CELLPADDING=0 BGCOLOR="#CCCC99"');  -- 1.4  bgcolor="88DDF9"
     htp.tablerowopen;
     htp.p('<TD>');
     htp.tableopen( cattributes => 'WIDTH="100%" BORDER=0 CELLSPACING="1" BGCOLOR="#FFFFFF"');  -- 1.4  bgcolor="88DDF9"
     htp.p('<TH COLSPAN="2" BGCOLOR="#CCCC99">');
     htp.p
     ( '<font color="#336699"><B>'
     ||p_title
     ||cdwpbase.ifThenElse
       ( p_him_id is not null
       , cdwpbase.nbsp(5)
       ||odwahelp.help_url(p_him_id => p_him_id, p_top => true)
       )
     ||'</B></FONT>
     ');
     htp.p('</TH>');
  end; -- open_palette

  procedure close_palette
  is
  begin
     htp.tableclose;
     htp.tableclose;
  end; -- close_palette

                                               
 procedure print_property
  ( p_property_name   in varchar2
   ,p_property_value  in varchar2
   ,p_property_display_value in varchar2 default null
   ,p_mandatory       in varchar2         default 'N'
   ,p_property_value2 in varchar2  default null
   ,p_property_value3 in varchar2  default null
   ,p_two_columns     in boolean   default true
  )  is
 begin
     htp.tableRowOpen;
     htp.p  
     ( '<TD'
     --||' WIDTH="360" '
     ||' VALIGN="TOP"'  -- fixed with for left column, used to be 160
     ||' ALIGN="LEFT" '  -- property labels align just like the RON does
     ||' BGCOLOR="#F7F7E7">'
     ||'<font face="Arial,Helvetica">'
     ||cdwpbase.ifThenElse
       ( (nvl(p_mandatory,'N')='Y')
       , '<font color="#FF0000">' -- red for mandatory properties
       , '<font color="#000000">' -- black for optional properties
       )
     ||replace( substr( p_property_name, 1, 14),' ', cdwpbase.nbsp(1))
     ||substr( p_property_name, 15)
     ||'</font>'
     ||'</font>'
     ||'</TD>'
     );
     htp.TableData
     ( cvalue=>
       '<font face="Arial,Helvetica">'
     ||'<font color="#000000">'
     || nvl
        ( nvl
          ( p_property_display_value
          , p_property_value
          )
        , cdwpbase.nbsp
        )  
     ||'</font>'
     ||'</font>'
       );
     if not p_two_columns or p_property_value2 is not null
     then
       htp.TableData
       ( cvalue=>
         '<font face="Arial,Helvetica">'
       ||'<font color="#000000">'
       ||  nvl
           ( p_property_value2
           , cdwpbase.nbsp
           )
       ||'</font>'
       ||'</font>'
       );
     end if;
     if not p_two_columns or p_property_value3 is not null
     then
       htp.TableData
       ( cvalue=>
         '<font face="Arial,Helvetica">'
       ||'<font color="#000000">'
       ||  nvl
           ( p_property_value3
           , cdwpbase.nbsp
           )
       ||'</font>'
       ||'</font>'
       );
     end if;
     htp.tableRowClose;
 end; --print_property

procedure print_privilege
( p_privilege in varchar2
, p_value     in varchar2 -- Y or something else
) is
begin
  print_property
  ( p_property_name => p_privilege
  , p_property_value => cdwpbase.ifThenElse
                        ( p_value = 'Y'
                        , cdwp.add_images
                          ('{check_mark.gif}')
                        , '-'
                        )
  );
end; -- print_privilege


-- 'DEL' , 'ADM', 'INS', 'SEL', 'UPD', 'VER', 'CMP', 'UPD_SPEC';
procedure role_privs
( p_grantee in varchar2
, p_role    in varchar2
) is
  l_grantee_acc_privs varchar2(20);
  l_role_name      varchar2(30)  := odwapriv.proper( p_role);
  l_role_irid      number(38)    := odwapriv.get_role_irid( p_role_name => l_role_name);
  l_grantee      varchar2(30)    := odwapriv.proper( p_grantee);
  l_grantee_type varchar2(1)     := odwapriv.get_grantee_type( p_grantee => p_grantee); -- R(ole) or U(ser)

  cursor c_acc
         ( b_role_name in varchar2
         , b_grantee   in varchar2
         )
  is
  select rle.username         role_name
  ,      acc.object_reference role_irid
  ,      acc.grantor_reference
  ,      acc.date_created
  ,      acc.date_changed
  ,      acc.access_privs
  ,      rle.created_by
  from   sdw_users            rle
  ,      sdw_access_rights    acc
  where  acc.grantee_reference = b_grantee
  and    acc.object_reference  = rle.irid
  and    rle.username          = b_role_name
  ;

begin
  print_property( Rob_msg.getMsg(Rob_msg.CAP329_ODWAPRED_ROLE, '', '', '', ''), l_role_name  );
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP330_ODWAPRED_GRANTEE, '', '', '', '')
  , cdwpbase.ifThenElse
    ( l_grantee_type = 'U'
    , cdwp.add_images('{user.gif}')
    , cdwp.add_images('{role.gif}')    
    )
  ||l_grantee  
  );
  for r_acc in c_acc
               ( b_role_name => l_role_name
               , b_grantee   => l_grantee
               ) loop
     l_grantee_acc_privs:= jr_acc_rights.ar_to_str
                           ( r_acc.access_privs
                           );
     if l_grantee_type = 'U'
     then
       print_privilege(Rob_msg.getMsg(Rob_msg.CAP331_ODWAPRED_GROPTION, '', '', '', ''), substr(l_grantee_acc_privs, 2,1));
       print_privilege(Rob_msg.getMsg(Rob_msg.CAP332_ODWAPRED_DELOPTION, '', '', '', ''), substr(l_grantee_acc_privs, 1,1));
       print_privilege(Rob_msg.getMsg(Rob_msg.CAP333_ODWAPRED_RECOPTION, '', '', '', ''), substr(l_grantee_acc_privs, 7,1));
       if l_grantee = r_acc.grantor_reference
       then
         print_privilege(Rob_msg.getMsg(Rob_msg.CAP334_ODWAPRED_ISOWNER, '', '', '', ''), 'Y');
       else
         print_privilege(Rob_msg.getMsg(Rob_msg.CAP334_ODWAPRED_ISOWNER, '', '', '', ''), '-');
       end if;
     end if;

     print_property( Rob_msg.getMsg(Rob_msg.CAP335_ODWAPRED_GRANTOR, '', '', '', ''), r_acc.grantor_reference);
     print_property( Rob_msg.getMsg(Rob_msg.CAP336_ODWAPRED_ROWNER, '', '', '', ''), nvl( odwapriv.get_role_owner(l_role_name ),odwapriv.get_role_owner(l_role_name )) );
     print_property( Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', ''), to_char( r_acc.date_created, 'dd-mon-yyyy hh24:mi'));
     print_property( Rob_msg.getMsg(Rob_msg.CAP338_ODWAPRED_DATECH, '', '', '', ''), to_char( r_acc.date_changed, 'dd-mon-yyyy hh24:mi'));
  end loop; -- r_acc
end; -- role_privs

procedure object_privs
( p_grantee    in varchar2
, p_irid       in varchar2
) is
  l_grantee_acc_privs varchar2(20);
  l_grantee      varchar2(30)    := odwapriv.proper( p_grantee);
  l_grantee_type varchar2(1)     := odwapriv.get_grantee_type( p_grantee => p_grantee); -- R(ole) or U(ser)

  cursor c_acc
         ( b_irid      in number
         , b_grantee   in varchar2
         )
  is
  select acc.object_reference role_irid
  ,      acc.grantor_reference
  ,      acc.date_created
  ,      acc.date_changed
  ,      acc.access_privs
  ,      acc.object_type
  from   sdw_access_rights    acc
  where  acc.grantee_reference = b_grantee
  and    acc.object_reference  = b_irid
  ;

begin
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP330_ODWAPRED_GRANTEE, '', '', '', '')
  , cdwpbase.ifThenElse
    ( l_grantee_type='U'
    , cdwp.add_images('{user.gif}')
    , cdwp.add_images('{role.gif}')
    )
  ||p_grantee
  );
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP339_ODWAPRED_OBJECT, '', '', '', '')
  , odwapriv.get_object_label( p_irid)
  );
  for r_acc in c_acc
               ( b_irid      => p_irid
               , b_grantee   => l_grantee
               ) loop
     l_grantee_acc_privs:= jr_acc_rights.ar_to_str
                           ( r_acc.access_privs
                           );
-- 'DEL' , 'ADM', 'INS', 'SEL', 'UPD', 'VER', 'CMP', 'UPD_SPEC';
     print_privilege(Rob_msg.getMsg(Rob_msg.CAP340_ODWAPRED_SELECT, '', '', '', ''), substr(l_grantee_acc_privs, 4,1));
     print_privilege(Rob_msg.getMsg(Rob_msg.CAP342_ODWAPRED_INSERT, '', '', '', ''), substr(l_grantee_acc_privs, 3,1));
     print_privilege(Rob_msg.getMsg(Rob_msg.CAP341_ODWAPRED_UPDATE, '', '', '', ''), substr(l_grantee_acc_privs, 5,1));
     print_privilege(Rob_msg.getMsg(Rob_msg.CAP343_ODWAPRED_DELETE, '', '', '', ''), substr(l_grantee_acc_privs, 1,1));
     print_privilege(Rob_msg.getMsg(Rob_msg.CAP344_ODWAPRED_VERSION, '', '', '', ''), substr(l_grantee_acc_privs, 6,1));
     print_privilege(Rob_msg.getMsg(Rob_msg.CAP345_ODWAPRED_ADMIN, '', '', '', ''), substr(l_grantee_acc_privs, 2,1));
     if r_acc.object_type ='WA'
     then
       print_privilege(Rob_msg.getMsg(Rob_msg.CAP346_ODWAPRED_COMPILE, '', '', '', ''), substr(l_grantee_acc_privs, 7,1));
       print_privilege(Rob_msg.getMsg(Rob_msg.CAP347_ODWAPRED_EDITSPEC, '', '', '', ''), substr(l_grantee_acc_privs, 8,1));
     end if;


     if l_grantee_type = 'U'
     then
       if l_grantee = r_acc.grantor_reference
       then
         print_privilege(Rob_msg.getMsg(Rob_msg.CAP334_ODWAPRED_ISOWNER, '', '', '', ''), 'Y');
       end if;
     end if;

     print_property( Rob_msg.getMsg(Rob_msg.CAP335_ODWAPRED_GRANTOR, '', '', '', ''), r_acc.grantor_reference);
     print_property( Rob_msg.getMsg(Rob_msg.CAP206_ODWAPROP_OWNER, '', '', '', ''), odwapriv.get_governor(p_irid ));
     print_property( Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', ''), to_char( r_acc.date_created, 'dd-mon-yyyy hh24:mi'));
     print_property( Rob_msg.getMsg(Rob_msg.CAP338_ODWAPRED_DATECH, '', '', '', ''), to_char( r_acc.date_changed, 'dd-mon-yyyy hh24:mi'));
  end loop; -- r_acc
end; -- object_privs

procedure object_in_wa_privs
( p_grantee    in varchar2
, p_irid       in number
, p_wa_irid    in number
) is
  l_object_acc_privs  varchar2(20);
  l_wa_acc_privs      varchar2(20);
  l_grantee_acc_privs varchar2(20);
  l_grantee           varchar2(30)    := odwapriv.proper( p_grantee);
  l_grantee_type      varchar2(1)     := odwapriv.get_grantee_type( p_grantee => p_grantee); -- R(ole) or U(ser)

  cursor c_acc
         ( b_irid      in number
         , b_grantee   in varchar2
         )
  is
  select acc.object_reference role_irid
  ,      acc.grantor_reference
  ,      acc.date_created
  ,      acc.date_changed
  ,      acc.access_privs
  ,      acc.object_type
  from   sdw_access_rights    acc
  where  acc.grantee_reference = b_grantee
  and    acc.object_reference  = b_irid
  ;
  r_acc_wa     c_acc%rowtype;
  r_acc_object c_acc%rowtype;
begin
  open c_acc
       ( b_irid      => p_irid
       , b_grantee   => l_grantee
       );
  fetch c_acc
  into  r_acc_object
  ;
  close c_acc;
  open c_acc
       ( b_irid      => p_wa_irid
       , b_grantee   => l_grantee
       );
  fetch c_acc
  into  r_acc_wa
  ;
  close c_acc;
  l_object_acc_privs:= jr_acc_rights.ar_to_str
                        ( r_acc_object.access_privs
                        );
  l_wa_acc_privs    := jr_acc_rights.ar_to_str
                        ( r_acc_wa.access_privs
                        );
  for i in 1..length(l_object_acc_privs) loop
    if substr( l_object_acc_privs, i, 1) = 'Y'
       and
       substr( l_wa_acc_privs, i, 1) = 'Y'
    then
      l_grantee_acc_privs:= l_grantee_acc_privs||'Y';
    else
      l_grantee_acc_privs:= l_grantee_acc_privs||'N';
    end if;
  end loop;
-- 'DEL' , 'ADM', 'INS', 'SEL', 'UPD', 'VER', 'CMP', 'UPD_SPEC';

  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP330_ODWAPRED_GRANTEE, '', '', '', '')
  , cdwpbase.ifThenElse
    ( l_grantee_type='U'
    , cdwp.add_images('{user.gif}')
    , cdwp.add_images('{role.gif}')
    )
  ||p_grantee
  , p_two_columns => false
  );


     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP043_CDWP_CONTEXT, '', '', '', '')
     , p_property_value =>  Rob_msg.getMsg(Rob_msg.CAP077_ODWACHCK_WA, '', '', '', '')
     , p_property_value2 => Rob_msg.getMsg(Rob_msg.CAP348_ODWAPRED_CON, '', '', '', '')
     , p_two_columns => false
     );
     print_property
     ( p_property_name =>cdwpbase.nbsp(1)
     , p_property_value => odwapriv.get_object_label( p_wa_irid)
     , p_property_value2 => odwapriv.get_object_label( p_irid)
     , p_property_value3 => cdwp.add_images('{folder.gif}')
                         ||' in '
                         ||cdwp.add_images('{workarea.gif}')
     , p_two_columns => false
     );
     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP340_ODWAPRED_SELECT, '', '', '', '')
     , p_property_value =>  substr(l_wa_acc_privs, 4,1)
     , p_property_value2 =>  substr(l_object_acc_privs, 4,1)
     , p_property_value3 =>  cdwpbase.ifThenElse
                             ( substr(l_grantee_acc_privs, 4,1) = 'Y'
                             , check_gif
                             , '-'
                             )
     , p_two_columns => false
     );
     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP342_ODWAPRED_INSERT, '', '', '', '')
     , p_property_value =>  substr(l_wa_acc_privs, 3,1)
     , p_property_value2 =>  substr(l_object_acc_privs, 3,1)
     , p_property_value3 =>  cdwpbase.ifThenElse
                             ( substr(l_grantee_acc_privs, 3,1) = 'Y'
                             , check_gif
                             , '-'
                             )
     , p_two_columns => false
     );
     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP341_ODWAPRED_UPDATE, '', '', '', '')
     , p_property_value =>  substr(l_wa_acc_privs, 5,1)
     , p_property_value2 =>  substr(l_object_acc_privs, 5,1)
     , p_property_value3 =>  cdwpbase.ifThenElse
                             ( substr(l_grantee_acc_privs, 5,1) = 'Y'
                             , check_gif
                             , '-'
                             )
     , p_two_columns => false
     );
     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP343_ODWAPRED_DELETE, '', '', '', '')
     , p_property_value =>  substr(l_wa_acc_privs, 1,1)
     , p_property_value2 =>  substr(l_object_acc_privs, 1,1)
     , p_property_value3 =>  cdwpbase.ifThenElse
                             ( substr(l_grantee_acc_privs, 1,1) = 'Y'
                             , check_gif
                             , '-'
                             )
     , p_two_columns => false
     );
     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP344_ODWAPRED_VERSION, '', '', '', '')
     , p_property_value =>  substr(l_wa_acc_privs, 6,1)
     , p_property_value2 =>  substr(l_object_acc_privs, 6,1)
     , p_property_value3 =>  cdwpbase.ifThenElse
                             ( substr(l_grantee_acc_privs, 6,1) = 'Y'
                             , check_gif
                             , '-'
                             )
     , p_two_columns => false
     );
     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP345_ODWAPRED_ADMIN, '', '', '', '')
     , p_property_value =>  substr(l_wa_acc_privs, 2,1)
     , p_property_value2 =>  substr(l_object_acc_privs, 2,1)
     , p_property_value3 =>  cdwpbase.ifThenElse
                             ( substr(l_grantee_acc_privs, 2,1) = 'Y'
                             , check_gif
                             , '-'
                             )
     , p_two_columns => false
     );
     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP335_ODWAPRED_GRANTOR, '', '', '', '')
     , p_property_value =>  r_acc_wa.grantor_reference
     , p_property_value2 =>  r_acc_object.grantor_reference
     , p_two_columns => false
     );
     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP206_ODWAPROP_OWNER, '', '', '', '')
     , p_property_value =>  odwapriv.get_governor(p_wa_irid )
     , p_property_value2 =>  odwapriv.get_governor(p_irid )
     , p_two_columns => false
     );
     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', '')
     , p_property_value =>  to_char( r_acc_wa.date_created, 'dd-mon-yyyy hh24:mi')
     , p_property_value2 =>  to_char( r_acc_object.date_created, 'dd-mon-yyyy hh24:mi')
     , p_two_columns => false
     );
     print_property
     ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP338_ODWAPRED_DATECH, '', '', '', '')
     , p_property_value =>  to_char( r_acc_wa.date_changed, 'dd-mon-yyyy hh24:mi')
     , p_property_value2 =>  to_char( r_acc_object.date_changed, 'dd-mon-yyyy hh24:mi')
     , p_two_columns => false
     );
end; -- object_in_wa_privs


procedure fol_props
( p_fol_irid in number
) is
  cursor c_fol
         ( b_fol_irid in number
         ) is
    select fol.date_created
    ,      fol.created_by
    ,      fol.date_changed
    ,      fol.changed_by
    ,      fol.owning_user
    ,      fol.remark
    from   i$sdd_folders  fol
    where  fol.irid = b_fol_irid
    and    fol.ivid = cdwpbase.get_best_ivid( fol.irid)
    ;
  r_fol c_fol%rowtype;
begin
   open c_fol( b_fol_irid => p_fol_irid);
   fetch c_fol
   into  r_fol;
   close c_fol;
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', '')
   , p_property_value => odwapriv.get_object_label( p_fol_irid)
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP206_ODWAPROP_OWNER, '', '', '', '')
   , p_property_value =>  r_fol.owning_user
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP023_CDWP_COMMENT, '', '', '', '')
   , p_property_value =>  r_fol.remark
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', '')
   , p_property_value =>  to_char( r_fol.date_created, 'dd-mon-yyyy hh24:mi')
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP349_ODWAPRED_CREATOR, '', '', '', '')
   , p_property_value =>  r_fol.created_by
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP338_ODWAPRED_DATECH, '', '', '', '')
   , p_property_value =>  to_char( r_fol.date_changed, 'dd-mon-yyyy hh24:mi')
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.DSP250_ODWAINFO_MODBY, '', '', '', '')
   , p_property_value =>  r_fol.changed_by
   );
end; -- fol_props

procedure wa_props
( p_wa_irid in number
) is
  cursor c_wa
         ( b_wa_irid in number
         ) is
    select wa.owner
    ,      wa.name
    ,      wa.date_created
    ,      wa.created_by
    ,      wa.date_changed
    ,      wa.changed_by
    ,      wa.description
    from   i$sdd_workareas wa
    where  wa.irid = b_wa_irid
    ;
  r_wa c_wa%rowtype;
begin
   open c_wa( b_wa_irid => p_wa_irid);
   fetch c_wa
   into  r_wa;
   close c_wa;
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', '')
   , p_property_value => cdwp.add_images('{workarea.gif}')
                       ||r_wa.name
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP206_ODWAPROP_OWNER, '', '', '', '')
   , p_property_value =>  r_wa.owner
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP008_CDWP_DESC, '', '', '', '')
   , p_property_value =>  r_wa.description
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', '')
   , p_property_value =>  to_char( r_wa.date_created, 'dd-mon-yyyy hh24:mi')
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP349_ODWAPRED_CREATOR, '', '', '', '')
   , p_property_value =>  r_wa.created_by
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP338_ODWAPRED_DATECH, '', '', '', '')
   , p_property_value =>  to_char( r_wa.date_changed, 'dd-mon-yyyy hh24:mi')
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.DSP250_ODWAINFO_MODBY, '', '', '', '')
   , p_property_value =>  r_wa.changed_by
   );
end; -- wa_props

      
procedure cfg_props
( p_cfg_irid in number
) is
  cursor c_cfg
         ( b_cfg_irid in number
         ) is
    select cfg.date_created
    ,      cfg.created_by
    ,      cfg.date_changed
    ,      cfg.changed_by
    ,      cfg.description
    ,      cfg.name
    ,      acc.grantor_reference owner
    from   i$sdd_configurations  cfg
    ,      sdw_access_rights     acc
    where  cfg.irid = b_cfg_irid
    and    cfg.ivid = cdwpbase.get_best_ivid( cfg.irid)
    and    acc.object_reference = cfg.irid
    and    acc.grantor_reference = acc.grantee_reference
    ;
  r_cfg c_cfg%rowtype;
begin
   open c_cfg( b_cfg_irid => p_cfg_irid);
   fetch c_cfg
   into  r_cfg;
   close c_cfg;
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP019_CDWP_NAME, '', '', '', '')
   , p_property_value => cdwp.add_images('{configuration.gif}')
                       ||r_cfg.name
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP206_ODWAPROP_OWNER, '', '', '', '')
   , p_property_value =>  r_cfg.owner
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP008_CDWP_DESC, '', '', '', '')
   , p_property_value =>  r_cfg.description
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', '')
   , p_property_value =>  to_char( r_cfg.date_created, 'dd-mon-yyyy hh24:mi')
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP349_ODWAPRED_CREATOR, '', '', '', '')
   , p_property_value =>  r_cfg.created_by
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP338_ODWAPRED_DATECH, '', '', '', '')
   , p_property_value =>  to_char( r_cfg.date_changed, 'dd-mon-yyyy hh24:mi')
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.DSP250_ODWAINFO_MODBY, '', '', '', '')
   , p_property_value =>  r_cfg.changed_by
   );
end; -- cfg_props



procedure user_props
( p_username in varchar2
) is
  cursor c_usr
         ( b_username in varchar2
         ) is
    select usr.full_user_name
    ,      usr.date_created
    ,      usr.date_changed
    ,      usr.created_by
    ,      usr.changed_by
    ,      usr.description
    ,      usr.sdd_owner
    ,      usr.status
--    ,      jr_sys_privs.ar_to_str(usr.system_privs)
    from   sdw_users usr
    where  usr.username = b_username
    ;
  r_usr c_usr%rowtype;
begin
   open c_usr( b_username => p_username);
   fetch c_usr
   into  r_usr;
   close c_usr;
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP350_ODWAPRED_USER, '', '', '', '')
   , p_property_value => p_username
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP351_ODWAPRED_FULLUSER, '', '', '', '')
   , p_property_value =>  r_usr.full_user_name
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP008_CDWP_DESC, '', '', '', '')
   , p_property_value =>  r_usr.description
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', '')
   , p_property_value =>  to_char( r_usr.date_created, 'dd-mon-yyyy hh24:mi')
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP349_ODWAPRED_CREATOR, '', '', '', '')
   , p_property_value =>  r_usr.created_by
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.CAP338_ODWAPRED_DATECH, '', '', '', '')
   , p_property_value =>  to_char( r_usr.date_changed, 'dd-mon-yyyy hh24:mi')
   );
   print_property
   ( p_property_name => Rob_msg.getMsg(Rob_msg.DSP250_ODWAINFO_MODBY, '', '', '', '')
   , p_property_value =>  r_usr.changed_by
   );
end; -- user_props


function check_upd_users
return boolean
IS
 repos_owner VARCHAR2(30);
 curr_user   VARCHAR2(30):=USER;
BEGIN
 repos_owner:=jr_sys_privs.get_repos_owner;
 IF (curr_user!=repos_owner)
     AND 
     NOT jr_sys_privs.has_privilege(curr_user,'MANAGE_USERS') 
 THEN
   --Insufficient privileges to manage users.
   return false;
 ELSE
   return true;
 END IF;
exception
when others
then
  return false;
END;

procedure saveRoleGrant
( p_session_id       in number    default null
, p_role_name        in varchar2  
, p_grantee          in varchar2
, p_grant_option     in varchar2  default 'N'
, p_delete_option    in varchar2  default 'N'
, p_reconcile_option in varchar2  default 'N'
, p_reconcile        in varchar2  default 'N'
) is
 l_grantee_type varchar2(1):= odwapriv.get_grantee_type(p_grantee => p_grantee);
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'saveRoleGrant'
  );
  reset_report;
  add_line
  ( 'Granting role '
  ||p_role_name
  ||' to '
  ||p_grantee);
  -- try to perform the roleGrant. If this does not succeed, return with 
  -- error message to the grant role page
  -- else move on to the node_selected page
  begin
    add_line('... granting '||p_role_name);
    jr_role.grant_role
    ( p_role_to_grant     => p_role_name
    , p_grantee           => p_grantee
    , p_grant_option      => p_grant_option ='Y'
    , p_delete_option     => p_delete_option ='Y'
    , p_reconcile_option  => p_reconcile_option ='Y'
    , p_reconcile         => p_reconcile ='Y'
    , p_override          => true
    );
   add_line(' Successfully Granted access roles to '
   ||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')
   ||' '||p_grantee
   );
   action_report
    ( p_session_id             => p_session_id
    , p_node_value             => p_grantee
    , p_node_type              => '{TYPE='||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')||'}'
    , p_root_value             => '{TYPE='||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')||'}'
    , p_root_classification    => '{TYPE='||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')||'}'
    , p_refresh_tree           => true
    , p_action_report          => g_action_report
    , p_display_label          => 'Granted role '||p_role_name||' to '
                                 ||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')
                                 ||' '||p_grantee
    );

  exception
    when others
    then
    odwapred.grantRole
    ( p_session_id        => odwactxt.get_session_id
    , p_role_name         => p_role_name
    , p_grantee           => p_grantee
    , p_grant_option      => p_grant_option
    , p_delete_option     => p_delete_option
    , p_reconcile_option  => p_reconcile_option
    , p_reconcile         => p_reconcile 
    , p_error_message     => sqlerrm
    );
  end;
end; -- saveRoleGrant      

procedure saveNewRoleGrant
( p_session_id       in number    default null
, p_role_name        in varchar2  
, p_grantee          in owa_util.ident_arr
, p_grant_option     in varchar2  default 'N'
, p_delete_option    in varchar2  default 'N'
, p_reconcile_option in varchar2  default 'N'
, p_reconcile        in varchar2  default 'N'
) is
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'saveRoleGrant'
  );
   reset_report;
   add_line
   (' Granting Role '||p_role_name||' to Users/Roles'
   );
   if p_grant_option = 'Y'
   then
     add_line('  - with Grant option');
   end if;
   if p_delete_option = 'Y'
   then
     add_line('  - with Delete option');
   end if;
   if p_reconcile_option = 'Y'
   then
     add_line('  - with Reconcile option');
   end if;
   if p_reconcile = 'Y'
   then
     add_line('---------------------------------------------------');
     add_line('Perform reconcile of all new Role/User combinations');
   end if;

  -- try to perform the roleGrant. If this does not succeed, return with 
  -- error message to the grant role page
  -- else move on to the node_selected page
  for i in 1..p_grantee.count loop
    begin
      jr_role.grant_role
      ( p_role_to_grant     => p_role_name
      , p_grantee           => p_grantee(i)
      , p_grant_option      => p_grant_option ='Y'
      , p_delete_option     => p_delete_option ='Y'
      , p_reconcile_option  => p_reconcile_option ='Y'
      , p_reconcile         => p_reconcile ='Y'
      , p_override          => true
      );
      add_line(' to '||p_grantee(i));
  exception
    when others
    then
    odwapred.grantRole
    ( p_session_id        => odwactxt.get_session_id
    , p_role_name         => p_role_name
    , p_grantee           => p_grantee(i)
    , p_grant_option      => p_grant_option
    , p_delete_option     => p_delete_option
    , p_reconcile_option  => p_reconcile_option
    , p_reconcile         => p_reconcile 
    , p_error_message     => sqlerrm
    );
  end;
  end loop; -- i in  1..p_grantee.count loop

   add_line(' Successfully Granted access privileges');
 action_report
  ( p_session_id             => p_session_id
  , p_node_value             => p_role_name
  , p_node_type              => '{TYPE=ROLE}'
  , p_root_value             => '{TYPE=ROLE}'
  , p_root_classification    => '{TYPE=ROLE}'
  , p_refresh_tree           => true
  , p_action_report          => g_action_report
  , p_display_label          => 'Granted role '||p_role_name||' to Users/Roles'
  );

end; -- saveNewRoleGrant      


procedure saveGranteesNewRoleGrants
( p_session_id       in number    default null
, p_role_name        in owa_util.ident_arr
, p_grantee          in varchar2  
, p_grant_option     in varchar2  default 'N'
, p_delete_option    in varchar2  default 'N'
, p_reconcile_option in varchar2  default 'N'
, p_reconcile        in varchar2  default 'N'
) is
 l_grantee_type varchar2(1):= odwapriv.get_grantee_type(p_grantee => p_grantee);
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'saveRoleGrant'
  );
  reset_report;
  -- try to perform the roleGrant. If this does not succeed, return with 
  -- error message to the grant role page
  -- else move on to the node_selected page
  add_line
  ( 'Granting role'
  ||cdwpbase.ifThenElse
    ( p_role_name.count > 1
     ,'s'
     )
  ||' to '
  ||p_grantee);
  for i in 1..p_role_name.count loop
    begin
      add_line('... granting '||p_role_name(i));
      jr_role.grant_role
      ( p_role_to_grant     => p_role_name(i)
      , p_grantee           => p_grantee
      , p_grant_option      => p_grant_option ='Y'
      , p_delete_option     => p_delete_option ='Y'
      , p_reconcile_option  => p_reconcile_option ='Y'
      , p_reconcile         => p_reconcile ='Y'
      , p_override          => true
      );
  exception
    when others
    then
    odwapred.grantRole
    ( p_session_id        => odwactxt.get_session_id
    , p_role_name         => p_role_name(1)
    , p_grantee           => p_grantee
    , p_grant_option      => p_grant_option
    , p_delete_option     => p_delete_option
    , p_reconcile_option  => p_reconcile_option
    , p_reconcile         => p_reconcile 
    , p_error_message     => sqlerrm
    );
  end;
  end loop; -- i in  1..p_role_name.count loop
 add_line(' Successfully Granted access roles to '
 ||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')
 ||' '||p_grantee
 );
 action_report
  ( p_session_id             => p_session_id
  , p_node_value             => p_grantee
  , p_node_type              => '{TYPE='||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')||'}'
  , p_root_value             => '{TYPE='||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')||'}'
  , p_root_classification    => '{TYPE='||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')||'}'
  , p_refresh_tree           => true
  , p_action_report          => g_action_report
  , p_display_label          => 'Granted roles to '
                               ||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')
                               ||' '||p_grantee
  );
end; -- saveGranteesNewRoleGrants      


procedure grantToGrantee
( p_session_id       in number   default null
, p_role_name        in varchar2 default null
, p_grantee          in varchar2 
, p_grant_option     in varchar2 default null
, p_delete_option    in varchar2 default null
, p_reconcile_option in varchar2 default null
, p_reconcile        in varchar2 default null
, p_error_message    in varchar2 default null
) is
  l_role_list varchar2(10000);
  cursor c_rle( b_grantee in varchar2) is
    select usr.username role_name
    ,      usr.irid     role_irid
    from   sdw_users usr
    where  usr.user_type = 0
    and    usr.username <> 'PUBLIC'
    and    usr.username <> b_grantee -- do not grant role to itself
    and    not exists
           ( select 'x'
             from   sdw_access_rights acc
             where  acc.object_reference = usr.irid
             and    acc.grantee_reference = b_grantee
             )
   order
   by      usr.username
   ;
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'grantToGrantee'
  );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
  open_palette
  ( 'Grant role '||p_role_name
  ||' to '||p_grantee
  , p_him_id => 686
  );
  if p_error_message is not null
  then
    -- errorMessage
    print_property
    ( 'Error'
    ,  '<b>Try Again: '||p_error_message||'</b>'
    , p_mandatory=> 'Y'
    );
  end if; -- p_error_message is not null

  htp.formopen
  ( curl         => 'odwapred.saveGranteesNewRoleGrants'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="roleGrantForm" '
  , ctarget      => ''
  );
    htp.p
    ( ' <INPUT TYPE="hidden" NAME="p_grantee" '
    ||' VALUE="'||p_grantee||'">'
    );
  -- Roles
  l_role_list:= htf.formselectopen
                   ( cname   => 'p_role_name'
                   , cprompt => ''
                   , nsize => 5
                   , cattributes => 'MULTIPLE'
                   );
  for r_rle in c_rle( b_grantee => p_grantee) loop
    if  jr_acc_rights.has_access
      ( object_irid  => r_rle.role_irid
      , grantee      => USER
      , privilege    => 'ADM'
      )
      OR
      odwapriv.get_role_owner( r_rle.role_name) = USER  -- user must have reconcile privilege on the role
    then
      l_role_list:= l_role_list
      ||'<OPTION VALUE="'||r_rle.role_name||'"'
         ||cdwpbase.ifThenElse
           ( p_grantee = r_rle.role_name
           , 'SELECTED=Y'
           )
       ||'>'
       ||r_rle.role_name
       ;
     end if;
  end loop; -- r_rle
  l_role_list:= l_role_list
  ||htf.formselectclose
  ;

  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP329_ODWAPRED_ROLE, '', '', '', '')
  , l_role_list
  , p_mandatory=> 'Y'
  );
  if jr_role.get_grantee_type
     ( p_grantee => p_grantee
     ) = 'U' -- it is a real user
  then
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP331_ODWAPRED_GROPTION, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_grant_option'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse( p_grant_option is not null, 'Y')
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP332_ODWAPRED_DELOPTION, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_delete_option'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse( p_delete_option is not null, 'Y')
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP333_ODWAPRED_RECOPTION, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_reconcile_option'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse( p_reconcile_option is not null, 'Y')
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
    print_property
    ( 'Reconcile'
    , htf.formCheckBox
      ( CNAME        => 'p_reconcile'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse( p_reconcile is not null, 'Y')
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
  end if; -- grantee type = 'U'
/*  
         print_privilege('Grant Option', substr(l_grantee_acc_privs, 2,1));
       print_privilege('Delete Option', substr(l_grantee_acc_privs, 1,1));
       print_privilege('Reconcile Option', substr(l_grantee_acc_privs, 7,1));
*/
  -- RoleDescription
  cdwp.tableRowOpen;
  cdwp.TableDataValue
  ( cdwpbase.nbsp(10)
  ||'<INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP311_ODWAPREF_SAVE, '', '', '', '')||'" '
  ||' onClick="javascript:roleGrantForm.submit()"; return true;'
  ||'>'
  || cdwpbase.nbsp(10)
  ||' <INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP243_ODWASPOT_CANCEL, '', '', '', '')||'" '
  ||' onClick="javascript:top.rightSide.paletteFrame.history.back();return true;"'
  ||' >'
  , p_attributes => 'VALIGN="TOP" colspan=2'
  );
  htp.formClose;
  cdwp.tableRowClose;
  close_palette;
  htp.bodyClose;
end; -- grantToGrantee




procedure grantRole
( p_session_id       in number   default null
, p_role_name        in varchar2 
, p_grantee          in varchar2 default null
, p_grant_option     in varchar2 default null
, p_delete_option    in varchar2 default null
, p_reconcile_option in varchar2 default null
, p_reconcile        in varchar2 default null
, p_error_message    in varchar2 default null
) is
  l_grantee_list varchar2(10000);
  cursor c_grt( b_role_name in varchar2) is
    select usr.username grantee_name
    ,      decode
           ( usr.user_type 
           , 0 , 'Role'
           , 1, 'User'
           )  grantee_type
    from   sdw_users usr
    where  not exists
           ( select 'x'
             from   sdw_access_rights acc
             ,      sdw_users         rle
             where  rle.username = b_role_name
             and    acc.object_reference = rle.irid
             and    acc.grantee_reference = usr.username
             )
   and     username <> b_role_name -- do not grant role to itself
   order
   by      usr.user_type
   ,       usr.username
   ;
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'grantRole'
  );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
  open_palette
  ( p_title => 'Grant role '||p_role_name||' to user or role'
  , p_him_id => 630
  );

  if p_error_message is not null
  then
    -- errorMessage
    print_property
    ( 'Error'
    ,  '<b>Try Again: '||p_error_message||'</b>'
    , p_mandatory=> 'Y'
    );
  end if; -- p_error_message is not null

  htp.formopen
  ( curl         => 'odwapred.saveNewRoleGrant'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="roleGrantForm" '
  , ctarget      => ''
  );
    htp.p
    ( ' <INPUT TYPE="hidden" NAME="p_role_name" '
    ||' VALUE="'||p_role_name||'">'
    );
  -- Grantee
  l_grantee_list:= htf.formselectopen
                   ( cname   => 'p_grantee'
                   , cprompt => ''
                   , nsize   => 5
                   , cattributes => 'MULTIPLE'
                   );
  for r_grt in c_grt( b_role_name => p_role_name) loop
      l_grantee_list:= l_grantee_list
      ||'<OPTION VALUE="'||r_grt.grantee_name||'"'
         ||cdwpbase.ifThenElse
           ( p_grantee = r_grt.grantee_name
           , 'SELECTED=Y'
           )
       ||'>'
       ||r_grt.grantee_name||' ('||r_grt.grantee_type||')'
       ;
  end loop; -- r_grt
  l_grantee_list:= l_grantee_list
  ||htf.formselectclose
  ;

  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP330_ODWAPRED_GRANTEE, '', '', '', '')
  , l_grantee_list
  , p_mandatory=> 'Y'
  );
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP331_ODWAPRED_GROPTION, '', '', '', '')
  , htf.formCheckBox
    ( CNAME        => 'p_grant_option'
    , CVALUE       => 'Y'
    , CCHECKED     => cdwpbase.ifThenElse( p_grant_option is not null, 'Y')
    , CATTRIBUTES  => ''
    )
  , p_mandatory=> 'N'
  );
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP332_ODWAPRED_DELOPTION, '', '', '', '')
  , htf.formCheckBox
    ( CNAME        => 'p_delete_option'
    , CVALUE       => 'Y'
    , CCHECKED     => cdwpbase.ifThenElse( p_delete_option is not null, 'Y')
    , CATTRIBUTES  => ''
    )
  , p_mandatory=> 'N'
  );
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP333_ODWAPRED_RECOPTION, '', '', '', '')
  , htf.formCheckBox
    ( CNAME        => 'p_reconcile_option'
    , CVALUE       => 'Y'
    , CCHECKED     => cdwpbase.ifThenElse( p_reconcile_option is not null, 'Y')
    , CATTRIBUTES  => ''
    )
  , p_mandatory=> 'N'
  );
  print_property
  ( 'Reconcile'
  , htf.formCheckBox
    ( CNAME        => 'p_reconcile'
    , CVALUE       => 'Y'
    , CCHECKED     => cdwpbase.ifThenElse( p_reconcile is not null, 'Y')
    , CATTRIBUTES  => ''
    )
  , p_mandatory=> 'N'
  );
/*  
         print_privilege('Grant Option', substr(l_grantee_acc_privs, 2,1));
       print_privilege('Delete Option', substr(l_grantee_acc_privs, 1,1));
       print_privilege('Reconcile Option', substr(l_grantee_acc_privs, 7,1));
*/
  -- RoleDescription
  cdwp.tableRowOpen;
  cdwp.TableDataValue
  ( cdwpbase.nbsp(10)
  ||'<INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP311_ODWAPREF_SAVE, '', '', '', '')||'" '
  ||' onClick="javascript:roleGrantForm.submit()"; return true;'
  ||'>'
  || cdwpbase.nbsp(10)
  ||' <INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP243_ODWASPOT_CANCEL, '', '', '', '')||'" '
  ||' onClick="javascript:top.rightSide.paletteFrame.history.back();return true;"'
  ||' >'
  , p_attributes => 'VALIGN="TOP" colspan=2'
  );
  htp.formClose;
  cdwp.tableRowClose;
  close_palette;
  htp.bodyClose;
end; -- grantRole

procedure grantPrivs
( p_session_id in number
, p_grantee    in varchar2  default null
, p_irid       in varchar2
, p_select     in varchar2  default null
, p_insert     in varchar2  default null
, p_update     in varchar2  default null
, p_delete     in varchar2  default null
, p_admin      in varchar2  default null
, p_version    in varchar2  default null
, p_compile    in varchar2  default null
, p_upd_spec   in varchar2  default null
, p_error_message    in varchar2 default null
) is
  l_object_type varchar2(3):= odwapriv.get_object_type(p_irid => p_irid); -- wa, cfg or cel
  l_object_label varchar2(2000):= odwapriv.get_object_label
                                  ( p_irid => p_irid
                                  );
  l_grantee_list varchar2(10000);

  l_grantor_acc_privs varchar2(20);

  cursor c_grt( b_irid in number) is
    select usr.username grantee_name
    ,      decode
           ( usr.user_type 
           , 0 , 'Role'
           , 1, 'User'
           )  grantee_type
    from   sdw_users usr
    where  not exists
           ( select 'x'
             from   sdw_access_rights acc
             where  acc.object_reference = b_irid
             and    acc.grantee_reference = usr.username
             )
   order
   by      usr.user_type
   ,       usr.username
   ;
begin
  odwactxt.update_context
  ( p_session_id    => odwactxt.get_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'edit_object_privs'
  );
  l_grantor_acc_privs:= jr_acc_rights.ar_to_str
                        ( jr_acc_rights.get_acc_rights
                          ( object_irid => p_irid
                          , grantee     => USER
                          )
                        );
  l_object_label:= cdwpbase.ifThenElse
                    ( l_object_type = 'WA'
                    , cdwp.add_images('{workarea.gif}')
                    ||cdwpbase.get_wa_name(p_wa_irid => p_irid)
                    , cdwpbase.ifThenElse
                      ( l_object_type ='CFG'
                      , cdwp.add_images('{configuration.gif}')
                      ||cdwpbase.get_cfg_name
                        ( p_cfg_ivid => cdwpbase.get_best_ivid( p_irid)
                        )
                      , cdwp.add_images('{folder.gif}')
                      ||jr_name.get_path
                        ( id => p_irid
                        , format => 'NAME'
                        )  -- Folder
                      )
                    );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
  open_palette
  ( p_title =>Rob_msg.getMsg(Rob_msg.DSP340_ODWAPRED_GRANTOBJ, l_object_label, '', '', '')
  , p_him_id => 665
  );
  if p_error_message is not null
  then
    -- errorMessage
    print_property
    ( 'Error'
    ,  '<b>Try Again: '||p_error_message||'</b>'
    , p_mandatory=> 'Y'
    );
  end if; -- p_error_message is not null

  htp.formopen
  ( curl         => 'odwapred.saveNewObjPrivsGrantees'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="privilegesForm" '
  , ctarget      => ''
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_session_id" '
  ||' VALUE="'||to_char(odwactxt.get_session_id)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_object_type" '
  ||' VALUE="'||l_object_type||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_irid" '
  ||' VALUE="'||p_irid||'">'
  );
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP339_ODWAPRED_OBJECT, '', '', '', '')
  , l_object_label
  , p_mandatory=> 'Y'
  );
  -- Grantee
  l_grantee_list:= htf.formselectopen
                   ( cname   => 'p_grantee'
                   , cprompt => ''
                   , nsize => 5
                   , cattributes => 'MULTIPLE'
                   );
  for r_grt in c_grt( b_irid => p_irid) loop
      l_grantee_list:= l_grantee_list
      ||'<OPTION VALUE="'||r_grt.grantee_name||'"'
         ||cdwpbase.ifThenElse
           ( p_grantee = r_grt.grantee_name
           , 'SELECTED=Y'
           )
       ||'>'
       ||r_grt.grantee_name||' ('||r_grt.grantee_type||')'
       ;
  end loop; -- r_grt
  l_grantee_list:= l_grantee_list
  ||htf.formselectclose
  ;

  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP330_ODWAPRED_GRANTEE, '', '', '', '')
  , l_grantee_list
  , p_mandatory=> 'Y'
  );


  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_select" '
  ||' VALUE="'||'Y'||'">'
  );
  print_privilege(Rob_msg.getMsg(Rob_msg.CAP340_ODWAPRED_SELECT, '', '', '', ''), 'Y');
  if substr(l_grantor_acc_privs, 3,1)='Y' 
  then
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP342_ODWAPRED_INSERT, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_insert'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl(p_insert,'N') ='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
  else
    print_privilege(Rob_msg.getMsg(Rob_msg.CAP342_ODWAPRED_INSERT, '', '', '', ''), '-');  
  end if;


  if substr(l_grantor_acc_privs, 5,1)='Y' 
  then
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP341_ODWAPRED_UPDATE, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_update'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl(p_update,'N') ='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
  else
    print_privilege(Rob_msg.getMsg(Rob_msg.CAP341_ODWAPRED_UPDATE, '', '', '', ''), '-');  
  end if;
  if substr(l_grantor_acc_privs, 1,1)='Y' 
  then
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP343_ODWAPRED_DELETE, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_delete'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl(p_delete,'N') ='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
  else
    print_privilege(Rob_msg.getMsg(Rob_msg.CAP343_ODWAPRED_DELETE, '', '', '', ''), '-');  
  end if;
  if substr(l_grantor_acc_privs, 2,1)='Y' 
  then
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP345_ODWAPRED_ADMIN, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_admin'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl(p_admin,'N') ='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
  else
    print_privilege(Rob_msg.getMsg(Rob_msg.CAP345_ODWAPRED_ADMIN, '', '', '', ''), '-');  
  end if;
  if odwapriv.get_versioned_repository
  then
    if substr(l_grantor_acc_privs, 6,1)='Y' 
    then
      print_property
      ( Rob_msg.getMsg(Rob_msg.CAP344_ODWAPRED_VERSION, '', '', '', '')
      , htf.formCheckBox
        ( CNAME        => 'p_version'
        , CVALUE       => 'Y'
        , CCHECKED     => cdwpbase.ifThenElse
                          ( nvl(p_version,'N') ='Y' 
                          , 'Y'
                          )
        , CATTRIBUTES  => ''
        )
      , p_mandatory=> 'N'
      );
    else
      print_privilege(Rob_msg.getMsg(Rob_msg.CAP344_ODWAPRED_VERSION, '', '', '', ''), '-');  
    end if;
     if l_object_type ='WA'
     then
        if substr(l_grantor_acc_privs, 7,1)='Y' 
        then
          print_property
          ( Rob_msg.getMsg(Rob_msg.CAP346_ODWAPRED_COMPILE, '', '', '', '')
          , htf.formCheckBox
            ( CNAME        => 'p_compile'
            , CVALUE       => 'Y'
            , CCHECKED     => cdwpbase.ifThenElse
                              ( nvl( p_compile, 'N')='Y' 
                              , 'Y'
                              )
            , CATTRIBUTES  => ''
            )
          , p_mandatory=> 'N'
          );
        else
          print_privilege(Rob_msg.getMsg(Rob_msg.CAP346_ODWAPRED_COMPILE, '', '', '', ''), '-');  
        end if;
        if substr(l_grantor_acc_privs, 8,1)='Y' 
        then
          print_property
          ( Rob_msg.getMsg(Rob_msg.CAP347_ODWAPRED_EDITSPEC, '', '', '', '')
          , htf.formCheckBox
            ( CNAME        => 'p_upd_spec'
            , CVALUE       => 'Y'
            , CCHECKED     => cdwpbase.ifThenElse
                              ( nvl( p_upd_spec, 'N')='Y' 
                              , 'Y'
                              )
            , CATTRIBUTES  => ''
            )
          , p_mandatory=> 'N'
          );
        else
          print_privilege(Rob_msg.getMsg(Rob_msg.CAP347_ODWAPRED_EDITSPEC, '', '', '', ''), '-');  
        end if;
     end if; -- r_acc.object_type ='WA'
   end if; -- get_versioned_repository
   if l_object_type ='CEL'      
   then     
     print_property
      ( Rob_msg.getMsg(Rob_msg.DSP339_ODWAPRED_RECURSE, '', '', '', '')
      , htf.formCheckBox
        ( CNAME        => 'p_recursive'
        , CVALUE       => 'Y'
        , CCHECKED     => ''
        , CATTRIBUTES  => ''
        )
      , p_mandatory=> 'N'
      );
   end if; -- l_object_type ='FOL'
   print_property( Rob_msg.getMsg(Rob_msg.CAP335_ODWAPRED_GRANTOR, '', '', '', ''), USER);
   print_property( Rob_msg.getMsg(Rob_msg.CAP206_ODWAPROP_OWNER, '', '', '', ''), odwapriv.get_governor(p_irid ));
   print_property( Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', ''), to_char( sysdate, 'dd-mon-yyyy hh24:mi'));
  cdwp.tableRowOpen;
  cdwp.TableDataValue
  ( cdwpbase.nbsp(10)
  ||'<INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP311_ODWAPREF_SAVE, '', '', '', '')||'" '
  ||' onClick="javascript:privilegesForm.submit()"; return true;'
  ||'>'
  || cdwpbase.nbsp(10)
  ||' <INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP243_ODWASPOT_CANCEL, '', '', '', '')||'" '
  ||' onClick="javascript:if(window.opener != top.opener){top.close();}else{top.rightSide.paletteFrame.history.back();return true;}"'
  ||' >'
  , p_attributes => 'VALIGN="TOP" colspan=2'
  );
  cdwp.tableRowClose;
  htp.formClose;
  close_palette;
  htp.bodyClose;
end; -- grantPrivs


procedure grantObject
( p_session_id in number
, p_grantee    in varchar2  
, p_object_type in varchar2 -- values WA, CFG, FOL
, p_context_irid  in number default null -- can be the Parent Folder or Workarea Irid
, qFolderIrid  in number    default null
, qFolderIvid  in number    default null
, p_select     in varchar2  default null
, p_insert     in varchar2  default null
, p_update     in varchar2  default null
, p_delete     in varchar2  default null
, p_admin      in varchar2  default null
, p_version    in varchar2  default null
, p_compile    in varchar2  default null
, p_upd_spec   in varchar2  default null
, p_error_message    in varchar2 default null
) is
  l_object_list varchar2(10000);


  cursor c_fol( b_grantee in varchar2              
              ) is
    select fol.irid
    ,      jr_name.get_path( fol.irid, 'NAME','',0) fol_label
    ,      fol.name
    ,      fol.root_flag
    from   i$sdd_folders fol
    where  not exists
           ( select 'x'
             from   sdw_access_rights acc
             where  acc.object_reference = fol.irid
             and    acc.grantee_reference = b_grantee
             )
   and     fol.ivid = cdwpbase.get_best_ivid( fol.irid)
   order
   by      2
   ;
  cursor c_cfg( b_grantee in varchar2              
              ) is
    select cfg.irid
    ,      cfg.name
    from   i$sdd_configurations cfg
    where  not exists
           ( select 'x'
             from   sdw_access_rights acc
             where  acc.object_reference = cfg.irid
             and    acc.grantee_reference = b_grantee
             )
   and     cfg.ivid = cdwpbase.get_best_ivid( cfg.irid)
   order
   by      2
   ;
  cursor c_wa( b_grantee in varchar2              
              ) is
    select wa.irid
    ,      wa.name
    from   i$sdd_workareas wa
    where  not exists
           ( select 'x'
             from   sdw_access_rights acc
             where  acc.object_reference = wa.irid
             and    acc.grantee_reference = b_grantee
             )
   order
   by      2
   ;
begin
  odwactxt.update_context
  ( p_session_id    => odwactxt.get_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'edit_object_privs'  );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  -- JavaScript, required to invoke RObjS
  odwarobs.js_invoke_robs
  ( p_type_id          =>  4845 -- 4844 = FOLDER, 4845 is supertype
  , p_form_name        => 'folderForm'
  , p_ivid_field       => 'qFolderIvid'
  , p_irid_field       => 'qFolderIrid'
  , p_workarea_irid    => odwactxt.get_workarea_irid
  , p_function_name    => 'invokeRobsFolderIrid'
  );

  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
  open_palette
  ( p_title => 'Grant Privileges on '
                  ||cdwpbase.ifThenElse
                    ( p_object_type = 'WA'
                    , cdwp.add_images('{workarea.gif}')
                    ||'Workarea'             
                    , cdwpbase.ifThenElse
                    ( p_object_type = 'CFG'
                    , cdwp.add_images('{configuration.gif}')
                      ||'Configuration'             
                    , cdwp.add_images('{folder.gif}')
                      ||'Folder/Application System'             
                     )
                   )
                  ||' to '
                  ||p_grantee
  , p_him_id => 610
  );

  if p_error_message is not null
  then
    -- errorMessage
    print_property
    ( 'Error'
    ,  '<b>Try Again: '||p_error_message||'</b>'
    , p_mandatory=> 'Y'
    );
  end if; -- p_error_message is not null
  htp.formopen
  ( curl         => 'odwapred.grantObject'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="folderForm" '
  , ctarget      => ''
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_session_id" '
  ||' VALUE="'||to_char(odwactxt.get_session_id)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_object_type" '
  ||' VALUE="'||cdwpbase.ifThenElse( p_object_type = 'FOL', 'CEL', p_object_type)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_grantee" '
  ||' VALUE="'||p_grantee||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_context_irid" '
  ||' VALUE="'||to_char(p_context_irid)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="qFolderIrid" '
  ||' VALUE="'||to_char(qFolderIrid)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="qFolderIvid" '
  ||' VALUE="'||to_char(qFolderIvid)||'">'
  );
  
  htp.formClose;

  htp.formopen
  ( curl         => 'odwapred.saveNewObjectsPrivileges'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="privilegesForm" '
  , ctarget      => ''
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_session_id" '
  ||' VALUE="'||to_char(odwactxt.get_session_id)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_object_type" '
  ||' VALUE="'||cdwpbase.ifThenElse( p_object_type = 'FOL', 'CEL', p_object_type)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_context_irid" '
  ||' VALUE="'||to_char(p_context_irid)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_grantee" '
  ||' VALUE="'||p_grantee||'">'
  );
  if qFolderIrid is not null
  then
    htp.p
    ( ' <INPUT TYPE="hidden" NAME="p_browse_irid" '
    ||' VALUE="'||to_char(qFolderIrid)||'">'
    );
  end if; -- qFolderIrid is not null
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP330_ODWAPRED_GRANTEE, '', '', '', '')
  , p_grantee
  , p_mandatory=> 'Y'
  );
  -- Object
  l_object_list:= htf.formselectopen
                   ( cname   => 'p_irid'
                   , cprompt => ''
                   , nsize   => 5
                   , cattributes => 'MULTIPLE'
                   );
  if p_object_type in ('CEL','FOL','APP')
  then
    for r_fol in c_fol( b_grantee=> p_grantee) loop
     if jr_acc_rights.has_access
        ( object_irid  => r_fol.irid
        , grantee      => USER
        , privilege    => 'ADM'
        )
        or
        jr_role.get_object_owner(p_irid => r_fol.irid) = 'USER'
     then
       if (
           p_context_irid is null
           and
           r_fol.root_flag ='Y'
          )
          or
          p_context_irid = jr_acc_rights.get_owning_container( obj_irid => r_fol.irid)
        then
          l_object_list:= l_object_list
          ||'<OPTION VALUE="'||to_char(r_fol.irid)||'"'
           ||'>'
           ||r_fol.name||' - '||r_fol.fol_label
           ;
        end if;
      end if;
    end loop; -- r_grt
    
    
  elsif p_object_type='WA'
  then
    for r_wa in c_wa( b_grantee=> p_grantee) loop
     if jr_acc_rights.has_access
        ( object_irid  => r_wa.irid
        , grantee      => USER
        , privilege    => 'ADM'
        )
        or
        jr_role.get_object_owner(p_irid => r_wa.irid) = 'USER'
     then
        l_object_list:= l_object_list
        ||'<OPTION VALUE="'||to_char(r_wa.irid)||'"'
         ||'>'
         ||r_wa.name
         ;
      end if;
    end loop; -- r_wa
  elsif p_object_type='CFG'
  then
    for r_cfg in c_cfg( b_grantee=> p_grantee) loop
     if jr_acc_rights.has_access
        ( object_irid  => r_cfg.irid
        , grantee      => USER
        , privilege    => 'ADM'
        )
        or
        jr_role.get_object_owner(p_irid => r_cfg.irid) = 'USER'
     then
        l_object_list:= l_object_list
        ||'<OPTION VALUE="'||to_char(r_cfg.irid)||'"'
         ||'>'
         ||r_cfg.name
         ;
      end if;
    end loop; -- r_cfg
  end if; -- p_object_type ='FOL'
  l_object_list:= l_object_list
  ||htf.formselectclose
  ;

  print_property
  ( cdwpbase.ifThenElse
    ( p_object_type = 'WA'
    , cdwp.add_images('{workarea.gif}')
    ||'Workarea'             
    , cdwpbase.ifThenElse
    ( p_object_type = 'CFG'
    , cdwp.add_images('{configuration.gif}')
      ||'Configuration'             
    , cdwp.add_images('{folder.gif}')
      ||cdwpbase.ifThenElse
        ( p_context_irid is null
        , 'Root Folders/<BR>Application Systems'             
        , 'Child Folders/<BR>Application Systems of '
        ||odwapriv.get_object_label(p_irid => p_context_irid,p_include_gif => false)
        )
     )
   )
  , l_object_list
  , p_mandatory=> 'Y'
  );
  if p_object_type in ('CEL','FOL','APP')
  then
    print_property
    ( cdwp.add_images('{folder.gif}')
        ||'Any Folder'
    , ' <TABLE BORDER=1 BGCOLOR="#FFFFFF"><TR><TD>'
        ||jr_name.GET_PATH
          ( EL_ID => nvl( qFolderIvid, qFolderIrid)
          , EL_TYPE=> 4845 -- 4844 = FOLDER, 4845 is supertype
          , FORMAT => 'NAME'
          )
        ||cdwpbase.ifThenElse
          ( qFolderIvid is not null
          , ' ('||odwavrsn.get_version_label( p_ivid => to_number(qFolderIvid))||')'
          )
        ||cdwpbase.ifThenElse
          ( qFolderIvid is null
          , cdwpbase.nbsp(15)
          , cdwpbase.nbsp(4)
          )
        ||' <TD BGCOLOR="#FFFFFF"> '
        ||' <A href="javascript:invokeRobsFolderIrid()">'
        ||cdwp.add_images( '{browse_round.gif}')
        ||'</A>'
        ||odwahelp.help_url( p_him_id => 615, p_top=> true)
        ||'</TD>'
        ||'</TR></TABLE>'
    , p_mandatory=> 'N'
    );
  end if; -- p_object_type in ('CEL','FOL','APP')

  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_select" '
  ||' VALUE="'||'Y'||'">'
  );
  print_privilege(Rob_msg.getMsg(Rob_msg.CAP340_ODWAPRED_SELECT, '', '', '', ''), 'Y');
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP342_ODWAPRED_INSERT, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_insert'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl(p_insert,'N') ='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP341_ODWAPRED_UPDATE, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_update'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl(p_update,'N') ='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP343_ODWAPRED_DELETE, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_delete'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl(p_delete,'N') ='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP345_ODWAPRED_ADMIN, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_admin'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl(p_admin,'N') ='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
  if odwapriv.get_versioned_repository
  then
      print_property
      ( Rob_msg.getMsg(Rob_msg.CAP344_ODWAPRED_VERSION, '', '', '', '')
      , htf.formCheckBox
        ( CNAME        => 'p_version'
        , CVALUE       => 'Y'
        , CCHECKED     => cdwpbase.ifThenElse
                          ( nvl(p_version,'N') ='Y' 
                          , 'Y'
                          )
        , CATTRIBUTES  => ''
        )
      , p_mandatory=> 'N'
      );
     if p_object_type ='WA'
     then
          print_property
          ( Rob_msg.getMsg(Rob_msg.CAP346_ODWAPRED_COMPILE, '', '', '', '')
          , htf.formCheckBox
            ( CNAME        => 'p_compile'
            , CVALUE       => 'Y'
            , CCHECKED     => cdwpbase.ifThenElse
                              ( nvl( p_compile, 'N')='Y' 
                              , 'Y'
                              )
            , CATTRIBUTES  => ''
            )
          , p_mandatory=> 'N'
          );
          print_property
          ( Rob_msg.getMsg(Rob_msg.CAP347_ODWAPRED_EDITSPEC, '', '', '', '')
          , htf.formCheckBox
            ( CNAME        => 'p_upd_spec'
            , CVALUE       => 'Y'
            , CCHECKED     => cdwpbase.ifThenElse
                              ( nvl( p_upd_spec, 'N')='Y' 
                              , 'Y'
                              )
            , CATTRIBUTES  => ''
            )
          , p_mandatory=> 'N'
          );
     end if; -- p_object_type ='WA'
   end if; -- get_versioned_repository
   if p_object_type in ('CEL', 'FOL' )
   then     
     print_property
      ( Rob_msg.getMsg(Rob_msg.DSP339_ODWAPRED_RECURSE, '', '', '', '')
      , htf.formCheckBox
        ( CNAME        => 'p_recursive'
        , CVALUE       => 'Y'
        , CCHECKED     => ''
        , CATTRIBUTES  => ''
        )
      , p_mandatory=> 'N'
      );
   end if; -- l_object_type ='FOL'
   print_property( Rob_msg.getMsg(Rob_msg.CAP335_ODWAPRED_GRANTOR, '', '', '', ''), USER);
   print_property( Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', ''), to_char( sysdate, 'dd-mon-yyyy hh24:mi'));
  cdwp.tableRowOpen;
  cdwp.TableDataValue
  ( cdwpbase.nbsp(10)
  ||'<INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP311_ODWAPREF_SAVE, '', '', '', '')||'" '
  ||' onClick="javascript:privilegesForm.submit()"; return true;'
  ||'>'
  || cdwpbase.nbsp(10)
  ||' <INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP243_ODWASPOT_CANCEL, '', '', '', '')||'" '
  ||' onClick="javascript:top.rightSide.paletteFrame.history.back();return true;"'
  ||' >'
  , p_attributes => 'VALIGN="TOP" colspan=2'
  );
  cdwp.tableRowClose;
  htp.formClose;
  close_palette;
  htp.bodyClose;
end; -- grantObject


procedure revokeObjectPrivileges
( p_session_id in number    default null
, p_irid          in number
, p_grantee_name  in varchar2  
) is
  cursor c_acc
         ( b_irid      in number
         , b_grantee   in varchar2
         )
  is
  select acc.irid
  ,      acc.object_type
  from   sdw_access_rights    acc
  where  acc.grantee_reference = b_grantee
  and    acc.object_reference  = b_irid
  ;
  r_acc c_acc%rowtype;
begin  
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'revokeObjectPrivileges'
  );
  open c_acc( b_irid      => p_irid
            , b_grantee   => p_grantee_name
            );
  fetch c_acc
  into  r_acc;
  close c_acc;
  begin
    if r_acc.irid is not null
    then
      begin
        jr_acc_rights.del
        ( irid => r_acc.irid
        );
      exception
        when no_data_found  -- catch because of BUG 1477668
        then
          null;
        when others
        then
          --1.4  to attack undefined/spurious/irreproducible problems
          -- the hard way
         add_line( handle_exception);
      end;
    else
      add_line(' Access privileges presumed to be removed.');
    end if; -- r_acc.irid is not null
    -- Message
    add_line
    ( 'Revoked object privileges '
    ||' from '
    ||p_grantee_name
    );
  exception
    when others
    then
      add_line
      ( 'Error'
      ||sqlerrm      
      );      
  end; 
  action_report
  ( p_session_id             => p_session_id
  , p_node_value             => p_grantee_name
  , p_node_type              => '{IRID='||p_irid||'}'
  , p_root_value             => ''
  , p_root_classification    => ''
  , p_refresh_tree => true
  , p_refresh_tree_level => 2
  , p_action_report => g_action_report
  , p_display_label => 'Revoking Access Privileges'
  ||' on '||odwapriv.get_object_label(p_irid =>  to_number(p_irid),p_include_gif => true)
  ||' from '||p_grantee_name
  );
end; -- revokeObjectPrivileges


procedure recurseNested
( p_grantee    in varchar2
, p_irid       in number
, p_base_privs in varchar2
, p_level      in number default 1
) is
 l_acc_irid   number;
 l_base_privs varchar2(10);
  -- select child-folders of b_irid
  cursor c_sub( b_irid in number) is
  select distinct fol.irid irid
    from I$SDD_FOLDERS fol
        ,I$SDD_FOLDER_MEMBERS fm
   where fm.folder_reference=b_irid
     and fm.ownership_flag='Y'
     and fol.irid=fm.member_object
     ;
begin
  if p_level < 99  -- protection against infinite recursive loops
  then  
    for r_sub in c_sub( b_irid => p_irid) loop
       --Get irid for access rights
       select min(acc.irid)
         into l_acc_irid
         from SDW_ACCESS_RIGHTS acc
        where acc.object_reference= r_sub.irid
          and acc.grantee_reference=UPPER(p_grantee)
          ;
      add_line
      ( lpad('.', p_level * 3)
      ||cdwpbase.ifThenElse
        ( l_acc_irid is null
        , '..inserting'
        , '..updating'
        )
      ||'.. on '
      ||odwapriv.get_object_label(p_irid => r_sub.irid,p_include_gif => false)
      );
      begin
       if l_acc_irid is null
       then
         -- insert new set of access privileges
         -- as logical AND of p_base_privs and current users' privileges
         l_acc_irid:= jr_acc_rights.ins
                ( object_irid  => r_sub.irid
                , object_type  => 'CEL'
                , grantee      => p_grantee
                , access_privs => jr_acc_rights.str_to_ar
                                  ( jr_role.AND_PRIVS
                                    ( p_first  => p_base_privs
                                    , p_second => jr_acc_rights.ar_to_str
                                                  ( jr_acc_rights.get_acc_rights
                                                    ( object_irid => r_sub.irid
                                                    , grantee => 'USER'
                                                    )
                                                  )
                                    )
                                  )
                );
  
       else
         -- update existing set of access privileges     
         -- as logical OR between existing grantee's privs and the (AND of p_base_privs and current users' privileges)
         jr_acc_rights.upd
         ( irid  => l_acc_irid
         , access_privs => jr_acc_rights.str_to_ar
                      ( jr_role.OR_PRIVS
                        ( p_first =>  jr_acc_rights.ar_to_str
                                                    ( jr_acc_rights.get_acc_rights
                                                      ( object_irid => r_sub.irid
                                                      , grantee => p_grantee
                                                      )
                                                    )
                        , p_second => jr_role.AND_PRIVS
                                      ( p_first  => p_base_privs
                                      , p_second => jr_acc_rights.ar_to_str
                                                    ( jr_acc_rights.get_acc_rights
                                                      ( object_irid => r_sub.irid
                                                      , grantee => 'USER'
                                                      )
                                                    )
                                      )
                        )
                      )
         );
         end if; -- l_acc_irid is null
       exception
         when jr_acc_rights.access_rights_error
         then
           if jr_acc_rights.has_access(object_irid => r_sub.irid ,grantee => USER,privilege => 'ADM')
           then
             add_line
              ( lpad('.', p_level * 3)
              ||'..failed to grant privileges on '
              ||odwapriv.get_object_label(p_irid => r_sub.irid,p_include_gif => false)              
              );
           else
             add_line
              ( lpad('.', p_level * 3)
              ||'..failed to grant privileges on '
              ||odwapriv.get_object_label(p_irid => r_sub.irid,p_include_gif => false)
              ||' because you ('||USER||') do not have administrate privileges on this Folder.'
              );
           end if; -- jr_acc_rights.has_access(object_irid => r_sub.irid ,grantee => USER,privilege => 'ADM')
       end; -- ins/upd access privileges
        recurseNested
        ( p_grantee    => p_grantee
        , p_irid       => r_sub.irid
        , p_base_privs => p_base_privs
        , p_level => p_level + 1
        );            
    end loop; -- r_sub
  end if; -- p_level < 99
end; -- recurseNested


-- this procedure calls JR_ACC_RIGHTS to update the access rights for 
-- p_grantee on p_irid; subsequently it calls node selected for the current grant
procedure saveObjectPrivileges
( p_session_id in number
, p_grantee    in varchar2
, p_irid       in varchar2
, p_select     in varchar2  default null
, p_insert     in varchar2  default null
, p_update     in varchar2  default null
, p_delete     in varchar2  default null
, p_admin      in varchar2  default null
, p_version    in varchar2  default null
, p_compile    in varchar2  default null
, p_upd_spec   in varchar2  default null
, p_recursive  in varchar2  default null
) is
  l_object_type varchar2(10):=odwapriv.get_object_type(to_number(p_irid));
begin
  reset_report;
  add_line
  ('Updating access privileges '
  ||' on '||odwapriv.get_object_label(p_irid =>  to_number(p_irid),p_include_gif => false)
  ||' for '||p_grantee
  );
  -- 'DEL' , 'ADM', 'INS', 'SEL', 'UPD', 'VER', 'CMP', 'UPD_SPEC';
  jr_acc_rights.upd
  ( object_irid  => to_number( p_irid)
  , grantee      => p_grantee
  , access_privs => jr_acc_rights.str_to_ar
                    ( nvl(p_delete, 'N')
                    ||nvl(p_admin , 'N')
                    ||nvl(p_insert, 'N')
                    ||nvl(p_select, 'N')
                    ||nvl(p_update, 'N')
                    ||nvl(p_version, 'N')
                    ||nvl(p_compile, 'N')
                    ||nvl(p_upd_spec, 'N')
                    )
  );
  -- if object_type = FOL andf p_recursive =Y then grant privs on all child folders
  if odwapriv.get_object_type(to_number(p_irid)) = 'CEL'
     and
     p_recursive = 'Y'
  then
    -- recurse nested folders
    -- if access rights already exist on folder, upd else ins
    -- make sure USER does not try to grant privs he does not have himself
    recurseNested
    ( p_grantee    => p_grantee
    , p_irid       => to_number( p_irid)
    , p_base_privs => nvl(p_delete, 'N')
                    ||nvl(p_admin , 'N')
                    ||nvl(p_insert, 'N')
                    ||'Y'
                    ||nvl(p_update, 'N')
                    ||nvl(p_version, 'N')
                    ||'N'
                    ||'N'
    );
  end if;
  action_report
  ( p_session_id             => p_session_id
  , p_node_value             => p_grantee
  , p_node_type              => '{IRID='||p_irid||'}'
                            ||'{GRANTEE='||p_grantee||'}'
                            || '{TYPE=GRANTEE_OF_'
                              ||cdwpbase.ifThenElse
                                ( l_object_type = 'CEL'
                                , 'FOL'
                                , l_object_type
                                )                                
                            ||'}{'
                            ||cdwpbase.ifThenElse
                                ( l_object_type = 'CEL'
                                , 'FOL'
                                , l_object_type
                                )                                
                            ||'_IRID='||p_irid||'}'
  , p_root_value             => ''
  , p_root_classification    => '{TYPE='||cdwpbase.ifThenElse
                                ( l_object_type = 'CEL'
                                , 'FOL'
                                , l_object_type
                                )||'}'
  , p_refresh_tree => false
  , p_action_report => g_action_report
  , p_display_label => 'Updating Access Privileges'
  ||' on '||odwapriv.get_object_label(p_irid =>  to_number(p_irid),p_include_gif => true)
  ||' for '||p_grantee
  );
exception
  when others
  then
    edit_object_privs
    ( p_session_id 
    , p_grantee    
    , p_irid       
    , p_select     
    , p_insert     
    , p_update     
    , p_delete     
    , p_admin      
    , p_version    
    , p_compile    
    , p_upd_spec   
    , p_error_message => sqlerrm
    ||g_action_report
    );     
end; -- saveObjectPrivileges


procedure saveNewObjectPrivileges
( p_session_id in number
, p_grantee    in varchar2
, p_object_type in varchar2
, p_irid       in varchar2
, p_select     in varchar2  default null
, p_insert     in varchar2  default null
, p_update     in varchar2  default null
, p_delete     in varchar2  default null
, p_admin      in varchar2  default null
, p_version    in varchar2  default null
, p_compile    in varchar2  default null
, p_upd_spec   in varchar2  default null
, p_recursive  in varchar2  default null
) is
 l_acc_irid number(38);
 l_parent_folder_irid number(38);
begin
  reset_report;
  add_line
  ('Updating access privileges '
  ||' on '||odwapriv.get_object_label(p_irid =>  to_number(p_irid),p_include_gif => false)
  ||' for '||p_grantee
  );
  l_acc_irid:= jr_acc_rights.ins
              ( object_irid  => to_number( p_irid)
              , object_type  => p_object_type
              , grantee      => p_grantee
              , access_privs => jr_acc_rights.str_to_ar
                                ( nvl(p_delete, 'N')
                                ||nvl(p_admin , 'N')
                                ||nvl(p_insert, 'N')
                                ||nvl(p_select, 'N')
                                ||nvl(p_update, 'N')
                                ||nvl(p_version, 'N')
                                ||nvl(p_compile, 'N')
                                ||nvl(p_upd_spec, 'N')
                                )
              );
  -- if object_type = FOL then now also grant sel on all parent folders
  if p_object_type = 'CEL'  
  then
    l_parent_folder_irid:= to_number(p_irid);
    loop
      l_parent_folder_irid:= jr_acc_rights.get_owning_container( l_parent_folder_irid);
      if l_parent_folder_irid is null
      then
        exit;
      else
/*      procedure GRANT_PRIV(object_irid in number
                    ,grantee in varchar2
                    ,privilege in varchar2
                    ,cascade in boolean:=FALSE);
*/
        jr_acc_rights.grant_priv
        ( object_irid => l_parent_folder_irid
        , grantee     => p_grantee
        , privilege   => 'SEL'
        , cascade     => false
        );
      end if;
    end loop;
  end if; -- p_object_type = 'CEL'
  -- if object_type = FOL andf p_recursive =Y then grant privs on all child folders
  if p_object_type = 'CEL'
     and
     p_recursive = 'Y'
  then
    -- recurse nested folders
    -- if access rights already exist on folder, upd else ins
    -- make sure USER does not try to grant privs he does not have himself
    recurseNested
    ( p_grantee    => p_grantee
    , p_irid       => to_number( p_irid)
    , p_base_privs => nvl(p_delete, 'N')
                    ||nvl(p_admin , 'N')
                    ||nvl(p_insert, 'N')
                    ||'Y'
                    ||nvl(p_update, 'N')
                    ||nvl(p_version, 'N')
                    ||'N'
                    ||'N'
    );
  end if;
  action_report
  ( p_session_id             => p_session_id
  , p_node_value             => ''
  , p_node_type              => '{TYPE=GRANTEE_OF_'
                              ||cdwpbase.ifThenElse
                                ( p_object_type = 'CEL'
                                , 'FOL'
                                , p_object_type
                                )                                
                            ||'}{'
                            ||cdwpbase.ifThenElse
                                ( p_object_type = 'CEL'
                                , 'FOL'
                                , p_object_type
                                )                                
                            ||'_IRID='||p_irid||'}'
                            ||'{GRANTEE='||p_grantee||'}'
  , p_root_value             => ''
  , p_root_classification    => '{TYPE='||cdwpbase.ifThenElse
                                ( p_object_type = 'CEL'
                                , 'FOL'
                                , p_object_type
                                )||'}'
  , p_refresh_tree => false
  , p_action_report => g_action_report
  , p_display_label => 'Inserting Access Privileges'
  ||' on '||odwapriv.get_object_label(p_irid =>  to_number(p_irid),p_include_gif => true)
  ||' for '||p_grantee
  );
exception
  when others
  then
    grantPrivs
    ( p_session_id 
    , p_grantee    
    , p_irid       
    , p_select     
    , p_insert     
    , p_update     
    , p_delete     
    , p_admin      
    , p_version    
    , p_compile    
    , p_upd_spec   
    , p_error_message => sqlerrm
    ||g_action_report
    );     
end; -- saveNewObjectPrivileges

procedure saveNewObjPrivsGrantees
( p_session_id in number
, p_grantee    in odwapriv.t_string_table
, p_object_type in varchar2
, p_irid       in varchar2
, p_select     in varchar2  default null
, p_insert     in varchar2  default null
, p_update     in varchar2  default null
, p_delete     in varchar2  default null
, p_admin      in varchar2  default null
, p_version    in varchar2  default null
, p_compile    in varchar2  default null
, p_upd_spec   in varchar2  default null
, p_recursive  in varchar2  default null
) is
 type t_fol_tbl 
 is table of number(38)
 index by binary_integer
 ;
 l_fol_tbl t_fol_tbl;

 l_acc_irid number(38);
 l_parent_folder_irid number(38);
begin
  reset_report;
  add_line
  ( 'Granting access on '
  ||cdwpbase.ifThenElse
     ( p_object_type = 'WA'
     , 'Workarea'
     , cdwpbase.ifThenElse
       ( p_object_type = 'CFG'
       , 'Configuration'
       , 'Folder'
       )
     )
  ||' '
  ||odwapriv.get_object_label(p_irid => to_number(p_irid),p_include_gif => false)
  );
   add_line( '- select');
   add_line( cdwpbase.ifThenElse( p_insert = 'Y', '- insert'));
   add_line( cdwpbase.ifThenElse( p_update = 'Y', '- update'));
   add_line( cdwpbase.ifThenElse( p_delete = 'Y', '- delete'));
   add_line( cdwpbase.ifThenElse( p_admin = 'Y', '- administrate'));
   add_line( cdwpbase.ifThenElse( p_version = 'Y', '- version'));
   add_line( cdwpbase.ifThenElse( p_compile = 'Y', '- compile/refresh'));
   add_line( cdwpbase.ifThenElse( p_upd_spec = 'Y', '- edit/update specification'));
    if p_object_type = 'CEL'  
    then
      l_parent_folder_irid:= to_number(p_irid);
      loop
        l_parent_folder_irid:= jr_acc_rights.get_owning_container( l_parent_folder_irid);
        if l_parent_folder_irid is null
        then
          exit;
        else
         l_fol_tbl( l_fol_tbl.count + 1):= l_parent_folder_irid;
        end if;
      end loop;
    end if;   
  for i in 1..p_grantee.count loop  
    add_line('==================================');
    add_line(' Inserting privileges for '||p_grantee(i)||'...');
    -- if object_type = FOL then now also grant sel on all parent folders
    -- the jr_acc_rights.ins procedure apparently checks rightaway whether the parent folder
    -- has been granted; therefore, we need to walk down the tree, starting at the root
    if p_object_type = 'CEL'  
    then
      for j in reverse 1..l_fol_tbl.count loop
         add_line
         (' Inserting select privilege on Parent Folder '
         ||odwapriv.get_object_label(p_irid => l_fol_tbl(j),p_include_gif => false)
         ||'....'
         );

          jr_acc_rights.grant_priv
          ( object_irid => l_fol_tbl(j)
          , grantee     => p_grantee(i)
          , privilege   => 'SEL'
          , cascade     => false
          );
      end loop;
    end if; -- p_object_type = 'CEL'
    l_acc_irid:= jr_acc_rights.ins
                ( object_irid  => to_number( p_irid)
                , object_type  => p_object_type
                , grantee      => p_grantee(i)
                , access_privs => jr_acc_rights.str_to_ar
                                  ( nvl(p_delete, 'N')
                                  ||nvl(p_admin , 'N')
                                  ||nvl(p_insert, 'N')
                                  ||nvl(p_select, 'N')
                                  ||nvl(p_update, 'N')
                                  ||nvl(p_version, 'N')
                                  ||nvl(p_compile, 'N')
                                  ||nvl(p_upd_spec, 'N')
                                  )
                );
    -- if object_type = FOL andf p_recursive =Y then grant privs on all child folders
    if p_object_type = 'CEL'
       and
       p_recursive = 'Y'
    then
      -- recurse nested folders
      -- if access rights already exist on folder, upd else ins
      -- make sure USER does not try to grant privs he does not have himself
      add_line(' Starting recursive processing of childfolders for '||p_grantee(i));
      recurseNested
      ( p_grantee    => p_grantee(i)
      , p_irid       => to_number( p_irid)
      , p_base_privs => nvl(p_delete, 'N')
                      ||nvl(p_admin , 'N')
                      ||nvl(p_insert, 'N')
                      ||'Y'
                      ||nvl(p_update, 'N')
                      ||nvl(p_version, 'N')
                      ||'N'
                      ||'N'
      );
    end if;
  end loop; -- i in 1..p_grantee.count
 add_line('==================================');
 add_line(' Successfully Granted access privileges');
 action_report
  ( p_session_id             => p_session_id
  , p_node_value             => ''
  , p_node_type              => '{TYPE='
                              ||cdwpbase.ifThenElse
                                ( p_object_type = 'CEL'
                                , 'FOL'
                                , p_object_type
                                )                                
                            ||'}{'
                            ||cdwpbase.ifThenElse
                                ( p_object_type = 'CEL'
                                , 'FOL'
                                , p_object_type
                                )                                
                            ||'_IRID='||p_irid||'}'
  , p_root_value             => ''
  , p_root_classification    => '{TYPE='
                              ||cdwpbase.ifThenElse
                                ( p_object_type = 'CEL'
                                , 'FOL'
                                , p_object_type
                                )                                
                            ||'}{'
                            ||cdwpbase.ifThenElse
                                ( p_object_type = 'CEL'
                                , 'FOL'
                                , p_object_type
                                )                                
                            ||'_IRID='||p_irid||'}'
  , p_refresh_tree           => true
  , p_action_report          => g_action_report
  , p_display_label          => 'Grant access privileges on '
                              ||cdwpbase.ifThenElse
                               ( p_object_type = 'WA'
                               , 'Workarea'
                               , cdwpbase.ifThenElse
                                 ( p_object_type = 'CFG'
                                 , 'Configuration'
                                 , 'Folder'
                                 )
                               )
                            ||' '
                            ||odwapriv.get_object_label(p_irid => to_number(p_irid),p_include_gif => false)
);

exception
  when others
  then
    grantPrivs
    ( p_session_id 
    , p_grantee(1)    
    , p_irid       
    , p_select     
    , p_insert     
    , p_update     
    , p_delete     
    , p_admin      
    , p_version    
    , p_compile    
    , p_upd_spec   
    , p_error_message => sqlerrm
    ||' - '
    ||jr_role.get_error_description
    ||' after '
    ||g_action_report
    );     
end; -- saveNewObjPrivsGrantees

procedure saveNewObjectsPrivileges
( p_session_id in number
, p_grantee    in varchar2
, p_object_type in varchar2
, p_irid       in odwapriv.t_string_table default odwapriv.g_empty_table
, p_select     in varchar2  default null
, p_insert     in varchar2  default null
, p_update     in varchar2  default null
, p_delete     in varchar2  default null
, p_admin      in varchar2  default null
, p_version    in varchar2  default null
, p_compile    in varchar2  default null
, p_upd_spec   in varchar2  default null
, p_recursive  in varchar2  default null
, p_context_irid  in varchar2 default null
, p_browse_irid  in varchar2 default null
) is
 l_irid number(38);
 l_acc_irid number(38);
 l_parent_folder_irid number(38);
 l_grantee_type varchar2(1):= odwapriv.get_grantee_type(p_grantee => p_grantee);
 l_full_grantee_type varchar2(10):= cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE');
 l_irid_tbl  odwapriv.t_string_table:= p_irid;
begin
   reset_report;
   if p_browse_irid is not null
   then
     l_irid_tbl( l_irid_tbl.count + 1):= p_browse_irid;
   end if; -- p_browse_irid is not null
   add_line
   (' Granting access privileges on '
   ||cdwpbase.ifThenElse
     ( p_object_type = 'WA'
     , 'Workarea'
     , cdwpbase.ifThenElse
       ( p_object_type = 'CFG'
       , 'Configuration'
       , 'Folder'
       )
     )
   ||cdwpbase.ifThenElse  -- add an s for plural if access is granted on more than one object
     ( l_irid_tbl.count > 1
     , 's'
     )
   );
   add_line( '- select');
   add_line( cdwpbase.ifThenElse( p_insert = 'Y', '- insert'));
   add_line( cdwpbase.ifThenElse( p_update = 'Y', '- update'));
   add_line( cdwpbase.ifThenElse( p_delete = 'Y', '- delete'));
   add_line( cdwpbase.ifThenElse( p_admin = 'Y', '- administrate'));
   add_line( cdwpbase.ifThenElse( p_version = 'Y', '- version'));
   add_line( cdwpbase.ifThenElse( p_compile = 'Y', '- compile/refresh'));
   add_line( cdwpbase.ifThenElse( p_upd_spec = 'Y', '- edit/update specification'));
   for i in 1..l_irid_tbl.count loop
 -- null;
   l_irid := to_number(l_irid_tbl(i));
    l_acc_irid:= jr_acc_rights.ins
                ( object_irid  => to_number( l_irid_tbl(i))
                , object_type  => p_object_type
                , grantee      => p_grantee
                , access_privs => jr_acc_rights.str_to_ar
                                  ( nvl(p_delete, 'N')
                                  ||nvl(p_admin , 'N')
                                  ||nvl(p_insert, 'N')
                                  ||nvl(p_select, 'N')
                                  ||nvl(p_update, 'N')
                                  ||nvl(p_version, 'N')
                                  ||nvl(p_compile, 'N')
                                  ||nvl(p_upd_spec, 'N')
                                  )
                );
    -- if object_type = FOL then now also grant sel on all parent folders
    add_line
    (' on '
    ||odwapriv.get_object_label(p_irid => to_number(l_irid_tbl(i)),p_include_gif => false)
    );
    if p_object_type = 'CEL'  
    then
      l_parent_folder_irid:= to_number(l_irid_tbl(i));
      loop
        l_parent_folder_irid:= jr_acc_rights.get_owning_container( l_parent_folder_irid);
        if l_parent_folder_irid is null
        then
          exit;
        else
          jr_acc_rights.grant_priv
          ( object_irid => l_parent_folder_irid
          , grantee     => p_grantee
          , privilege   => 'SEL'
          , cascade     => false
          );
        end if;
      end loop;
    end if; -- p_object_type = 'CEL'
    -- if object_type = FOL andf p_recursive =Y then grant privs on all child folders
    if p_object_type = 'CEL'
       and
       p_recursive = 'Y'
    then
      -- recurse nested folders
      -- if access rights already exist on folder, upd else ins
      -- make sure USER does not try to grant privs he does not have himself
      recurseNested
      ( p_grantee    => p_grantee
      , p_irid       => to_number( l_irid_tbl(i))
      , p_base_privs => nvl(p_delete, 'N')
                      ||nvl(p_admin , 'N')
                      ||nvl(p_insert, 'N')
                      ||'Y'
                      ||nvl(p_update, 'N')
                      ||nvl(p_version, 'N')
                      ||'N'
                      ||'N'
      );
    end if;
 end loop; -- i in 1..l_irid_tbl.count
 add_line(' Successfully Granted access privileges');
 add_line(jr_role.get_error_description);
 action_report
  ( p_session_id             => p_session_id
  , p_node_value             => p_grantee
  , p_node_type              => '{TYPE='||l_full_grantee_type ||'}'
  , p_root_value             => '{TYPE='||l_full_grantee_type ||'}'
  , p_root_classification    => '{TYPE='||l_full_grantee_type ||'}'
  , p_refresh_tree           => true
  , p_action_report          => g_action_report
  , p_display_label          => 'Grant access privileges to '||l_full_grantee_type||' '||p_grantee
  );
exception
  when others
  then
    add_line(jr_role.get_error_description);
    grantObject
    ( p_session_id => p_session_id 
    , p_grantee    => p_grantee    
    , p_object_type => p_object_type
    , p_context_irid => p_context_irid
    , p_select => p_select     
    , p_insert => p_insert     
    , p_update => p_update     
    , p_delete => p_delete     
    , p_admin  => p_admin      
    , p_version => p_version    
    , p_compile => p_compile    
    , p_upd_spec => p_upd_spec   
    , p_error_message => sqlerrm
    ||' - '
    ||g_action_report
    );     
end; -- saveNewObjectsPrivileges

function get_grantor
( p_grantee    in varchar2
, p_irid       in varchar2
) return varchar2
is
  cursor c_acc
         ( b_irid      in number
         , b_grantee   in varchar2
         )
  is
  select acc.grantor_reference
  from   sdw_access_rights    acc
  where  acc.grantee_reference = b_grantee
  and    acc.object_reference  = b_irid
  ;
  r_acc c_acc%rowtype;
begin
  open c_acc( b_irid      => p_irid
            , b_grantee   => p_grantee
            );
  fetch c_acc
  into  r_acc;
  close c_acc;
  return r_acc.grantor_reference;
end; -- get_grantor


procedure edit_object_privs
( p_session_id in number
, p_grantee    in varchar2
, p_irid       in varchar2
, p_select     in varchar2  default null
, p_insert     in varchar2  default null
, p_update     in varchar2  default null
, p_delete     in varchar2  default null
, p_admin      in varchar2  default null
, p_version    in varchar2  default null
, p_compile    in varchar2  default null
, p_upd_spec   in varchar2  default null
, p_error_message    in varchar2 default null
) is
  l_grantee_acc_privs varchar2(20);
  l_grantee      varchar2(30)    := odwapriv.proper( p_grantee);
  l_grantee_type varchar2(1)     := odwapriv.get_grantee_type( p_grantee => p_grantee); -- R(ole) or U(ser)

  l_grantor_acc_privs varchar2(20);

  cursor c_acc
         ( b_irid      in number
         , b_grantee   in varchar2
         )
  is
  select acc.object_reference role_irid
  ,      acc.grantor_reference
  ,      acc.date_created
  ,      acc.date_changed
  ,      acc.access_privs
  ,      acc.object_type
  from   sdw_access_rights    acc
  where  acc.grantee_reference = b_grantee
  and    acc.object_reference  = b_irid
  ;
  r_acc c_acc%rowtype;

begin
  odwactxt.update_context
  ( p_session_id    => odwactxt.get_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'edit_object_privs'
  );
  open c_acc( b_irid      => p_irid
            , b_grantee   => p_grantee
            );
  fetch c_acc
  into  r_acc;
  close c_acc;
  l_grantee_acc_privs:= jr_acc_rights.ar_to_str
                        ( r_acc.access_privs
                        );
  l_grantor_acc_privs:= jr_acc_rights.ar_to_str
                        ( jr_acc_rights.get_acc_rights
                          ( object_irid => p_irid
                          , grantee     => USER
                          )
                        );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
  open_palette
  ( p_title => 'Edit Grant to '||p_grantee
            ||' on '||odwapriv.get_object_label( p_irid)

  , p_him_id => 640
  );

  if p_error_message is not null
  then
    -- errorMessage
    print_property
    ( 'Error'
    ,  '<b>Try Again: '||p_error_message||'</b>'
    , p_mandatory=> 'Y'
    );
  end if; -- p_error_message is not null

  htp.formopen
  ( curl         => 'odwapred.saveObjectPrivileges'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="privilegesForm" '
  , ctarget      => ''
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_session_id" '
  ||' VALUE="'||to_char(odwactxt.get_session_id)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_irid" '
  ||' VALUE="'||p_irid||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_grantee" '
  ||' VALUE="'||p_grantee||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_select" '
  ||' VALUE="'||'Y'||'">'
  );
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP330_ODWAPRED_GRANTEE, '', '', '', '')
  , cdwpbase.ifThenElse
    ( l_grantee_type = 'U'
    , cdwp.add_images('{user.gif}')
    , cdwp.add_images('{role.gif}')    
    )
  ||l_grantee  
  );
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP339_ODWAPRED_OBJECT, '', '', '', '')
  , odwapriv.get_object_label( p_irid)
  );
  open c_acc( b_irid      => p_irid
            , b_grantee   => p_grantee
            );
  fetch c_acc
  into  r_acc;
  close c_acc;
  l_grantee_acc_privs:= jr_acc_rights.ar_to_str
                        ( r_acc.access_privs
                        );
  -- 'DEL' , 'ADM', 'INS', 'SEL', 'UPD', 'VER', 'CMP', 'UPD_SPEC';
  print_privilege(Rob_msg.getMsg(Rob_msg.CAP340_ODWAPRED_SELECT, '', '', '', ''), substr(l_grantee_acc_privs, 4,1));
  -- Grantor can only grant privileges he owns himself!
  if substr(l_grantor_acc_privs, 3,1)='Y' 
  then
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP342_ODWAPRED_INSERT, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_insert'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl( p_insert, substr(l_grantee_acc_privs, 3,1))='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
  else
    print_privilege(Rob_msg.getMsg(Rob_msg.CAP342_ODWAPRED_INSERT, '', '', '', ''), '-');  
  end if;
  if substr(l_grantor_acc_privs, 5,1)='Y' 
  then
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP341_ODWAPRED_UPDATE, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_update'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl( p_update, substr(l_grantee_acc_privs, 5,1))='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
  else
    print_privilege(Rob_msg.getMsg(Rob_msg.CAP341_ODWAPRED_UPDATE, '', '', '', ''), '-');  
  end if;
  if substr(l_grantor_acc_privs, 1,1)='Y' 
  then
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP343_ODWAPRED_DELETE, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_delete'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl( p_delete, substr(l_grantee_acc_privs, 1,1))='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
  else
    print_privilege(Rob_msg.getMsg(Rob_msg.CAP343_ODWAPRED_DELETE, '', '', '', ''), '-');  
  end if;
  if substr(l_grantor_acc_privs, 2,1)='Y' 
  then
    print_property
    ( Rob_msg.getMsg(Rob_msg.CAP345_ODWAPRED_ADMIN, '', '', '', '')
    , htf.formCheckBox
      ( CNAME        => 'p_admin'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse
                        ( nvl( p_admin, substr(l_grantee_acc_privs, 2,1))='Y' 
                        , 'Y'
                        )
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'N'
    );
  else
    print_privilege(Rob_msg.getMsg(Rob_msg.CAP345_ODWAPRED_ADMIN, '', '', '', ''), '-');  
  end if;
  -- version privilege only applicable in a versioned repository
  -- ( and for users with the Version System privilege)
  if odwapriv.get_versioned_repository
  then
    if substr(l_grantor_acc_privs, 6,1)='Y' 
    then
      print_property
      ( Rob_msg.getMsg(Rob_msg.CAP344_ODWAPRED_VERSION, '', '', '', '')
      , htf.formCheckBox
        ( CNAME        => 'p_version'
        , CVALUE       => 'Y'
        , CCHECKED     => cdwpbase.ifThenElse
                          ( nvl( p_version, substr(l_grantee_acc_privs, 6,1))='Y' 
                          , 'Y'
                          )
        , CATTRIBUTES  => ''
        )
      , p_mandatory=> 'N'
      );
    else
      print_privilege(Rob_msg.getMsg(Rob_msg.CAP344_ODWAPRED_VERSION, '', '', '', ''), '-');  
    end if;
     if r_acc.object_type ='WA'
     then
        if substr(l_grantor_acc_privs, 7,1)='Y' 
        then
          print_property
          ( Rob_msg.getMsg(Rob_msg.CAP346_ODWAPRED_COMPILE, '', '', '', '')
          , htf.formCheckBox
            ( CNAME        => 'p_compile'
            , CVALUE       => 'Y'
            , CCHECKED     => cdwpbase.ifThenElse
                              ( nvl( p_compile, substr(l_grantee_acc_privs, 7,1))='Y' 
                              , 'Y'
                              )
            , CATTRIBUTES  => ''
            )
          , p_mandatory=> 'N'
          );
        else
          print_privilege(Rob_msg.getMsg(Rob_msg.CAP346_ODWAPRED_COMPILE, '', '', '', ''), '-');  
        end if;
        if substr(l_grantor_acc_privs, 8,1)='Y' 
        then
          print_property
          ( Rob_msg.getMsg(Rob_msg.CAP347_ODWAPRED_EDITSPEC, '', '', '', '')
          , htf.formCheckBox
            ( CNAME        => 'p_upd_spec'
            , CVALUE       => 'Y'
            , CCHECKED     => cdwpbase.ifThenElse
                              ( nvl( p_upd_spec, substr(l_grantee_acc_privs, 8,1))='Y' 
                              , 'Y'
                              )
            , CATTRIBUTES  => ''
            )
          , p_mandatory=> 'N'
          );
        else
          print_privilege(Rob_msg.getMsg(Rob_msg.CAP347_ODWAPRED_EDITSPEC, '', '', '', ''), '-');  
        end if;
  
     end if; -- r_acc.object_type ='WA'
   end if; -- get_versioned_repository
   if r_acc.object_type ='CEL'      
   then     
     print_property
      ( 'Grant privs on all nested child folders as well?'
      , htf.formCheckBox
        ( CNAME        => 'p_recursive'
        , CVALUE       => 'Y'
        , CCHECKED     => ''
        , CATTRIBUTES  => ''
        )
      , p_mandatory=> 'N'
      );
   end if; -- l_object_type ='FOL'

   if l_grantee_type = 'U'
   then
     if l_grantee = r_acc.grantor_reference
     then
       print_privilege(Rob_msg.getMsg(Rob_msg.CAP334_ODWAPRED_ISOWNER, '', '', '', ''), 'Y');
     end if;
   end if;
   print_property( Rob_msg.getMsg(Rob_msg.CAP335_ODWAPRED_GRANTOR, '', '', '', ''), r_acc.grantor_reference);
   print_property( Rob_msg.getMsg(Rob_msg.CAP206_ODWAPROP_OWNER, '', '', '', ''), odwapriv.get_governor(p_irid ));
   print_property( Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', ''), to_char( r_acc.date_created, 'dd-mon-yyyy hh24:mi'));
   print_property( Rob_msg.getMsg(Rob_msg.CAP338_ODWAPRED_DATECH, '', '', '', ''), to_char( r_acc.date_changed, 'dd-mon-yyyy hh24:mi'));
  cdwp.tableRowOpen;
  cdwp.TableDataValue
  ( cdwpbase.nbsp(10)
  ||'<INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP311_ODWAPREF_SAVE, '', '', '', '')||'" '
  ||' onClick="javascript:privilegesForm.submit()"; return true;'
  ||'>'
  || cdwpbase.nbsp(10)
  ||' <INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP243_ODWASPOT_CANCEL, '', '', '', '')||'" '
  ||' onClick="javascript:top.rightSide.paletteFrame.history.back();return true;"'
  ||' >'
  , p_attributes => 'VALIGN="TOP" colspan=2'
  );
  cdwp.tableRowClose;
  htp.formClose;
  close_palette;
  htp.bodyClose;
end; -- edit_object_privs


procedure editRoleGrant
( p_session_id       in number   default null
, p_role_name        in varchar2 
, p_grantee          in varchar2 default null
, p_grant_option     in varchar2 default null
, p_delete_option    in varchar2 default null
, p_reconcile_option in varchar2 default null
, p_reconcile        in varchar2 default null
, p_error_message    in varchar2 default null
) is
  l_grantee_list varchar2(10000);
  cursor c_acc
         ( b_role_name in varchar2
         , b_grantee   in varchar2
         )
  is
  select rle.username         role_name
  ,      acc.object_reference role_irid
  ,      acc.grantor_reference
  ,      acc.date_created
  ,      acc.date_changed
  ,      acc.access_privs
  ,      rle.created_by
  from   sdw_users            rle
  ,      sdw_access_rights    acc
  where  acc.grantee_reference = b_grantee
  and    acc.object_reference  = rle.irid
  and    rle.username          = b_role_name
  ;
  r_acc c_acc%rowtype;
  l_grantee      varchar2(30)    := odwapriv.proper( p_grantee);
  l_grantee_type varchar2(1)     := odwapriv.get_grantee_type( p_grantee => p_grantee); -- R(ole) or U(ser)
  l_grantee_acc_privs  varchar2(30);
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'editRoleGrant'
  );
  open c_acc( b_role_name => p_role_name
            , b_grantee   => p_grantee
            );
  fetch c_acc
  into  r_acc;
  close c_acc;
  l_grantee_acc_privs:= jr_acc_rights.ar_to_str
                        ( r_acc.access_privs
                        );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
  open_palette
  ( p_title => 'Edit Role Grant on '||p_role_name||' to '||p_grantee
  , p_him_id => 635
  );

  if p_error_message is not null
  then
    -- errorMessage
    print_property
    ( 'Error'
    ,  '<b>Try Again: '||p_error_message||'</b>'
    , p_mandatory=> 'Y'
    );
  end if; -- p_error_message is not null

  htp.formopen
  ( curl         => 'odwapred.saveRoleGrant'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="roleGrantForm" '
  , ctarget      => ''
  );
    htp.p
    ( ' <INPUT TYPE="hidden" NAME="p_role_name" '
    ||' VALUE="'||p_role_name||'">'
    );
    htp.p
    ( ' <INPUT TYPE="hidden" NAME="p_grantee" '
    ||' VALUE="'||p_grantee||'">'
    );
  print_property(  Rob_msg.getMsg(Rob_msg.CAP329_ODWAPRED_ROLE, '', '', '', ''), r_acc.role_name  );
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP330_ODWAPRED_GRANTEE, '', '', '', '')
  , cdwpbase.ifThenElse
    ( l_grantee_type = 'U'
    , cdwp.add_images('{user.gif}')
    , cdwp.add_images('{role.gif}')    
    )
  ||l_grantee  
  );
     if l_grantee_type = 'U'
     then
        print_property
        ( Rob_msg.getMsg(Rob_msg.CAP331_ODWAPRED_GROPTION, '', '', '', '')
        , htf.formCheckBox
          ( CNAME        => 'p_grant_option'
          , CVALUE       => 'Y'
          , CCHECKED     => cdwpbase.ifThenElse( substr(l_grantee_acc_privs, 2,1)='Y' , 'Y')
          , CATTRIBUTES  => ''
          )
        , p_mandatory=> 'N'
        );
        print_property
        ( Rob_msg.getMsg(Rob_msg.CAP332_ODWAPRED_DELOPTION, '', '', '', '')
        , htf.formCheckBox
          ( CNAME        => 'p_delete_option'
          , CVALUE       => 'Y'
          , CCHECKED     => cdwpbase.ifThenElse( substr(l_grantee_acc_privs, 1,1)='Y' , 'Y')
          , CATTRIBUTES  => ''
          )
        , p_mandatory=> 'N'
        );
        print_property
        ( Rob_msg.getMsg(Rob_msg.CAP333_ODWAPRED_RECOPTION, '', '', '', '')
        , htf.formCheckBox
          ( CNAME        => 'p_reconcile_option'
          , CVALUE       => 'Y'
          , CCHECKED     => cdwpbase.ifThenElse( substr(l_grantee_acc_privs, 7,1)='Y' , 'Y')
          , CATTRIBUTES  => ''
          )
        , p_mandatory=> 'N'
        );
     end if;

     print_property( Rob_msg.getMsg(Rob_msg.CAP335_ODWAPRED_GRANTOR, '', '', '', ''), r_acc.grantor_reference);
     print_property( Rob_msg.getMsg(Rob_msg.CAP336_ODWAPRED_ROWNER, '', '', '', ''), nvl( odwapriv.get_role_owner(p_role_name ),odwapriv.get_role_owner(p_role_name )) );
     print_property( Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', ''), to_char( r_acc.date_created, 'dd-mon-yyyy hh24:mi'));
     print_property( Rob_msg.getMsg(Rob_msg.CAP338_ODWAPRED_DATECH, '', '', '', ''), to_char( r_acc.date_changed, 'dd-mon-yyyy hh24:mi'));
    

  cdwp.tableRowOpen;
  cdwp.TableDataValue
  ( cdwpbase.nbsp(10)
  ||'<INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP311_ODWAPREF_SAVE, '', '', '', '')||'" '
  ||' onClick="javascript:roleGrantForm.submit()"; return true;'
  ||'>'
  || cdwpbase.nbsp(10)
  ||' <INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP243_ODWASPOT_CANCEL, '', '', '', '')||'" '
  ||' onClick="javascript:top.rightSide.paletteFrame.history.back();return true;"'
  ||' >'
  , p_attributes => 'VALIGN="TOP" colspan=2'
  );
  htp.formClose;
  cdwp.tableRowClose;
  close_palette;  --1.6  to fix issue 213 - no palette displayed
  htp.bodyClose;
end; -- editRoleGrant

procedure deleteRole
( p_session_id in number    default null
, p_role_name  in varchar2  
) is
begin  
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'deleteRole'
  );
  reset_report;
  jr_role.delete_role
  ( p_role_name => p_role_name
  );
  add_line( jr_role.get_error_description);
  action_report
  ( p_session_id => odwactxt.get_session_id
  , p_node_type  => '{TYPE=ROOT}'
  , p_node_value => 'ROLE'
  , p_root_value => 'ROLE'
  , p_root_classification => '{TYPE=ROOT}'
  , p_action_report => 'Role '||p_role_name||' has been deleted.'
  , p_refresh_tree => true
  , p_refresh_tree_level => 1 -- refresh parent node
  , p_display_label => 'Delete Role'
  );
  exception
    when others
    then
      add_line( 'Deletion of Role '||p_role_name||' has failed.');
      add_line( 'Exception Occurred: '||sqlerrm);
      add_line( 'Exception Occurred: '||jr_role.get_error_description);
      action_report
      ( p_session_id => odwactxt.get_session_id
      , p_node_type  => '{TYPE=ROOT}'
      , p_node_value => 'ROLE'
      , p_root_value => 'ROLE'
      , p_root_classification => '{TYPE=ROOT}'
      , p_action_report => g_action_report
      , p_refresh_tree => false
      , p_display_label => 'Delete Role'
      );
end; -- deleteRole

procedure doRevokeRole
( p_session_id in number    default null
, p_role_name  in varchar2  
, p_grantee    in varchar2  
, p_reconcile  in varchar2  default 'N'
) is
 l_grantee_type varchar2(1):= odwapriv.get_grantee_type(p_grantee => p_grantee);
begin  
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'doRevokeRole'
  );
  reset_report;
  add_line
  ( 'Revoke Role '
  ||p_role_name
  ||' from '
  ||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')
  ||' '||p_grantee
  ||cdwpbase.ifThenElse
    ( p_reconcile = 'Y'
    , ' with reconcile.'
    )
  );
  begin
    jr_role.revoke_role
    ( p_role_to_revoke => p_role_name
    , p_grantee        => p_grantee
    , p_revoke_privs   => (p_reconcile = 'Y')
    );
    -- Message
    add_line
    ( 'Revoked '
    ||p_role_name
    ||' from '
    ||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')
    ||' '||p_grantee
    );
    add_line( jr_role.get_error_description);
  exception
    when others
    then
      add_line
      ( 'An Error has occurred '
      ||sqlerrm
      ||' - '
      ||jr_role.get_error_description
      );      
      add_line
      ( 'While revoking '
      ||p_role_name
      ||' from '
      ||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')
      ||' '||p_grantee
      );
  end; 

  action_report
  ( p_session_id => odwactxt.get_session_id
  , p_node_type  => '{TYPE='||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')||'}'
  , p_node_value => p_grantee
  , p_root_value => cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')
  , p_root_classification => '{TYPE='||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')||'}'
  , p_action_report => g_action_report
  , p_refresh_tree => true
  , p_refresh_tree_level => 2 -- refresh parent
  , p_display_label => 'Revoked Role '||p_role_name||' from '
    ||cdwpbase.ifThenElse( l_grantee_type='U','USER','ROLE')
    ||' '||p_grantee

  );
end; -- doRevokeRole


procedure js_confirmRevokeReconcile
( p_session_id in number
, p_user_name in varchar2 default null
, p_role_name in varchar2 default null
) is
begin
   htp.p
   ('
   <SCRIPT language="JavaScript">
   <!--hide from old browsers

   function revokeReconcile()
   {
   if (document.revokeForm.p_reconcile.checked == 1)
   {
     if ( confirm("Are you sure you want to revoke '||p_role_name
     ||' from '||p_user_name||' with reconcile option? Note that this option will remove access privileges from '||p_user_name
     ||' that were granted directly or only through '||p_role_name||'!")
        )
     {
       document.location="odwapred.doRevokeRole?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_grantee='||p_user_name
                    ||chr(38)||'p_role_name='||p_role_name
                    ||chr(38)||'p_reconcile=Y'
                    ||'";
     }
   }
   else
       document.location="odwapred.doRevokeRole?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_grantee='||p_user_name
                    ||chr(38)||'p_role_name='||p_role_name
                    ||chr(38)||'p_reconcile=N'
                    ||'";
   
   } //reconcileRevoke
  //-->
  </SCRIPT>
  ');
end; -- js_confirmRevokeReconcile

procedure revokeRole
( p_session_id in number    default null
, p_role_name  in varchar2  default null
, p_grantee    in varchar2  default null
, p_reconcile  in varchar2  default 'N'
, p_error      in varchar2  default null
)  is
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'reconcile'
  );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  js_confirmRevokeReconcile
  ( p_session_id => odwactxt.get_session_id
  , p_user_name  => p_grantee
  , p_role_name  => p_role_name
  );
  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
  open_palette
  ( p_title => 'Revoke '
  ||p_role_name
  ||' from '
  ||p_grantee
  , p_him_id => 666
  );
  if p_error is not null
  then
    -- errorMessage
    print_property
    ( 'Error'
    ,  '<b>Try Again: '||p_error||'</b>'
    , p_mandatory=> 'Y'
    );
  end if; -- p_error_message is not null

  htp.formopen
  ( curl         => 'odwapred.doRevokeRole'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="revokeForm" '
  , ctarget      => ''
  );
    htp.p
    ( ' <INPUT TYPE="hidden" NAME="p_role_name" '
    ||' VALUE="'||p_role_name||'">'
    );
    htp.p
    ( ' <INPUT TYPE="hidden" NAME="p_grantee" '
    ||' VALUE="'||p_grantee||'">'
    );
  -- RoleName
  print_property
  (  Rob_msg.getMsg(Rob_msg.CAP329_ODWAPRED_ROLE, '', '', '', '')
  , p_role_name
  , p_mandatory=> 'Y'
  );
  -- User
  print_property
  ( 'User'
  , p_grantee
  , p_mandatory=> 'Y'
  );
  print_property
  ( 'With Reconcile?'
  , htf.formCheckBox
    ( CNAME        => 'p_reconcile'
    , CVALUE       => 'Y'
    , CCHECKED     => cdwpbase.ifThenElse( p_reconcile ='Y' , 'Y')
    , CATTRIBUTES  => ''
    )
  , p_mandatory=> 'Y'
  );
  print_property
  ( cdwpbase.nbsp(1)
  , '<INPUT type="button" value="Revoke" '
  ||' onClick="javascript:revokeReconcile()"'
  ||'>'
  ||cdwpbase.nbsp(5)
  || ' <INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP243_ODWASPOT_CANCEL, '', '', '', '')||'" '
  ||' onClick="javascript:top.rightSide.paletteFrame.history.back();return true;"'
  ||' >'
  );
  htp.formClose;
  cdwp.tableRowClose;
  close_palette;
  htp.bodyClose;
end; -- revokeRole


procedure js_confirmReconcileRevoke
( p_session_id in number
, p_user_name in varchar2 default null
) is
begin
   htp.p
   ('
   <SCRIPT language="JavaScript">
   <!--hide from old browsers

   function reconcileRevoke()
   {
   if (document.reconcileForm.p_revoke.checked == 1)
   {
     if ( confirm("Are you sure you want to reconcile '
     ||cdwpbase.ifThenElse
       ( p_user_name is not null
       , 'user '||p_user_name
       , 'all users'
       )
     ||' with Revoke option? Note that this option will rebuild the set of user object privileges'
     ||' from scratch, only based on the roles granted to the user. Additional privileges will be revoked!")
        )
     {
       document.location="odwapred.executeReconcile?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_grantee='||p_user_name
                    ||chr(38)||'p_revoke=Y'
                    ||'";
     }
   }
   else
       document.location="odwapred.executeReconcile?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_grantee='||p_user_name
                    ||chr(38)||'p_revoke=N' -- 1.2  to solve issue 139: even if you do not specify Revoke, it still does!
                    ||'";
   
   } //reconcileRevoke
  //-->
  </SCRIPT>
  ');
end; -- js_confirmReconcileRevoke



-- this procedure will ask the user to set the reconcile options
-- and to reconfirm the desire to reconcile
-- depending on the parameter values provided, certain options
-- available and certain privileges are required:
-- 
-- p_role_name not null:  Recconcile privilege on role is required
--                        recursive (Y/N) is an option
-- p_role_name is null :  Manage_Users privilege is required
--                        revoke (Y/N) is an option (needs to be reconfirmed)
procedure reconcile
( p_session_id in number    default null
, p_role_name  in varchar2  default null
, p_grantee    in varchar2  default null
, p_recursive  in varchar2  default 'N'
, p_revoke     in varchar2  default 'N'
, p_error      in varchar2  default null
)  is
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'reconcile'
  );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  if p_role_name is null
  then
    js_confirmReconcileRevoke
    ( p_session_id => odwactxt.get_session_id
    , p_user_name  => p_grantee
    );
  end if;
  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
  open_palette
  ( p_title => 'Reconcile'
  ||cdwpbase.ifThenElse
    ( p_role_name is not null
    , ' Role '||p_role_name
    )
  ||cdwpbase.ifThenElse
    ( p_grantee is not null
    , cdwpbase.ifThenElse
      ( p_role_name is not null
      , ' for user '||p_grantee
      , ' all roles for user '||p_grantee
      )
    )
  ||cdwpbase.ifThenElse
    ( p_recursive = 'Y'
    , ' (recursively)'
    )
  , p_him_id => 648
  );
  if p_error is not null
  then
    -- errorMessage
    print_property
    ( 'Error'
    ,  '<b>Try Again: '||p_error||'</b>'
    , p_mandatory=> 'Y'
    );
  end if; -- p_error_message is not null

  htp.formopen
  ( curl         => 'odwapred.executeReconcile'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="reconcileForm" '
  , ctarget      => ''
  );
    htp.p
    ( ' <INPUT TYPE="hidden" NAME="p_role_name" '
    ||' VALUE="'||p_role_name||'">'
    );
    htp.p
    ( ' <INPUT TYPE="hidden" NAME="p_grantee" '
    ||' VALUE="'||p_grantee||'">'
    );
  -- RoleName
  print_property
  (  Rob_msg.getMsg(Rob_msg.CAP329_ODWAPRED_ROLE, '', '', '', '')
  , nvl( p_role_name, 'All Roles') 
  , p_mandatory=> 'Y'
  );
  -- User
  print_property
  ( 'User'
  , nvl( p_grantee, 'All Users') 
  , p_mandatory=> 'Y'
  );
  if p_role_name is not null
  then
    print_property
    ( 'Recursively?'
    , htf.formCheckBox
      ( CNAME        => 'p_recursive'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse( p_recursive ='Y' , 'Y')
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'Y'
    );
  end if; -- p_role_name is not null
  if p_role_name is null
  then
    print_property
    ( 'With Revoke?'
    , htf.formCheckBox
      ( CNAME        => 'p_revoke'
      , CVALUE       => 'Y'
      , CCHECKED     => cdwpbase.ifThenElse( p_revoke ='Y' , 'Y')
      , CATTRIBUTES  => ''
      )
    , p_mandatory=> 'Y'
    );
  else
    htp.p
    ( ' <INPUT TYPE="hidden" NAME="p_revoke" '
    ||' VALUE="N">'
    );  
  end if; -- p_role_name is not null
  print_property
  ( cdwpbase.nbsp(1)
  , '<INPUT type="button" value="Reconcile" '
  ||cdwpbase.ifThenElse
    ( p_role_name is null
    , ' onClick="javascript:reconcileRevoke()"'
    , ' onClick="javascript:document.reconcileForm.submit()"'
    )
  ||'>'
  ||cdwpbase.nbsp(5)
  || ' <INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP243_ODWASPOT_CANCEL, '', '', '', '')||'" '
  ||' onClick="javascript:top.rightSide.paletteFrame.history.back();return true;"'
  ||' >'
  );
  htp.formClose;
  cdwp.tableRowClose;
  close_palette;
  htp.bodyClose;
end; -- reconcile

procedure executeReconcile
( p_session_id in number    default null
, p_role_name  in varchar2  default null
, p_grantee    in varchar2  default null
, p_recursive  in varchar2  default 'N'
, p_revoke     in varchar2  default 'N'
) is
  l_start_time number(38):= dbms_utility.get_time;
  l_end_time   number(38);
begin
  -- perform reconcile as indicated, using JR_ROLE
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'executeReconcile'
  );
  reset_report;
  add_line(' Reconciling '
  ||cdwpbase.ifThenElse
    ( p_role_name is null
    , 'All Roles'
    , 'Role '||p_role_name
    )
  );
  add_line
  ( cdwpbase.ifThenElse
    ( p_grantee is null
    , ' for all grantees of this role'
    , ' for user '||p_grantee
    )
  );
  if p_recursive = 'Y'
  then
    add( 'Recursively processing nested roles');
  end if;
    if p_role_name is null
    then
      add_line('....Reconciling '||p_grantee||' (all roles)');
      jr_role.reconcile_user
      ( p_user_name => p_grantee
      , p_revoke    => p_revoke ='Y'
      );  
      add_line( jr_role.get_error_description);
    else
      add_line('....Reconciling role '||p_role_name||' for '||nvl( p_grantee,'all grantees'));
      jr_role.reconcile_role
      ( p_role_name => p_role_name
      , p_grantee   => p_grantee
      , p_recursive => p_recursive ='Y'
      );
      add_line( jr_role.get_error_description);
    end if; -- p_role_name is null
  l_end_time:= dbms_utility.get_time;
  
  add_line
  ( 'Reconcile Time '
  ||to_char( round((l_end_time - l_start_time)/10)/10)
  ||' seconds'
  );
  action_report
  ( p_session_id => odwactxt.get_session_id
  , p_node_type  => cdwpbase.ifThenElse( p_grantee is not null, '{TYPE=USER}','{TYPE=ROLE}')
  ||'{GRANTEE='||p_grantee||'}{ROLE='||p_role_name||'}'
  , p_node_value => cdwpbase.ifThenElse( p_grantee is not null, p_grantee, p_role_name)
  , p_root_value => cdwpbase.ifThenElse( p_grantee is not null, 'USER','ROLE')
  , p_root_classification => cdwpbase.ifThenElse( p_grantee is not null, '{TYPE=USER}','{TYPE=ROLE}')
  , p_action_report => g_action_report
  , p_refresh_tree => (p_grantee is not null and p_role_name is null) -- user is completely reconciled (all roles)
  , p_display_label => 'Reconcile'
                      ||cdwpbase.ifThenElse
                        ( p_role_name is not null
                        , ' Role '||p_role_name
                        )
                      ||cdwpbase.ifThenElse
                        ( p_grantee is not null
                        , cdwpbase.ifThenElse
                          ( p_role_name is not null
                          , ' for user '||p_grantee
                          , ' all roles for user '||p_grantee
                          )
                        )
                      ||cdwpbase.ifThenElse
                        ( p_recursive = 'Y'
                        , ' (recursively)'
                        )
  );

  exception
    when others
    then
      odwapred.reconcile
      ( p_session_id => odwactxt.get_session_id
      , p_role_name  => p_role_name
      , p_grantee    => p_grantee
      , p_recursive  => p_recursive
      , p_revoke     => p_revoke
      , p_error      => 'Exception occurred '
      ||sqlerrm
      ||' - '
      ||jr_role.get_error_description
      ); 
end; -- executeReconcile


procedure saveRole
( p_session_id    in number    default null
, roleName        in varchar2  default null
, oldRoleName        in varchar2  default null
, fullRoleName    in varchar2  default null
, roleDescription in varchar2  default null
) is
  l_role_irid number(38);
  cursor c_rle( b_irid in number)
    is
    select rle.username rolename
    from   sdw_users rle
    where  rle.irid = b_irid
    ;
  r_rle c_rle%rowtype;
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'saveRole'
  );
  reset_report;
  -- can be an update or an insert
  l_role_irid:= odwapriv.get_role_irid(p_role_name => nvl( oldRoleName, roleName));
  if l_role_irid is null
  then 
    -- NEW ROLE
  
    -- check if values are okay
    -- if not: createRole( p_session_id, p_role_name, p_description)
    -- else
    l_role_irid := jr_role.create_role
                  ( p_role_name   => roleName
                  , p_description => roleDescription
                  );
    add_line('Created Role '||roleName);
    add_line('Description: '||roleDescription);
    -- now display the property palette for the role just created
    open c_rle( b_irid => l_role_irid);
    fetch c_rle
    into  r_rle;
    close c_rle;
/*    node_selected
    ( p_session_id => odwactxt.get_session_id
    , p_node_type  => '{TYPE=ROLE}'
    , p_node_value => r_rle.rolename
    , p_root_value => 'ROLE'
    , p_root_classification => '{TYPE=ROLE}'
    , p_refresh_tree => true
    );
*/    
    action_report
    ( p_session_id => odwactxt.get_session_id
    , p_node_type  => '{TYPE=ROLE}'
    , p_node_value => r_rle.rolename
    , p_root_value => 'ROLE'
    , p_root_classification => '{TYPE=ROLE}'
    , p_action_report => 'Role '||r_rle.rolename||' has been created.'
    , p_refresh_tree => true
    , p_display_label => 'Create Role'
    );
  else
    jr_role.update_role
    ( p_role_name => oldRoleName
    , p_new_role_name => roleName
    , p_full_role_name => fullRoleName
    , p_description    => roleDescription
    );      
    action_report
    ( p_session_id => odwactxt.get_session_id
    , p_node_type  => '{TYPE=ROLE}'
    , p_node_value => roleName
    , p_root_value => 'ROLE'
    , p_root_classification => '{TYPE=ROLE}'
    , p_action_report => 'Role '||roleName||' has been updated.'
    , p_refresh_tree  =>  (oldRoleName <> roleName)
    , p_refresh_tree_level => 1
    , p_display_label => 'Update Role'
    );
  end if; -- l_role_irid is null
exception
  when others
  then
    if l_role_irid is null
    then
      createRole
      ( p_session_id
      , roleName
      , roleDescription
      , sqlerrm
      ||' - '
      ||jr_role.get_error_description
      );
    else
      editRole
      ( p_session_id
      , roleName
      , oldRoleName
      , fullRoleName
      , roleDescription
      , sqlerrm
      ||' - '
      ||jr_role.get_error_description
      );
    end if;
end; -- saveRole


procedure createRole
( p_session_id    in number   default null
, p_role_name     in varchar2 default null
, p_full_role_name in varchar2 default null
, p_description   in varchar2 default null
, p_error_message in varchar2 default null
)  is
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'createRole'
  );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"');
  open_palette
  ( p_title => 'Create New Role'
  , p_him_id => 210
  );
  if p_error_message is not null
  then
    -- errorMessage
    print_property
    ( 'Error'
    ,  '<b>Try Again: '||p_error_message||'</b>'
    , p_mandatory=> 'Y'
    );
  end if; -- p_error_message is not null

  htp.formopen
  ( curl         => 'odwapred.saveRole'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="roleForm" '
  , ctarget      => ''
  );
  htp.formhidden(cname => 'p_session_id', cvalue => to_char(odwactxt.get_session_id));
  -- RoleName
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP352_ODWAPRED_ROLE, '', '', '', '')
  ,   htf.formText
  ( cname       => 'roleName'
  , cvalue      => p_role_name
  , csize       => 25
  , cmaxlength  => 30
  )
  , p_mandatory=> 'Y'
  );
  -- FullRoleName
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP353_ODWAPRED_FULLROLE, '', '', '', '')
  ,   htf.formText
  ( cname       => 'fullRoleName'
  , cvalue      => p_full_role_name
  , csize       => 40
  , cmaxlength  => 240
  )
  , p_mandatory=> 'N'
  );
  -- RoleDescription
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP008_CDWP_DESC, '', '', '', '')
  ,' <TEXTAREA NAME="roleDescription" ROWS=4 COLS=30 WRAP="VIRTUAL"></TEXTAREA>'
  , p_mandatory=> 'N'
  );
  cdwp.tableRowOpen;
  cdwp.TableDataValue
  ( cdwpbase.nbsp(10)
  ||'<INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP311_ODWAPREF_SAVE, '', '', '', '')||'" '
  ||' onClick="javascript:roleForm.submit()"; return true;'
  ||'>'
  || cdwpbase.nbsp(10)
  ||' <INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP243_ODWASPOT_CANCEL, '', '', '', '')||'" '
  ||' onClick="javascript:top.rightSide.paletteFrame.history.back();return true;"'
  ||' >'
  , p_attributes => 'VALIGN="TOP" colspan=2'
  );
  htp.formClose;
  cdwp.tableRowClose;
  close_palette;
  htp.bodyClose;
end; -- createRole

procedure js_init
( p_text in varchar2
, p_item in varchar2
) is
begin
  htp.p
  (' <SCRIPT LANGUAGE="JavaScript"><!--


  function init()
  {
  
   '||p_item||'="'
   ||replace( replace( p_text,chr(13),'\r'),chr(10),'')
   ||'";'
   );
  htp.p('

} // init()
    //--></SCRIPT>
  ');
end; -- js_init


procedure editRole
( p_session_id     in number   default null
, p_role_name      in varchar2 default null
, p_old_role_name  in varchar2 default null
, p_full_role_name in varchar2 default null
, p_description   in varchar2 default null
, p_error_message in varchar2 default null
)  is
  cursor c_role( b_rolename in varchar2)
  is
  select rle.username         role_name
  ,      rle.full_user_name   full_role_name
  ,      rle.date_created
  ,      rle.date_changed
  ,      rle.created_by
  ,      rle.changed_by
  ,      rle.description
  from   sdw_users            rle
  where  rle.username          = b_rolename
  ;
  r_role c_role%rowtype;

begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'editRole'
  );
  open c_role( b_rolename => nvl( p_old_role_name, p_role_name));
  fetch c_role
  into  r_role;
  close c_role;
  htp.htmlOpen;
  htp.headOpen;
  js_init
  ( p_item => 'document.roleForm.roleDescription.value'
  , p_text => nvl( p_description,r_role.description)
  );
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.bodyOpen
  ( cattributes=>'BGCOLOR="#FFFFFF" '
  ||' onLoad="init()"
  ');
  open_palette
  ( p_title => 'Update Role Specification for '||r_role.role_name
  , p_him_id => 600
  );

  if p_error_message is not null
  then
    -- errorMessage
    print_property
    ( 'Error'
    ,  '<b>Try Again: '||p_error_message||'</b>'
    , p_mandatory=> 'Y'
    );
  end if; -- p_error_message is not null

  htp.formopen
  ( curl         => 'odwapred.saveRole'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="roleForm" '
  , ctarget      => ''
  );
  htp.formhidden(cname => 'p_session_id', cvalue => to_char(odwactxt.get_session_id));
  htp.formhidden(cname => 'oldRoleName', cvalue => r_role.role_name);
  -- RoleName
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP352_ODWAPRED_ROLE, '', '', '', '')
  ,   htf.formText
  ( cname       => 'RoleName'
  , cvalue      => nvl( p_role_name, r_role.role_name)
  , csize       => 40
  , cmaxlength  => 240
  )
  , p_mandatory=> 'N'
  );
  -- FullRoleName
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP353_ODWAPRED_FULLROLE, '', '', '', '')
  ,   htf.formText
  ( cname       => 'fullRoleName'
  , cvalue      => nvl( p_full_role_name, r_role.full_role_name)
  , csize       => 40
  , cmaxlength  => 240
  )
  , p_mandatory=> 'N'
  );
  -- RoleDescription
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP008_CDWP_DESC, '', '', '', '')
  ,' <TEXTAREA NAME="roleDescription" ROWS=4 COLS=30 WRAP="VIRTUAL"></TEXTAREA>'
  , p_mandatory=> 'N'
  );
  print_property( Rob_msg.getMsg(Rob_msg.CAP336_ODWAPRED_ROWNER, '', '', '', ''),odwapriv.get_role_owner(r_role.role_name ));
  print_property( Rob_msg.getMsg(Rob_msg.CAP337_ODWAPRED_DATECR, '', '', '', ''), to_char( r_role.date_created, 'dd-mon-yyyy hh24:mi'));
  print_property( Rob_msg.getMsg(Rob_msg.CAP338_ODWAPRED_DATECH, '', '', '', ''), to_char( r_role.date_changed, 'dd-mon-yyyy hh24:mi'));
  cdwp.tableRowOpen;
  cdwp.TableDataValue
  ( cdwpbase.nbsp(10)
  ||'<INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP311_ODWAPREF_SAVE, '', '', '', '')||'" '
  ||' onClick="javascript:roleForm.submit()"; return true;'
  ||'>'
  || cdwpbase.nbsp(10)
  ||' <INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP243_ODWASPOT_CANCEL, '', '', '', '')||'" '
  ||' onClick="javascript:top.rightSide.paletteFrame.history.back();return true;"'
  ||' >'
  , p_attributes => 'VALIGN="TOP" colspan=2'
  );
  cdwp.tableRowClose;
  htp.formClose;
  --htp.tableClose;
  close_palette;
  htp.bodyClose;
end; -- editRole



procedure js_confirmDelete
( p_session_id in number
, p_role_name in varchar2
) is
begin
   htp.p
   ('
   <SCRIPT language="JavaScript">
   <!--hide from old browsers

   function deleteRole()
   {
     if ( confirm("Are you sure you want to delete role '||p_role_name
              ||' (including all grants made of and to this role)?")
        )
     {
       document.location="odwapred.deleteRole?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_role_name='||p_role_name||'";
     }
   } //deleteRole
  //-->
  </SCRIPT>
  ');
end; -- js_confirmDelete

procedure js_confirmRevoke
( p_session_id in number
, p_role_name in varchar2
, p_grantee   in varchar2
) is
begin
   htp.p
   ('
   <SCRIPT language="JavaScript">
   <!--hide from old browsers

   function revokeRole()
   {
       document.location="odwapred.revokeRole?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_role_name='||p_role_name
                    ||chr(38)||'p_grantee='||p_grantee||'";
   } //revokeeRole
  //-->
  </SCRIPT>
  ');
end; -- js_confirmRevoke

procedure js_confirmRevokePrivs
( p_session_id in number
, p_irid       in varchar2
, p_grantee    in varchar2
) is
begin
   htp.p
   ('
   <SCRIPT language="JavaScript">
   <!--hide from old browsers

   function revokePrivs()
   {
     if ( confirm("Are you sure you want to revoke all privileges '
              ||' on '||odwapriv.get_object_label(p_irid => p_irid,p_include_gif => false)
              ||' from '||p_grantee||'?")
        )
     {
       document.location="odwapred.revokeObjectPrivileges?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_irid='||p_irid
                    ||chr(38)||'p_grantee_name='||p_grantee||'";
     }
   } //deleteRole
  //-->
  </SCRIPT>
  ');
end; -- js_confirmRevokePrivs


function get_menu_bar
( p_root_type in varchar2
, p_irid      in varchar2
, p_grantee   in varchar2
, p_grantee_type in varchar2
, p_type      in varchar2
, p_role_name in varchar2
, p_role_irid in varchar2
, p_session_id in number
, p_node_value in varchar2
) return varchar2
is
  l_menu_bar varchar2(20000);
begin 
  if p_root_type in ('ROLE','USER')
  then
     l_menu_bar:= l_menu_bar
      ||htf.anchor
        ( ctext=> cdwp.add_images
                    ( '{foldergr.gif}'
                    , p_attributes => 'ALT="Grant privileges on'
                    ||cdwpbase.ifThenElse
                      ( p_type in ('GRANTEE_OF_FOL','FOL')
                      , ' a subfolder of '
                      ||odwapriv.get_object_label
                        ( p_irid => to_number( p_irid)
                        , p_include_gif => false
                        )
                      , ' a root folder'
                      )
                    ||' to '
                    ||p_grantee
                    ||'"'
                    )
        , curl => 'odwapred.grantObject?'
                  ||'p_session_id='||to_char(p_session_id)
                  ||chr(38)||'p_grantee='||p_grantee
                  ||chr(38)||'p_object_type=FOL'
                  ||chr(38)||'p_context_irid='
                  ||cdwpbase.ifThenElse
                    ( p_type in ('GRANTEE_OF_FOL','FOL')
                    , to_number(p_irid)
                    )
        )
      ||htf.anchor
        ( ctext=> cdwp.add_images
                    ( '{workareagr.gif}'
                    , p_attributes => 'ALT="Grant privileges on a Workarea to '
                    ||p_grantee
                    ||'"'
                    )
        , curl => 'odwapred.grantObject?'
                  ||'p_session_id='||to_char(p_session_id)
                  ||chr(38)||'p_grantee='||p_grantee
                  ||chr(38)||'p_object_type=WA'
        )
      ||htf.anchor
        ( ctext=> cdwp.add_images
                    ( '{configurationgr.gif}'
                    , p_attributes => 'ALT="Grant privileges on a Configuration '
                    ||p_grantee
                    ||'"'
                    )
        , curl => 'odwapred.grantObject?'
                  ||'p_session_id='||to_char(p_session_id)
                  ||chr(38)||'p_grantee='||p_grantee
                  ||chr(38)||'p_object_type=CFG'
        )
        ;
  end  if; -- p_root_type in ('ROLE','USER')
  if p_type = 'GRANTEE_OF_ROLE'
  then
    -- allow revoke role, grant role to additional grantee, edit role_grant
    -- if creator or admin privs on role, user may grant role to others

    if jr_acc_rights.has_access
      ( object_irid  => p_role_irid
      , grantee      => USER
      , privilege    => 'ADM'
      )
      OR
      jr_role.get_role_owner( p_role_name) = USER
    then
       l_menu_bar:= l_menu_bar
        ||cdwpbase.ifThenElse
          ( p_grantee_type='U'
          ,  htf.anchor
            ( ctext=> cdwp.add_images
                      ( '{editprivsgr.gif}'
                      , p_attributes => 'ALT="Edit Role Grant of '||p_role_name||' to '||p_grantee||'"'
                      )
            , curl => 'odwapred.editRoleGrant?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_role_name='||p_role_name
                    ||chr(38)||'p_grantee='||p_grantee
           )
          )
        || htf.anchor
            ( ctext=> cdwp.add_images
                      ( '{deletiongr.gif}'
                      , p_attributes => 'ALT="Revoke Role '||p_role_name||' from '||p_grantee||'"'
                      )
            , curl => 'javascript:revokeRole()'
            )
        ||htf.anchor
          ( ctext=> cdwp.add_images
                      ( '{db_user_rolegr.gif}'
                      , p_attributes => 'ALT="Grant Role '||p_role_name||' to other grantees"'
                      )
          , curl => 'odwapred.grantRole?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_role_name='||p_role_name
          )
            ;
     end if; -- creator or administrator   
     -- if user has reconcile privilege on this role
     if jr_acc_rights.has_access
        ( object_irid  => p_role_irid
        , grantee      => USER
        , privilege    => 'CMP'
        )
        and 
        p_grantee_type ='U'
     then
       l_menu_bar:= l_menu_bar
        ||htf.anchor
          ( ctext=> cdwp.add_images
                      ( '{reconcilegr.gif}'
                      , p_attributes => 'ALT="Reconcile Role '||p_role_name||' for user '||p_grantee||'"'
                      )
          , curl => 'odwapred.reconcile?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_role_name='||p_role_name
                    ||chr(38)||'p_grantee='||p_grantee
          );     
     end if; -- grant to a user, USER has reconcile option on this role

  end if; -- p_type = 'GRANTEE_OF_ROLE'
  if p_type = 'GRANTED_ROLE'
  then
    -- allow revoke role, grant role to additional grantee, edit role_grant
    -- if creator or admin privs on role, user may grant role to others
    if jr_acc_rights.has_access
      ( object_irid  => p_role_irid
      , grantee      => USER
      , privilege    => 'ADM'
      )
      OR
      jr_role.get_role_owner( p_role_name) = USER
    then
       l_menu_bar:= l_menu_bar
        ||htf.anchor
          ( ctext=> cdwp.add_images
                      ( '{db_user_rolegr.gif}'
                      , p_attributes => 'ALT="Grant additional Roles to '||p_grantee||'"'
                      )
          , curl => 'odwapred.grantToGrantee?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_grantee='||p_grantee
          )
        ||cdwpbase.ifThenElse
          ( p_grantee_type='U'
          , htf.anchor
            ( ctext=> cdwp.add_images
                        ( '{editprivsgr.gif}'
                        , p_attributes => 'ALT="Edit Role Grant of '||p_role_name||' to '||p_grantee||'"'
                        )
            , curl => 'odwapred.editRoleGrant?'
                      ||'p_session_id='||to_char(p_session_id)
                      ||chr(38)||'p_role_name='||p_role_name
                      ||chr(38)||'p_grantee='||p_grantee
           )
         )
        || htf.anchor
            ( ctext=> cdwp.add_images
                      ( '{deletiongr.gif}'
                      , p_attributes => 'ALT="Revoke Role '||p_role_name||' from '||p_grantee||'"'
                      )
            , curl => 'javascript:revokeRole()'
            );
     end if; -- creator or administrator   
     -- if user has reconcile privilege on this role
     if jr_acc_rights.has_access
        ( object_irid  => p_role_irid
        , grantee      => USER
        , privilege    => 'CMP'
        )
        and 
        p_grantee_type ='U'
     then
       l_menu_bar:= l_menu_bar
        ||htf.anchor
          ( ctext=> cdwp.add_images
                      ( '{reconcilegr.gif}'
                      , p_attributes => 'ALT="Reconcile Role '||p_role_name||' for user '||p_grantee||'"'
                      )
          , curl => 'odwapred.reconcile?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_role_name='||p_role_name
                    ||chr(38)||'p_grantee='||p_grantee
          );
     end if; -- grant to a user, USER has reconcile option on this role
  end if; -- p_type = 'GRANTED_ROLE'
  if p_type='ROLE' 
  then
  -- Bug 2616764: We do not want the users to create new roles until the Role management 
  -- is proven efficient.
/*     if jr_role.get_check_upd_users
     then
       l_menu_bar:= l_menu_bar
        ||htf.anchor
          ( ctext=> cdwp.add_images
                      ( '{additiongr.gif}'
                      , p_attributes => 'ALT="Create New Role"'
                      )
          , curl => 'odwapred.createRole?'
                    ||'p_session_id='||to_char(p_session_id)
          )
        || cdwpbase.ifThenElse
           ( p_role_name <> 'PUBLIC'
           , htf.anchor
             ( ctext=> cdwp.add_images
                      ( '{deletiongr.gif}'
                      , p_attributes => 'ALT="Delete Role"'
                      )
              , curl => 'javascript:deleteRole()'
              )
           )
        ;
    end if; -- check_upd_users
*/    -- if creator or admin privs on role, user may grant role to others
    if jr_acc_rights.has_access
      ( object_irid  => p_role_irid
      , grantee      => USER
      , privilege    => 'ADM'
      )
      OR
      jr_role.get_role_owner( p_role_name) = USER
    then
       l_menu_bar:= l_menu_bar
        ||cdwpbase.ifThenElse
           ( p_role_name <> 'PUBLIC'
           , htf.anchor
             ( ctext=> cdwp.add_images
                        ( '{db_user_rolegr.gif}'
                        , p_attributes => 'ALT="Grant Role '||p_role_name||' to Users/Roles"'
                        )
            , curl => 'odwapred.grantRole?'
                      ||'p_session_id='||to_char(p_session_id)
                      ||chr(38)||'p_role_name='||p_role_name
            )
          ||htf.anchor
            ( ctext=> cdwp.add_images
                        ( '{editprivsgr.gif}'
                        , p_attributes => 'ALT="Edit Role properties"'
                        )
            , curl => 'odwapred.editRole?'
                      ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_role_name='||p_role_name
            )
          )
          ;
     end if; -- creator or administrator
     -- if user has reconcile privilege on this role
     if jr_acc_rights.has_access
        ( object_irid  => p_role_irid
        , grantee      => USER
        , privilege    => 'CMP'
        )
     then
       l_menu_bar:= l_menu_bar
        ||cdwpbase.ifThenElse
          ( p_role_name <> 'PUBLIC'
          , htf.anchor
            ( ctext=> cdwp.add_images
                        ( '{reconcilegr.gif}'
                        , p_attributes => 'ALT="Reconcile Role '||p_role_name||' for all users"'
                        )
            , curl => 'odwapred.reconcile?'
                      ||'p_session_id='||to_char(p_session_id)
                      ||chr(38)||'p_role_name='||p_role_name
                      ||chr(38)||'p_grantee='
            )
          );
    end if; -- grant to a user, USER has reconcile option on this role
  end if; --   p_type='ROLE'

  
-- Bug 2616764: No Additional Role management facilities  
/*  if p_type = 'USER'
  then
     if jr_role.get_check_upd_users
     then
       l_menu_bar:= l_menu_bar
        ||htf.anchor
          ( ctext=> cdwp.add_images
                      ( '{reconcilegr.gif}'
                      , p_attributes => 'ALT="Reconcile user '||odwapriv.proper(p_node_value)||' (with all granted roles)"'
                      )
          , curl => 'odwapred.reconcile?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_role_name='
                    ||chr(38)||'p_grantee='||odwapriv.proper(p_node_value)
          );
    end if; -- check_upd_users
       l_menu_bar:= l_menu_bar
      ||htf.anchor
        ( ctext=> cdwp.add_images
                    ( '{db_user_rolegr.gif}'
                    , p_attributes => 'ALT="Grant (other) Role to '
                    ||odwapriv.proper(p_node_value)
                    ||'"'
                    )
        , curl => 'odwapred.grantToGrantee?'
                  ||'p_session_id='||to_char(p_session_id)
                  ||chr(38)||'p_grantee='||odwapriv.proper(p_node_value)
        );
  end if; -- p_type='USER'
*/ 
 if p_type in ('FOL', 'WA', 'CFG')
     and
     p_root_type in ('ROLE','USER')
  then
     if jr_acc_rights.has_access
        ( object_irid  => p_irid
        , grantee      => USER
        , privilege    => 'ADM'
        )
     then
       -- existing privileges can only be updated by the original grantor
       -- or the current owner of the object       
       if jr_role.get_object_owner(p_irid => p_irid) = USER
          or 
          get_grantor( p_irid => p_irid, p_grantee => p_grantee) = USER
       then   
         l_menu_bar:= l_menu_bar
          ||htf.anchor
            ( ctext=> cdwp.add_images
                        ( '{editprivsgr.gif}'
                        , p_attributes => 'ALT="Edit privileges for '||p_grantee
                        ||' on '||odwapriv.get_object_label( p_irid => p_irid, p_include_gif=> false)
                        ||'"'
                        )
            , curl => 'odwapred.edit_object_privs?'
                      ||'p_session_id='||to_char(p_session_id)
                      ||chr(38)||'p_irid='||p_irid
                      ||chr(38)||'p_grantee='||p_grantee
            );
          if p_grantee <> USER
          then
           l_menu_bar:= l_menu_bar
            ||htf.anchor
              ( ctext=> cdwp.add_images
                          ( '{deletiongr.gif}'
                          , p_attributes => 'ALT="Revoke privileges for '||p_grantee
                          ||' on '||odwapriv.get_object_label( p_irid => p_irid, p_include_gif=> false)
                          ||'"'
                          )
              , curl => 'javascript:revokePrivs()'
              );         
          end if; -- p_grantee <> USER
       end if; -- was grantor or is owner
    end if; -- had ADM access
  end if; -- p_type=FOL
  if p_type in ('FOL', 'WA', 'CFG', 'ROOTFOL')
     and
     p_root_type in ('FOL', 'WA', 'CFG', 'ROOTFOL')
  then
     if jr_acc_rights.has_access
        ( object_irid  => p_irid
        , grantee      => USER
        , privilege    => 'ADM'
        )
        or
        jr_role.get_object_owner(p_irid => p_irid) = 'USER'
     then
       l_menu_bar:= l_menu_bar
        ||htf.anchor
          ( ctext=> cdwp.add_images
                      ( '{db_user_rolegr.gif}'
                      , p_attributes => 'ALT="Grant privileges'
                        ||' on '||odwapriv.get_object_label( p_irid => p_irid, p_include_gif=> false)                      
                        ||' to users/roles"'
                      )
          , curl => 'odwapred.grantPrivs?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_irid='||p_irid
          );
     end if; -- owner or ADMIN privs
  end if; -- type in WA,CFG, FOL
  if p_type in ('GRANTEE_OF_FOL', 'GRANTEE_OF_WA', 'GRANTEE_OF_CFG')
  then
     if jr_acc_rights.has_access
        ( object_irid  => p_irid
        , grantee      => USER
        , privilege    => 'ADM'
        )
        or
        jr_role.get_object_owner(p_irid => p_irid) = 'USER'
     then
       l_menu_bar:= l_menu_bar
        ||htf.anchor
          ( ctext=> cdwp.add_images
                      ( '{db_user_rolegr.gif}'
                      , p_attributes => 'ALT="Grant privileges'
                        ||' on '||odwapriv.get_object_label( p_irid => p_irid, p_include_gif=> false)                      
                        ||' to other users/roles"'
                      )
          , curl => 'odwapred.grantPrivs?'
                    ||'p_session_id='||to_char(p_session_id)
                    ||chr(38)||'p_irid='||p_irid
          );
       -- existing privileges can only be updated by the original grantor
       -- or the current owner of the object       
       if jr_role.get_object_owner(p_irid => p_irid) = USER
          or 
          get_grantor( p_irid => p_irid, p_grantee => p_grantee) = USER
       then   
         l_menu_bar:= l_menu_bar
          ||htf.anchor
            ( ctext=> cdwp.add_images
                        ( '{editprivsgr.gif}'
                        , p_attributes => 'ALT="Edit privileges for '||p_grantee
                        ||' on '||odwapriv.get_object_label( p_irid => p_irid, p_include_gif=> false)                      
                        ||'"'
                        )
            , curl => 'odwapred.edit_object_privs?'
                      ||'p_session_id='||to_char(p_session_id)
                      ||chr(38)||'p_irid='||p_irid
                      ||chr(38)||'p_grantee='||p_grantee
            );
          if p_grantee <> USER
          then
           l_menu_bar:= l_menu_bar
            ||htf.anchor
              ( ctext=> cdwp.add_images
                          ( '{deletiongr.gif}'
                          , p_attributes => 'ALT="Revoke privileges for '||p_grantee
                          ||' on '||odwapriv.get_object_label( p_irid => p_irid, p_include_gif=> false)                      
                          ||'"'
                          )
              , curl => 'javascript:revokePrivs()'
              );         
          end if; -- p_grantee <> USER
       end if; -- was grantor or is owner
    end if; -- had ADM access
  end if; -- p_type in ('GRANTEE_OF_FOL', 'GRANTEE_OF_WA', 'GRANTEE_OF_CFG')
  if p_type ='ROOT'
     and
     P_node_value ='ROLE'
  then
  -- Bug 2616764: We do not want the users to create new roles until the Role management 
  -- is proven efficient.
/*     if jr_role.get_check_upd_users
     then
       l_menu_bar:= l_menu_bar
        ||htf.anchor
          ( ctext=> cdwp.add_images
                      ( '{additiongr.gif}'
                      , p_attributes => 'ALT="Create New Role"'
                      )
          , curl => 'odwapred.createRole?'
                    ||'p_session_id='||to_char(p_session_id)
          )
        ;
    end if; -- check_upd_users
*/
    null; 
  end if; -- type=ROOT, node_value = ROLE
-- Bug 2616764: No Additional Role Management Stuff
/*  if p_root_type = 'ROLE'
     and
     p_type ='ROLE'
  then
       l_menu_bar:= l_menu_bar
      ||htf.anchor
        ( ctext=> cdwp.add_images
                    ( '{db_user_rolegr.gif}'
                    , p_attributes => 'ALT="Grant (other) Role to '
                    ||odwapriv.proper(p_node_value)
                    ||'"'
                    )
        , curl => 'odwapred.grantToGrantee?'
                  ||'p_session_id='||to_char(p_session_id)
                  ||chr(38)||'p_grantee='||odwapriv.proper(p_node_value)
        );
  end if;*/
 if length(l_menu_bar) > 10
 then
  l_menu_bar:= cdwp.add_images( p_text => '{ft-left.gif}')
    ||l_menu_bar
    ||cdwp.add_images
      ( p_text => '{ft-right.gif}'
      , p_attributes=> ' Qwidth="25" '
      )
    ;
  end if; 
  return l_menu_bar;
  --paletteFrame( p_session_id); -- until I come up with something better
end; -- menu_bar


procedure js_refreshTree
( p_refresh_tree_level in number default null
) is
begin
  htp.p
  (' <SCRIPT LANGUAGE="JavaScript"><!--


  function refreshTree()
  {
  ');
  if p_refresh_tree_level = 0
  then
    htp.p(' top.reloadNode();');
  else
    htp.p(' top.reloadNodeIdx( top.getParentNodeRelative( top.selectedIdx, '||to_char(p_refresh_tree_level)||'));');
  end if; -- p_refresh_tree_level = 0
  htp.p('    
  } // init()
    //--></SCRIPT>
  ');
end; -- js_refreshTree

procedure node_selected
( p_session_id             in number
, p_node_value             in varchar2 default null
, p_node_type              in varchar2 default null
, p_root_value             in varchar2
, p_root_classification    in varchar2 default null
, p_refresh_tree           in varchar2 default 'N' 
) is
  l_type varchar2(30):= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'TYPE');
  l_root_type varchar2(30):= cdwpbase.get_tag_value( p_string=> p_root_classification, p_tag => 'TYPE');
  l_irid varchar2(38):= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'IRID');
  l_fol_irid varchar2(38):= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'FOL_IRID');
  l_wa_irid varchar2(38):= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'WA_IRID');
  l_menu_bar varchar2(2000);
  l_role_name    varchar2(30)  := odwapriv.proper( p_node_value);
  l_grantee      varchar2(30);
  l_role_irid    number(38);
  l_grantee_type varchar2(1);
  l_show_palette boolean:= true;

  
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'nodeSelected'
  );
  l_grantee      := cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'GRANTEE');
  l_grantee_type := odwapriv.get_grantee_type( p_grantee => l_grantee); -- R(ole) or U(ser)
  if l_type in ('ROLE', 'USER')
  then
     l_grantee:= odwapriv.proper(p_node_value);
  end if;
  if l_type in ('GRANTEE_OF_FOL', 'GRANTEE_OF_WA', 'GRANTEE_OF_CFG')
  then
     l_irid:= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => substr(l_type, 12)||'_IRID');
  end if;
  if l_type in ('WA','CFG','FOL','ROOTFOL')
  then
     l_irid:= nvl( l_irid, cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => cdwpbase.ifThenElse( l_type='ROOTFOL', 'FOL',l_type)||'_IRID'));
     if l_irid =''
     then
       l_irid:= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'IRID');
     end if;
  end if;
  l_role_irid    := odwapriv.get_role_irid( p_role_name => l_role_name);
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  js_confirmDelete
  ( p_session_id => odwactxt.get_session_id
  , p_role_name => p_node_value
  );
  js_confirmRevokePrivs
  ( p_session_id => odwactxt.get_session_id
  , p_grantee    => l_grantee
  , p_irid       => l_irid
  );
  if p_refresh_tree ='Y'
  then
    js_refreshTree;
  end if;
  if l_type = 'GRANTEE_OF_ROLE'
  then
    l_role_name    := cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'ROLE');
    l_role_irid    := odwapriv.get_role_irid( p_role_name => l_role_name);
    js_confirmRevoke
    ( p_session_id => odwactxt.get_session_id
    , p_role_name  => l_role_name
    , p_grantee    => l_grantee
    );
  end if; -- l_type = 'GRANTEE_OF_ROLE'
  if l_type = 'GRANTED_ROLE'
  then
    l_role_name    := p_node_value;
    l_role_irid    := odwapriv.get_role_irid( p_role_name => l_role_name);
    js_confirmRevoke
    ( p_session_id => odwactxt.get_session_id
    , p_role_name  => l_role_name
    , p_grantee    => l_grantee
    );
  end if; -- l_type = 'GRANTED_ROLE'
  cdwp.include_stnd_styles;
  htp.p('<STYLE> TD { vertical-align:top } </STYLE>');
  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"'
  ||cdwpbase.ifThenElse
    ( p_refresh_tree ='Y'
    , ' onLoad="refreshTree()"'
    )
  );
  htp.p
  ('<!--'
  ||'node value = '||p_node_value
  ||'root classification = '||p_root_classification
  ||'node type = '||p_node_type  
  ||'Derived value: '
  ||' Root type = '||l_root_type  
  ||' type = '||l_type  
  ||' grantee = '||l_grantee
  ||' rolename = '||l_role_name
  ||' irid = '||l_irid
  
  ||'-->'
  );

  -- selected nodes can be:        type = (cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'TYPE'))
  -- - USER                         USER
  -- - ROLE                         ROLE
  -- - granted ROLE                 GRANTED_ROLE or GRANTEE_OF_ROLE
  -- - granted Workarea             WA
  -- - granted Folder               FOL (and WA_IRID is null)
  -- - granted Folder in Workarea   FOL (and WA_IRID is not null)
  -- - granted Configuration        CFG
  
  -- depending on the l_type and the privileges of the current user, we may provide
  -- a menu bar offering extra options (like Edit Privileges, Create/Grant/Reconcile Role etc.
  
  l_menu_bar:= 
  get_menu_bar
  ( p_root_type  => l_root_type
  , p_irid       => l_irid
  , p_grantee    => l_grantee
  , p_grantee_type  => l_grantee_type
  , p_type       => l_type
  , p_role_name  => l_role_name
  , p_role_irid  => l_role_irid
  , p_session_id => odwactxt.get_session_id
  , p_node_value => p_node_value
  );
  
  htp.tableopen( cattributes => 'BORDER="0" align="RIGHT"');  -- 1.4  bgcolor="88DDF9"
  cdwp.tableRowOpen;
  htp.tabledata
  ( cvalue=> l_menu_bar
  , calign => 'right'
  , cattributes => 'Zwidth="400" ABGCOLOR="#cccccc"'
  );
  cdwp.tableRowClose;
  cdwp.tableClose;
  htp.nl;
  htp.nl;

--  htp.tableopen( cattributes => 'BORDER="2" ');  -- 1.4  bgcolor="88DDF9"
  
  if l_type in ('ROLE', 'USER')
  then
    open_palette('User/Role Palette');
    user_props
    ( p_username => p_node_value
    );
  elsif l_type in ('WA', 'CFG', 'FOL','ROOTFOL')
        and
        l_root_type in ('WA', 'CFG', 'FOL','ROOTFOL')
  then
    open_palette('Palette '||odwapriv.get_object_label(p_irid => l_irid));
    if l_type = 'WA'
    then
      wa_props
      ( p_wa_irid => l_irid
      );
    elsif l_type = 'CFG'
    then
      cfg_props
      ( p_cfg_irid => l_irid
      );
    elsif l_type in ( 'FOL', 'ROOTFOL')
    then
      fol_props
      ( p_fol_irid => l_irid
      );
    end if;
  else
    if l_type <> 'ROOT'
    then
      open_palette
      ( 'Privileges Palette'
        || cdwpbase.ifThenElse
           ( l_type in ( 'GRANTEE_OF_ROLE', 'GRANTED_ROLE')
           , ' for grant of <BR>'
             || cdwpbase.ifThenElse
                ( l_type = 'GRANTEE_OF_ROLE'
                , l_role_name
                , p_node_value
                )
           , cdwpbase.ifThenElse
             ( l_type in ( 'GRANTEE_OF_WA', 'GRANTEE_OF_FOL', 'GRANTEE_OF_CFG')
             , ' for grants on <BR>'
               || odwapriv.get_object_label( p_irid => l_irid, p_include_gif=> true)
             )
           )
           || cdwpbase.ifThenElse
              ( l_type in ( 'GRANTEE_OF_ROLE', 'GRANTED_ROLE', 'GRANTEE_OF_WA', 'GRANTEE_OF_FOL', 'GRANTEE_OF_CFG')
              , ' to '
                || cdwpbase.ifThenElse
                   ( l_grantee_type ='U'
                   , cdwp.add_images('{user.gif}')
                   , cdwp.add_images('{role.gif}')
                   )
                ||l_grantee
              )
      );       
      
  --    htp.tableheader
  --    ( ccolspan => 4
  --    , cvalue =>   '<font color="#FFFFFF"><font face="MS LineDraw"><font size=-1><B>'
  --                    ||cdwpbase.nbsp(25)
  --                   ||'Privileges Palette'
  --                    ||cdwpbase.ifThenElse
  --                      ( l_type in ( 'GRANTEE_OF_ROLE', 'GRANTED_ROLE')
  --                      , ' for grant of '
  --                      ||'<BR>'
  --                      ||cdwpbase.ifThenElse
  --                       ( l_type = 'GRANTEE_OF_ROLE'
  --                       , cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'ROLE')
  --                       , p_node_value
  --                       )
  --                     , cdwpbase.ifThenElse
  --                       ( l_type in ( 'GRANTEE_OF_WA', 'GRANTEE_OF_FOL', 'GRANTEE_OF_CFG')
  --                       , ' for grants on '
  --                       ||'<BR>'
  --                       ||odwapriv.get_object_label( p_irid => l_irid, p_include_gif=> true)
  --                       )
  --                      )
  --                    ||cdwpbase.ifThenElse
  --                      ( l_type in ( 'GRANTEE_OF_ROLE', 'GRANTED_ROLE', 'GRANTEE_OF_WA', 'GRANTEE_OF_FOL', 'GRANTEE_OF_CFG')
  --                      , ' to '
  --                      ||cdwpbase.ifThenElse
  --                        ( l_grantee_type ='U'
  --                        , cdwp.add_images('{user.gif}')
  --                        , cdwp.add_images('{role.gif}')
  --                        )
  --                      ||l_grantee
  --                     )
  --                    ||cdwpbase.nbsp(25)
  --                    ||'</B></font></font></font>'
  --    , cattributes => 'BGCOLOR="#000080" ALIGN="CENTER"'
  --    );
  
  
      if l_type in ( 'GRANTEE_OF_ROLE', 'GRANTED_ROLE')
      then
        role_privs
        ( p_grantee => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'GRANTEE')
        , p_role    => cdwpbase.ifThenElse
                       ( l_type = 'GRANTEE_OF_ROLE'
                       , cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'ROLE')
                       , p_node_value
                       )
        );
      elsif l_type in ( 'FOL','CFG')
      then
        if nvl(instr( p_node_type, 'WA_IRID'),0) > 0
        then
          object_in_wa_privs
          ( p_grantee => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'GRANTEE')
          , p_irid    => to_number(cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'IRID'))
          , p_wa_irid => to_number(cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'WA_IRID'))
          );
        else
          object_privs
          ( p_grantee => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'GRANTEE')
          , p_irid    => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'IRID')
          );
        end if;
      elsif l_type in ('WA', 'GRANTEE_OF_WA')
      then
        object_privs
        ( p_grantee => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'GRANTEE')
        , p_irid    => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'WA_IRID')
        );
      elsif l_type in ('CFG', 'GRANTEE_OF_CFG')
      then
        object_privs
        ( p_grantee => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'GRANTEE')
        , p_irid    => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'CFG_IRID')
        );
      elsif l_type in ('FOL', 'GRANTEE_OF_FOL')
      then
        if l_root_type = 'WA'
        then
          object_in_wa_privs
          ( p_grantee => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'GRANTEE')
          , p_irid    => to_number(cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'FOL_IRID'))
          , p_wa_irid => to_number(cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'WA_IRID'))
          );
        else
          object_privs
          ( p_grantee => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'GRANTEE')
          , p_irid    => cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'FOL_IRID')
          );
        end if; -- l_root_type = 'WA'
      end if; -- l_type in ( 'GRANTEE_OF_ROLE', 'GRANTED_ROLE')
    end if; -- l_type <>'ROOT'   
  end if; -- l_type in ( 'USER','ROLE')
  close_palette;
  htp.bodyClose;
  htp.htmlClose;

  --paletteFrame( p_session_id); -- until I come up with something better
end; -- node_selected


-- this procedure will display an overview of process details
-- either passed in p_action_report or in g_rpt_tbl
-- on acknowledging this report by pressing the OK button
-- the screen will navigate to either
-- Node_Selected (with the parameter values provided)
-- or to p_url
procedure action_report
( p_session_id             in number
, p_node_value             in varchar2 default null
, p_node_type              in varchar2 default null
, p_root_value             in varchar2 default null
, p_root_classification    in varchar2 default null
, p_refresh_tree           in boolean default false
, p_refresh_tree_level     in number   default 0  -- 0 is node itself, 1 is parent
, p_action_report          in varchar2 default null
, p_url                    in varchar2 default null
, p_display_label          in varchar2 default null
) is

  l_action_report varchar2(32000):= nvl( p_action_report, g_action_report);

begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'action_Report'
  );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  if p_refresh_tree
  then
    js_refreshTree(p_refresh_tree_level);
  end if;
  js_init(p_text => l_action_report, p_item=> 'document.reportForm.report.value');
  cdwp.include_stnd_styles;
  htp.p('<STYLE> TD { vertical-align:top } </STYLE>');
  htp.headClose;
  htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"'
  ||cdwpbase.ifThenElse
    ( p_refresh_tree
    , ' onLoad="init();refreshTree()"'
    , ' onLoad="init()"'
    )
  );
  htp.p
  ('<!--'
  ||'node value = '||p_node_value
  ||'root classification = '||p_root_classification
  ||'node type = '||p_node_type   
  ||'-->'
  );


  open_palette(Rob_msg.getMsg(Rob_msg.CAP356_ODWAPRED_COMPLETE, '', '', '', ''));
  htp.formopen
  ( curl         => 'no url'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="reportForm" '
  , ctarget      => ''
  );
  htp.formhidden(cname => 'p_session_id', cvalue => to_char(odwactxt.get_session_id));
  print_property
  ( 'Action'
  , p_display_label
  , p_mandatory=> 'N'
  );
  -- ActionReport
  print_property
  ( Rob_msg.getMsg(Rob_msg.CAP008_CDWP_DESC, '', '', '', '')
  ,' <TEXTAREA NAME="report" ROWS=10 COLS=30 WRAP="VIRTUAL"></TEXTAREA>'
  , p_mandatory=> 'N'
  );
  cdwp.tableRowOpen;
  cdwp.TableDataValue
  ( cdwpbase.nbsp(10)
  ||'<INPUT type="button" value="'||Rob_msg.getMsg(Rob_msg.CAP355_ODWAPRED_OK, '', '', '', '')||'" '
  ||cdwpbase.ifThenElse
    ( p_url is not null
    , ' onClick="document.location.href='''||p_url||'''"'
    , ' onClick="document.location.href=''odwapred.node_selected?p_session_id='||to_char(odwactxt.get_session_id)
    ||chr(38)||'p_node_value='||p_node_value
    ||chr(38)||'p_node_type='||p_node_type
    ||chr(38)||'p_root_value='||p_root_value
    ||chr(38)||'p_root_classification='||p_root_classification
    ||'''"'
    )
  ||'>'
  , p_attributes => 'VALIGN="TOP" colspan=2'
  );
  cdwp.tableRowClose;

  close_palette;
  htp.bodyClose;
  htp.htmlClose;

  --paletteFrame( p_session_id); -- until I come up with something better
end; -- action_report

end; -- odwapred
/