Monday, December 24, 2012

Script to find all the responsibilities of a user

Script to find all the responsibilities of a user
----------------------------------------------

set linseize 600
col user_name for a15
col RESPONSIBILITY_NAME for a35
SELECT fu.user_name,
         fr.responsibility_name,
         furg.start_date,
         furg.end_date
    FROM fnd_user_resp_groups_direct furg,
         fnd_user fu,
         fnd_responsibility_tl fr
   WHERE     upper(fu.user_name) = upper('&user_name')
         AND furg.user_id = fu.user_id
         AND furg.responsibility_id = fr.responsibility_id
         AND fr.language = USERENV ('LANG')
ORDER BY start_date;

Output:
======

Enter value for user_name: JPRASAD
old   8:    WHERE     upper(fu.user_name) = upper('&user_name')
new   8:    WHERE     upper(fu.user_name) = upper('JPRASAD')

USER_NAME       RESPONSIBILITY_NAME                      START_DATE      END_DATE
--------------- ---------------------------------------- --------------- ---------------
JPRASAD         Preferences SSWA                         21-APR-12
JPRASAD         User Management                          21-APR-12
JPRASAD         Functional Administrator                 21-APR-12
JPRASAD         OM Super User - Lifesize APJ             22-APR-12
JPRASAD         System Administrator                     05-MAY-12
JPRASAD         Logical Apps                             05-MAY-12
JPRASAD         Workflow User Web Applications           05-MAY-12
JPRASAD         OM Super User - North America            05-MAY-12
JPRASAD         System Administration                    05-MAY-12
JPRASAD         Oracle Diagnostics Tool                  05-MAY-12
JPRASAD         Alert Manager                            05-MAY-12
JPRASAD         Workflow Administrator Web Applications  05-MAY-12
JPRASAD         Application Developer                    05-MAY-12
JPRASAD         XML Publisher Administrator              05-MAY-12