SQL Query to findout list of users connected to Oracle Apps Instance

/*********************************************************
*PURPOSE: To list information about the users who are    *
*         loggedin in past 1 hour                        *
*AUTHOR: Shailender Thallam                              *
**********************************************************/
SELECT DISTINCT fu.user_name User_Name,
  fr.RESPONSIBILITY_KEY Responsibility,
  (SELECT user_function_name
     FROM fnd_form_functions_vl fffv
    WHERE (fffv.function_id = ic.function_id)
  ) Current_Function                                               ,
  TO_CHAR(ic.first_connect, 'dd-mm-yyyy hh24:mi:ss') first_connect ,
  TO_CHAR(ic.last_connect, 'dd-mm-yyyy hh24:mi:ss') last_connect   ,
  ppx.full_name                                                    ,
  fu.email_address                                                 ,
  ppx.employee_number                                              ,
  pbg.name Business_Group
   FROM fnd_user fu    ,
  fnd_responsibility fr,
  icx_sessions ic      ,
  per_people_x ppx     ,
  per_business_groups pbg
  WHERE fu.user_id        = ic.user_id
AND fr.responsibility_id  = ic.responsibility_id
AND ic.responsibility_id IS NOT NULL
AND fu.employee_id        = ppx.person_id(+)
AND ppx.business_group_id = pbg.business_group_id(+)
AND ic.last_connect       &gt; sysdate-1/24 ;

To track further we need to configure profile option ‘Sign-On: Audit Level‘, below is the information from metalink

Sign-On: Audit Level can be set at 4 different levels. The level you specify will determine which Change Tracking information Integra Apps will capture.

The four levels (from lowest to highest level of audit) and the information that they capture are:

1. None
Tracks no additional info.

2. User
Tracks:
who signs on to Oracle Apps
the time users log on and off
the terminals in use

3. Responsibility
Tracks:
User Info
the responsibilities user choose
how much time users spend using each responsibility

4. Form
Tracks:
User Info
Responsibility Info
the forms users choose
how long users spend using each form

Based on the level chosen, the information captured gets stored in the following tables:

FND_LOGINS
FND_LOGIN_RESPONSIBILITIES
FND_LOGIN_RESP_FORMS
The information in these tables never gets overwritten. However, it should not create any additional overhead for our clients.
The “created by” and “updated by” information is ALWAYS captured regardless of how this profile option is set.

SQL Query to findout list of users connected to Oracle Apps Instance

For Further Reading