Wednesday, July 25, 2007

How to Get User's Status in the ICX_SESSIONS Table

Connect to Application by using apps user and run the following SQL Query

SELECT (SELECT user_function_name
FROM fnd_form_functions_vl fffv
WHERE (fffv.function_id = a.function_id)) "Current Function",
TO_CHAR (first_connect, 'MM/DD/YYYY HH:MI:SS') start_time,
TO_CHAR (last_connect,
'MM/DD/YYYY HH:MI:SS'
) "Date and time of last hit",
TO_CHAR (SYSDATE, 'HH:MI:SS') current_time, user_name, session_id,
(SYSDATE - last_connect) * 24 * 60 mins_idle,
fnd_profile.value_specific ('ICX_SESSION_TIMEOUT',
a.user_id,
a.responsibility_id,
a.responsibility_application_id,
a.org_id,
NULL
) TIMEOUT,
counter "How many hits a User has made",
a.limit_connects "No of hits allowed in session"
FROM icx_sessions a, fnd_user b
WHERE a.user_id = b.user_id AND last_connect > SYSDATE - 1 / 24;