To get Program name for session waiting on Wait event

 set pagesize 24 linesize 200 echo off feedback 1 wrap on verify off

column reqid format 9999999999 heading "Request Id"

column sesid format a10 heading "Session"

column ospid format a10 heading "OS Pid"

column USER_CONCURRENT_PROGRAM_NAME format a45 heading "Program Name"

column user_name format a20 heading "Requestor"

select

   a.request_id reqid,a.status_code,a.phase_code,e.USER_CONCURRENT_PROGRAM_NAME,f.user_name,

   d.sid||','||d.serial# sesid,c.spid ospid,d.event

from

   applsys.fnd_concurrent_requests a,

   applsys.fnd_concurrent_processes b,

   v$process c, v$session d,

   apps.fnd_concurrent_programs_tl e,

   apps.fnd_user f

where

   a.controlling_manager=b.concurrent_process_id

   and c.spid=a.oracle_process_id

   and c.addr=d.paddr

   and e.concurrent_program_id=a.concurrent_program_id

   and a.requested_by = f.user_id

   and d.sid in (select sid from v$session where event like '%&event%')

   and a.phase_code='R'

   and b.instance_number=(select instance_number from v$instance)

   and e.language=USERENV('LANG');