Tuesday 1 October 2019

Find User Last Login Date in Oracle Apps EBS


Find User Last Login Date in Oracle Apps EBS




SELECT distinct user_name, 
NVL(last_logon_date, creation_date) "FU.LLD", 
NVL(iss.icx_logon_date, last_logon_date) icx_logon_date, 
nvl(to_char(nvl(icx_logon_date,last_logon_date),'dd-mon-yyyy'),'Never') Last_Login_Date,
description,
email_address
FROM 
apps.fnd_user, 
(select MAX(last_connect) icx_logon_date,user_id usid from apps.icx_sessions group by user_id) iss
WHERE user_id = iss.usid (+)
and end_date IS NULL
AND NVL(last_logon_date, creation_date)  <= trunc(sysdate - 60)
and NVL(iss.icx_logon_date, NVL(last_logon_date, creation_date)) <= trunc(sysdate - 60)
and user_name not in ('GUEST', 'SYSADMIN', 'AUTOINSTALL', 'WIZARD','DISCOVERER', 'SYSCHK', 'APPSMGR')
order by 1;