/* appusers.sql
     show the applications users currently connected based on the sid
*/
set lines 132
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 Time" format a10
col zoom_level head "Zoom Level" 
col startedat head "Logon At" format a19
col spid head DBProc format a6
col sid head SID format 99999
accept trgtform char default ALL prompt 'What is the Apps form you are interested in <all> : '
select to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM') startedat, 
       a.time,
       u.user_name, 
       u.description ,
       s.sid, 
       p.spid,
       u.description,
       a.user_form_name
  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 l.start_time = (select max(l2.start_time) 
                         from applsys.fnd_logins l2
                        where l2.pid = l.pid)
group by u.user_name, u.description,a.time,a.user_form_name, s.sid,p.spid
order by to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM'),a.time;