Database count to send mail daily reports
CREATE PROC SEND_SEKURIT_PENDING_AUTOMAIL
AS
BEGIN
SELECT A.ORD, A.NAMEE,A.AGEING,A.Pending_For_OCR_DW01,B.Pending_For_OCR_DW02,NULL as NC01, NULL as NC02,A.Pending_For_OCR_DW01+B.Pending_For_OCR_DW02 as Total,A.Responsibility
INTO #TEMPSEKURITMAILALTER FROM
(
SELECT 1 as ORD,'OCR Process' as NAMEE, '<2' AS AGEING, count(BARCODE_NO) AS Pending_For_OCR_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW01'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 1 as ORD, 'OCR Process' as NAMEE, '>2'AS AGEING,count(BARCODE_NO)AS Pending_For_OCR_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW01'
AND convert(varchar,INVOICE_SCANNING_DATE,112)
getdate()-2
UNION
SELECT 1 as ORD,'OCR Process' as NAMEE, '>2'AS AGEING,count(BARCODE_NO)AS Pending_For_OCR_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW02'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 2 as ORD,'Clarification' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS Rejection_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW01' AND REJECT_REASON <>'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'1'
)as C
LEFT OUTER JOIN
(
SELECT 2 as ORD, 'Clarification' as NAMEE,'<2'AS AGEING, count(BARCODE_NO)AS Rejection_OCR_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW02' AND REJECT_REASON <>'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 2 as ORD,'Clarification' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS Rejection_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW02' AND REJECT_REASON <>'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'1'
)as D
ON C.AGEING=D.AGEING
UNION
SELECT E.ORD,E.NAMEE,E.AGEING,E.EDI_Pending_DW01,F.EDI_Pending_DW02,NULL as NC01, NULL as NC02,E.EDI_Pending_DW01+F.EDI_Pending_DW02 as Total,E.Responsibility
FROM
(
SELECT 3 as ORD,'EDIFACT Creation' as NAMEE,'<2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW01' AND REJECT_REASON ='1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 3 as ORD,'EDIFACT Creation' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW01' AND REJECT_REASON ='1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 3 as ORD,'EDIFACT Creation' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW02,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW02' AND REJECT_REASON ='1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 4 as ORD,'SAP Integration' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS AS2_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='EDI' AND COMPANY_CODE='DW01'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 4 as ORD,'SAP Integration' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS AS2_Pending_DW02,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='EDI' AND COMPANY_CODE='DW02'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'
SET @Body =@Body +''
SET @Body =@Body +''
SET @Body =@Body +' '
SET @Body =@Body +'Sekurit-Pending Invoice Statistics | '
SET @Body =@Body +''
SET @Body =@Body +'Status | '
SET @Body =@Body +'Ageing | '
SET @Body =@Body +'DW01 | '
SET @Body =@Body +'DW02 | '
SET @Body =@Body +'NC01 | '
SET @Body =@Body +'NC02 | '
SET @Body =@Body +'Total | '
SET @Body =@Body +'Responsibility | '
DECLARE SEKURIT_MAIL CURSOR FOR
SELECT NAMEE,AGEING,PENDING_FOR_OCR_DW01,PENDING_FOR_OCR_DW02,NC01,NC02,TOTAL,RESPONSIBILITY FROM #TEMPSEKURITMAILALTER
OPEN SEKURIT_MAIL
FETCH NEXT FROM SEKURIT_MAIL INTO @NAME,@AGEING,@PENDING_FOR_OCR_DW01,@PENDING_FOR_OCR_DW02,@NC01,@NC02,@TOTAL,@RESPONSIBILITY
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@AGEING='Total')
BEGIN
SET @Body=@Body+''
IF(@NAME<>'OCR Process'AND @NAME<>'Clarification' AND @NAME<>'EDIFACT Creation'AND @NAME<>'SAP Integration ')
BEGIN
SET @Body=@Body+''+ISNULL(@NAME,'')+' | '
END
SET @Body=@Body+''+ISNULL(@AGEING,'')+' | '
SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW01,'')+' | '
SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW02,'')+' | '
SET @Body=@Body+''+ISNULL(@NC01,0)+' | '
SET @Body=@Body+''+ISNULL(@NC02,0)+' | '
SET @Body=@Body+''+ISNULL(@TOTAL,0)+' | '
SET @Body=@Body+''+ISNULL(@RESPONSIBILITY,'')+' | '
SET @Body=@Body+' '
END
ELSE
BEGIN
SET @Body=@Body+''
IF(@row%2=1)
SET @Body=@Body+''+ISNULL(@NAME,'')+' | '
SET @Body=@Body+''+ISNULL(@AGEING,'')+' | '
SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW01,'')+' | '
SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW02,'')+' | '
SET @Body=@Body+''+ISNULL(@NC01,'')+' | '
SET @Body=@Body+''+ISNULL(@NC02,'')+' | '
SET @Body=@Body+''+ISNULL(@TOTAL,0)+' | '
SET @Body=@Body+''+ISNULL(@RESPONSIBILITY,'')+' | '
SET @row=@row+1
END
FETCH NEXT FROM SEKURIT_MAIL INTO @NAME,@AGEING,@PENDING_FOR_OCR_DW01,@PENDING_FOR_OCR_DW02,@NC01,@NC02,@TOTAL,@RESPONSIBILITY
END
CLOSE SEKURIT_MAIL
DEALLOCATE SEKURIT_MAIL
SET @Body=@Body+ ' '
--SET @Body = @Body + '*This is a system generated mail.Please donot reply to this. | '
PRINT @Body
DROP TABLE #TEMPSEKURITMAILALTER
--
--EXEC msdb.dbo.sp_send_dbmail
--
--@recipients = 'Vivek.Srivatsan@saint-gobain.com;Muralidaran.M@saint-gobain.com;Prithviraj.S@saint-gobain.com;Sujoy.Mukherjee@saint-gobain.com;Parthasarathi.S@saint-gobain.com;Gowdhami.M@saint-gobain.com;Chandra.G@saint-gobain.com',
--@copy_recipients = 'Sundaram.DR@saint-gobain.com;Jagathratchagan.J@saint-gobain.com;Nagarajan.R2@saint-gobain.com',
--@subject = @Subject,
--@body = @Body,
--@body_format = 'HTML'
END
|
No comments:
Post a Comment