HOWTO : Getting User Responsability on EBS 11i using a Database user (BOLINF or APPS)

October 31st, 2013 por Jorge Acevedo Flores
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

Find below the SQL statement to generate an extract with active users together with their active responsibilities.

The statement extracts all the active assignments of responsibilities by using the FND_USER_RESP_GROUPS table which combines DIRECT and INDIRECT responsibilities.

If you need only the DIRECT responsibilities than you also have the option to use the seeded view FND_USER_RESP_GROUPS_DIRECT. If you need only the INDIRECT responsibilities (added by roles – like Application Diagnostics and others) than use the seeded view FND_USER_RESP_GROUPS_INDIRECT.

As always adjust the SQL to cover your needs.

SELECT
fuser.USER_NAME USER_NAME
, per.FULL_NAME FULL_NAME
, per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, frt.RESPONSIBILITY_NAME RESPONSIBILITY
FROM
FND_USER fuser
, PER_PEOPLE_F per
, FND_USER_RESP_GROUPS furg
, FND_RESPONSIBILITY_TL frt
WHERE
fuser.EMPLOYEE_ID = per.PERSON_ID
AND fuser.USER_ID = furg.USER_ID
AND (to_char(fuser.END_DATE) is null
OR fuser.END_DATE > sysdate)
AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
AND (to_char(furg.END_DATE) is null
OR furg.END_DATE > sysdate)
AND frt.LANGUAGE = ‘US’
ORDER BY
, fuser.USER_NAME;

I hope this will be useful.

J.