Modifications diverses
[auf_coda_additions.git] / db / vues / auf_v_controleTiersCDOS.sql
CommitLineData
22ab1788
EMS
1USE [coda-prod]
2GO
3SET ANSI_NULLS ON
4GO
5SET QUOTED_IDENTIFIER ON
6GO
7
8ALTER VIEW [dbo].[auf_v_controleTiersCDOS]
9AS
10
11SELECT
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
19FROM oas_element
20WHERE
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
4e57e206 29 code LIKE 'S_______'
22ab1788
EMS
30 )
31 AND subanal <> 46 -- 76 = oui et 46 = non
32
33UNION
34
35SELECT
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
45FROM oas_element
46WHERE
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
4e57e206 55 code LIKE 'S_______'
22ab1788
EMS
56 )
57 AND accounttype <> 40 -- 40 veut dire element de gauche
58
59UNION
60
61SELECT
62 'DANS L''ONGLET IMPUTATION,LA DESCRIPTION OBLIGATOIRE N''A PAS ETE COCHE' categorie,
63 code,
64 [name],
65 adddate,
66 usrname,
67 '' anomalie
68FROM oas_element
69WHERE
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
4e57e206 78 code LIKE 'S_______'
22ab1788
EMS
79 )
80 AND descr <> 76 -- 76 veut dire oui obligatoire
81
82UNION
83
84SELECT
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
92FROM oas_element
93WHERE
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
4e57e206 102 code LIKE 'S_______'
22ab1788
EMS
103 )
104 AND subslevel <> 0 -- 0 = Fournisseur est associes a un autre element
105
106UNION
107
108SELECT
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
116FROM oas_element
117WHERE
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
128UNION
129
130SELECT
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é'
22ab1788 139 WHEN split <> 49 THEN 'niv. éclatement <> Néant'
22ab1788
EMS
140 WHEN statpay <> 84 THEN 'statut paiement non disponible'
141 END anomalie
142FROM oas_element
143WHERE
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%'
4e57e206 152 OR (code LIKE 'S_______' AND len(code) > 5)
22ab1788
EMS
153 )
154 AND (
155 matchable <> 76
22ab1788 156 OR split <> 49
22ab1788
EMS
157 OR statpay <> 84
158 )
159 AND code <> 'SGA12003' -- Cette anomalie ne peut plus etre corrigee
160
161UNION
162
163SELECT
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
177FROM oas_element
178WHERE
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%'
4e57e206 186 OR (code LIKE 'S_______' AND len (code) < 5)
22ab1788
EMS
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
196UNION
197
198SELECT
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
206FROM oas_element
207WHERE
208 cmpcode = 'AUF'
209 AND elmlevel = 2
210 AND deldate IS NULL
211 AND endyear LIKE 0
4e57e206 212 AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 'S_______')
22ab1788
EMS
213 AND code NOT IN (
214 'SCA22143','SCA22151','SGA12003','CGRM451','CGRM462','CGRM448', 'SVU11623'
215 )
216 AND custsuppacc <> 76
217
218UNION
219
220SELECT
221 'ONGLET CONDITIONS, ESCOMPTE AUTORISE, LA CASE "NON" N''A PAS ETE COCHE' categorie,
222 code,
223 [name],
224 adddate,
225 usrname,
226 '' anomalie
227FROM oas_element
228WHERE
229 cmpcode = 'AUF'
230 AND elmlevel = 2
231 AND deldate IS NULL
232 AND endyear LIKE 0
4e57e206 233 AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 'S_______')
22ab1788
EMS
234 AND discenable <> 46
235GO