Current users logged into Oracle E-Business Suite

From time to time, before I bring down the E-Business Suite Applications, I do check on the number of users who are currently in the system. I would then need to inform the users and then bring down the applications, or rather bring them down if no one is around.

The Script that I use to check on the number of users, if any and then bring down the applicaitons is shown below..


select distinct fu.user_name User_Name, fu.email_address emailid,
fu.description, TO_CHAR(IC.FIRST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"First Accessed",
TO_CHAR(IC.LAST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"Last Accessed", ic.nls_territory, FVL.RESPONSIBILITY_NAME "RESPONSIBILITY NAME", fr.RESPONSIBILITY_KEY "Responsibility Key", ic.function_type,ic.time_out, fu.user_id, fu.employee_id,
ic.responsibility_application_id, ic.responsibility_id, ic.org_id, ic.counter, fr.menu_id, ic.disabled_flag
from fnd_user fu,
fnd_responsibility fr, icx_sessions ic, fnd_responsibility_VL FVL
where fu.user_id = ic.user_id AND
FR.RESPONSIBILITY_KEY=FVL.RESPONSIBILITY_KEY AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect > sysdate - (ic.time_out/60)/96
order by "Last Accessed" desc;

===

Below is another way of checking the current users within Oracle E-Business Suite.

====


set lin 999
select distinct ic.disabled_flag,
  fu.user_name User_Name,
  fr.RESPONSIBILITY_KEY Responsibility,
  fu.user_id,
  fu.description,
  fu.employee_id,
  ic.responsibility_application_id,
  ic.responsibility_id,
  ic.function_type,
  ic.counter,
  ic.first_connect,
  ic.last_connect,
  ic.nls_territory,
  ic.time_out,
  fr.menu_id,
  fr.responsibility_key
from fnd_user fu,
  fnd_responsibility fr,
  icx_sessions ic
where fu.user_id          = ic.user_id
and fr.responsibility_id  = ic.responsibility_id
and ic.disabled_flag      ='N'
and IC.RESPONSIBILITY_ID is not null
and ic.last_connect  > sysdate - (ic.time_out/60)/96;



===============

Sometimes, I would need to check the number of users who were accessing the EBS in the last 24 hours. They may have logged out. But the below script is much handy in finding the users who logged in the past.


SELECT DISTINCT fu.user_name User_Name,fu.email_address emailid,
TO_CHAR(IC.FIRST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"First Accessed",
TO_CHAR(IC.LAST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"Last Accessed",FVL.RESPONSIBILITY_NAME "RESPONSIBILITY NAME", fr.RESPONSIBILITY_KEY "Responsibility Key", ic.function_type FROM fnd_user fu,
fnd_responsibility fr, icx_sessions ic, fnd_responsibility_VL FVL
WHERE fu.user_id = ic.user_id AND
FR.RESPONSIBILITY_KEY=FVL.RESPONSIBILITY_KEY AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id IS NOT NULL AND
ic.last_connect LIKE SYSDATE
order by "Last Accessed" desc;


Hope the above helps..

Regards,
Ajai Mundackal