--在Job BES_Daily_FTP_filedownload 中使用
ALTER proc [dbo].[RSP_FN_UNAPPLIED_Mail_Reminder]
as
Declare @MailAddr varchar(max)
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
--Generate receive person list
set @MailAddr = ''
select @MailAddr=@MailAddr+s.User_Mail+';'
from Mst_UserInfo s
where User_IsValid=1 and substring(User_IsMail,1,1)='8' and User_Mail is not null and User_Mail!=''
order by s.User_Mail
--print @MailAddr
--generate mail body
SET @xml =
CAST((
SELECT [Business_Unit] 'td',''
,isnull([Customer_Name],remitter_name) 'td',''
,[Receipt_No] 'td',''
,convert(varchar(10),[Receipt_Date],120) 'td',''
,convert(varchar(100),cast(Receipt_Unapplied_Amount as money),1) 'td','' --Commas every three digits
,convert(varchar(100),cast(Receipt_Amount as money),1) 'td',''
,[Currency_Name] 'td',''
,Bank_Name 'td' ,''
,[Receipt_Remark] 'td'
-- ,[Bank_Account]
-- ,[Customer_JDE_No]
-- ,[Receipt_Year]
-- ,[Receipt_Period]
-- ,[Remitter_Bank_Name]
-- ,[Bank_Branch]
-- ,[Bank_Account]
-- ,[Bank_Charge_Amount]
-- ,[Receipt_Type]
-- ,[Receipt_GL_Date]
-- ,[Receipt_Applied_Amount]
-- ,[Receipt_Status]
-- ,[Receipt_Status_No]
-- ,[Batch_Name]
-- ,[Batch_Status]
-- ,[Customer_Oracle_no]
-- ,[Action_Date]
-- ,[Serial_No]
-- ,[Receipt_Remark]
-- ,[IsHistory]
-- ,[Download_Date]
FROM [BankReceipt]
left join mst_bankinfo on [Remitter_Bank_Name] = bank_id
Where receipt_status_no in (100,300,1000) and business_unit in ('pvg','pws','sgc') and Receipt_Status<>'Unidentified'
order by Receipt_No desc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1>Unapplied Receipt Report</H1>
<body><style type="text/css">
h1,body{font:10pt,"Arial"}
h1{font:small-caps 14pt}
table,td,th {border:1px solid #7F7F7F;border-collapse:collapse;}
table{width:100%}
th{background-color:#C0C0C0;font-weight:bold}
</style>
<table>
<tr>
<th>Entity</th>
<th>Remitter Name</th>
<th>Receipt No</th>
<th style="width:82px">Receive Date</th>
<th>Receipt Remain Amt</th>
<th>Receipt Total Amt</th>
<th>Currency</th>
<th>Remitter Bank Name</th>
<th>Receipt Remark</th>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
--send mail
--EXEC msdb.dbo.sp_send_dbmail
--@blind_copy_recipients = @MailAddr,
--@body = @body,
--@body_format ='HTML',
--@subject ='Unapplied Receipt Report'
exec msdb.dbo.CL_SendSingleMail
''--sendtousermailSysMail [Sys.Admin@emerson.com]
,''--cc
,@MailAddr--@MailAddr--bcc
,'SysMail'--sendername
,'Sys.Admin@emerson.com'--senderaddr
,'Unapplied Receipt Report'--mailsubject
,@body--mailcontent
,'normal' --importance low/normal/high
