Why isn’t there a standard report that lists all Concurrent Requests that have ended in error? Which System Administrator does not want to schedule such a report to run every morning? Oracle does have a report that reports on all Concurrent Requests (FNDCPCRQ). I don’t want to browse through 1445 reports that ran yesterday, do I? Ok, so I rigged that standard program to produce what I want.
SELECT cr.request_id ReqId, cp.description Prog, lk.meaning STATUS, fu.user_name Requestor, to_char(cr.actual_start_date,'DD-MON-RR HH24:MI:SS') Started, to_char(cr.actual_completion_date,'DD-MON-RR HH24:MI:SS') Completed FROM fnd_concurrent_requests cr, fnd_concurrent_programs_vl cp, fnd_lookups lk, fnd_user fu, fnd_application fa WHERE cr.concurrent_program_id = cp.concurrent_program_id AND cp.application_id = cr.program_application_id AND cp.application_id = fa.application_id AND cr.status_code = lk.lookup_code AND lk.lookup_type = 'CP_STATUS_CODE' AND lk.meaning IN ('Error','Warning') AND cr.requested_by = fu.user_id AND cr.actual_start_date BETWEEN nvl(Fnd_Date.canonical_to_date('$p_start_date'),sysdate-1) AND nvl(Fnd_Date.canonical_to_date('$p_end_date'),sysdate) ORDER BY lk.meaning, cr.request_id;
And if I want to send an e-mail of counts, spool the output and grep for Errors/Warnings… I kept the output simple, with no headers etc so that it is smart phone friendly…
errors=`grep 'Error' $spool_file|wc -l|cut -d' ' -f1` warnings=`grep 'Warning' $spool_file|wc -l|cut -d' ' -f1` mutt -s "Unsuccessful Concurrent Programs" -a $spool_file $p_email<<EOF $errors Errors; $warnings Warnings -- See attachment EOF
