DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, December 28, 2016

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",
         fu.user_name
    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'
         AND FU.EMPLOYEE_ID = PAPF.PERSON_ID(+)
GROUP BY papf.full_name,
         papf.email_address,
         papf.employee_number,
         fu.start_date,
         fu.end_date,
         flt.responsibility_name,
         fa.application_name,
         fu.user_name
ORDER BY fa.application_name

-- Narasimha Rao

No comments: