Query to find all responsibilities of a user

The following query finds all the responsibilities that are assigned to a user. This query can be useful if you want to know if a user has a particular responsibility or any responsibility that has been end dated. However, if you just want to see the current "Active" responsibilities of the user, uncomment the "FURG.END_DATE" condition (very bottom line of the query).

In the following example, I used "PSHARMA" as my user name to list all my responsibilities.

-------------------------------------------------------------------------------

-- Query to find all responsibilities of a user

-------------------------------------------------------------------------------

SELECT fu.user_name                "User Name",

       frt.responsibility_name     "Responsibility Name",

       furg.start_date             "Start Date",

       furg.end_date               "End Date",      

       fr.responsibility_key       "Responsibility Key",

       fa.application_short_name   "Application Short Name"

  FROM fnd_user_resp_groups_direct        furg,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.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('AMOHSIN')  -- <change it>

   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))

 ORDER BY frt.responsibility_name;