Hi
How 
Are 
You.

Hi How Are You.

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

Hi
How
Are 
You.

#awk '{ printf "%s ", $0 }' file

Hi How Are You.