Description:
QUERY TO FIND THE DETAILS OF CONCURRENT REQUESTS WAIT FOR MORE THAN ONE HOUR.
Query:
set head on;
set feed off;
set pages 0;
set verify off;
set feedback off;
set linesize 250;
select u.USER_NAME||’,’||cr.REQUEST_ID||’,’||cp.USER_CONCURRENT_PROGRAM_NAME||’,’||cr.ARGUMENT_TEXT||’,’||
cr.REQUESTED_START_DATE||’,’||sysdate||’,’||cr.phase_code||’,’||cr.status_code||’,’||
(sysdate-cr.REQUESTED_START_DATE)*24*60||’,’||cr.ORACLE_PROCESS_ID
from
apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_programs_tl cp,
apps.fnd_user u
–v$process p
–v$session s
where
cr.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
and cr.REQUESTED_BY = u.USER_ID
and ((cr.PHASE_CODE = ‘R’ and cr.STATUS_CODE in (‘W’))
or (cr.PHASE_CODE = ‘P’ and cr.STATUS_CODE = ‘Q’))
and cr.hold_flag != ‘Y’
and
cr.RESUBMIT_INTERVAL is null and
cp.USER_CONCURRENT_PROGRAM_NAME IS NOT NULL
and cr.ORACLE_PROCESS_ID = p.SPID
and p.ADDR = s.PADDR
and (sysdate-cr.REQUESTED_START_DATE)*24*60 > 29
order by cr.REQUESTED_START_DATE;
exit;
