8 Replies Latest reply on Apr 4, 2015 6:42 AM by Ahmed.Abbas

    When did a user last used a particular Responsibility.

    2652054

      Hi,

       

      I am looking for a way which can provide me information regarding when did a user last used a particular Responsibility ( eg CIC User,CRM Administrator etc).

       

      For eg , If I last used CIC User responsibility last on 30th March 2015, then is there anyway I could fetch this information ?

       

      Any help with backend query or front end navigation would be much appreciated.

       

      Regards,

      Praveen

        • 1. Re: When did a user last used a particular Responsibility.
          Kj - Kiran Jadhav

          Please see - Auditing: How Do I Audit Responsibilities and Data? (Doc ID 436316.1)

           

          -Kj

          • 2. Re: When did a user last used a particular Responsibility.
            914664

            First of all, auditing at the responsibility level is enabled as a system profile option called Sign-On:Audit Level. Sign-On:Audit Level can be done for the site, responsibility and user and can be done at the form, responsibility and the user level. In other words, it works like a matrix. If you decide to audit a responsibility, then you need to decide at what level: form, responsibility or user. If you audit the responsibility at the form level, then you will get the most details about who used the responsibility, what forms they accessed using this responsibility, when they accessed the responsibility and when they left the responsibility. If auditing the responsibility is done at the responsibility level, you will not get the forms that the responsibility accessed but you will get everything else. If you audit the responsibility at the user level, you will get the user that accessed the responsibility, when they started and when they stopped. Very limited data


            Hope this helps


            Thanks

            Manish

            • 3. Re: When did a user last used a particular Responsibility.
              ;) ApPsMasTi ;)

              see if this help

               

              select a.LOGIN_ID,a.START_TIME,a.END_TIME,c.RESPONSIBILITY_NAME,b.USER_ID,d.USER_NAME from FND_LOGIN_RESPONSIBILITIES a,

              fnd_logins b,

              FND_RESPONSIBILITY_tl c,

              fnd_user d

              where

              a.LOGIN_ID=b.LOGIN_ID

              and a.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID

              and b.USER_ID=d.USER_ID

              --and b.user_id=1013895

              --and a.RESP_APPL_ID=1

              and d.USER_NAME=:USERNAME

              order by a.START_TIME desc;


              you can dig FND_USER for last login

               


              from front end

              you have to enabled the profile value 'Signon: Audit Level  and use the report "Signon Audit Responsibilities" for the purpose.

               

              hope this works

               

              AppsMAsti

              Sharing is Caring

              • 4. Re: When did a user last used a particular Responsibility.
                2652054

                Hi AppsMAsti ,

                 

                 

                Thanks for your reply.

                fnd_user's  last login field would always get updated whenever the user logs in into the application irrespective of the responsibility used.

                 

                Also, I got 0 rows returned for the above query.

                 

                Next, sign on audit level would also give the information of the responsibilities which are currently being used by the user.It will not provide information when was a particular responsibility last used by the user.

                 

                Hence I am looking for some help.

                 

                Any assistance is much appreciated.

                • 5. Re: When did a user last used a particular Responsibility.
                  ;) ApPsMasTi ;)

                  please see

                   

                  https://community.oracle.com/message/12715873#12715873

                   

                  These are the tables you need to look for user login details besides the FND_USERS table.

                  i) FND_LOGINS

                  ii)FND_LOGIN_RESPONSIBILITIES

                  iii)FND_LOGIN_RESP_FORMS

                   

                  ApPsMaStI

                  sharing is Caring

                  • 6. Re: When did a user last used a particular Responsibility.
                    Ahmed.Abbas
                    SELECT user_name,   fr.responsibility_key responsibility,   (SELECT user_function_name   FROM fnd_form_functions_vl fffv   WHERE (fffv.function_id = A.function_id)   ) "Current Function",   TO_CHAR(first_connect, 'DD/MM/YYYY HH:MI:SS') start_time,   TO_CHAR(last_connect, 'DD/MM/YYYY HH:MI:SS') "Date and time of last hit",   TO_CHAR(SYSDATE, 'HH:MI:SS') CURRENT_TIME,   session_id,   (SYSDATE - last_connect) * 24 * 60 mins_idle,   fnd_profile.value_specific('ICX_SESSION_TIMEOUT', A.user_id, A.responsibility_id, A.responsibility_application_id, A.org_id, NULL) TIMEOUT,   counter "How many hits a User has made",   A.limit_connects "Num of hits allowed in session" FROM icx_sessions A,   fnd_user b,   fnd_responsibility fr WHERE A.user_id             = b.user_id AND fr.responsibility_id(+) = A.responsibility_id AND user_name              = nvl(:P_user_name , user_name ) and upper (fr.responsibility_key)  = upper(':P_resp_name') ORDER BY last_connect DESC;
                    • 7. Re: When did a user last used a particular Responsibility.
                      2652054

                      Thanks Ahmed Abbas, this works perfect 

                       

                      please don't mind but I have modified the query a little so that exact responsibility could come up  and kindly suggest if this is okay

                       

                      SELECT user_name,

                             frt.responsibility_name,

                             fr.responsibility_key responsibility,

                             (SELECT user_function_name

                                FROM fnd_form_functions_vl fffv

                               WHERE (fffv.function_id = A.function_id)) "Current Function",

                             TO_CHAR(first_connect, 'DD/MM/YYYY HH:MI:SS') start_time,

                             TO_CHAR(last_connect, 'DD/MM/YYYY HH:MI:SS') "Date and time of last hit",

                             TO_CHAR(SYSDATE, 'HH:MI:SS') CURRENT_TIME,

                             session_id,

                             (SYSDATE - last_connect) * 24 * 60 mins_idle,

                             fnd_profile.value_specific('ICX_SESSION_TIMEOUT',

                                                        A.user_id,

                                                        A.responsibility_id,

                                                        A.responsibility_application_id,

                                                        A.org_id,

                                                        NULL) TIMEOUT,

                             counter "How many hits a User has made",

                             A.limit_connects "Num of hits allowed in session"

                        FROM icx_sessions A, fnd_user b, fnd_responsibility fr,fnd_responsibility_tl frt

                      WHERE A.user_id = b.user_id AND

                             fr.responsibility_id(+) = A.responsibility_id AND  fr.responsibility_id = frt.responsibility_id and

                             user_name = nvl(:USER_NAME, user_name) and

                            upper(frt.responsibility_name)=upper(':P_resp_name') --upper (fr.responsibility_key)  = upper(':P_resp_name')

                      ORDER BY last_connect DESC;

                      • 8. Re: When did a user last used a particular Responsibility.
                        Ahmed.Abbas
                        Hi -  Sure , i Don't Mind  and yes it is now better :)  Thanks