create or replace package body odwapriv
is
/*****************************************************************************************
Purpose Provides HTML report in tree navigation style showing users and roles
in the Oraxle Repository, with all their associated privileges on
Folders, Workareas, Configurations
Usage
Remarks
Revision history
When Who Construct
Revision What
------------------------------------------------------------------------------------------
$REVISION_HISTORY$
10-Sep-2003 Kannan Parthasarathy
- B2881790: Fixed GIF file name.
04-Dec-2002 Kannan Parthasarathy
- B2661620: Do not show deleted objects in the tree.
19-Nov-2002 Kannan Parthasarathy
- Fixed OAC P1 violations.
15-oct-2002 Kannan Parthasarathy
- Preserve visual attributes for new splash image
26-sep-2002 Kannan Parthasarathy
New splash image
24-sep-2002 Kannan Parthasarathy
- Changes made to accommodate new logo.
02-mar-2001 Lucas Jellema
1.4 - allow initial context to be set (to jump from Folder in Navigator to Grant access on folder)
29-jan-2001 Lucas Jellema
1.3 - load less of the tree in the intial load
- solve other problems
12-jan-2001 Peter Ebell
1.2 - Added Self Service header, and applied look and feel to tables
22-dec-2000 Lucas Jellema
1.1 - Use gray gifs.
08-dec-2000 Lucas Jellema
1.0 - Initial creation to support Role Browsing and Management
13-oct-2000 Lucas Jellema
1.0 - Initial creation (heavily borrowing from odwapriv)
*****************************************************************************************/
--
-- private constants
--
REVISION_LABEL constant varchar2(30) := '$x.y::1.4 $';
PACKAGE_NAME constant varchar2(30) := 'ODWAPRIV';
check_gif constant varchar2(200):= cdwp.add_images
('{check_mark.gif}');
-- this function returns the string that is derived from the input string
-- by taking uppercase, translating spaces to underscores and limiting the length to 30 characters
function proper
( p_string in varchar2
) return varchar2
is
begin
return upper( translate( substr( p_string, 1, 30), ' ', '_'));
end; -- proper
function get_versioned_repository
return boolean
is
begin
return substr( jr_acc_rights.ar_to_str( jr_acc_rights.full_access_rights), 6,1) = 'Y';
end;
-- this function returns the irid of a Role given its Role Name
function get_role_irid
( p_role_name in varchar2
) return number
is
cursor c_usr
( b_role_name in varchar2
) is
select usr.irid
from sdw_users usr
where usr.username = p_role_name
;
l_role_irid number(38);
begin
open c_usr( b_role_name => proper( p_role_name));
fetch c_usr
into l_role_irid
;
close c_usr;
return l_role_irid;
end; -- get_role_irid
-- gets owner of any object
function get_governor
( object_irid in number
) return varchar2
is
begin
return jr_role.get_object_owner(p_irid => object_irid);
end;
-- this function returns the creator of a Role given its Role Name
function get_role_creator
( p_role_name in varchar2
) return varchar2
is
cursor c_usr
( b_role_name in varchar2
) is
select usr.created_by
from sdw_users usr
where usr.username = b_role_name
;
l_role_creator varchar2(30);
begin
open c_usr( b_role_name => proper( p_role_name));
fetch c_usr
into l_role_creator
;
close c_usr;
return l_role_creator;
end; -- get_role_creator
function get_role_owner
( p_role_name in varchar2
) return varchar2
is
cursor c_usr
( b_role_name in varchar2
) is
select acc.grantee_reference
from sdw_users usr
, sdw_access_rights acc
where usr.username = b_role_name
and acc.object_reference = usr.irid
and acc.grantee_reference = acc.grantor_reference
;
l_role_owner varchar2(30);
begin
open c_usr( b_role_name => proper( p_role_name));
fetch c_usr
into l_role_owner
;
close c_usr;
return l_role_owner;
end; -- get_role_owner
-- this function returns the type - R(ole) or U(ser) of a grantee
function get_grantee_type
( p_grantee in varchar2
) return varchar2
is
cursor c_usr
( b_grantee in varchar2
) is
select decode
( usr.user_type
, 0, 'R'
, 1, 'U'
, 'U'
)
from sdw_users usr
where usr.username = b_grantee
;
l_grantee_type varchar2(1);
begin
open c_usr( b_grantee => p_grantee);
fetch c_usr
into l_grantee_type
;
close c_usr;
return l_grantee_type;
end; -- get_grantee_type
function get_object_type
( p_irid in number
) return varchar2
is
cursor c_type( b_irid in number)
is
select acc.object_type
from sdw_access_rights acc
where acc.object_reference = b_irid
;
l_object_type varchar2(10);
begin
open c_type( b_irid => p_irid);
fetch c_type
into l_object_type;
close c_type;
return l_object_type;
end; -- get_object_type
function get_object_label
( p_irid in number
, p_include_gif in boolean default true
) return varchar2
is
l_object_type varchar2(6):= get_object_type( p_irid);
l_object_label varchar2(2000);
begin
l_object_label:= cdwpbase.ifThenElse
( l_object_type = 'WA'
, cdwpbase.ifThenElse
( p_include_gif
, cdwp.add_images('{workarea.gif}')
)
||cdwpbase.get_wa_name(p_wa_irid => p_irid)
, cdwpbase.ifThenElse
( l_object_type ='CFG'
, cdwpbase.ifThenElse
( p_include_gif
, cdwp.add_images('{configuration.gif}')
)
||cdwpbase.get_cfg_name
( p_cfg_ivid => cdwpbase.get_best_ivid( p_irid)
)
, cdwpbase.ifThenElse
( p_include_gif
,cdwp.add_images('{folder.gif}')
)
||jr_name.get_path
( id => p_irid
, format => 'NAME'
) -- Folder
)
);
return l_object_label;
end; -- get_object_label
--
-- public procedures and functions
--
procedure navigatorTree
( p_session_id in number
)
is
l_first boolean;
begin
odwactxt.update_context
( p_session_id => p_session_id
, p_package_name => PACKAGE_NAME
, p_procedure_name=> 'navigatorTree'
);
htp.htmlOpen;
htp.headOpen;
cdwp.write_about(package_name, revision_label);
htp.headClose;
--
-- body
--
htp.bodyOpen
( cattributes=>'BGCOLOR="#FFFFFF" '
||'onLoad="top.init();top.drawOnLoad()"'
);
htp.p(Rob_msg.GetMsg(Rob_msg.DSP302_ODWAPRIV_NAVTREE,'','','',''));
htp.bodyClose;
htp.htmlClose;
end; -- navigatorTree
procedure paletteFrame
( p_session_id in number
, p_initial_context in varchar2 default null
)
is
l_irid varchar2(50):= cdwpbase.get_tag_value( p_string=> p_initial_context, p_tag => 'IRID');
begin
-- create session when necessary, refresh session cache, set workarea context
-- from here on reference to odwactxt.get_... functions for any context value
odwactxt.update_context
( p_session_id => p_session_id
, p_package_name => PACKAGE_NAME
, p_procedure_name=> 'paletteFrame'
);
htp.htmlOpen;
htp.headOpen;
cdwp.write_about(package_name, revision_label);
htp.headClose;
htp.bodyOpen(cattributes=>'BGCOLOR="#FFFFFF"'
||cdwpbase.ifThenElse
( instr(p_initial_context, 'GRANT_FOL') > 0
, 'onLoad="document.location.href=''odwapred.grantPrivs?p_session_id='||to_char(odwactxt.get_session_id)
||chr(38)||'p_irid='||l_irid||'''"'
)
);
htp.nl;
htp.tableData
( ' '
||cdwp.add_images( p_text => '{rob_splash.gif}', p_attributes => 'ALT=ROB Splash Screen') -- ,p_static => p_static)
||'
'
||hwsf.fontOpen(HWS.MS_SANS_SERIF, 6)
||Rob_msg.GetMsg(Rob_msg.DSP170_ROB,'','','','')
||hwsf.fontClose||'
'
||hwsf.fontOpen(HWS.COMIC_SANS_MS)
||cdwp.release_banner
||hwsf.fontClose
, 'RIGHT'
, cattributes=>'ROWSPAN="20" WIDTH="400"'
);
htp.bodyClose;
htp.htmlClose;
end; -- paletteFrame
procedure nested_folders_outside_wa
( p_folder_irid in number
, p_level in number
) is
cursor c_nested_apps( b_folder_irid in number)
is
select
app.irid app_id
, app.name app_name
, 1 app_version -- must be replaced with the real version label
, app.display_title app_display_title
, app.types app_types
, decode
( app.types
, 4844, 'FOL'
, 5009, 'APP'
, 'CNT') app_type
from sdd_folders app
, sdd_folder_members fm
where app.irid = fm.member_object
and fm.folder_reference = b_folder_irid
and app.ivid = cdwpbase.get_best_ivid( app.irid)
order
by app.name
;
l_icon varchar2(200);
l_link varchar2(2000);
l_prev_app_irid number(38):=0;
begin
for r_nested_apps in c_nested_apps(b_folder_irid => p_folder_irid) loop
l_icon:= cdwp.add_images
( p_text => '{'
||cdwpbase.ifThenElse
( r_nested_apps.app_type = 'APP'
, 'app_sys'
, 'folder'
)
||'.gif}'
);
if r_nested_apps.app_id <> l_prev_app_irid
then
l_prev_app_irid:= r_nested_apps.app_id;
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_nested_apps.app_name
, p_node_level => p_level + 1
, p_has_children => false
, p_is_expanded => false
, p_value => ''
, p_additional_label => ''
, p_is_inflatable => true
, p_classification => '{TYPE=FOL}'
||'{FOL_IRID='||to_char( r_nested_apps.app_id)||'}'
);
end if;
end loop; -- r_nested_apps
end; -- nested_folders_outside_wa
procedure nested_folders_in_wa
( p_folder_irid in number
, p_wa_irid in number
, p_level in number
) is
l_icon varchar2(200);
l_link varchar2(2000);
cursor c_nested_apps
( b_folder_irid in number
, b_wa_irid in number
)
is
select
app.irid app_id
, app.name app_name
, 1 app_version -- must be replaced with the real version label
, app.display_title app_display_title
, app.types app_types
, decode
( app.types
, 4844, 'FOL'
, 5009, 'APP'
, 'CNT') app_type
from sdd_folders app
, sdd_folder_members fm
, i$sdd_wa_context wac
, i$sdd_wa_context wac_root
where app.irid = fm.member_object
and fm.folder_reference = b_folder_irid
and fm.parent_ivid = wac_root.object_ivid
and wac.workarea_irid = b_wa_irid
and wac_root.workarea_irid = b_wa_irid
and wac.object_ivid = app.ivid
order
by app.name
;
begin
for r_nested_app in c_nested_apps
( b_folder_irid => p_folder_irid
, b_wa_irid => p_wa_irid
) loop
l_icon:= cdwp.add_images
( p_text => '{'
||cdwpbase.ifThenElse
( r_nested_app.app_type = 'APP'
, 'app_sys'
, 'folder'
)
||'.gif}'
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_nested_app.app_name
, p_node_level => p_level + 1
, p_has_children => false
, p_is_expanded => false
, p_value => ''
, p_additional_label => ''
, p_is_inflatable => true
, p_classification => '{TYPE=FOL}'
||'{FOL_IRID='||to_char( r_nested_app.app_id)||'}'
||'{WA_IRID='||to_char( p_wa_irid)||'}'
);
end loop; -- r_nested_app
end; -- nested_folders_in_wa
procedure inflate_grt_nested_fol_in_wa
( p_node_level in number -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
, p_grantee in varchar2
, p_wa_irid in number
, p_parent_folder_irid in number
) is
l_icon varchar2(200);
l_link varchar2(2000);
cursor c_granted_nested_fol_in_wa
( b_grantee in varchar2
, b_workarea_irid in number
, b_parent_folder_irid in number
)
is
select acc.object_reference fol_irid
, fol.name fol_name
, fol.container_subtype fol_subtype -- FOL, APP
from sdw_access_rights acc
, i$sdd_folders fol
, i$sdd_wa_context wac
, i$sdd_wa_context wac_parent
, i$sdd_folder_members fm
where acc.grantee_reference = b_grantee
and acc.object_reference = fol.irid
and fol.irid = fm.member_object
and fm.folder_reference = b_parent_folder_irid
and fol.ivid = wac.object_ivid
and fm.parent_ivid = wac_parent.object_ivid
and wac.workarea_irid = b_workarea_irid
and wac_parent.workarea_irid = b_workarea_irid
order
by fol_name
;
begin
for r_granted_nested_fol_in_wa in c_granted_nested_fol_in_wa
( b_grantee => p_grantee
, b_workarea_irid => p_wa_irid
, b_parent_folder_irid => p_parent_folder_irid
) loop
l_icon:= cdwp.add_images
( p_text => cdwpbase.ifThenElse
( r_granted_nested_fol_in_wa.fol_subtype = 'APP'
, '{app_sys.gif}'
, '{folder.gif}'
)
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_granted_nested_fol_in_wa.fol_name
, p_node_level => p_node_level + 2
, p_has_children => true
, p_is_expanded => false
, p_value => to_char(r_granted_nested_fol_in_wa.fol_irid)
, p_additional_label => ''
, p_is_inflatable => true
, p_classification => '{TYPE=FOL}'
||'{IRID='||to_char(r_granted_nested_fol_in_wa.fol_irid)||'}'
||'{WA_IRID='||to_char(p_wa_irid)||'}'
||'{GRANTEE='||p_grantee||'}'
);
end loop; -- r_granted_nested_fol_in_wa
end; -- inflate_grt_nested_fol_in_wa
procedure inflate_granted_nested_fol
( p_node_level in number -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
, p_grantee in varchar2
, p_parent_folder_irid in number
) is
l_icon varchar2(200);
cursor c_granted_nested_fol
( b_grantee in varchar2
, b_parent_folder_irid in number
)
is
select distinct
acc.object_reference fol_irid
, fol.name fol_name
, fol.container_subtype fol_subtype -- FOL, APP
from sdw_access_rights acc
, i$sdd_folders fol
, i$sdd_folder_members fm
where acc.object_reference = fol.irid
and acc.grantee_reference = b_grantee
and fol.irid = fm.member_object
and fm.folder_reference = b_parent_folder_irid
and fol.ivid = cdwpbase.get_best_ivid( fol.irid) -- most recent version on MAIN
order
by fol_name
;
begin
for r_granted_nested_fol in c_granted_nested_fol
( b_grantee => upper(p_grantee)
, b_parent_folder_irid => p_parent_folder_irid
) loop
l_icon:= cdwp.add_images
( p_text => cdwpbase.ifThenElse
( r_granted_nested_fol.fol_subtype = 'APP'
, '{app_sys.gif}'
, '{folder.gif}'
)
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_granted_nested_fol.fol_name
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => to_char(r_granted_nested_fol.fol_irid)
, p_additional_label => ''
, p_is_inflatable => true
, p_classification => '{TYPE=FOL}'
||'{IRID='||to_char(r_granted_nested_fol.fol_irid)||'}'
||'{GRANTEE='||p_grantee||'}'
);
end loop; -- r_granted_nested_fol
end; -- inflate_granted_nested_fol
procedure inflate_granted_root_fol
( p_node_level in number -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
, p_grantee in varchar2
) is
l_icon varchar2(200);
l_link varchar2(2000);
l_first boolean:= true;
cursor c_granted_root_fol
( b_grantee in varchar2)
is
select acc.object_reference fol_irid
, fol.name fol_name
, fol.container_subtype fol_subtype -- FOL, APP
from sdw_access_rights acc
, i$sdd_folders fol
where acc.grantee_reference = b_grantee
and acc.object_reference = fol.irid
and fol.ivid = cdwpbase.get_best_ivid( fol.irid) -- most recent version on MAIN
and fol.root_flag = 'Y'
order
by fol_name
;
begin
for r_granted_root_fol in c_granted_root_fol( b_grantee => p_grantee) loop
l_icon:= cdwp.add_images
( p_text => cdwpbase.ifThenElse
( r_granted_root_fol.fol_subtype = 'APP'
, '{app_sys.gif}'
, '{folder.gif}'
)
);
if l_first
then
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{folder.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.DSP303_ODWAPRIV_FOLASYS,'','','','')||'"'
)
||cdwpbase.nbsp(1)
||Rob_msg.GetMsg(Rob_msg.CAP130_ODWAPRIV_CON,'','','','')
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_FOL'
, p_additional_label => ''
, p_is_inflatable => false
, p_classification => 'nolink'
);
l_first:= false;
end if; -- l_first
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_granted_root_fol.fol_name
, p_node_level => p_node_level + 2
, p_has_children => true
, p_is_expanded => false
, p_value => to_char(r_granted_root_fol.fol_irid)
, p_additional_label => ''
, p_is_inflatable => true
, p_classification => '{TYPE=FOL}'
||'{IRID='||to_char(r_granted_root_fol.fol_irid)||'}'
||'{GRANTEE='||p_grantee||'}'
);
end loop; -- r_granted_root_fol
end; -- inflate_granted_root_fol
procedure inflate_granted_root_fol_in_wa
( p_node_level in number -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
, p_grantee in varchar2
, p_wa_irid in number
) is
l_icon varchar2(200);
l_link varchar2(2000);
cursor c_granted_root_fol_in_wa
( b_grantee in varchar2
, b_workarea_irid in number
)
is
select acc.object_reference fol_irid
, fol.name fol_name
, fol.container_subtype fol_subtype -- FOL, APP
, wac.object_ivid fol_ivid
from sdw_access_rights acc
, i$sdd_folders fol
, i$sdd_wa_context wac
where acc.grantee_reference = b_grantee
and acc.object_reference = fol.irid
and fol.ivid = wac.object_ivid
and fol.root_flag = 'Y'
and wac.workarea_irid = b_workarea_irid
order
by fol_name
;
begin
for r_granted_root_fol_in_wa in c_granted_root_fol_in_wa
( b_grantee => p_grantee
, b_workarea_irid => p_wa_irid
) loop
if not odwavrsn.is_in_wastebasket(p_ivid => r_granted_root_fol_in_wa.fol_ivid) then
l_icon:= cdwp.add_images
( p_text => cdwpbase.ifThenElse
( r_granted_root_fol_in_wa.fol_subtype = 'APP'
, '{app_sys.gif}'
, '{folder.gif}'
)
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_granted_root_fol_in_wa.fol_name
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => to_char(r_granted_root_fol_in_wa.fol_irid)
, p_additional_label => ''
, p_is_inflatable => true
, p_classification => '{TYPE=FOL}'
||'{IRID='||to_char(r_granted_root_fol_in_wa.fol_irid)||'}'
||'{WA_IRID='||to_char(p_wa_irid)||'}'
||'{GRANTEE='||p_grantee||'}'
);
end if;
end loop; -- r_granted_root_fol_in_wa
end; -- inflate_granted_root_fol_in_wa
procedure inflate_granted_workareas
( p_node_level in number -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
, p_grantee in varchar2
) is
l_icon varchar2(200);
l_link varchar2(2000);
l_first boolean := true;
cursor c_granted_wa
( b_grantee in varchar2)
is
select acc.object_reference wa_irid
, wa.name wa_name
from sdw_access_rights acc
, i$sdd_workareas wa
where acc.grantee_reference = b_grantee
and acc.object_reference = wa.irid
order
by wa.name
;
begin
l_icon:= cdwp.add_images
( p_text => '{workarea.gif}'
);
for r_granted_wa in c_granted_wa( b_grantee => p_grantee) loop
if l_first
then
l_first := false;
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{workarea.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP131_ODWAPRIV_GRWA,'','','','')||'"'
)
||cdwpbase.nbsp(1)
||Rob_msg.GetMsg(Rob_msg.DSP181_ODWA_WAS,'','','','')
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_WA'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
end if;
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_granted_wa.wa_name
, p_node_level => p_node_level + 2
, p_has_children => true
, p_is_expanded => false
, p_value => ''
, p_additional_label => ''
, p_is_inflatable => true
, p_classification => '{TYPE=WA}{WA_IRID='||to_char(r_granted_wa.wa_irid)||'}'
||'{GRANTEE='||p_grantee||'}'
);
end loop; -- r_granted_wa
end; -- inflate_granted_workareas
procedure infl_fol_in_wa_granted_to
( p_node_level in number -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
, p_irid in number
, p_wa_irid in number
, p_object_type in varchar2 -- values are : WA, CFG, APP, FOL
) is
l_icon varchar2(200);
l_link varchar2(2000);
l_first boolean := true;
l_last_grantee_type number(1):=9;
cursor c_granted_to
( b_irid in number)
is
select grantee.username grantee_name
, grantee.user_type -- 0 = ROLE, 1 = USER
from sdw_users grantee
, sdw_access_rights acc
where acc.grantee_reference = grantee.username
and acc.object_reference = b_irid
order
by grantee.user_type desc
, grantee.username
;
begin
for r_granted_to in c_granted_to( b_irid => p_irid) loop
if l_last_grantee_type <> r_granted_to.user_type
then
l_last_grantee_type := r_granted_to.user_type;
if r_granted_to.user_type = 1 -- USERS
then
l_icon:= cdwp.add_images
( p_text => '{user.gif}'
);
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{user.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP132_ODWAPRIV_GRUSERS,'','','','')||'"'
)
||cdwpbase.nbsp(1)
||Rob_msg.GetMsg(Rob_msg.CAP132_ODWAPRIV_GRUSERS,'','','','')
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_TO_USERS'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
else
l_icon:= cdwp.add_images
( p_text => '{role.gif}'
);
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{db_user_role.gif}'
, p_attributes => 'ALT="Granted To Roles"'
)
||cdwpbase.nbsp(1)
||'Granted to Roles'
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_TO_ROLES'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
end if; -- r_granted_to.user_type = 1 -- USERS
end if; -- l_last_grantee_type <> r_granted_to.user_type
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||cdwpbase.ifThenElse -- if this grantee has not at least SEL on WA, print name in italic
( not jr_acc_rights.has_access
( object_irid => p_wa_irid
, grantee => r_granted_to.grantee_name
, privilege => 'SEL'
)
,''
)
||r_granted_to.grantee_name
||cdwpbase.ifThenElse
( not jr_acc_rights.has_access
( object_irid => p_wa_irid
, grantee => r_granted_to.grantee_name
, privilege => 'SEL'
)
,''
)
, p_node_level => p_node_level + 2
, p_has_children => false
, p_is_expanded => false
, p_value => r_granted_to.grantee_name
, p_additional_label => l_link
, p_is_inflatable => r_granted_to.user_type = 0
, p_classification => '{TYPE=GRANTEE_OF_'||p_object_type||'}'
||'{GRANTEE='||r_granted_to.grantee_name||'}'
||'{'||p_object_type||'_IRID='||to_char(p_irid)||'}'
||'{WA_IRID='||to_char(p_wa_irid)||'}'
);
end loop; -- r_granted_to
end; -- infl_fol_in_wa_granted_to
procedure inflate_object_granted_to
( p_node_level in number -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
, p_irid in number
, p_object_type in varchar2 -- values are : WA, CFG, APP, FOL
) is
l_icon varchar2(200);
l_link varchar2(2000);
l_first boolean := true;
l_last_grantee_type number(1):=9;
cursor c_granted_to
( b_irid in number)
is
select grantee.username grantee_name
, grantee.user_type -- 0 = ROLE, 1 = USER
from sdw_users grantee
, sdw_access_rights acc
where acc.grantee_reference = grantee.username
and acc.object_reference = b_irid
order
by grantee.user_type desc
, grantee.username
;
begin
for r_granted_to in c_granted_to( b_irid => p_irid) loop
if l_last_grantee_type <> r_granted_to.user_type
then
l_last_grantee_type := r_granted_to.user_type;
if r_granted_to.user_type = 1 -- USERS
then
l_icon:= cdwp.add_images
( p_text => '{user.gif}'
);
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{user.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP132_ODWAPRIV_GRUSERS,'','','','')||'"'
)
||cdwpbase.nbsp(1)
||Rob_msg.GetMsg(Rob_msg.CAP132_ODWAPRIV_GRUSERS,'','','','')
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_TO_USERS'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
else
l_icon:= cdwp.add_images
( p_text => '{role.gif}'
);
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{db_user_role.gif}'
, p_attributes => 'ALT="Granted To Roles"'
)
||cdwpbase.nbsp(1)
||'Granted to Roles'
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_TO_ROLES'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
end if; -- r_granted_to.user_type = 1 -- USERS
end if; -- l_last_grantee_type <> r_granted_to.user_type
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_granted_to.grantee_name
, p_node_level => p_node_level + 2
, p_has_children => false
, p_is_expanded => false
, p_value => r_granted_to.grantee_name
, p_additional_label => l_link
, p_is_inflatable => r_granted_to.user_type = 0
, p_classification => '{TYPE=GRANTEE_OF_'||p_object_type||'}'
||'{GRANTEE='||r_granted_to.grantee_name||'}'
||'{'||p_object_type||'_IRID='||to_char(p_irid)||'}'
);
end loop; -- r_granted_to
end; -- inflate_object_granted_to
procedure inflate_granted_configurations
( p_node_level in number -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
, p_grantee in varchar2
) is
l_icon varchar2(200);
l_link varchar2(2000);
l_first boolean := true;
cursor c_granted_cfg
( b_grantee in varchar2)
is
select distinct -- we are not interested in multiple versions of the configurations; access privis are not version dependent or related
acc.object_reference cfg_irid
, cfg.name cfg_name
from sdw_access_rights acc
, i$sdd_configurations cfg
where acc.grantee_reference = b_grantee
and acc.object_reference = cfg.irid
and cfg.ivid = cdwpbase.get_best_ivid( cfg.irid)
;
begin
l_icon:= cdwp.add_images
( p_text => '{configuration.gif}'
);
for r_granted_cfg in c_granted_cfg( b_grantee => p_grantee) loop
if l_first
then
l_first := false;
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{configuration.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP133_ODWAPRIV_GRCONF,'','','','')||'"'
)
||cdwpbase.nbsp(1)
||Rob_msg.GetMsg(Rob_msg.DSP176_ODWA_CONFIG,'','','','')
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_CFG'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
end if;
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_granted_cfg.cfg_name
, p_node_level => p_node_level + 2
, p_has_children => false
, p_is_expanded => false
, p_value => to_char(r_granted_cfg.cfg_irid)
, p_additional_label => ''
, p_is_inflatable => false
, p_classification => '{TYPE=CFG}{IRID='||to_char(r_granted_cfg.cfg_irid)||'}'
||'{GRANTEE='||p_grantee||'}'
);
end loop; -- r_granted_cfg
end; -- inflate_granted_configurations
procedure inflate_granted_roles
( p_node_level in number -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
, p_grantee in varchar2
) is
l_icon varchar2(200);
l_link varchar2(2000);
l_first boolean := true;
cursor c_granted_roles
( b_grantee in varchar2)
is
select rle.username role_name
, acc.object_reference role_irid
from sdw_users rle
, sdw_access_rights acc
where acc.grantee_reference = b_grantee
and acc.object_reference = rle.irid
;
begin
l_icon:= cdwp.add_images
( p_text => '{db_user_role.gif}'
);
for r_granted_roles in c_granted_roles( b_grantee => p_grantee) loop
if l_first
then
l_first := false;
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{db_user_role.gif}'
, p_attributes => 'ALT="Granted Roles"'
)
||cdwpbase.nbsp(1)
||'Roles'
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_ROLES'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
end if;
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_granted_roles.role_name
, p_node_level => p_node_level + 2
, p_has_children => true
, p_is_expanded => false
, p_value => r_granted_roles.role_name
, p_additional_label => l_link
, p_is_inflatable => true
, p_classification => '{TYPE=GRANTED_ROLE}'
||'{GRANTEE='||p_grantee||'}'
);
end loop; -- r_granted_roles
end; -- inflate_granted_roles
procedure inflate_granted_to
( p_node_level in number
, p_role_name in varchar2
) is
l_icon varchar2(200);
l_link varchar2(2000);
l_first boolean := true;
l_last_grantee_type number(1):= 9;
cursor c_granted_to
( b_role_name in varchar2)
is
select acc.grantee_reference grantee_name
, grantee.user_type -- 0 = ROLE, 1 = USER
from sdw_users rle
, sdw_users grantee
, sdw_access_rights acc
where acc.object_reference = rle.irid
and rle.username = b_role_name
and acc.grantee_reference = grantee.username
order
by grantee.user_type desc
, grantee.username
;
cursor c_not_granted_to
( b_role_name in varchar2)
is
select usr.username
, usr.irid
, usr.user_type -- 0 = ROLE, 1 = USER
from sdw_users usr
where not exists
( select 'x'
from sdw_users rle
, sdw_users grantee
, sdw_access_rights acc
where acc.object_reference = rle.irid
and rle.username = b_role_name
and acc.grantee_reference = usr.username
)
order
by usr.user_type desc
, usr.username
;
begin
for r_granted_to in c_granted_to( b_role_name => p_role_name) loop
if l_last_grantee_type <> r_granted_to.user_type
then
l_last_grantee_type := r_granted_to.user_type;
if r_granted_to.user_type = 1 -- USERS
then
l_icon:= cdwp.add_images
( p_text => '{user.gif}'
);
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{user.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP132_ODWAPRIV_GRUSERS,'','','','')||'"'
)
||cdwpbase.nbsp(1)
||Rob_msg.GetMsg(Rob_msg.CAP132_ODWAPRIV_GRUSERS,'','','','')
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_TO_USERS'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
else
l_icon:= cdwp.add_images
( p_text => '{role.gif}'
);
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{db_user_role.gif}'
, p_attributes => 'ALT="Granted To Roles"'
)
||cdwpbase.nbsp(1)
||'Granted to Roles'
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_TO_ROLES'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
end if; -- r_granted_to.user_type = 1 -- USERS
end if; -- l_last_grantee_type <> r_granted_to.user_type
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_granted_to.grantee_name
, p_node_level => p_node_level + 2
, p_has_children => false
, p_is_expanded => false
, p_value => r_granted_to.grantee_name
, p_additional_label => l_link
, p_is_inflatable => r_granted_to.user_type = 0
, p_classification => '{TYPE=GRANTEE_OF_ROLE}'
||'{GRANTEE='||r_granted_to.grantee_name||'}'
||'{ROLE='||p_role_name||'}'
||cdwpbase.ifThenElse
( p_node_level > 100 -2
, 'nolink'
)
);
end loop; -- r_granted_to
for r_not_granted_to in c_not_granted_to( b_role_name => p_role_name) loop
if jr_role.is_grantee_of_role
( p_grantee => r_not_granted_to.username
, p_role_name => p_role_name
, p_recursive => true
)
then
if l_last_grantee_type <> r_not_granted_to.user_type
then
l_last_grantee_type := r_not_granted_to.user_type;
if r_not_granted_to.user_type = 1 -- USERS
then
l_icon:= cdwp.add_images
( p_text => '{user.gif}'
);
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{user.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP134_ODWAPRIV_IGRUSERS,'','','','')||'"'
)
||cdwpbase.nbsp(1)
||''||Rob_msg.GetMsg(Rob_msg.CAP134_ODWAPRIV_IGRUSERS,'','','','')||''
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_TO_USERS'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
else
l_icon:= cdwp.add_images
( p_text => '{role.gif}'
);
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{db_user_role.gif}'
, p_attributes => 'ALT="Indirectly Granted To Roles"'
)
||cdwpbase.nbsp(1)
||'Indirectly Granted to Roles'
, p_node_level => p_node_level + 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'GRANTED_TO_ROLES'
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => 'nolink'
);
end if; -- r_not_granted_to.user_type = 1 -- USERS
end if; -- l_last_grantee_type <> r_not_granted_to.user_type
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_not_granted_to.username
, p_node_level => p_node_level + 2
, p_has_children => false
, p_is_expanded => false
, p_value => r_not_granted_to.username
, p_additional_label => l_link
, p_is_inflatable => false
, p_classification => '{TYPE=INDIRECT_GRANTEE_OF_ROLE}'
||'{GRANTEE='||r_not_granted_to.username||'}'
||'{ROLE='||p_role_name||'}'
||'nolink'
);
end if; -- if jr_role.is_grantee_of_role
end loop; -- r_not_granted_to
end; -- inflate_granted_to
-- this procedures loads the Workareas
procedure load_wa
is
cursor c_wa
is
select wa.irid
, wa.OWNER
, wa.NAME
, wa.KIND
, wa.DESCRIPTION
from sdd_workareas wa
order
by wa.name
;
l_icon varchar2(200);
l_link varchar2(2000);
begin
for r_wa in c_wa loop
jr_context.set_workarea( r_wa.irid);
l_icon:= cdwp.add_images
( p_text => '{workarea.gif}'
, p_attributes => 'ALT="'||r_wa.name
||' - '||r_wa.description
||'"'
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_wa.name
, p_node_level => 2
, p_has_children => true -- if there are children, the next step will include them and find_folder_nodes will correct this setting where appropriate
, p_is_expanded => false
, p_value => to_char(r_wa.irid)
, p_additional_label => '' --l_link
, p_is_inflatable => true
, p_classification => '{TYPE=WA}{WA_IRID='||to_char(r_wa.irid)||'}'
);
end loop; -- r_wa
end; -- load_wa
procedure inflate_root_in_wa
( p_wa_irid in number
) is
l_icon varchar2(200);
l_link varchar2(2000);
l_prev_app_irid number(38):=0;
begin
jr_context.set_workarea( p_wa_irid);
for r_app_vrsn in odwa_fol.c_root_app loop
l_icon:= cdwp.add_images
( p_text => '{'
||cdwpbase.ifThenElse
( r_app_vrsn.app_type = 'APP'
, 'app_sys'
, 'folder'
)
||'.gif}'
);
l_link:= cdwpbase.nbsp(2)
||cdwp.report_link
( p_app_id => r_app_vrsn.app_id
, p_app_ivid => r_app_vrsn.app_ivid
, p_type_of => 'APP_DETAILS'
, p_text => '..'
, p_bookmark => ''
, p_target => '_new'
, p_wa_id => p_wa_irid
);
htmltree.add_node
( p_display_label => cdwpbase.ifThenElse
( r_app_vrsn.state ='O' -- checked out
, cdwp.add_images('{check_mark.gif}')
)
||l_icon
||cdwpbase.nbsp(1)
||r_app_vrsn.app_name
-- ||cdwpbase.ifThenElse
-- ( r_app_vrsn.vlabel is not null
-- , ' ('||r_app_vrsn.vlabel||')'
-- )
, p_node_level => 3
, p_has_children => true
, p_is_expanded => false
, p_value => to_char(r_app_vrsn.app_id)
, p_additional_label => l_link
, p_is_inflatable => true -- there are always the element types within the folder
, p_classification => '{TYPE=FOL}'
||'{WA_IRID='||to_char(p_wa_irid)||'}'
||'{FOL_IRID='||to_char(r_app_vrsn.app_id)||'}'
);
end loop; -- r_app_vrsn
end; -- inflate_root_in_wa
procedure inflate_config
( p_cfg_irid in number
, p_node_level in number
) is
cursor c_cfg( b_irid in number)
is
select cfg.IRID
, cfg.IVID
, cfg.NAME
, cfg.DESCRIPTION
, ov.vlabel
, nvl(ov.state, 'N') state
from sdd_configurations cfg
, sdd_object_versions ov
where ov.ivid = cfg.ivid
and cfg.irid = b_irid
order
by ov.branch_id -- to at least group by branch; probably need name of branch as well
, ov.sequence_in_branch
;
l_icon varchar2(200);
l_link varchar2(2000);
l_first boolean:= true;
begin
l_icon:= cdwp.add_images
( p_text => '{configuration.gif}'
);
for r_cfg in c_cfg( b_irid => p_cfg_irid) loop
if l_first
then
l_first:= false;
inflate_object_granted_to
( p_node_level => p_node_level
, p_irid => p_cfg_irid
, p_object_type => 'CFG'
);
if r_cfg.state <> 'N' -- UNVERSIONED
then
htmltree.add_node
( p_display_label => cdwp.add_images
( p_text => '{versiontree.gif}'
)
||cdwpbase.nbsp(1)
||'Versions'
, p_node_level => 3
, p_has_children => true
, p_is_expanded => false
, p_value => ''
, p_additional_label => ''
, p_is_inflatable => false
, p_classification => 'nolink'
);
end if; -- r_cfg.state <> 'N' -- UNVERSIONED
end if; -- l_first
if r_cfg.state <> 'N' -- UNVERSIONED
then
htmltree.add_node
( p_display_label => cdwpbase.ifThenElse
( r_cfg.state ='O' -- checked out
, cdwp.add_images('{check_mark.gif}')
)
||l_icon
||cdwpbase.nbsp(1)
||r_cfg.name
||' ('||r_cfg.vlabel||')'
, p_node_level => 4
, p_has_children => false
, p_is_expanded => false
, p_value => to_char(r_cfg.ivid)
, p_additional_label => ''
, p_is_inflatable => false -- for the moment we do not allow drill down to folders in configurations
, p_classification => 'nolink'
);
end if; -- r_cfg.state <> 'N' -- UNVERSIONED
end loop; -- r_cfg
end; -- inflate_config
procedure inflate_configs
is
cursor c_cfg
is
select cfg.IRID
, cfg.IVID
, cfg.NAME
, cfg.DESCRIPTION
, ov.vlabel
, nvl(ov.state, 'N') state
from sdd_configurations cfg
, sdd_object_versions ov
where ov.ivid = cfg.ivid
order
by cfg.name
, ov.branch_id -- to at least group by branch; probably need name of branch as well
, ov.sequence_in_branch
;
l_icon varchar2(200);
l_link varchar2(2000);
l_prev_cfg_irid number(38):=0;
begin
-- CONFIGURATIONS
-- =================
jr_context.set_workarea( to_number(null));
l_icon:= cdwp.add_images
( p_text => '{configuration.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.DSP176_ODWA_CONFIG,'','','','')||'"'
);
for r_cfg in c_cfg loop
if r_cfg.irid <> l_prev_cfg_irid
then
l_prev_cfg_irid:= r_cfg.irid;
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_cfg.name
, p_node_level => 2
, p_has_children => r_cfg.state <> 'N'
, p_is_expanded => false
, p_value => to_char(r_cfg.irid)
, p_additional_label => ''
, p_is_inflatable => true
, p_classification => '{TYPE=CFG}'
||'{CFG_IRID='||to_char(r_cfg.irid)||'}'
);
end if;
end loop; -- r_cfg
end; -- inflate_configs
procedure inflate_all_folders
is
l_icon varchar2(200);
l_link varchar2(2000);
l_prev_app_irid number(38):=0;
begin
-- now outside workarea context, any folder potentially has
-- multiple versions. We will return one node at level 2 for each root-folder
-- and one or more nodes at level 3 for each version of each root folder
-- when a folder is not versioned, there will be but one level: level 2
for r_app_vrsn in odwa_fol.c_root_app loop
l_icon:= cdwp.add_images
( p_text => '{'
||cdwpbase.ifThenElse
( r_app_vrsn.app_type = 'APP'
, 'app_sys'
, 'folder'
)
||'.gif}'
);
l_link:= cdwpbase.nbsp(2)
||cdwp.report_link
( p_app_id => r_app_vrsn.app_id
, p_app_ivid => r_app_vrsn.app_ivid
, p_type_of => 'APP_DETAILS'
, p_text => '..'
, p_bookmark => ''
, p_target => '_NEW'
);
if r_app_vrsn.app_id <> l_prev_app_irid
then
l_prev_app_irid:= r_app_vrsn.app_id;
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_app_vrsn.app_name
, p_node_level => 2
, p_has_children => true
, p_is_expanded => false
, p_value => cdwpbase.ifThenElse -- if application is unversioned, it may act as a link itself
( r_app_vrsn.state = 'N'
, to_char(r_app_vrsn.app_ivid)
, to_char(r_app_vrsn.app_id)
)
, p_additional_label => cdwpbase.ifThenElse -- if application is unversioned, it may act as a link itself
( r_app_vrsn.state = 'N'
, l_link
)
, p_is_inflatable => true
, p_classification => '{TYPE=ROOTFOL}'
||'{FOL_IRID='||to_char( r_app_vrsn.app_id)||'}'
||'{IRID='||to_char( r_app_vrsn.app_id)||'}'
);
end if;
end loop; -- r_app_vrsn
end; -- inflate_all_folders
procedure load_users
is
cursor c_usr
is
select rle.username username
, rle.full_user_name full_username
, rle.description description
, rle.created_by creator
from sdw_users rle
where rle.user_type = 1
order
by 1
;
l_icon varchar2(200);
begin
l_icon:= cdwp.add_images
( p_text => '{user.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP135_ODWAPRIV_ALLUSERS,'','','','')||'"'
);
for r_usr in c_usr loop
l_icon:= cdwp.add_images
( p_text => '{user.gif}'
, p_attributes => 'ALT="'||r_usr.full_username||'"'
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_usr.username
, p_node_level => 2
, p_has_children => true
, p_is_expanded => false
, p_value => r_usr.username
, p_additional_label => ''
, p_is_inflatable => true
, p_classification => '{TYPE=USER}'
);
end loop; -- r_usr
end; -- load_users
procedure load_roles
is
cursor c_rle
is
select rle.username rolename
, rle.full_user_name full_rolename
, rle.description description
, rle.created_by creator
from sdw_users rle
where rle.user_type = 0
order
by 1
;
l_icon varchar2(200);
begin
for r_rle in c_rle loop
l_icon:= cdwp.add_images
( p_text => '{role.gif}'
, p_attributes => 'ALT="'||r_rle.full_rolename||'"'
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||r_rle.rolename
, p_node_level => 2
, p_has_children => true
, p_is_expanded => false
, p_value => r_rle.rolename
, p_additional_label => '' --l_link
, p_is_inflatable => true
, p_classification => '{TYPE=ROLE}'
);
end loop; -- r_rle
end; -- load_roles
procedure inflate_Node
( p_node_index in number
, p_node_level in number -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
-- node levels in client are number 0..(highest level-1). in server 1..highest level
, p_num_of_nodes in number
, p_node_value in varchar2 default null
, p_node_type in varchar2 default null
, p_entry_point in number default null
, p_session_id in number
, p_root_value in varchar2
, p_root_classification in varchar2 default null
) 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_grantee varchar2(200):= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'GRANTEE');
l_wa_irid varchar2(40):= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'WA_IRID');
l_fol_irid varchar2(40):= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'FOL_IRID');
l_cfg_irid varchar2(40):= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'CFG_IRID');
begin
odwactxt.update_context
( p_session_id => p_session_id
, p_package_name => PACKAGE_NAME
, p_procedure_name=> 'inflate_node'
);
htmltree.reset_tbl;
-- if node type is Role or User, then let us investigate what the granted roles are
-- debuggin
/*
htmltree.add_node
( p_display_label => 'Inflate type='
||p_node_type
||' value='
||p_node_value
||';root='||l_root_type
||';type='||l_type
, p_node_level => p_node_level + 1
, p_has_children => false
, p_is_expanded => false
, p_value => ''
, p_additional_label => ''
, p_is_inflatable => false
, p_classification => 'no_link'
);
*/
if l_type in ('ROLE','USER', 'GRANTED_ROLE')
then
inflate_granted_roles
( p_node_level => p_node_level
, p_grantee => p_node_value
);
inflate_granted_workareas
( p_node_level => p_node_level
, p_grantee => p_node_value
);
inflate_granted_root_fol
( p_node_level => p_node_level
, p_grantee => p_node_value
);
inflate_granted_configurations
( p_node_level => p_node_level
, p_grantee => p_node_value
);
end if; -- l_type in ('ROLE','USER')
if l_type ='ROOT'
then
if p_node_value ='USER'
then
load_users;
end if;
if p_node_value ='ROLE'
then
load_roles;
end if;
-- the root node Configurations
if p_node_value ='CFG'
then
inflate_configs;
end if;
-- the root node Workareas
if p_node_value ='WA'
then
load_wa;
end if;
-- the root node for All Containers
if p_node_value ='FOL'
then
inflate_all_folders;
end if;
end if; -- l_type='ROOT'
-- a node for a specific Configuration
if l_type = 'CFG'
then
inflate_config
( p_cfg_irid => to_number( l_cfg_irid)
, p_node_level => p_node_level
);
/*
inflate_object_granted_to
( p_node_level => p_node_level
, p_irid => to_number(cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'CFG_IRID'))
, p_object_type => 'CFG'
);
*/
end if; -- l_type = 'CFG'
if l_type ='WA'
then
if l_root_type ='WA'
then
inflate_object_granted_to
( p_node_level => p_node_level
, p_irid => to_number( l_wa_irid)
, p_object_type => 'WA'
);
inflate_root_in_wa( p_wa_irid => to_number( l_wa_irid));
elsif nvl( l_wa_irid, l_grantee) is not null
then
inflate_granted_root_fol_in_wa
( p_node_level => p_node_level
, p_grantee => l_grantee
, p_wa_irid => l_wa_irid
);
end if; -- l_root_type ='WA'
end if; -- l_type ='WA'
if l_type in ( 'FOL','APP','ROOTFOL','ROOT')
then
if l_root_type in ('ROLE','USER')
then
if nvl(instr( p_node_type, 'WA_IRID'),0) > 0
then
inflate_grt_nested_fol_in_wa
( p_node_level => p_node_level
, p_grantee => l_grantee
, p_parent_folder_irid => to_number(cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'IRID'))
, p_wa_irid => to_number( l_wa_irid)
);
else
inflate_granted_nested_fol
( p_node_level => p_node_level
, p_grantee => l_grantee
, p_parent_folder_irid => to_number(cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'IRID'))
);
end if; -- nvl(instr( p_node_type, 'WA_IRID'),0) > 0
elsif l_root_type = 'WA'
then
nested_folders_in_wa
( p_folder_irid => to_number( l_fol_irid)
, p_wa_irid => to_number( l_wa_irid)
, p_level => p_node_level
);
infl_fol_in_wa_granted_to
( p_node_level => p_node_level
, p_irid => to_number(l_fol_irid)
, p_wa_irid => to_number( l_wa_irid)
, p_object_type => 'FOL'
);
elsif l_root_type in ( 'FOL','APP','ROOTFOL','ROOT')
then
-- inflate nested folders
nested_folders_outside_wa
( p_folder_irid => to_number(l_fol_irid)
, p_level => p_node_level
);
-- inflated grants made of folder to grantee
inflate_object_granted_to
( p_node_level => p_node_level
, p_irid => to_number(l_fol_irid)
, p_object_type => 'FOL'
);
end if; -- l_root_type in ('ROLE','USER')
end if; -- l_type ='FOL'
/*
if l_type in ('ROOTFOL','ROOTAPP')
then
inflate_object_granted_to
( p_node_level => p_node_level
, p_irid => to_number(l_fol_irid)
, p_object_type => 'FOL'
);
inflate_folder
( p_fol_irid => to_number (l_fol_irid)
, p_node_level => p_node_level
);
end if; -- l_type ='ROOTFOL'
*/
-- if node type is Role and Root Type is Role
-- and the current node is not a role granted to a role (because then we do not care who else has been granted that role)
-- then let us investigate to whom (users and roles) the role has been granted
if (
l_type = 'ROLE'
OR
l_type = 'GRANTEE_OF_ROLE'
)
and cdwpbase.get_tag_value( p_string=> p_root_classification, p_tag => 'TYPE') = 'ROLE'
then
inflate_granted_to
( p_node_level => p_node_level
, p_role_name => cdwpbase.ifThenElse
( l_type ='ROLE'
, p_node_value
, cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'GRANTEE')
)
);
end if; -- p_node_type in ('ROLE','USER')
htmltree.find_folder_nodes; -- to set the had_children property where appropriate
htmltree.write_inflate_code
( p_node_index => p_node_index
, p_num_of_nodes => p_num_of_nodes
, p_frame_locator => 'top.'
, p_entry_point => p_entry_point
);
end; -- inflate_node
procedure dataXchange
( p_session_id in number
)
is
begin
odwactxt.update_context
( p_session_id => p_session_id
, p_package_name => PACKAGE_NAME
, p_procedure_name=> 'dataXchange'
);
htp.htmlOpen;
htp.headOpen;
cdwp.write_about(package_name, revision_label);
htp.headClose;
htp.bodyOpen;
htp.bodyClose;
htp.htmlClose;
end; -- dataXchange
-- this procedures loads the Workareas, Root Folders and Configurations in the navigator tree
procedure load_folder_tree
is
l_icon varchar2(200);
l_link varchar2(2000);
l_prev_app_irid number(38):=0;
l_prev_cfg_irid number(38):=0;
begin
l_icon:= cdwp.add_images
( p_text => '{workarea.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.DSP179_ODWA_ALLWAS,'','','','')||'"'
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||'Workareas'
, p_node_level => 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'WA'
, p_additional_label => '' --l_link
, p_is_inflatable => true
, p_classification => '{TYPE=ROOT}'
);
l_icon:= cdwp.add_images
( p_text => '{configuration.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.DSP176_ODWA_CONFIG,'','','','')||'"'
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||Rob_msg.GetMsg(Rob_msg.DSP176_ODWA_CONFIG,'','','','')
, p_node_level => 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'CFG'
, p_additional_label => '' --l_link
, p_is_inflatable => true
, p_classification => '{TYPE=ROOT}'
);
l_icon:= cdwp.add_images
( p_text => '{AllFolders.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.DSP304_ODWAPRIV_UVVIEW,'','','','')||'"'
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||Rob_msg.GetMsg(Rob_msg.DSP183_ODWA_ALLFOLDERS,'','','','')
, p_node_level => 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'FOL'
, p_additional_label => '' --l_link
, p_is_inflatable => true
, p_classification => '{TYPE=ROOT}'
);
end; -- load_folder_tree
-- this procedure builds up the Users and Roles Tree for the current workarea
procedure load_tree
is
cursor c_usr
is
select rle.username username
, rle.full_user_name full_username
, rle.description description
, rle.created_by creator
from sdw_users rle
where rle.user_type = 1
order
by 1
;
cursor c_rle
is
select rle.username rolename
, rle.full_user_name full_rolename
, rle.description description
, rle.created_by creator
from sdw_users rle
where rle.user_type = 0
order
by 1
;
l_icon varchar2(200);
l_link varchar2(2000);
l_prev_app_irid number(38):=0;
l_prev_cfg_irid number(38):=0;
begin
htmltree.reset_tbl;
l_icon:= cdwp.add_images
( p_text => '{user.gif}'
, p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP135_ODWAPRIV_ALLUSERS,'','','','')||'"'
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||'Users'
, p_node_level => 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'USER'
, p_additional_label => '' --l_link
, p_is_inflatable => true
, p_classification => '{TYPE=ROOT}'
);
l_icon:= cdwp.add_images
( p_text => '{role.gif}'
, p_attributes => 'ALT="All Repository Roles"'
);
htmltree.add_node
( p_display_label => l_icon
||cdwpbase.nbsp(1)
||'Roles'
, p_node_level => 1
, p_has_children => true
, p_is_expanded => false
, p_value => 'ROLE'
, p_additional_label => '' --l_link
, p_is_inflatable => true
, p_classification => '{TYPE=ROOT}'
);
load_folder_tree;
htmltree.find_folder_nodes; -- to set the has_children property where appropriate
end; -- load_tree
procedure rightSide
( p_session_id in number
, p_initial_context in varchar2 default null
) is
begin
-- create session when necessary, refresh session cache, set workarea context
-- from here on reference to odwactxt.get_... functions for any context value
odwactxt.update_context
( p_session_id => p_session_id
, p_package_name => PACKAGE_NAME
, p_procedure_name=> 'rightSide'
);
htp.htmlOpen;
htp.headOpen;
cdwp.write_about(package_name, revision_label);
htp.headClose;
htp.framesetopen
( crows => '99%,1%'
, cattributes => 'BORDER="0" LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=405 TITLE="'
||Rob_msg.Getmsg(Rob_msg.DSP200_ODWA_SECURITY,'','','','')||'"'
);
htp.frame
( 'odwapriv.paletteFrame?p_session_id='
||to_char(odwactxt.get_session_id)
||chr(38)||'p_initial_context='||p_initial_context
, cname=> 'paletteFrame'
, cattributes =>'LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=405 TITLE="'
||Rob_msg.Getmsg(Rob_msg.DSP200_ODWA_SECURITY,'','','','')||'"'
);
htp.frame
( 'odwapriv.dataXchange?p_session_id='
||to_char(odwactxt.get_session_id)
, cname=> 'dataXchange'
, cscrolling => 'no'
, cattributes =>'LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=405 TITLE="'
||Rob_msg.Getmsg(Rob_msg.DSP200_ODWA_SECURITY,'','','','')||'"'
);
htp.framesetclose;
htp.htmlClose;
end; -- rightSide
procedure topSide
( p_session_id in number
)
is
begin
-- create session when necessary, refresh session cache, set workarea context
-- from here on reference to odwactxt.get_... functions for any context value
odwactxt.update_context
( p_session_id => p_session_id
, p_package_name => PACKAGE_NAME
, p_procedure_name=> 'topSide'
);
htp.htmlOpen;
htp.headOpen;
cdwp.write_about(package_name, revision_label);
htp.headClose;
htp.bodyopen(cattributes => 'BGCOLOR = #FFFFFF');
cdwp.tool_header('security');
htp.bodyClose;
htp.htmlClose;
end; -- topSide
-- main procedure for the select WA and Folder screen
-- sets up a frame that ultimately will look as follows:
-- -------------------------------
-- | |
-- | |
-- | | |
-- | navigatorTree| paletteFrame |
-- | |------------- |
-- | | dataXchange |
-- -------------------------------
-- { rightSide }
procedure list_users_and_roles
( p_session_id in number
, p_initial_context in varchar2 default null
) is
l_js_action varchar2(2000);
l_node_href varchar2(2000);
begin
-- create session when necessary, refresh session cache, set workarea context
-- from here on reference to odwactxt.get_... functions for any context value
odwactxt.update_context
( p_session_id => p_session_id
, p_package_name => PACKAGE_NAME
, p_procedure_name=> 'list_application_systems'
, p_folder_irid => null
, p_folder_ivid => null
, p_cfg_ivid => null
, p_workarea_irid => null
, p_pac_irid => null
, p_pac_ivid => null
);
htp.htmlOpen;
htp.headOpen;
l_js_action:=
'function pickNode( idx)
{
selectedIdx = idx;
redrawTree();
top.rightSide.paletteFrame.location = "odwapred.node_selected?p_session_id='||to_char(odwactxt.get_session_id)
||chr(38)||'p_node_value="+nodesTree[idx].value+"'
||chr(38)||'p_node_type="+nodesTree[idx].classification+"'
||chr(38)||'p_root_value="+nodesTree[getParentNodeAbsolute( idx, 1)].value+"'
||chr(38)||'p_root_classification="+nodesTree[getParentNodeAbsolute( idx, 1)].classification
;
} // pickNode
';
-- now we could use the function getParentNodeRelative( idx, levels) or getParentNodeAbsolute( idx, level)
-- to retrieve values/classifications of parent nodes
-- e.g. to get the root Folder irid for any selected node, refer to:
-- nodesTree[ getParentNodeAbsolute( idx, 0)].value
l_node_href:=
'pickNode(\"" +idx + "\" )'; -- (\"" +elId + "\","+idx+" )'; --\""+name+\"")';
htp.htmlOpen;
htp.headOpen;
cdwp.write_about(package_name, revision_label);
cdwp.include_report_styles;
-- build the tree and its data in this Frame
load_tree;
htmltree.js_tree
( p_codeFrameName => 'top'
, p_treeFrameName => 'navigatorTree'
, p_treeFramePath => 'top.navigatorTree'
, p_xchangeFramePath => 'top.rightSide.dataXchange'
, p_js_action => l_js_action
, p_node_href => l_node_href
, p_inflate_request => 'odwapriv.inflate_Node'
, p_inflate_parameters => '"'
||chr(38)
||'p_session_id='||to_char(odwactxt.get_session_id)
||chr(38)
||'p_root_value="+nodesTree[getParentNodeAbsolute( idx, 1)].value'
||'+"'
||chr(38)
||'p_root_classification="+nodesTree[getParentNodeAbsolute( idx, 1)].classification'
);
cdwp.write_about(package_name, revision_label);
htp.title('Users, Roles and Privilegess');
-- allow frames in this document to include calls to the On Line Help System by including the
-- JavaScript function that can invoke the Help Window
odwahelp.js_invoke_help;
htp.headClose;
htp.framesetopen
( crows => '130,*'
, cattributes => 'BORDER=0 FRAMEBORDER=0 LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=405 TITLE="'
||Rob_msg.Getmsg(Rob_msg.DSP200_ODWA_SECURITY,'','','','')||'"'
);
htp.frame
( csrc => 'odwapriv.topSide?p_session_id='
||to_char(odwactxt.get_session_id)
, cname => 'topSide'
, cattributes => 'SCROLLING=NO LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=405 TITLE="'
||Rob_msg.Getmsg(Rob_msg.DSP200_ODWA_SECURITY,'','','','')||'"'
);
htp.framesetopen
( ccols => '40%,60%'
, cattributes => 'BORDER="2" LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=405 TITLE="'
||Rob_msg.Getmsg(Rob_msg.DSP200_ODWA_SECURITY,'','','','')||'"'
);
htp.frame
( csrc => 'odwapriv.navigatorTree?p_session_id='
||to_char(odwactxt.get_session_id)
, cname=> 'navigatorTree'
, cattributes => 'LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=405 TITLE="'
||Rob_msg.Getmsg(Rob_msg.DSP200_ODWA_SECURITY,'','','','')||'"'
);
htp.frame
( 'odwapriv.rightSide?p_session_id='
||to_char(odwactxt.get_session_id)
||chr(38)||'p_initial_context='||p_initial_context
, cname=> 'rightSide'
, cattributes => 'LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=405 TITLE="'
||Rob_msg.Getmsg(Rob_msg.DSP200_ODWA_SECURITY,'','','','')||'"'
);
htp.framesetclose;
htp.framesetclose;
htp.htmlClose;
end; -- list_users_and_roles
end; -- odwapriv
/