bc26b162f843e3304014d24dcee1c5ed06202722
[auf_coda_additions.git] / db / vues / auf_v_controleTiersCDOS.sql
1 USE [coda-prod]
2 GO
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7
8 ALTER VIEW [dbo].[auf_v_controleTiersCDOS]
9 AS
10
11 SELECT
12 'DANS L''ONGLET OPTION, LE SOUS-ANALYSE A ETE COCHE ALORS QU''IL NE DEVRAIT PAS'
13 AS categorie,
14 code,
15 [name],
16 adddate,
17 usrname,
18 '' anomalie
19 FROM oas_element
20 WHERE
21 cmpcode = 'AUF'
22 AND elmlevel = 2
23 AND deldate IS NULL
24 AND endyear LIKE 0
25 AND (
26 code LIKE 'c%' OR
27 code LIKE 'd%' OR
28 code LIKE 'o%' OR
29 code LIKE 's%'
30 )
31 AND subanal <> 46 -- 76 = oui et 46 = non
32
33 UNION
34
35 SELECT
36 'DANS L''ONGLET IMPUTATION, N''A PAS ETE CREE COMME ELEMENT DE GAUCHE' categorie,
37 code,
38 [name],
39 adddate,
40 usrname,
41 CASE
42 WHEN accounttype = 9 THEN 'compte de bilan'
43 WHEN accounttype = 56 THEN 'compte de resultat'
44 END anomalie
45 FROM oas_element
46 WHERE
47 cmpcode = 'AUF'
48 AND elmlevel = 2
49 AND deldate IS NULL
50 AND endyear LIKE 0
51 AND (
52 code LIKE 'c%' OR
53 code LIKE 'd%' OR
54 code LIKE 'o%' OR
55 code LIKE 's%'
56 )
57 AND accounttype <> 40 -- 40 veut dire element de gauche
58
59 UNION
60
61 SELECT
62 'DANS L''ONGLET IMPUTATION,LA DESCRIPTION OBLIGATOIRE N''A PAS ETE COCHE' categorie,
63 code,
64 [name],
65 adddate,
66 usrname,
67 '' anomalie
68 FROM oas_element
69 WHERE
70 cmpcode = 'AUF'
71 AND elmlevel = 2
72 AND deldate IS NULL
73 AND endyear LIKE 0
74 AND (
75 code LIKE 'c%' OR
76 code LIKE 'd%' OR
77 code LIKE 'o%' OR
78 code LIKE 's%'
79 )
80 AND descr <> 76 -- 76 veut dire oui obligatoire
81
82 UNION
83
84 SELECT
85 'DANS L''ONGLET IMPUTATION, L''OPTION ELEMENT ASSOCIE PAR DEFAULT N''A PAS ETE COCHE'
86 AS categorie,
87 code,
88 [name],
89 adddate,
90 usrname,
91 'Niv. associes = ' + CONVERT(varchar(2),subslevel) anomalie
92 FROM oas_element
93 WHERE
94 cmpcode = 'AUF'
95 AND elmlevel = 2
96 AND deldate IS NULL
97 AND endyear LIKE 0
98 AND (
99 code LIKE 'c%' OR
100 code LIKE 'd%' OR
101 code LIKE 'o%' OR
102 code LIKE 's%'
103 )
104 AND subslevel <> 0 -- 0 = Fournisseur est associes a un autre element
105
106 UNION
107
108 SELECT
109 'DANS L''ONGLET IMPUTATION, L''OPTION SYNTHESE DE COMPTE DOIT ETRE A ' +
110 '"BAILLEUR" OU "MEMBRE"' AS categorie,
111 code,
112 [name],
113 adddate,
114 usrname,
115 'accountsummary = ' + CONVERT(varchar(20),accountsummary) anomalie
116 FROM oas_element
117 WHERE
118 cmpcode = 'AUF'
119 AND elmlevel = 2
120 AND deldate IS NULL
121 AND endyear LIKE 0
122 AND code LIKE 'c%'
123 AND (
124 (code NOT LIKE 'cgrm%' AND accountsummary <> 'BAILLEUR')
125 OR (code LIKE 'cgrm%' AND accountsummary <> 'MEMBRE')
126 )
127
128 UNION
129
130 SELECT
131 'DANS L''ONGLET LETTRAGE ET STATUTS ( PAIEMENTS AUTOMATIQUES,STATUTS PAR DEFAUT)'
132 AS categorie,
133 code,
134 [name],
135 adddate,
136 usrname,
137 CASE
138 WHEN matchable <> 76 THEN 'élém lettrage non coché'
139 WHEN summary <> 95 THEN 'niv. synthese <> 2'
140 WHEN split <> 49 THEN 'niv. éclatement <> Néant'
141 WHEN
142 ltrim(rtrim(settle)) = ''
143 OR settle IS NULL
144 THEN 'Tiers payeur n''a pas été inscrit'
145 WHEN statpay <> 84 THEN 'statut paiement non disponible'
146 END anomalie
147 FROM oas_element
148 WHERE
149 cmpcode = 'AUF'
150 AND elmlevel = 2
151 AND deldate IS NULL
152 AND endyear LIKE 0
153 AND code NOT LIKE 'cgrm%'
154 AND (
155 code LIKE 'c%'
156 OR code LIKE 'o%'
157 OR (code LIKE 's%' AND len(code) > 5)
158 )
159 AND (
160 matchable <> 76
161 OR summary <> 95
162 OR split <> 49
163 OR ltrim(rtrim(settle)) = ''
164 OR settle IS NULL
165 OR statpay <> 84
166 )
167 AND code <> 'SGA12003' -- Cette anomalie ne peut plus etre corrigee
168
169 UNION
170
171 SELECT
172 'DANS L''ONGLET LETTRAGE ET STATUTS ( PAIEMENTS AUTOMATIQUES,STATUTS PAR DEFAUT)'
173 AS categorie,
174 code,
175 [name],
176 adddate,
177 usrname,
178 CASE
179 WHEN matchable <> 76 THEN 'élém lettrage non coché'
180 WHEN summary <> 102 THEN 'niv. synthese <> compte'
181 WHEN split <> 49 THEN 'niv. éclatement <> Néant'
182 WHEN ltrim(rtrim(settle)) <> '' THEN 'Tiers payeur a été inscrit'
183 WHEN statpay <> 84 THEN 'statut paiement non disponible'
184 END anomalie
185 FROM oas_element
186 WHERE
187 cmpcode = 'AUF'
188 AND elmlevel = 2
189 AND deldate IS NULL
190 AND endyear LIKE 0
191 AND (
192 code LIKE 'd%'
193 OR code LIKE 'cgrm%'
194 OR (code LIKE 'S%' AND len (code) < 5)
195 )
196 AND (
197 matchable <> 76
198 OR summary <> 102
199 OR split <> 49
200 OR NOT (ltrim(rtrim(settle)) = '' OR settle IS NULL)
201 OR statpay <> 84
202 )
203
204 UNION
205
206 SELECT
207 'ONGLET CONDITIONS, LA CASE EST-CE UN ELEMENT CLIENT/FOURNISSEUR N''A PAS ETE COCHE'
208 AS categorie,
209 code,
210 [name],
211 adddate,
212 usrname,
213 '' anomalie
214 FROM oas_element
215 WHERE
216 cmpcode = 'AUF'
217 AND elmlevel = 2
218 AND deldate IS NULL
219 AND endyear LIKE 0
220 AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 's%')
221 AND code NOT IN (
222 'SCA22143','SCA22151','SGA12003','CGRM451','CGRM462','CGRM448', 'SVU11623'
223 )
224 AND custsuppacc <> 76
225
226 UNION
227
228 SELECT
229 'ONGLET CONDITIONS, ESCOMPTE AUTORISE, LA CASE "NON" N''A PAS ETE COCHE' categorie,
230 code,
231 [name],
232 adddate,
233 usrname,
234 '' anomalie
235 FROM oas_element
236 WHERE
237 cmpcode = 'AUF'
238 AND elmlevel = 2
239 AND deldate IS NULL
240 AND endyear LIKE 0
241 AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 's%')
242 AND discenable <> 46
243 GO