[su_note note_color=”#e2e2e1″][su_spoiler title=”Shell Script”]
#! /bin/ksh
##########################################################################
# This script is to automate Daily RCR Status Mail.
###########################################################################
if [ -r $HOME/.profile ]
then
. $HOME/.profile
else
print "environment not available"
fi
TODAY=`date +%m_%d_%y`
LOG_FILE=$LOG_DIR/Daily_RCR_STATUS_${TODAY}.log
##Call SQL File
var=`sqlplus -s $DBUSER/$DBPWD@$DBHOST << EOF
#$SQL_SET
@Path/Scripts/Daily_RCR_report.sql
exit
EOF`
##Mail Code
echo "To: dummy-recepent@email.com
From:dummy-alert@email.com
Subject: Daily RCR Status on $sysdate
MIME-version: 1.0
Content-type: text/html; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
" > ~/some
#echo "$var">> ~/some
cat report.htm >> ~/some
echo "
" >> ~/some
echo "Kindly let us know in case of any queries." >> ~/some
echo "
" >> ~/some
echo "Thanks" >> ~/some
echo "
" >> ~/some
echo "Your Team" >> ~/some
cat ~/some | mail dummy-recepent@email.com
rm ~/some
rm report.htm
[/su_spoiler][/su_note][su_spacer size=”10″]

[su_note note_color=”#e2e2e1″][su_spoiler title=”Report SQL”]
-- cat /scripts/Daily_run_report.sql---connect $DBUSER/$DBPWD@$DBHOST
----call css file here
@/opt/siebel/xcsprod/Xerox/Scripts/set_markup.sqlset heading on
set pages 100
TTITLE LEFT _DATE CENTER '

Daily RCR Report : Your Team

‘ –
RIGHT ‘

Red = Quick Action Required

‘
spool report.htm
–Insert SQL statement here, which will fetch data for your report. It uses ‘Case-When-Else’ of SQL to format color code on report.
Select child.par_req_id “RCR ID”,
CASE
when child.status = ‘ERROR’ then
‘‘||child.status||’‘
ELSE
‘‘||child.status||’‘
END “Status”
,to_char(child.actl_start_dt,’DD/MM/YYYY HH24:MI:SS’) “Start Date(EST)”,
to_char(child.actl_end_dt,’DD/MM/YYYY HH24:MI:SS’) “End Date(EST)”,param.value “WF Name”,par.rpt_interval||’ ‘||par.rpt_uom “Frequency”,child.completion_text “Error Msg”
from siebel.S_SRM_REQUEST par,
siebel.S_SRM_REQUEST child, siebel.S_SRM_REQ_PARAM param
where par.row_id = child.par_req_id
and par.req_type_cd = ‘RPT_PARENT’
and par.row_id = param.req_id
and par.STATUS = ‘ACTIVE’
and child.req_type_cd = ‘RPT_INSTANCE’
and param.ACTPARAM_ID in (‘1-4DTOMO’,’1-7WXQ’,’1-7X4F’)
and param.value not like ‘%Pricing%’
and child.actl_end_dt is not null
and child.actl_end_dt in (
select max(actl_end_dt) from siebel.S_SRM_REQUEST
where req_type_cd = ‘RPT_INSTANCE’
and actl_end_dt is not null
and par_req_id = par.Row_Id)
order by child.actl_start_dt desc;
spool off
exit
[/su_spoiler][/su_note][su_spacer size=”10″]

[su_note note_color=”#e2e2e1″][su_spoiler title=”CSS SQL”]

-- cat /scripts/set_markup.sql
set markup HTML ON HEAD " -
SQL*Plus Report" -
BODY "" -
TABLE "border='1' width='90%' align='center'" -
ENTMAP OFF SPOOL ON
[/su_spoiler][/su_note][su_spacer size=”10″]