REM dailyhogsalert.sql
REM This is the SQL used for ALERT: Performance Hogs Yesterday
REM If you do not use Oracle ALerts, take out the into &xxxx
REM information.  We defined this alert as both a detailed and
REM summary alert, with the detail going to each user who ran a
REM request that tool longer than 20 minutes to run, and the summary going
REM to our IS staff.

select request_id,
   USER_CONCURRENT_PROGRAM_NAME,
   u.email_address,
   to_char(actual_start_date,'DD-MON-YY HH24:MI'),
   to_char(actual_completion_date,'DD-MON-YY HH24:MI'),
   round((r.actual_completion_date-r.actual_start_date)*(60*24),2),
   r.argument_text,name 
   into &REQ, &PROG, &USER, &ST, &COM, &RUNTIME, &PAR, &INSTANCE 
   from fnd_user u, 
		fnd_concurrent_programs p,
        fnd_concurrent_requests r,
		sys.v_$database
   where 
   actual_completion_date >= 
   to_date((to_char(SYSDATE,'DD-MON-YY')||' 08:00'),'DD-MON-YY HH24:MI')
   and actual_start_date <=
   to_date((to_char(SYSDATE,'DD-MON-YY')||' 17:00'),'DD-MON-YY HH24:MI')
   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 round((r.actual_completion_date-r.actual_start_date)*(60*24),2)

