Sysadmin queries.

find users who have SYSADMIN responsibility

SELECT fu.USER_NAME,fu.DESCRIPTION,furgd.start_date,furgd.end_date,frvl.responsibility_name

FROM fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frvl, fnd_user fu
WHERE furgd.responsibility_id = frvl.responsibility_id
AND fu.user_id = furgd.user_id
AND(to_char(furgd.end_date) is null
OR furgd.end_date > sysdate)
AND frvl.end_date is null
AND frvl.responsibility_name = 'System Administrator';

Another Script
==============
SELECT u.USER_NAME,u.START_DATE,u.END_DATE
       ,tr.RESPONSIBILITY_NAME
       ,r.START_DATE RESP_START_DATE,r.END_DATE RESP_END_DATE
    FROM fnd_user u,
         fnd_user_resp_groups_all ur,
         fnd_responsibility r,
         fnd_responsibility_tl tr,
         fnd_security_groups_vl s,
         fnd_application fa
  WHERE  1=1
  AND ur.responsibility_application_id = r.application_id
  AND ur.responsibility_id = r.responsibility_id
  AND tr.responsibility_id = r.responsibility_id
  AND u.user_id = ur.user_id
  AND ur.security_group_id = s.security_group_id
  AND r.application_id = fa.application_id
  AND trunc(sysdate) between  ur.start_date and nvl(ur.end_date,sysdate)
  AND trunc(sysdate) between  u.start_date and nvl(u.end_date,sysdate)
  AND tr.LANGUAGE='US'
  and RESPONSIBILITY_NAME='System Administrator'


With all details.
===========
SELECT fu.*
FROM fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frvl, fnd_user fu
WHERE furgd.responsibility_id = frvl.responsibility_id
AND fu.user_id = furgd.user_id
AND(to_char(furgd.end_date) is null
OR furgd.end_date > sysdate)
AND frvl.end_date is null
AND frvl.responsibility_name = 'System Administrator';


-------------------------------------------------------------------------------
-- Query to find all responsibilities of a user
-------------------------------------------------------------------------------
SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",     
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('SYSADMIN')  -- <change it>
   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
 ORDER BY frt.responsibility_name;


Another query to remove duplicates..
===========================
SELECT fu.user_id, fu.user_name, fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
and user_name like 'AHMED' --- for all user or for perticular user
-- AND fur.responsibility_application_id = 275 -- to check users for perticular responsibility
order by user_name


query to check list of users with last login date and end date
============================================
SQL>select USER_NAME,START_DATE,END_DATE,DESCRIPTION,LAST_LOGON_DATE
from fnd_user
where CREATED_BY<>1;