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 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150
| CREATE PROCEDURE prc_BlocageClient
AS
BEGIN
SET NOCOUNT ON;
DECLARE @nombreFacture INT = 0;
DECLARE @nombreJourFacture INT = 0;
DECLARE @nombreImpaye INT = 0;
SELECT @nombreFacture = P_Valeur
FROM SP_Param
WHERE P_Code = 'nombreFacture';
SELECT @nombreImpaye = P_Valeur
FROM SP_Param
WHERE P_Code = 'nombreImpaye';
SELECT @nombreJourFacture = P_Valeur
FROM SP_Param
WHERE P_Code = 'nombreJourFacture';
DECLARE @cte TABLE (NbreIncident int,
CT_Code varchar(17),
SO_ID int)
DECLARE @out_update TABLE (SO_ID int,
CT_Code varchar(17),
CT_Intitule varchar(69));
IF @nombreFacture > 0
BEGIN
WITH cte_NbFacture as ( SELECT count(*) as NbreFacture, CT_Code,SO_Id
FROM RT_ECHEANCE e
WHERE DO_Type IN (6,7)
AND EC_Montant >0
GROUP BY CT_Code,SO_Id
HAVING count(*)>@nombreFacture )
INSERT INTO @cte (SO_ID,CT_Code,NbreIncident)
SELECT SO_ID,CT_Code,NbreFacture
FROM cte_NbFacture;
UPDATE vClientBlocage
SET CT_ControlEnc = 2
OUTPUT inserted.SO_ID,inserted.CT_Num,inserted.CT_Intitule INTO @out_update
WHERE EXISTS (SELECT '*'
FROM @cte b
WHERE b.CT_Code = vClientBlocage.CT_Num
AND b.SO_Id = vClientBlocage.SO_Id)
AND CT_ControlEnc<>2
INSERT INTO BlocageLog (SocieteID,CT_Num,CT_Intitule,BlocageType,BlocageDate,ErrorNumber)
SELECT c.SO_ID,
c.CT_Code,
o.CT_Intitule,
'nombreFacture = '+ CAST(c.NbreIncident as varchar(3)) AS BlocageType,
GETDATE() as BlocageDate,
0 as ErrorNumber
FROM @cte c
INNER JOIN @out_update o ON c.SO_ID = o.SO_ID AND c.CT_Code = o.CT_Code
END
IF @nombreImpaye > 0
BEGIN
DELETE FROM @out_update;
DELETE from @cte;
WITH cte_Impaye as ( SELECT count(*) as NbreFacture, CT_Code,SO_Id
FROM RT_ECHEANCE
WHERE DO_Type =99
AND EC_Type = 1
AND EC_Montant >0
GROUP BY CT_Code,SO_Id
HAVING count(*)>@nombreImpaye )
INSERT INTO @cte (SO_ID,CT_Code,NbreIncident)
SELECT SO_ID,CT_Code,NbreFacture
FROM cte_Impaye;
UPDATE vClientBlocage
SET CT_ControlEnc = 2
OUTPUT inserted.SO_ID,deleted.CT_Num,deleted.CT_Intitule INTO @out_update
WHERE EXISTS (SELECT '*'
FROM @cte b
WHERE b.CT_Code = vClientBlocage.CT_Num
AND b.SO_Id = vClientBlocage.SO_Id)
AND CT_ControlEnc<>2
INSERT INTO BlocageLog (SocieteID,CT_Num,CT_Intitule,BlocageType,BlocageDate,ErrorNumber)
SELECT c.SO_ID,
c.CT_Code,
o.CT_Intitule,
'nombreImpaye = '+ CAST(c.NbreIncident as varchar(3)) AS BlocageType,
GETDATE() as BlocageDate,
0 as ErrorNumber
FROM @cte c
INNER JOIN @out_update o ON c.SO_ID = o.SO_ID AND c.CT_Code = o.CT_Code
END
IF @nombreJourFacture>0
BEGIN
DELETE FROM @out_update;
DELETE from @cte;
WITH cte_nbjFacture as ( SELECT MIN(DATEDIFF(dd,do_date,cast(GETDATE() as date))) as NbreFacture, CT_Code,SO_Id
FROM RT_ECHEANCE
WHERE DO_Type in (6,7)
AND EC_Montant >0
AND DATEDIFF(dd,do_date,cast(GETDATE() as date))>@nombreJourFacture
GROUP BY CT_Code,SO_Id )
INSERT INTO @cte (SO_ID,CT_Code,NbreIncident)
SELECT SO_ID,CT_Code,NbreFacture
FROM cte_nbjFacture
UPDATE vClientBlocage
SET CT_ControlEnc = 2
OUTPUT inserted.SO_ID,deleted.CT_Num,deleted.CT_Intitule INTO @out_update
WHERE EXISTS (SELECT '*'
FROM @cte b
WHERE b.CT_Code = vClientBlocage.CT_Num
AND b.SO_Id = vClientBlocage.SO_Id)
AND CT_ControlEnc<>2
INSERT INTO BlocageLog (SocieteID,CT_Num,CT_Intitule,BlocageType,BlocageDate,ErrorNumber)
SELECT c.SO_ID,
c.CT_Code,
o.CT_Intitule,
'nombreJourFacture = '+ CAST(c.NbreIncident as varchar(3)) AS BlocageType,
GETDATE() as BlocageDate,
0 as ErrorNumber
FROM @cte c
INNER JOIN @out_update o ON c.SO_ID = o.SO_ID AND c.CT_Code = o.CT_Code
END
END |
Partager