1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
| SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sébastien SAUMON et Christian SAUVIAT
-- Create date: <04/02/09>
-- Description: Détection des receptions en erreur et envoi d'email à <a href="mailto:ssaumon@coopatl.lan">ssaumon@coopatl.lan</a>;
-- copie à <a href="mailto:csauviat@coopatl.lan">csauviat@coopatl.lan</a> et <a href="mailto:aperry@coopatl.lan">aperry@coopatl.lan</a>
-- =============================================
ALTER PROCEDURE [dbo].[detection_reception_erreur2]
-- =============================================
-- SCRIPT EXECUTABLE EN ACTIVITE
-- Create date: <04/02/09>
-- Modification : < 17/06/09>
-- Recherche dans les tables hlreclp et hlrecpp
-- =============================================
AS
SET NOCOUNT ON
-- =============================================
-- Déclaration des variables
DECLARE @T TABLE (wekobj VARCHAR(256))
DECLARE @wekobjs VARCHAR(max)
DECLARE @dbname VarChar (30)
DECLARE @Err VARCHAR(256)
DECLARE @Cpt Int
-- SET @dbname = 'RFXCOOPPROD'
-- insertion des erreurs (?)
INSERT INTO reflex.hlw000p (hlcssn, WENRGP, wekobj)
OUTPUT inserted.wekobj INTO @T
SELECT 'RECDEBUG', '1', r1cact + ' ' + r1cdpo + ' ' + CAST(r1nann AS varchar(32)) + ' ' + CAST(r1nrec AS varchar(32)) + ' ' + CAST(r1nlir AS varchar(32))
FROM reflex.hlreclp
INNER JOIN reflex.hlrecpp
ON recact = r1cact
AND recdpo = r1cdpo
AND renann = r1nann
AND renrec = r1nrec
WHERE r1nann = 5
AND R1QBVR = 0
AND retmev = 1
AND RETRVA = 0;
-- concaténation des erreurs pour envoi d'un mail
SET @wekobjs = '';
SELECT @wekobjs = @wekobjs + wekobj +'<br>'
FROM @T
WHERE NOT EXISTS(SELECT *
FROM reflex.hlw000p
WHERE hlcssn = 'RECDEBUG'
AND wekobj=@Err)
IF ISNULL(@wekobjs, '') <> ''
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients='xx@coopatl.lan',
@copy_recipients='xxx@coopatl.lan;xxx@coopatl.lan',
@subject = 'Reception PRD en erreur',
@body = @wekobjs,
@body_format = 'HTML';
END |
Partager