11.5.10.2 APPS active middle-tier user sessions script is
build on top of the following tables:

fnd_logins
fnd_user
v$process
v$session

-------------------------------------
REM
REM It's configured to work with SQL Directory (SQLDIR).
REM SQLDIR is a utility that allows easy organization and
REM execution of SQL*Plus scripts using user-friendly menu.
REM
REM
REM File:
REM a_user_ses_R11i.10.2.sql
REM
REM <SQLDIR_GRP>APPS APPS_ADMIN USER MOST</SQLDIR_GRP>
REM
REM SQLDIR Group Descriptions:
REM APPS - APPS General
REM APPS_INST - APPS Installation
REM APPS_CONC_PROG - APPS Concurrent Programs
REM APPS_CONC_MAN - APPS Concurrent Managers
REM APPS_ADMIN - APPS Administration
REM DBF - Data Files
REM TABSP - Tablespace
REM UTIL - Utility
REM INDX - Index
REM LOG - Redo Log
REM RBS - Rollback
REM MAINT - Maintenance
REM REVERSE - Reverse Engineering
REM SGA - SGA Maintenance
REM TAB - Table
REM USER - User Management
REM STATS - Statistics
REM STORAGE - Storage Management
REM INIT - Database Init Parameters
REM LATCH - Latches
REM LOCK - Locks
REM SEGMENT - Segment Management
REM BACKUP - Backup Management
REM PQ - Parallel Query
REM TRACE - SQL Tracing Tuning
REM PART - Partitioning
REM MOST - Favorite Scripts
REM
REM
REM
REM
REM Purpose:
REM <SQLDIR_TXT>
REM This script reports APPS active middle-tier sessions
REM (works with newest 11.5.10.2 framework)
REM </SQLDIR_TXT>
REM
REM Usage:
REM a_user_ses_R11i.10.2.sql
REM
REM Example:
REM a_user_ses_R11i.10.2.sql
REM

accept 1 prompt "Enter the type of sessions you'd like to report on [web|frm]: "

set lines 132
set trims on

col db_pid format a6 heading "DB-PID"
col mt_pid format a6 heading "MT-PID"
col sid_serial format a16 heading "USR:SID,SERIAL"
col apps_user format a12 heading "Apps User"
col apps_logon_time format a15 heading "-- Apps --|Logon Time"
col mt_detail format a15 heading "Middle Tier"
col mt_start_time format a15 heading "-- M-Tier --|Logon Time"
col module format a10 heading "Module"
col action format a35 heading "Module Detail"

alter session set nls_date_format='MON-DD HH24:MI';

select /*+ ORDERED */
p.spid db_pid
, s.process mt_pid
, s.username||':'||SUBSTR(s.sid||','||s.serial#,1,15 ) sid_serial
, fl.start_time apps_logon_time
, fu.user_name apps_user
, s.machine||'.'||s.osuser mt_detail
--, logon_time mt_start_time
, substr(module,1,10) module, action
from fnd_logins fl
, fnd_user fu
, v$process p
, v$session s
where decode('&&1','web',fl.spid,1)=decode('&&1','web',s .process,1)
and fl.pid=p.pid
and fl.process_spid=p.spid
and fl.serial#=p.serial#
and s.process is not null
and s.paddr = p.addr
and fl.user_id=fu.user_id
and fl.end_time is null
/

----------------------------

his script will allow you to report on either WEB or FORMS connections.

Sample output:

06:00:04 APPS@COMP:ser1> @a_user_ses_R11i.10.2.sql
Enter the type of sessions you'd like to report on [web|frm]: frm

Session altered.

old 14: where decode('&&1','web',fl.spid,1)=decode('&&1','web',s .process,1)
new 14: where decode('frm','web',fl.spid,1)=decode('frm','web',s .process,1)

-- Apps --
DB-PID MT-PID USR:SID,SERIAL Logon Time Apps User Middle Tier Module Module Detail
------ ------ ---------------- --------------- ------------ --------------- ---------- -----------------------------------
2759 29722 APPS:54,11200 JAN-10 12:25 FST midtier.applmgr FNDSCSGN Comp1 Quality Data Collection
8039 2267 APPS:56,15166 JAN-08 06:59 ANASOCTA midtier.applmgr FNDSCSGN Comp2 NA System Administrator
8039 2267 APPS:100,7794 JAN-08 06:59 ANASOCTA midtier.applmgr FNDSCAUS FRM:ANASOCTA:Comp2 NA System A
18153 28439 APPS:137,20169 JAN-11 05:39 BDOLSING midtier.applmgr FNDSCSGN Comp2 OM Manager 2
13684 27954 APPS:161,23900 JAN-11 05:32 GUEST midtier.applmgr POXRQERQ FRM:GDOUGLAS:Requisitions - COM
17453 4935 APPS:178,16991 JAN-10 15:10 GMULIGAN midtier.applmgr FNDSCSGN COM_OZ
2455 26430 APPS:189,21028 JAN-11 05:14 KRUPLAIS midtier.applmgr APXINWKB FRM:KRUPLAIS:Payables Administra
29218 5851 APPS:206,1725 JAN-08 09:05 WSRIGHT midtier.applmgr FNDSCSGN Comp2 OM Manager 2
29218 5851 APPS:218,10694 JAN-08 09:05 WSRIGHT midtier.applmgr FNDRSRUN FRM:WSRIGHT:Comp2 OM Manager 2
19560 23496 APPS:236,9943 JAN-11 05:32 GDOUGLAS midtier.applmgr fnd.framew 23496
19560 23496 APPS:236,9943 JAN-11 05:11 GUEST midtier.applmgr fnd.framew 23496
19560 23496 APPS:236,9943 JAN-11 02:24 GUEST midtier.applmgr fnd.framew 23496
19560 23496 APPS:236,9943 JAN-11 02:15 GUEST midtier.applmgr fnd.framew 23496

............