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 |
