SQL Query to get list of user,assigned responsibilties and assigned modules

Below SQL Query helps us to get list of users, assigned responsibilities and its modules.

Query gets “Last time the user used the responsibility”, User’s Person Name, Email address, Users’ Employee Number, User ID’s Start date, User ID’s End date, Assigned Responsibility name, Responsibility’s Module name, 
User’s User-id.

SELECT MAX (flr.start_time) “Last accessed”,
         papf.full_name “Person name”,
         papf.email_address “Email Id”,
         papf.employee_number “Employee number”,
         fu.start_date “Start date”,
         fu.end_date “End Date”,
         flt.responsibility_name “Responsibility Name”,
         fa.application_name “Module”,
    FROM fnd_logins fl,
         fnd_login_Responsibilities flr,
         fnd_user fu,
         fnd_responsibility_tl flt,
         fnd_application_vl fa,
         per_all_people_f papf
   WHERE     flr.login_id = fl.login_id
         AND flt.responsibility_id = flr.responsibility_id
         AND fu.user_id = fl.user_id
         AND fa.application_id = FLT.APPLICATION_ID
         AND FLR.RESP_APPL_ID = fa.application_id
         AND flr.resp_appl_id = flt.application_id
         AND FLT.LANGUAGE = ‘US’
GROUP BY papf.full_name,
ORDER BY fa.application_name

— Narasimha Rao

  • December 28, 2016 | 13 views
  • Comments