REM weeklyhogsalert.sql
REM This is the SQL code for ALERT:  Performance Hogs This Week
REM that tells our programmers what the long-running requests were 
REM during business hours for our users.  If you do not use Oracle
REM Alert, take out the INTO &xxxx part of the statement.  

select request_id, USER_CONCURRENT_PROGRAM_NAME, u.email_address,
	   to_char(r.actual_start_date,'DD-MON-YY HH24:MI'),
	   to_char(r.actual_completion_date,'DD-MON-YY HH24:MI'),
	   round((r.actual_completion_date-r.actual_start_date)*(60*24),2),name
into  &REQ, &PROG, &USER, &ST, &COM, &RUNTIME, &INSTANCE
from  fnd_user u,fnd_concurrent_programs p,
	fnd_concurrent_requests r,sys.v_$database
where to_char(actual_start_date,'HH24:MI') between '08:00' and '18:00'
	and  to_char(actual_start_date,'DY') in ('MON','TUE','WED','THU','FRI')
	and  p.concurrent_program_id = r.concurrent_program_id
	and  r.program_application_id = p.application_id
	and  r.requested_by = u.user_id(+)
      and  round((r.actual_completion_date-r.actual_start_date)*(60*24),2) > 20
	order by r.oracle_id,p.USER_CONCURRENT_PROGRAM_NAME,
	round((r.actual_completion_date-r.actual_start_date)*(60*24),2)
