SELECT
fu.user_name,
fcpt.USER_CONCURRENT_PROGRAM_NAME,
fcpt.description,
fcp.CONCURRENT_PROGRAM_NAME,
fcr.REQUEST_ID,
round((fcr.actual_completion_date – decode (trunc(fcr.request_date),fcr.requested_start_date,fcr.request_date,fcr.requested_start_date))*60*24) WaitTimeMIN,
DECODE(fcr.PHASE_CODE,‘C’,‘Completed’,‘R’,‘Running’,fcr.PHASE_CODE) PHASE_CODE,
DECODE(fcr.STATUS_CODE,‘C’,‘Completed’,‘R’,‘Running’,‘W’,‘Paused’,‘E’,‘Error’,‘G’, ‘Warning’, fcr.STATUS_CODE) STATUS_CODE,
to_char(fcr.request_date,‘DD-MON-YYYY HH24:MI:SS’) request_date,
to_char(fcr.requested_start_date,‘DD-MON-YYYY HH24:MI:SS’) start_time,
to_char(fcr.actual_completion_date,‘DD-MON-YYYY HH24:MI:SS’) complete_time
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_user fu
WHERE  1=1
AND    fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
AND    fcpt.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
AND    fcr.requested_by = fu.user_id
AND    trunc(fcr.request_date) BETWEEN sysdate – 1 AND sysdate
AND    fcr.status_code IN (‘E’,‘G’)
ORDER BY fcr.status_code,fcp.CONCURRENT_PROGRAM_NAME,fcr.REQUEST_ID;

#!/bin/sh
#
# Author: Jiltin
# Test email for future cronjobs
#
FILENAME=DailyReport
FILETEXT=$FILENAME.txt
source /usr/local/bin/production.env
sqlplus -s username/password@database  <<EOF
–alter session set current_schema=apps;

SET echo OFF
SET feedback OFF
SET heading on
SET linesize 221
SET pagesize 55
SET newpage 0

spool $FILETEXT

column today new_value CURR_DATE noprint
SELECT to_char(sysdate,‘DD-MON-YYYY HH24:MI:SS’) today from dual;

TTITLE left ‘JILERRCC’ CENTER ‘Jiltin Corporation’ –
        RIGHT ‘DATE:’ CURR_DATE skip 1 -
        CENTER ‘Concurrent Jobs Errored Yesterday’ -
        RIGHT ‘PAGE:’ format 999 SQL.PNO   skip 1 -

column user_name format a10
column USER_CONCURRENT_PROGRAM_NAME format a40
column description format a50
column CONCURRENT_PROGRAM_NAME format a10
column REQUEST_ID format 99999999
column WaitTimeMIN format 9999.99
column PHASE_CODE format a10
column STATUS_CODE format a10
column request_date format a20
column start_time format a20
column complete_time format a20

BREAK ON status_code SKIP 1

SELECT
     fu.user_name,
           fcpt.USER_CONCURRENT_PROGRAM_NAME,
           fcpt.description,
     fcp.CONCURRENT_PROGRAM_NAME,
           fcr.REQUEST_ID,
           round((fcr.actual_completion_date – decode (trunc(fcr.request_date),fcr.requested_start_date,fcr.request_date,fcr.requested_start_date))*60*24) WaitTimeMIN,
           DECODE(fcr.PHASE_CODE,‘C’,‘Completed’,‘R’,‘Running’,fcr.PHASE_CODE) PHASE_CODE,
           DECODE(fcr.STATUS_CODE,‘C’,‘Completed’,‘R’,‘Running’,‘W’,‘Paused’,‘E’,‘Error’,‘G’, ‘Warning’, fcr.STATUS_CODE) STATUS_CODE,
           to_char(fcr.request_date,‘DD-MON-YYYY HH24:MI:SS’) request_date,
           to_char(fcr.requested_start_date,‘DD-MON-YYYY HH24:MI:SS’) start_time,
           to_char(fcr.actual_completion_date,‘DD-MON-YYYY HH24:MI:SS’) complete_time
FROM  
       fnd_concurrent_requests fcr,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpt,
           fnd_user fu
WHERE  1=1
AND    fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
AND    fcpt.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
AND    fcr.requested_by = fu.user_id
and    trunc(fcr.request_date) between sysdate – 1 and sysdate
AND    fcr.status_code in (‘E’,‘G’)
order by fcr.status_code,fcp.CONCURRENT_PROGRAM_NAME,fcr.REQUEST_ID;

spool off;
EOF
mail -s "Yesterday Concurrent Program status! Reported on `date` " support@notesbit.com < $FILETEXT