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;
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;