Shell Programming and Scripting

### Local Variables Setup ### 
MAIL_DL="USA.Birlasoft.VCRP.Support@xerox.com,USA.ISP.Critical@xerox.com"
TODAY=`date '+%d-%h-%Y'`
TODAY_TIME=`date '+%d-%h-%Y %H:%M:%S'`
ISP_RESPONSE="${APP_HOME}/tmp/ISP_error_response.log"
ISP_PATH="${APP_HOME}/shell_scripts"
report_sheet="Error_Details.xls"
ERR_CNT=`cat $ISP_RESPONSE|grep 'Total No of -6'|cut -f2 -d:`

### Inbuilt Function Created to send mail to the Distribution List ###
send_email()
{
( cat "$ISP_RESPONSE"; uuencode "$ISP_PATH/$report_sheet" Error_Details.xls; )|mailx -s "-6 Error Status on $TODAY" "$MAIL_DL"
}

### Local Environment Setup ### 
if [ -r $APP_HOME/config/env ];
then
       . $APP_HOME/config/env;
else
          print `date +"%m/%d/%y %H:%M:%S"` " - Environment not available \n" >$ISP_RESPONSE
          cat $ISP_RESPONSE | mailx -s ' Process failed during local environment Setup!!' $MAIL_DL
          exit 1
fi


### Sql Variables Setup ### 
SQL_SETS="
set serveroutput on;
set echo off;
set feedback off;
set heading off;
whenever sqlerror exit 1;
whenever oserror exit 1;"

SQL_SET="
set pages 50000;
set tab on;
#set colsep '   ';
set serveroutput on;
#set trimspool on;
set heading on;
set echo off;
set linesize 220;
set trimout on;
set feedback off;
set space 8;
set lines 32000;"

SQL_CNT_ERR_RECS="SELECT SUM(CNT)
FROM
(
SELECT COUNT(1) CNT FROM VCRADMIN.T523QUERY_RESPONSE_METRICS WHERE TRUNC(REC_TS)=TRUNC(SYSDATE) AND RESULT_CODE='-6'
UNION ALL
SELECT COUNT(1) CNT FROM VCRADMIN.T522REQUEST_RESPONSE_METRICS WHERE TRUNC(REC_TS)=TRUNC(SYSDATE) AND RESULT_CODE='-6'
);"

SQL_CNT_TOTAL_RECS="SELECT COUNT(1) FROM VCRADMIN.T006RAW_RESPONSE_RECORD WHERE TRUNC(REC_TS)=TRUNC(SYSDATE);"

### Connecting to the DataBase ### 
CONN1=`sqlplus -s $DBUSER/$DBPWD@$DBHOST << EOF
$SQL_SETS
$SQL_CNT_ERR_RECS
EOF`

echo "$CONN1" | grep ORA
if [ $? -eq 0 ]
then
      echo "Failure: Issue during counting -6 from the DataBase" > $ISP_RESPONSE
      cat $ISP_RESPONSE | mailx -s ' Process failed during -6 count!!' $MAIL_DL
      exit 0
fi

CONN1=`echo $CONN1|sed -e 's/^ //g'`

### Connecting to the DataBase ### 
CONN2=`sqlplus -s $DBUSER/$DBPWD@$DBHOST << EOF
$SQL_SETS
$SQL_CNT_TOTAL_RECS
EOF`

echo "$CONN2" | grep ORA
if [ $? -eq 0 ]
then
      echo "Failure: Issue during counting of total transaction from the T006 Table" > $ISP_RESPONSE
      cat $ISP_RESPONSE | mailx -s ' Process failed during -6 count!!' $MAIL_DL
      exit 0
fi

CONN2=`echo $CONN2|sed -e 's/^ //g'`

### Connecting to the DataBase ###
CONN3=`sqlplus -s $DBUSER/$DBPWD@$DBHOST << EOF
$SQL_SET
spool $ISP_PATH/report.xls
COLUMN request_number HEADING 'REQUEST_NO'
COLUMN RESULT_CODE HEADING 'CODE'
COLUMN TRANSXTN_ID HEADING 'TRANSXTN_ID'
COLUMN TRANS_TYPE_CD HEADING 'RT'
COLUMN REC_TS HEADING 'DATE WITH TIME'
COLUMN REC_SRC HEADING 'SOURCE'
SELECT request_number,TRANS_TYPE_CD,TRANSXTN_ID,RESULT_CODE,REC_SRC,to_char(REC_TS,'MM/DD/YYYY HH24:MI:SS')REC_TS FROM VCRADMIN.T523QUERY_RESPONSE_METRICS where TRUNC(REC_TS)=To_char(SYSDATE)AND RESULT_CODE='-6'
UNION ALL
SELECT request_number,TRANS_TYPE_CD,TRANSXTN_ID,RESULT_CODE,REC_SRC,to_char(REC_TS,'MM/DD/YYYY HH24:MI:SS')REC_TS FROM VCRADMIN.T522REQUEST_RESPONSE_METRICS where TRUNC(REC_TS)=To_char(SYSDATE) AND RESULT_CODE='-6'
order by REC_TS desc;
spool off

EOF`
echo "$CONN3"> $report_sheet

echo "Total No of -6       : $CONN1 " > $ISP_RESPONSE
echo "Total No of Requests : $CONN2 \n\n" >> $ISP_RESPONSE

echo "Thanks" >> $ISP_RESPONSE
echo "VCRP Support Team \n" >> $ISP_RESPONSE

if [ $CONN1 -gt 0 ]
then
if [ $CONN1 -gt $ERR_CNT ]
then
      send_email
fi
fi

10 01 * * * ksh 'cd /apps/vcr/appl; . ./config/env;$APP_HOME/shell_scripts/isp_response_status_prod.ksh > /dev/null 2>&1 '

Like this
And this
And this

ISP_RESPONSE="${APP_HOME}/tmp/ISP_error_response.log"
ISP_PATH="${APP_HOME}/shell_scripts"
report_sheet="Error_Details.xls"