Oracle Apps Knowledge Sharing

Query to Fetch Proxy User activities in Oracle Apps R12 (JTF_PF_ALL_ACTIVITY_VL)

In this post, we have given a query which gives you the list of activities performed by a proxy user in Oracle apps R12. This comes handy for any debugging or audit reports.

 Key Tables:

JTF_PF_ALL_ACTIVITY_VL

FND_FORM_VL

JTF_DPF_PHYSICAL_PAGES_VL

FND_RESPONSIBILITY_VL

FND_USER

Query: 

SELECT DECODE(tech_stack, 
                   'JTF', NVL((SELECT physical_page_description
                                 FROM jtf_dpf_physical_pages_vl
                                WHERE physical_page_name=pagename
                                  AND ROWNUM              =1
                              )
                              ,pagename
                             ),
                   'OAF', DECODE(INSTR(pagename, '>') - 1, 
                                 -1, pagename, 
                                 SUBSTR(pagename, 0, INSTR(pagename, '>') - 1)
                                ), 
                   'FORM', NVL((SELECT USER_FORM_NAME
                                  FROM fnd_form_VL FF
                                 WHERE FR.APPLICATION_ID = FF.APPLICATION_ID
                                   AND FF.FORM_ID          =pagename
                                   AND ROWNUM              =1
                               )
                               ,pagename
                              ), 
                    pagename 
             ) action,
    fu.user_name,  
    fr.responsibility_name,
    pa.day  event_date,
    TO_CHAR(pa.TIMESTAMP,'HH24:MI:SS') event_time,
    pa.sessionid,
    pa.langid,
    pa.pagename
  FROM jtf_pf_all_activity_vl pa,
       fnd_user fu,
       fnd_responsibility_vl fr
  WHERE pa.proxyid        =fu.user_id
    AND pa.proxyid!         =-1
    AND fr.responsibility_id=pa.respid
    AND fr.application_id   =pa.appid
    /*
    AND pa.userid = (SELECT user_id 
                       FROM fnd_user 
                      WHERE user_name = 'XXSHARE'
                    )
    */
    AND pa.day BETWEEN TO_DATE('13-SEP-2016') AND TO_DATE('12-OCT-2016');