Oracle Apps Knowledge Sharing

Query to fetch the User logins details in Oracle applications R12 (ICX_SESSIONS)

In this post, we have given the query to fetch login details of iSuppliers and employees.

Key Table:

ICX_SESSIONS

Query (iSuppliers):

SELECT hou.short_code entity,
       fu.user_name,
       pv.vendor_name,
       pvc.email_address,
	   icx.first_connect,
       icx.last_connect,
       round((icx.last_connect - icx.first_connect)*24*60,2) ||' mins' duration
  FROM hr_operating_units    hou,
       ap_supplier_sites_all assa,
       po_vendor_contacts    pvc,
       po_vendors            pv,
       fnd_user              fu,
       icx_sessions          icx
 WHERE hou.organization_id = assa.org_id
   AND assa.vendor_id = pvc.vendor_id
   AND pv.vendor_id = pvc.vendor_id
   AND upper(pvc.email_address) = fu.user_name
   AND counter < limit_connects
   AND icx.user_id = fu.user_id
   AND fu.USER_ID > 1100;

Query (Employees):

SELECT fu.user_name,
       papf.full_name,
       papf.email_address,
	   icx.first_connect,
       icx.last_connect,
       round((icx.last_connect - icx.first_connect)*24*60,2) ||' mins' duration
  FROM per_all_people_f      papf,
       fnd_user              fu,
       icx_sessions          icx
 WHERE papf.person_id = fu.employee_id
   AND sysdate between papf.effective_start_date and papf.effective_end_date
   AND counter < limit_connects
   AND icx.user_id = fu.user_id
   AND fu.USER_ID > 1100