Query to find last login date for a user/responsibility

The following query may help to find users that haven't logged in for a long time or which responsibilities are actually used in the system.  You may filter as well on the count or date to print only users who haven't logged in for a year or not more than 10 times, etc.
The first query shows you per user when he logged in last, how long ago that was, how many times he logged in and whether he's an employee who's not terminated yet ..

I use two views. The second also fetches the last responsibility used. Note that per login you can have multiple records in fnd_login_responsibilities, but you can also have none ..

create or replace view xxx_user_counts_v
as
SELECT      U.User_Id
, U.User_Name
,       PX.Full_Name
,       (SELECT Count (D.Responsibility_Id)
         FROM   FND_USER_RESP_GROUPS_DIRECT D
         WHERE  D.User_Id = U.User_id) Nr_of_Responsibilities
,       (SELECT   PS.Actual_Termination_Date
         FROM   PER_PERIODS_OF_SERVICE PS
         WHERE  PS.Person_Id = U.Employee_Id
         AND      PS.Period_Of_Service_Id =
          (
            SELECT Max (PS2.Period_Of_Service_Id)
            FROM PER_PERIODS_OF_SERVICE PS2
            WHERE PS2.Person_Id = U.Employee_Id
          )
        ) Actual_Termination_Date
,           To_Char (Max (L.Start_Time),'DD-MM-YYYY HH24:MI:SS') Last_Login_Date
,           To_Char (Max (LR.Start_Time),'DD-MM-YYYY HH24:MI:SS') Last_Login_Date_Resp
,           Round ((Sysdate-Max (L.Start_Time))) Days_Since_Login
,           Count (L.Login_Id) Nr_Of_Logins
,           Max (L.Login_Id) Last_Login_Id
FROM      FND_USER          U
,           FND_LOGINS                  L
,           FND_LOGIN_RESPONSIBILITIES  LR
--,           FND_RESPONSIBILITY_VL       R
,       PER_PEOPLE_X        PX
WHERE       U.User_Id                          = L.User_Id (+)
AND         L.Login_Id                   = LR.Login_Id  (+)
--AND         LR.Responsibility_Id       = R.Responsibility_Id (+)
AND         (U.End_Date IS NULL OR U.End_Date > Sysdate)
AND     U.EMPLOYEE_ID         = PX.Person_Id (+)
GROUP BY  U.User_Name
,       U.User_Id
,     PX.Full_Name
,       U.Employee_Id
;



create or replace view xxx_user_counts_v2
as
select c.*
, (select rl.responsibility_name
   from FND_RESPONSIBILITY_VL rl
   , fnd_login_responsibilities r
   where r.login_id = c.last_login_id
   and r.responsibility_id = rl.responsibility_id
   and r.login_resp_id =
   (
     select max (r2.login_resp_id)
     from fnd_login_responsibilities r2
     where r2.login_id = r.login_id
   )
   ) last_resp_used
from xxx_user_counts_v c
;

Now you can do queries like 

Number of users who never have logged in, but do have an authorization

select count(*) from xxx_user_counts_v2 where nr_of_logins = 0 and nr_of_responsibilities > 0;

Number of users who haven't logged in for half a year and who have authorizations

select count(*) from xxx_user_counts_v2 where Days_Since_Login > 180 and nr_of_responsibilities > 0; 

Users who's employee record have been terminated, but still have access

select count(*) from xxx_user_counts_v2 where actual_termination_date < sydate;

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).