Modifications diverses
[auf_coda_additions.git] / db / procedures / auf_p_controleDeviseTBTC.sql
CommitLineData
4e57e206 1USE [coda-prod]
22ab1788
EMS
2GO
3
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
4e57e206 9-- EXEC auf_p_controleDeviseTBTC 0,0
22ab1788
EMS
10
11ALTER PROCEDURE [dbo].[auf_p_controleDeviseTBTC]
4e57e206
EMS
12 @exercice int,
13 @typepiece int
22ab1788 14AS
22ab1788
EMS
15SET NOCOUNT ON
16
22ab1788 17CREATE TABLE #ErrDevise (
4e57e206
EMS
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)
22ab1788
EMS
29)
30
22ab1788 31IF @typepiece = 0 AND @exercice = 0
4e57e206
EMS
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
22ab1788 64ELSE IF @typepiece = 0 AND @exercice <> 0
4e57e206
EMS
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
22ab1788 98ELSE IF @typepiece <> 0 AND @exercice = 0
4e57e206
EMS
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
22ab1788 132ELSE IF @typepiece <> 0 AND @exercice <> 0
4e57e206
EMS
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
22ab1788
EMS
167
168-- suppression des ecritures dans la somme total par exercice/el2/devdoc/etat piece
169
4e57e206
EMS
170SELECT exercice, el2, devDoc, etat_piece
171INTO #Errsuppr
172FROM #ErrDevise
173GROUP BY exercice, el2, devDoc, etat_piece
174HAVING SUM(valuedoc) = 0
22ab1788
EMS
175
176DELETE #ErrDevise
22ab1788 177FROM #Errsuppr t
4e57e206
EMS
178WHERE
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
22ab1788
EMS
183
184-- suppression des documents annulés
4e57e206 185-- LA SOMME DU DOCUMENT INITIAL = LA SOMME DU DOCUMENT ANULLE
22ab1788
EMS
186-- supprimer les documents annulés dont le doccode est comme '%-ANN%'
187
4e57e206
EMS
188SELECT DISTINCT
189 a.doccode doccodeA, a.docnum docnumA, i.doccode doccodeI, i.docnum docnumI
22ab1788 190INTO #DocAnnul
4e57e206
EMS
191FROM #ErrDevise a,#ErrDevise i
192WHERE
193 a.doccode LIKE '%-ANN%'
194 AND i.doccode LIKE '%-ANN%'
195 AND rtrim(a.descrl) LIKE '%' + i.doccode + ' ' + ltrim(rtrim(i.docnum))
22ab1788
EMS
196
197DELETE #ErrDevise
198FROM #DocAnnul
4e57e206
EMS
199WHERE
200 (doccode = doccodeA AND docnum = docnumA)
201 OR (doccode = doccodeI AND docnum = docnumI)
22ab1788
EMS
202
203-- supprimer les autres documents annulles
204
4e57e206
EMS
205SELECT DISTINCT
206 a.doccode doccodeA, a.docnum docnumA, i.doccode doccodeI,i.docnum docnumI
22ab1788 207INTO #DocAnnul2
4e57e206
EMS
208FROM #ErrDevise a, #ErrDevise i
209WHERE
210 a.doccode LIKE '%-ANN%'
211 AND rtrim(a.descrl) LIKE '%' + i.doccode + ' ' + ltrim(rtrim(i.docnum))
22ab1788
EMS
212
213DELETE #ErrDevise
214FROM #DocAnnul2
4e57e206
EMS
215WHERE
216 (doccode = doccodeA AND docnum = docnumA)
217 OR (doccode = doccodeI AND docnum = docnumI)
22ab1788
EMS
218
219SELECT * FROM #ErrDevise
220ORDER BY el2
4e57e206 221
22ab1788
EMS
222DROP TABLE #ErrDevise
223DROP TABLE #Errsuppr
22ab1788 224GO