Code:
ORACLE_HOME=/app/oracle/product/11.1.0/client_1 ; export ORACLE_HOME
PATH=$PATH:/app/oracle/product/11.1.0/client_1/bin ; export PATH
cd /crmapp/crmpm
m=`date +%Y%m%d`
sqlplus -s abcd/efgh@crmdb << EOF
set echo off
set newpage 0
set space 0
set pagesize 0
set linesize 20000
set feed off
set heading on
set trimspool on
spool Direct_Debit_Report_$m.csv
select 'SR_Number'||','||'BANNumber'||','||'Name'||','||'CPR#'||','||'DD_Ref_Num'||','||'MSISDN'||','||'BankName'||','||'Bank_Account_Name'||','||'AccountNumber'||','||'DD_Max_Amount'||','||'DD_Max_Amount_Amend'||','||'DD_StartDate'||','||'SR_Tier-3'||','||'SR_Created'||','||'SR_Status'||','||'SR_SubStatus'||','||'Description' from dual;
select sr.sr_num||','||b.ou_num||','||b.name||','||can.tax_iden_num||','||sr.X_DD_REF_NUM||','||a.duns_num||','||x.X_BANK_NAME||','||x.X_BANK_ACC_NAME||','||x.X_BANK_ACCOUNT_NUM||','||sr.X_DD_MAX_AMT||','||sr.X_DD_MAX_AMT_AMEND||','||sr.X_DD_START_DATE||','||sr.SR_SUB_AREA||','||SR.CREATED||','||sr.SR_STAT_ID||','||sr.sr_sub_stat_id||','||sr.desc_text
from siebel.s_org_ext a,siebel.s_org_ext b,siebel.s_srv_req sr,siebel.s_srv_req_x x,siebel.s_org_ext can
where a.ROW_ID = SR.CST_OU_ID AND
a.master_ou_id = can.row_id and
sr.row_id = x.par_row_id and
a.par_ou_id = b.row_id and
sr.sr_sub_area in ('Amendment','Cancellation','New') and
trunc(sr.created) >= (sysdate-7);
spool off
quit
EOF
#mailx -m -s "Direct Debit Report" xxx@viva.com.bh,yyy@viva.com.bh,aimakki.c@viva.com.bh,zzz@viva.com.bh << EOF
#mailx -m -s "Direct Debit Report $m" aaa@viva.com.bh << EOF
mailx -m -s "Direct Debit Report" aaa@viva.com.bh << EOF
Hi,
PFA Direct Debit SR's since yesterday.
Note: If the sheet is blank it indicates that there is no DD SR raised since yesterday.
Thanks & Regards,
CRM Team
$(/usr/bin/uuencode "/crmapp/crmpm/Direct_Debit_Report_$m.csv" Direct_Debit_Report_$m.csv)
EOF
rm /crmapp/crmpm/Direct_Debit_Report_$m.csv