Script to Monitor Concurrent requests average run time

Do you need a daily  report which would give an average run time of all concurrent requests in your environment?

So you would know which concurrent request is taking longer time and on which day:
The below script gives an average run time for all concurrent requests for the current day, previous day, day before yesterday and for the whole week:



set pagesize 500
set echo off
set feedback off
set linesize 200
col USER_CONCURRENT_PROGRAM_NAME for a75
col wkly_Time Heading 'Weekly Run | Time | Avg |(In Minutes) '
col dbydy_Tim Heading 'Day Before|Yesterday | Run Time | Avg |(In Minutes) '
col ydy_Tim Heading 'Yesterday | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading 'Today | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading &_DATE
select  wkly.user_concurrent_program_name, wkly.wkly_time , to_char(nvl(dbydy.dbydy_time,'     Not Run'))  Dbydy_tim,to_char(nvl(ydy.ydy_time,'     Not Run'))  ydy_tim,to_char(nvl(tdy.tdy_time,'     Not Run'))  tdy_tim
FROM
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') wkly_time
FROM apps.fnd_conc_req_summary_v
WHERE nvl(actual_start_date,sysdate) >= (sysdate-7)
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name) wkly,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') dbydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-2))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) dbydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') ydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-1))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) ydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') tdy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) tdy
WHERE wkly.user_concurrent_program_name =dbydy.user_concurrent_program_name (+)
AND   dbydy.user_concurrent_program_name = ydy.user_concurrent_program_name (+)
AND   ydy.user_concurrent_program_name  = tdy.user_concurrent_program_name (+)
order by wkly_time desc