/* formusers.sql
     show the applications sessions currently using a specified form
     can optionally limit it by active only
     if you need to limit by a user, use appsid.sql
*/
set lines 132
set verify off
set feedback off
col user_name head "Apps Signon" format a12 trunc 
col description head "Real Name" format a25 trunc
col user_form_name head "Form Name" format a30 trunc
col time head "Elapsed" format a7
col startedat head "Logon At" format a19
col spid head DBProc format a6
col sidctr head "#Sess" format 99999
col actstat head "Status" format a1
col currform format a30 trunc head CurrentForm
col lastcallet format a11
col sid format 99999
break on startedat on time on user_name on description on sidctr on actstat on spid
accept trgtuser char default ALL prompt 'What is the Apps short username you are interested in <all> : '
accept trgtform char default ALL prompt 'What is the Apps form you are interested in <all> : '
accept trgtstat char default N prompt 'Limit to active users only [y/n] <n> : '
select /*+ rule */
       to_char(s.logon_time,'mm/dd hh24:mi:ss') startedat, 
       a.time,
       floor(s.last_call_et/3600)||':'||
       floor(mod(s.last_call_et,3600)/60)||':'||
       mod(mod(s.last_call_et,3600),60) "LastCallET", 
       u.user_name, 
       u.description ,
       count(s.sid) sidctr, 
       s.sid,
       decode(sum(decode(s.status,'ACTIVE',1,0)),0,'I','A') actstat,
       p.spid,
       a.form_id || ' - ' || a.user_form_name currform
  from applsys.fnd_logins l,
       applsys.fnd_user u,
       apps.fnd_signon_audit_view a,
       v$process p,
       v$session s
 where s.paddr = p.addr
   and p.pid = l.pid
   and l.end_time is null
   and l.spid = s.process
   and l.start_time is not null
--   and l.start_time = u.last_logon_date
--   and l.session_number = u.session_number
   and l.user_id = u.user_id
   and u.user_id = a.user_id
   and p.pid = a.pid
   and ((upper(a.user_form_name) like upper('%&trgtform%')) or (upper('&trgtform') = 'ALL'))
   and ((s.status = 'ACTIVE' and upper('&trgtstat') = 'Y') or (upper('&trgtstat') != 'Y'))
   and ((upper(u.user_name) like upper('%&trgtuser%')) or (upper('&trgtuser') = 'ALL'))
   and l.start_time = (select max(l2.start_time) 
                         from applsys.fnd_logins l2
                        where l2.pid = l.pid)
group by to_char(s.logon_time,'mm/dd hh24:mi:ss'),
         s.sid,u.user_name, u.description,a.time,a.form_id || ' - ' || a.user_form_name,p.spid,
   floor(s.last_call_et/3600)||':'||
   floor(mod(s.last_call_et,3600)/60)||':'||
   mod(mod(s.last_call_et,3600),60)
order by 8 desc,5,to_char(s.logon_time,'mm/dd hh24:mi:ss'),a.time;