Query to get User and responsibilities in Oracle EBS

 Script to check user availability:

set lines 132
col USER_NAME format a50
select USER_ID,USER_NAME,START_DATE,END_DATE from FND_USER WHERE USER_NAME = upper(‘&user_name’);

Script to find list of responsibility attached to a specific userid OR all userid:

​set lines 132
col user_name format a25
col responsibility format a40
col application format a40
SELECT UNIQUE fu.user_id,
fu.user_name user_name,
fr.responsibility_key responsibility,
fa.application_name application
FROM fnd_user fu,
fnd_user_resp_groups fg,
fnd_application_tl fa,
fnd_responsibility fr
WHERE fg.user_id(+) = fu.user_id
AND fg.responsibility_application_id = fa.application_id
AND fa.application_id = fr.application_id
AND fg.responsibility_id = fr.responsibility_id
AND fu.user_name like upper(‘%&user_name%’)
AND fg.end_date is Null
ORDER BY fu.user_id,
fa.application_name,
fr.responsibility_key;

Find the application short name by Responsibility name:

​ SELECT FA.APPLICATION_SHORT_NAME,
                 FR.RESPONSIBILITY_KEY,
                 FRG.SECURITY_GROUP_KEY,                     
                 FRT.DESCRIPTION
 
     FROM FND_RESPONSIBILITY FR,
                 FND_APPLICATION FA,
                 FND_SECURITY_GROUPS FRG,
                 FND_RESPONSIBILITY_TL FRT
    WHERE FR.APPLICATION_ID = FA.APPLICATION_ID
      AND    FR.DATA_GROUP_ID = FRG.SECURITY_GROUP_ID
      AND    FR.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID
      AND    FRT.LANGUAGE = 'US'
      AND    FRT.RESPONSIBILITY_NAME = '&RESPONSIBILITY_NAME';

Detail SQL Query to get list of responsibilities for a user in oracle apps:

​SELECT fu.user_name                "&User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Responsibility Start Date",
       furg.end_date               "Responsibility 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
 ORDER BY fu.user_name,frt.responsibility_name;

Query to find the last access date to a Responsibility in R12​

SELECT frt.responsibility_name,
       Max(flr.start_time)
FROM   applsys.fnd_login_responsibilities flr,
       applsys.fnd_user fu,
       applsys.fnd_logins fl,
       applsys.fnd_responsibility_tl frt
WHERE  fl.login_id = flr.login_id
       AND fl.user_id = fu.user_id
      -- AND fu.user_name = '&Username' -- Comment for Complete List
       AND frt.responsibility_id = flr.responsibility_id
GROUP  BY frt.responsibility_name 

​SELECT frt.responsibility_name,fu.user_name,
       Max(flr.start_time) "Last Connect"
FROM   applsys.fnd_login_responsibilities flr,
       applsys.fnd_user fu,
       applsys.fnd_logins fl,
       applsys.fnd_responsibility_tl frt
WHERE  fl.login_id = flr.login_id
       AND fl.user_id = fu.user_id
      AND fu.user_name = '&Username' -- Comment for Complete User List
       AND frt.responsibility_id = flr.responsibility_id
       and frt.responsibility_name = '&ResponsibilityName' 
GROUP  BY frt.responsibility_name ,fu.user_name

​But the above query is only giving the results only if the users have used the standard oracle forms.
For Ex,If the user only used a web based form(Like the Supplier Form,iProcurement),its not showing in the results.

The Profile option Sign-On:Audit Level is set to Form at the site level.
Ran Sign-on Audit Responsibilities,Sign-on Audit Forms concurrent requests from system Administrator.Both the reports are not including if a user used web based form.

​