How to Find All Active Responsibilities of a User in Oracle R12(Query)

Query to find all responsibilities of a user


SELECT
    fu.user_name,
    frt.responsibility_name,
    furg.start_date,
    furg.end_date,
    fr.responsibility_key,
    fa.application_short_name
FROM
    apps.fnd_user_resp_groups_direct furg,
    apps.fnd_user fu,
    apps.fnd_responsibility_tl frt,
    apps.fnd_responsibility fr,
    apps.fnd_application_tl fat,
    apps.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('&P_USER_NAME')
    AND   SYSDATE BETWEEN nvl(furg.start_date,SYSDATE) 
             AND nvl(furg.end_date,SYSDATE + 1)
ORDER BY  1,2;

Output:

How to Find All Active Responsibilities of a User in Oracle R12(Query)

No comments