Responsibility/Function check for a user (SoD)

This script comes from Oracle Support (document 549100.1). It prints all the submenu's and functions for a given responsibility.


declare
-- this method prints the whole menu tree for a given responsibility
-- if you want to run for a different responsibility change the
-- l_resp name

l_resp_name varchar2(100) := '&responsibility_name';
l_menu_id number;
l_main_menu varchar2(100);

procedure dfs(p_menu_id in varchar2, p_level in number) is

cursor get_submenus is
select e.entry_sequence, m.menu_name, m.user_menu_name, e.sub_menu_id, e.function_id, f.function_name, e.prompt, f.web_html_call, f.user_function_name, e.grant_flag
from fnd_menus_vl m, fnd_menu_entries_vl e, fnd_form_functions_vl f
where e.sub_menu_id = m.menu_id(+)
and e.function_id = f.function_id(+)
and e.menu_id = p_menu_id
order by 1;

l_spaces varchar2(30) := '';
granted varchar2(30);

begin
-- spacing
for i in 1..p_level loop
l_spaces := l_spaces || '..';
end loop;

--loop through menus
for c in get_submenus loop
granted := '';
if c.grant_flag = 'Y' then granted := ' [granted]'; end if;
if c.sub_menu_id is null then
dbms_output.put_line(l_spaces || 'FUNCTION ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.function_name || ' (' || c.user_function_name || ')');
-- dbms_output.put_line(l_spaces || '...........src=' || c.web_html_call);
else
dbms_output.put_line(l_spaces || 'MENU (' || p_level || ') ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.menu_name || ' (' || c.user_menu_name || ')');
end if;
dfs(c.sub_menu_id, p_level+1);
end loop;
end;

begin
select menu_id into l_menu_id
from fnd_responsibility_vl where responsibility_name = l_resp_name;
select menu_name into l_main_menu from fnd_menus where menu_id = l_menu_id;
dbms_output.put_line('MAIN MENU ' || l_main_menu);
dfs(l_menu_id, 1);
end;
/ 

Now if we want to combine this logic so we check for a user which functions he has been granted through all responsibilities he has we could use something like this.

set serveroutput on size 1000000
declare
-- this method prints the whole menu tree for a given responsibility
-- if you want to run for a different responsibility change the
-- l_resp name

l_resp_name varchar2(100) := 'Inventory';
l_menu_id number;
l_main_menu varchar2(100);
l_user_name varchar2(240) := 'NEWUSER';

cursor c_user_resps (cp_user_name varchar2) is
select   r.responsibility_id, rb.menu_id, m.menu_name, rb.responsibility_name
from     fnd_user u
,        FND_USER_RESP_GROUPS_DIRECT r
,        fnd_responsibility_vl rb
,        fnd_menus m
where    u.user_id = r.user_id
and      u.user_name = cp_user_name
and      sysdate between r.start_date and nvl (r.end_date,sysdate+1)
and      r.responsibility_id = rb.responsibility_id
and      rb.menu_id = m.menu_id
;

procedure dfs(p_menu_id in varchar2, p_level in number) is

cursor get_submenus is
select e.entry_sequence, m.menu_name, m.user_menu_name, e.sub_menu_id, e.function_id, f.function_name, e.prompt, f.web_html_call, f.user_function_name, e.grant_flag
from fnd_menus_vl m, fnd_menu_entries_vl e, fnd_form_functions_vl f
where e.sub_menu_id = m.menu_id(+)
and e.function_id = f.function_id(+)
and e.menu_id = p_menu_id
order by 1;



l_spaces varchar2(30) := '';
granted varchar2(30);

begin
-- spacing
for i in 1..p_level loop
l_spaces := l_spaces || '..';
end loop;

--loop through menus
for c in get_submenus loop
granted := '';
if c.grant_flag = 'Y' then granted := ' [granted]'; end if;
if c.sub_menu_id is null then
dbms_output.put_line(l_spaces || 'FUNCTION ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.function_name || ' (' || c.user_function_name || ')');
-- dbms_output.put_line(l_spaces || '...........src=' || c.web_html_call);
else
dbms_output.put_line(l_spaces || 'MENU (' || p_level || ') ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.menu_name || ' (' || c.user_menu_name || ')');
end if;
dfs(c.sub_menu_id, p_level+1);
end loop;
end;

begin

  dbms_output.put_line ('CHECK USER: ' || l_user_name);
  for r in c_user_resps (cp_user_name => l_user_name)
  loop
   
--select menu_id into l_menu_id
--from fnd_responsibility_vl where responsibility_name = l_resp_name;
--select menu_name into l_main_menu from fnd_menus where menu_id = l_menu_id;
    dbms_output.put_line('MAIN MENU OF ' || r.responsibility_name || ' is ' || r.menu_name);
    dfs(r.menu_id, 1);
    dbms_output.put_line ('');

  end loop;
end;
/  

So what if we want to check if this user has two functions assigned to them which are conflicting for segregation of duties (SoD)? So assume we know which two functions should not be assigned to one user and we put that in a list of conflicting functions. Now we can check for a given user if this user has these conflicting functions somewhere in his menu's.

Just for a simple example

create table xxx_sod_functions
(function1 varchar2(240), function2 varchar2(240));

create table xxx_user_functions (user_name varchar(100), function_name varchar2(240),menu_id number, resp_id number);


insert into xxx_sod_functions values ('INV_CONSIGNED_CALL_ISP','WMS_WMSPRIPS');

Now if we change our code to this

set serveroutput on size 1000000
declare
-- this method prints the whole menu tree for a given responsibility
-- if you want to run for a different responsibility change the
-- l_resp name

l_resp_name varchar2(100) := 'Inventory';
l_menu_id number;
l_main_menu varchar2(100);
l_user_name varchar2(240) := 'NEWUSER';

cursor c_user_resps (cp_user_name varchar2) is
select   r.responsibility_id, rb.menu_id, m.menu_name, rb.responsibility_name, u.user_name
from     fnd_user u
,        FND_USER_RESP_GROUPS_DIRECT r
,        fnd_responsibility_vl rb
,        fnd_menus m
where    u.user_id = r.user_id
and      u.user_name = cp_user_name
and      sysdate between r.start_date and nvl (r.end_date,sysdate+1)
and      r.responsibility_id = rb.responsibility_id
and      rb.menu_id = m.menu_id
;

procedure dfs(p_menu_id in varchar2, p_level in number,  p_user_name in varchar2,p_resp_id in number) is

cursor get_submenus is
select e.entry_sequence, m.menu_name, m.user_menu_name, e.sub_menu_id, e.function_id, f.function_name, e.prompt, f.web_html_call, f.user_function_name, e.grant_flag
from fnd_menus_vl m, fnd_menu_entries_vl e, fnd_form_functions_vl f
where e.sub_menu_id = m.menu_id(+)
and e.function_id = f.function_id(+)
and e.menu_id = p_menu_id
order by 1;



l_spaces varchar2(30) := '';
granted varchar2(30);

begin
-- spacing
for i in 1..p_level loop
l_spaces := l_spaces || '..';
end loop;

--loop through menus
for c in get_submenus loop
granted := '';
if c.grant_flag = 'Y' then granted := ' [granted]'; end if;
if c.sub_menu_id is null then
dbms_output.put_line(l_spaces || 'FUNCTION ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.function_name || ' (' || c.user_function_name || ')');
if c.prompt is not null then insert into xxx_user_functions values (p_user_name, c.function_name,p_menu_id,p_resp_id); end if;
-- dbms_output.put_line(l_spaces || '...........src=' || c.web_html_call);
else
dbms_output.put_line(l_spaces || 'MENU (' || p_level || ') ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.menu_name || ' (' || c.user_menu_name || ')');
end if;
dfs(c.sub_menu_id, p_level+1,p_user_name,p_resp_id);
end loop;
end;

begin

  dbms_output.put_line ('CHECK USER: ' || l_user_name);
  for r in c_user_resps (cp_user_name => l_user_name)
  loop
  
--select menu_id into l_menu_id
--from fnd_responsibility_vl where responsibility_name = l_resp_name;
--select menu_name into l_main_menu from fnd_menus where menu_id = l_menu_id;
    dbms_output.put_line('MAIN MENU OF ' || r.responsibility_name || ' is ' || r.menu_name);
    dfs(r.menu_id, 1, r.user_name,r.responsibility_id);
    dbms_output.put_line ('');

  end loop;
 
  commit;
end;
/ 

We can simply find our users that have conflicting functions somewhere in their menu's.It shows me which functions are conflicting according to my SoD list and in which responsibilities and menu's I can find these.

select  f.user_name || ' conflicts between ' || f.function_name || ' in menu ' || m1.menu_name || ' of responsibility ' || r1.responsibility_name || ' with ' ||
f2.function_name || ' in menu ' || m2.menu_name || ' of responsibility ' || r2.responsibility_name conflict
from xxx_user_functions f, xxx_user_functions f2, xxx_sod_functions f3, fnd_menus m1, fnd_menus m2
, fnd_responsibility_vl r1, fnd_responsibility_vl r2
where f.user_name = f2.user_name
and f.function_name = f3.function1
and f2.function_name = f3.function2
and f.menu_id = m1.menu_id
and f2.menu_id = m2.menu_id
and f.resp_id = r1.responsibility_id
and f2.resp_id = r2.responsibility_id
;

Now we are almost there .. now we have this for one user, but we can also do this for all active users ... we take out all the dbms_outputs since we don't actually need to print the menu's

declare
-- this method prints the whole menu tree for a given responsibility
-- if you want to run for a different responsibility change the
-- l_resp name

l_resp_name varchar2(100) := 'Inventory';
l_menu_id number;
l_main_menu varchar2(100);
l_user_name varchar2(240) := 'NEWUSER';

cursor c_users
is
select user_name
from   fnd_user
where  sysdate between start_date and nvl (end_date,sysdate+1);

cursor c_user_resps (cp_user_name varchar2) is
select   r.responsibility_id, rb.menu_id, m.menu_name, rb.responsibility_name, u.user_name
from     fnd_user u
,        FND_USER_RESP_GROUPS_DIRECT r
,        fnd_responsibility_vl rb
,        fnd_menus m
where    u.user_id = r.user_id
and      u.user_name = cp_user_name
and      sysdate between r.start_date and nvl (r.end_date,sysdate+1)
and      r.responsibility_id = rb.responsibility_id
and      rb.menu_id = m.menu_id
;

procedure dfs(p_menu_id in varchar2, p_level in number,  p_user_name in varchar2,p_resp_id in number) is

cursor get_submenus is
select e.entry_sequence, m.menu_name, m.user_menu_name, e.sub_menu_id, e.function_id, f.function_name, e.prompt, f.web_html_call, f.user_function_name, e.grant_flag
from fnd_menus_vl m, fnd_menu_entries_vl e, fnd_form_functions_vl f
where e.sub_menu_id = m.menu_id(+)
and e.function_id = f.function_id(+)
and e.menu_id = p_menu_id
order by 1;



l_spaces varchar2(30) := '';
granted varchar2(30);

begin
-- spacing
for i in 1..p_level loop
l_spaces := l_spaces || '..';
end loop;

--loop through menus
for c in get_submenus loop
granted := '';
if c.grant_flag = 'Y' then granted := ' [granted]'; end if;
if c.sub_menu_id is null then
--dbms_output.put_line(l_spaces || 'FUNCTION ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.function_name || ' (' || c.user_function_name || ')');
if c.prompt is not null then insert into xxx_user_functions values (p_user_name, c.function_name,p_menu_id,p_resp_id); end if;
-- dbms_output.put_line(l_spaces || '...........src=' || c.web_html_call);
else
  null;
--dbms_output.put_line(l_spaces || 'MENU (' || p_level || ') ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.menu_name || ' (' || c.user_menu_name || ')');
end if;
dfs(c.sub_menu_id, p_level+1,p_user_name,p_resp_id);
end loop;
end;

begin

  delete from xxx_user_functions; commit;
  for u in c_users
  loop
    l_user_name := u.user_name;
   
  --dbms_output.put_line ('CHECK USER: ' || l_user_name);
  for r in c_user_resps (cp_user_name => l_user_name)
  loop
   
--select menu_id into l_menu_id
--from fnd_responsibility_vl where responsibility_name = l_resp_name;
--select menu_name into l_main_menu from fnd_menus where menu_id = l_menu_id;
    --dbms_output.put_line('MAIN MENU OF ' || r.responsibility_name || ' is ' || r.menu_name);
    dfs(r.menu_id, 1, r.user_name,r.responsibility_id);
    --dbms_output.put_line ('');

  end loop;
  end loop;
  commit;
end;
/  

And finally we get our list of users that have conflicting functions according to our definition.

select f.user_name, f.function_name, m1.menu_name, r1.responsibility_name, f2.function_name, m2.menu_name, r2.responsibility_name
from xxx_user_functions f, xxx_user_functions f2, xxx_sod_functions f3, fnd_menus m1, fnd_menus m2
, fnd_responsibility_vl r1, fnd_responsibility_vl r2
where f.user_name = f2.user_name
and f.function_name = f3.function1
and f2.function_name = f3.function2
and f.menu_id = m1.menu_id
and f2.menu_id = m2.menu_id
and f.resp_id = r1.responsibility_id
and f2.resp_id = r2.responsibility_id
; 

Now of course you can tune this further to check forms rather than functions (in case some one creates a custom function on an existing form, etc).