SELECT DISTINCT * FROM (
select
usr.user_name user_name
,ses.sid||’,’||ses.serial# sid_serial
from
apps.icx_sessions i
,apps.fnd_logins l
,apps.fnd_appl_sessions a
,apps.fnd_user usr
,gv$process v
,gv$session ses
where i.disabled_flag = ‘N’
and i.login_id = l.login_id
and l.end_time is null
and i.user_id = usr.user_id
and l.login_id = a.login_id
and a.audsid = ses.audsid
and l.pid = v.pid
and l.serial# = v.serial#
UNION
select
usr.user_name
,ses.sid||’,’||ses.serial#
from
apps.fnd_logins l
,apps.fnd_login_responsibilities r
,apps.fnd_user usr
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = r.login_id(+)
and r.end_time is null
and r.audsid = ses.audsid
UNION
select
usr.user_name
,ses.sid||’,’||ses.serial#
from
apps.fnd_logins l
,apps.fnd_login_resp_forms f
,apps.fnd_user usr
,apps.fnd_form_tl frm
,apps.fnd_form_functions ff
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and l.pid = v.pid
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = f.login_id(+)
and f.end_time is null
and f.form_id = frm.form_id(+)
and f.form_appl_id = frm.application_id(+)
and f.audsid = ses.audsid
and ff.form_id = frm.form_id
UNION
select
fu.user_name
,vs.SID || ‘,’ || vs.serial#
FROM
APPS.fnd_concurrent_requests cr,
v$process vp,
v$session vs,
apps.fnd_user fu
WHERE
cr.phase_code <> ‘I’
AND (cr.phase_code < ‘C’ OR cr.phase_code > ‘C’)
AND cr.status_code NOT IN (‘U’, ‘X’, ‘D’, ‘E’, ‘I’, ‘C’)
AND cr.oracle_process_id = vp.spid (+)
AND cr.oracle_session_id = vs.audsid (+)
AND fu.user_id = cr.requested_by
AND vs.sid is not null
)
ORDER BY 1,2
;
