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
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？