REM errorsalertsummary.sql
REM This is the SQL for ALERT: Errored or Terminating Jobs
REM If you do not use Oracle Alerts, take out the into &xxxx
REM part of the code.  We run this program every night, and 
REM send the results to the developers and our operators.  
REM Our operators review the errored programs and help the users
REM avoid the error again if they can, or assign the problem to
REM one of the developers if they cannot.

select to_char(req.actual_completion_date,'MON-DD-YY HH24:MI'),
	   req.request_id,
	   decode(req.phase_code,'C','Completed','R','Running'),
	   decode(req.status_code,'E','Error','T','Terminating'),
	   req.description || '(' || prg.user_concurrent_program_name || ')',
	   usr.user_name, req.parent_request_id, req.completion_text, name
into   &FIN_DATE, &REQ_ID, &PHASE, &STATUS, &DESCRIPTION, &REQTD_BY, 
	   &PAR_REQID, &COMPLETION_TEXT, &INSTANCE
from   sys.v_$database, applsys.fnd_concurrent_requests req,
	 applsys.fnd_concurrent_programs prg, applsys.fnd_user usr
where req.concurrent_program_id = prg.concurrent_program_id
  and req.program_application_id = prg.application_id
  and req.requested_by = usr.user_id
  and req.requested_start_date > sysdate-5
  and req.phase_code='C' and req.status_code='E'
  or  req.phase_code='R' and req.status_code='T'
order by req.actual_completion_date desc
