Modifications diverses
[auf_coda_additions.git] / db / procedures / auf_p_controleDeviseTBTC.sql
1 USE [coda-prod]
2 GO
3
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8
9 -- EXEC auf_p_controleDeviseTBTC 0,0
10
11 ALTER PROCEDURE [dbo].[auf_p_controleDeviseTBTC]
12 @exercice int,
13 @typepiece int
14 AS
15 SET NOCOUNT ON
16
17 CREATE TABLE #ErrDevise (
18 exercice int,
19 periode int,
20 el2 varchar(15),
21 descrL varchar(36) ,
22 devElem varchar(5),
23 doccode varchar(12),
24 docnum varchar(12),
25 devDoc varchar(5),
26 valuedoc money,
27 usrname varchar(12),
28 etat_piece varchar(18)
29 )
30
31 IF @typepiece = 0 AND @exercice = 0
32 INSERT INTO #ErrDevise
33 SELECT
34 h.yr exercice,
35 h.period,
36 l.el2,
37 l.descr,
38 e.cur devElem,
39 h.doccode,
40 h.docnum,
41 h.curdoc devDoc,
42 l.valuedoc,
43 l.usrname,
44 CASE h.status
45 WHEN 77 THEN 'En cours de saisie'
46 WHEN 78 THEN 'Réel'
47 WHEN 79 THEN 'Annulée'
48 WHEN 116 THEN 'Corbeille'
49 ELSE 'Code réservé'
50 END AS etat_piece
51 FROM oas_dochead h, oas_docline l, oas_element e
52 WHERE
53 h.cmpcode = 'AUF'
54 AND l.cmpcode = 'AUF'
55 AND e.cmpcode = 'AUF'
56 AND h.doccode= l.doccode
57 AND h.docnum = l.docnum
58 AND h.doccode <> 'G-CLOT-BILAN'
59 AND l.el2 LIKE 'T%'
60 AND h.curdoc <> e.cur
61 AND l.el2 = e.code
62 AND e.elmlevel = 2
63 AND e.deldate IS NULL
64 ELSE IF @typepiece = 0 AND @exercice <> 0
65 INSERT INTO #ErrDevise
66 SELECT
67 h.yr exercice,
68 h.period,
69 l.el2,
70 l.descr,
71 e.cur devElem,
72 h.doccode,
73 h.docnum,
74 h.curdoc devDoc,
75 l.valuedoc,
76 l.usrname,
77 CASE h.status
78 WHEN 77 THEN 'En cours de saisie'
79 WHEN 78 THEN 'Réel'
80 WHEN 79 THEN 'Annulée'
81 WHEN 116 THEN 'Corbeille'
82 ELSE 'Code réservé'
83 END AS etat_piece
84 FROM oas_dochead h, oas_docline l, oas_element e
85 WHERE
86 h.cmpcode = 'AUF'
87 AND l.cmpcode = 'AUF'
88 AND e.cmpcode = 'AUF'
89 AND h.yr = @exercice
90 AND h.doccode= l.doccode
91 AND h.docnum = l.docnum
92 AND h.doccode <> 'G-CLOT-BILAN'
93 AND l.el2 LIKE 'T%'
94 AND h.curdoc <> e.cur
95 AND l.el2 = e.code
96 AND e.elmlevel = 2
97 AND e.deldate IS NULL
98 ELSE IF @typepiece <> 0 AND @exercice = 0
99 INSERT INTO #ErrDevise
100 SELECT
101 h.yr exercice,
102 h.period,
103 l.el2,
104 l.descr,
105 e.cur devElem,
106 h.doccode,
107 h.docnum,
108 h.curdoc devDoc,
109 l.valuedoc,
110 l.usrname,
111 CASE h.status
112 WHEN 77 THEN 'En cours de saisie'
113 WHEN 78 THEN 'Réel'
114 WHEN 79 THEN 'Annulée'
115 WHEN 116 THEN 'Corbeille'
116 ELSE 'Code réservé'
117 END AS etat_piece
118 FROM oas_dochead h, oas_docline l, oas_element e
119 WHERE
120 h.cmpcode = 'AUF'
121 AND l.cmpcode = 'AUF'
122 AND e.cmpcode = 'AUF'
123 AND h.status = @typepiece
124 AND h.doccode = l.doccode
125 AND h.docnum = l.docnum
126 AND h.doccode <> 'G-CLOT-BILAN'
127 AND l.el2 LIKE 'T%'
128 AND h.curdoc <> e.cur
129 AND l.el2 = e.code
130 AND e.elmlevel = 2
131 AND e.deldate IS NULL
132 ELSE IF @typepiece <> 0 AND @exercice <> 0
133 INSERT INTO #ErrDevise
134 SELECT
135 h.yr exercice,
136 h.period,
137 l.el2,
138 l.descr,
139 e.cur devElem,
140 h.doccode,
141 h.docnum,
142 h.curdoc devDoc,
143 l.valuedoc,
144 l.usrname,
145 CASE h.status
146 WHEN 77 THEN 'En cours de saisie'
147 WHEN 78 THEN 'Réel'
148 WHEN 79 THEN 'Annulée'
149 WHEN 116 THEN 'Corbeille'
150 ELSE 'Code réservé'
151 END AS etat_piece
152 FROM oas_dochead h, oas_docline l, oas_element e
153 WHERE
154 h.cmpcode = 'AUF'
155 AND l.cmpcode = 'AUF'
156 AND e.cmpcode = 'AUF'
157 AND h.yr = @exercice
158 AND h.status = @typepiece
159 AND h.doccode = l.doccode
160 AND h.docnum = l.docnum
161 AND h.doccode <> 'G-CLOT-BILAN'
162 AND l.el2 LIKE 'T%'
163 AND h.curdoc <> e.cur
164 AND l.el2 = e.code
165 AND e.elmlevel = 2
166 AND e.deldate IS NULL
167
168 -- suppression des ecritures dans la somme total par exercice/el2/devdoc/etat piece
169
170 SELECT exercice, el2, devDoc, etat_piece
171 INTO #Errsuppr
172 FROM #ErrDevise
173 GROUP BY exercice, el2, devDoc, etat_piece
174 HAVING SUM(valuedoc) = 0
175
176 DELETE #ErrDevise
177 FROM #Errsuppr t
178 WHERE
179 #ErrDevise.exercice = t.exercice
180 AND #ErrDevise.el2 = t.el2
181 AND #ErrDevise.devDoc = t.devDoc
182 AND #ErrDevise.etat_piece = t.etat_piece
183
184 -- suppression des documents annulés
185 -- LA SOMME DU DOCUMENT INITIAL = LA SOMME DU DOCUMENT ANULLE
186 -- supprimer les documents annulés dont le doccode est comme '%-ANN%'
187
188 SELECT DISTINCT
189 a.doccode doccodeA, a.docnum docnumA, i.doccode doccodeI, i.docnum docnumI
190 INTO #DocAnnul
191 FROM #ErrDevise a,#ErrDevise i
192 WHERE
193 a.doccode LIKE '%-ANN%'
194 AND i.doccode LIKE '%-ANN%'
195 AND rtrim(a.descrl) LIKE '%' + i.doccode + ' ' + ltrim(rtrim(i.docnum))
196
197 DELETE #ErrDevise
198 FROM #DocAnnul
199 WHERE
200 (doccode = doccodeA AND docnum = docnumA)
201 OR (doccode = doccodeI AND docnum = docnumI)
202
203 -- supprimer les autres documents annulles
204
205 SELECT DISTINCT
206 a.doccode doccodeA, a.docnum docnumA, i.doccode doccodeI,i.docnum docnumI
207 INTO #DocAnnul2
208 FROM #ErrDevise a, #ErrDevise i
209 WHERE
210 a.doccode LIKE '%-ANN%'
211 AND rtrim(a.descrl) LIKE '%' + i.doccode + ' ' + ltrim(rtrim(i.docnum))
212
213 DELETE #ErrDevise
214 FROM #DocAnnul2
215 WHERE
216 (doccode = doccodeA AND docnum = docnumA)
217 OR (doccode = doccodeI AND docnum = docnumI)
218
219 SELECT * FROM #ErrDevise
220 ORDER BY el2
221
222 DROP TABLE #ErrDevise
223 DROP TABLE #Errsuppr
224 GO