/* showappuser.sql
     show the applications user based on the apps logon username
*/
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 a35 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 svrstat head S format a1 trunc
col elapsedtime head "Elapsed Time" format a12
col program format a30 trunc
break on user_name,description,spid,sid,svrstat nodup
accept trgtusr char prompt 'What is the Apps Logon User ID : '
prompt Here are the sessions for user id &trgtusr....
select to_char(l.start_time,'mm/dd/yy hh:mi:ssAM') startedat, 
--       a.time,
       u.user_name, u.description,
       p.spid, s.sid,s.status svrstat,
       s.program 
--       ,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 u.user_name = upper('&trgtusr')
   and 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 l.start_time = (select max(l2.start_time) 
                         from applsys.fnd_logins l2
                        where l2.pid = l.pid)
order by to_char(l.start_time,'mm/dd/yy hh:mi:ssAM');

-- group by u.user_name,u.description,s.sid,s.status,p.spid,s.program
-- group by u.user_name,u.description,s.sid,s.status,p.spid,a.time,a.user_form_name
-- order by to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM'),a.time;

break on elapsedtime nodup
prompt Here are the Apps forms currently in use by &trgtusr.....
select a.user_form_name, max(a.time) elapsedtime
  from apps.fnd_signon_audit_view a, 
       v$process p, 
       applsys.fnd_logins l,
       v$session s,
       applsys.fnd_user u
 where a.pid = p.pid
   and p.addr = s.paddr
   and a.user_id = u.user_id
   and u.user_name = upper('&trgtusr')
   and l.user_id = u.user_id
   and l.start_time = (select max(l2.start_time) 
                         from applsys.fnd_logins l2
                        where l2.pid = l.pid)
group by a.user_form_name;