4 Replies Latest reply on Apr 20, 2015 11:53 AM by user599292

    active users and responsibilities r12

    user599292

      Is there a query for R12 of oracle EBS to list all active users and the responsibilities they have been assigned to? I only want to data to return the list of responsibilities for active users, i.e. those without an end_date.

        • 1. Re: active users and responsibilities r12
          Sanjay Desai EBS

          pl. check the following SQL

           

          select u.user_name,

          a.user_id, a.responsibility_id, a.responsibility_application_id, a.start_date, a.end_date, b.responsibility_name, c.application_name

          from  fnd_user u, fnd_user_resp_groups_direct a, fnd_responsibility_vl b, fnd_application_vl c

          where NVL(u.end_date,SYSDATE+1) > SYSDATE

          --and   u.user_id = 5707

          and   a.user_id = u.user_id

          and   b.responsibility_id = a.responsibility_id

          and   c.application_id = a.responsibility_application_id

          order by u.user_name

          • 2. Re: active users and responsibilities r12
            user599292

            returned 0 records.

            • 3. Re: active users and responsibilities r12
              ;) ApPsMasTi ;)

              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 "AMOHSIN" 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;


              source:- Oracle Apps R12 Help: Query to find all responsibilities of a user


               

              ApPsMaStI

              sharing is Caring

               

               


              • 4. Re: active users and responsibilities r12
                user599292

                I am after a master report of all users and their associated responsibilities, I would have the run the above over 500 times per user.