--在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 =‘
Unapplied Receipt Report
    
    
‘SET@body=@body+@xml+‘
    
    
Entity
    
Remitter Name
    
Receipt No
    
Receive Date
    
Receipt Remain Amt
Receipt Total Amt
Currency
    
Remitter Bank Name
  
Receipt Remark
     
    
      ‘    
    
--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