Modifications diverses
[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 split <> 49 THEN 'niv. éclatement <> Néant'
140 WHEN statpay <> 84 THEN 'statut paiement non disponible'
141 END anomalie
142 FROM oas_element
143 WHERE
144 cmpcode = 'AUF'
145 AND elmlevel = 2
146 AND deldate IS NULL
147 AND endyear LIKE 0
148 AND code NOT LIKE 'cgrm%'
149 AND (
150 code LIKE 'c%'
151 OR code LIKE 'o%'
152 OR (code LIKE 'S_______' AND len(code) > 5)
153 )
154 AND (
155 matchable <> 76
156 OR split <> 49
157 OR statpay <> 84
158 )
159 AND code <> 'SGA12003' -- Cette anomalie ne peut plus etre corrigee
160
161 UNION
162
163 SELECT
164 'DANS L''ONGLET LETTRAGE ET STATUTS ( PAIEMENTS AUTOMATIQUES,STATUTS PAR DEFAUT)'
165 AS categorie,
166 code,
167 [name],
168 adddate,
169 usrname,
170 CASE
171 WHEN matchable <> 76 THEN 'élém lettrage non coché'
172 WHEN summary <> 102 THEN 'niv. synthese <> compte'
173 WHEN split <> 49 THEN 'niv. éclatement <> Néant'
174 WHEN ltrim(rtrim(settle)) <> '' THEN 'Tiers payeur a été inscrit'
175 WHEN statpay <> 84 THEN 'statut paiement non disponible'
176 END anomalie
177 FROM oas_element
178 WHERE
179 cmpcode = 'AUF'
180 AND elmlevel = 2
181 AND deldate IS NULL
182 AND endyear LIKE 0
183 AND (
184 code LIKE 'd%'
185 OR code LIKE 'cgrm%'
186 OR (code LIKE 'S_______' AND len (code) < 5)
187 )
188 AND (
189 matchable <> 76
190 OR summary <> 102
191 OR split <> 49
192 OR NOT (ltrim(rtrim(settle)) = '' OR settle IS NULL)
193 OR statpay <> 84
194 )
195
196 UNION
197
198 SELECT
199 'ONGLET CONDITIONS, LA CASE EST-CE UN ELEMENT CLIENT/FOURNISSEUR N''A PAS ETE COCHE'
200 AS categorie,
201 code,
202 [name],
203 adddate,
204 usrname,
205 '' anomalie
206 FROM oas_element
207 WHERE
208 cmpcode = 'AUF'
209 AND elmlevel = 2
210 AND deldate IS NULL
211 AND endyear LIKE 0
212 AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 'S_______')
213 AND code NOT IN (
214 'SCA22143','SCA22151','SGA12003','CGRM451','CGRM462','CGRM448', 'SVU11623'
215 )
216 AND custsuppacc <> 76
217
218 UNION
219
220 SELECT
221 'ONGLET CONDITIONS, ESCOMPTE AUTORISE, LA CASE "NON" N''A PAS ETE COCHE' categorie,
222 code,
223 [name],
224 adddate,
225 usrname,
226 '' anomalie
227 FROM oas_element
228 WHERE
229 cmpcode = 'AUF'
230 AND elmlevel = 2
231 AND deldate IS NULL
232 AND endyear LIKE 0
233 AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 'S_______')
234 AND discenable <> 46
235 GO