1 USE [BES_ADV]
2 GO
3 /****** Object: StoredProcedure [dbo].[RSP_FN_UNAPPLIED_Mail_Reminder] Script Date: 2015/6/15 16:19:58 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 --在Job BES_Daily_FTP_filedownload 中使用
9 alter proc [dbo].[RSP_FN_UNIdentify_Mail_Reminder]
10 as
11 Declare @MailAddr varchar(max)
12 DECLARE @xml NVARCHAR(MAX)
13 DECLARE @body NVARCHAR(MAX)
14
15 --Generate receive person list
16 set @MailAddr = ''
17 select @MailAddr=@MailAddr+s.User_Mail+';'
18 from Mst_UserInfo s
19 where User_IsValid=1 and substring(User_IsMail,1,1)='8' and User_Mail is not null and User_Mail!=''
20 order by s.User_Mail
21 --print @MailAddr
22
23 --generate mail body
24 SET @xml =
25 CAST((
26 SELECT [Business_Unit] 'td',''
27 ,isnull([Customer_Name],remitter_name) 'td',''
28 ,[Receipt_No] 'td',''
29 ,convert(varchar(10),[Receipt_Date],120) 'td',''
30 ,convert(varchar(100),cast(Receipt_Unapplied_Amount as money),1) 'td','' --Commas every three digits
31 ,convert(varchar(100),cast(Receipt_Amount as money),1) 'td',''
32 ,[Currency_Name] 'td',''
33 ,Bank_Name 'td'
34 FROM [BankReceipt]
35 left join mst_bankinfo on [Remitter_Bank_Name] = bank_id
36 Where receipt_status_no in (100,300,1000) and business_unit in ('pvg','pws','sgc')
37 and Receipt_Status='Unidentified'
38 order by Receipt_No desc
39 FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
40
41 SET @body ='<html><H1>Unidentified Receipt Report</H1>
42 <body><style type="text/css">
43 h1,body{font:10pt,"Arial"}
44 h1{font:small-caps 14pt}
45 table,td,th {border:1px solid #7F7F7F;border-collapse:collapse;}
46 table{width:920px}
47 th{background-color:#C0C0C0;font-weight:bold}
48 </style>
49 <table>
50 <tr>
51 <th>Entity</th>
52 <th>Remitter Name</th>
53 <th>Receipt No</th>
54 <th>Receive Date</th>
55 <th>Receipt Remain Amt</th>
56 <th>Receipt Total Amt</th>
57 <th>Currency</th>
58 <th>Remitter Bank Name</th>
59 </tr>'
60 SET @body = @body + @xml +'</table></body></html>'
61
62 --send mail
63 --EXEC msdb.dbo.sp_send_dbmail
64 --@blind_copy_recipients = @MailAddr,
65 --@body = @body,
66 --@body_format ='HTML',
67 --@subject ='Unapplied Receipt Report'
68
69 exec msdb.dbo.CL_SendSingleMail
70 ''--sendtousermailSysMail [Sys.Admin@emerson.com]
71 ,''--cc
72 ,@MailAddr--@MailAddr--bcc
73 ,'SysMail'--sendername
74 ,'Sys.Admin@emerson.com'--senderaddr
75 ,'Unidentified Receipt Report'--mailsubject
76 ,@body--mailcontent
77 ,'normal' --importance low/normal/high
