Concurrent Requests Average/Max/Min hours runtime details

set
linesize 200

col
username for a10

col
status for a10

col
phase for a10

col
PNAME for a70

col
request_id for 99999999999

col
PNAME for a40

select

b.user_name
username,

a.USER_CONCURRENT_PROGRAM_NAME as PNAME,

avg((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24)
avg_Hrs_running,

max((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24)
Max_Hrs_running,

min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24)
Min_Hrs_running

from

apps.fnd_conc_req_summary_v
a,

apps.fnd_user b

where

phase_code
= ‘C’ and status_code = ‘C’ and

a.REQUESTED_START_DATE
> sysdate-30 and

upper(a.USER_CONCURRENT_PROGRAM_NAME)
like upper(‘%&str%’) and

a.REQUESTED_BY=b.user_id

group
by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;