Modifs diverses
[auf_coda_additions.git] / db / vues / auf_v_controleFournisseurs.sql
CommitLineData
22ab1788
EMS
1USE [coda-prod]
2GO
3SET ANSI_NULLS ON
4GO
5SET QUOTED_IDENTIFIER ON
6GO
7
8ALTER VIEW [dbo].[auf_v_controleFournisseurs]
9AS
10
11SELECT
12 'DANS L''ONGLET OPTION, LE SOUS-ANALYSE DOIT ETRE COCHE A "NON"' categorie,
13 code,
14 [name],
15 adddate,
16 usrname,
17 '' anomalie
18FROM oas_element
19WHERE
20 cmpcode = 'AUF'
21 AND elmlevel = 2
22 AND deldate IS NULL
23 AND endyear LIKE 0
24 AND code LIKE 'F%'
25 AND subanal <> 46 -- 76 = oui et 46 = non
26
27UNION
28
29SELECT
30 'DANS L''ONGLET IMPUTATION, N''A PAS ETE CREE COMME ELEMENT DE GAUCHE' categorie,
31 code,
32 [name],
33 adddate,
34 usrname,
35 CASE
36 WHEN accounttype = 9 THEN 'compte de bilan'
37 WHEN accounttype = 56 THEN 'compte de resultat'
38 END anomalie
39FROM oas_element
40WHERE
41 cmpcode = 'AUF'
42 AND elmlevel = 2
43 AND deldate IS NULL
44 AND endyear LIKE 0
45 AND code LIKE 'F%'
46 AND accounttype <> 40 -- 40 veut dire element de gauche
47
48UNION
49
50SELECT
51 'DANS L''ONGLET IMPUTATION,LA DESCRIPTION OBLIGATOIRE N''A PAS ETE COCHE' categorie,
52 code,
53 [name],
54 adddate,
55 usrname,
56 '' anomalie
57FROM oas_element
58WHERE
59 cmpcode = 'AUF'
60 AND elmlevel = 2
61 AND deldate IS NULL
62 AND endyear LIKE 0
63 AND code LIKE 'F%'
64 AND descr <> 76 -- 76 veut dire oui obligatoire
65
66UNION
67
68SELECT
69 'DANS L''ONGLET IMPUTATION, L''OPTION ELEMENT ASSOCIE PAR DEFAULT A ETE COCHE' categorie,
70 code,
71 [name],
72 adddate,
73 usrname,
74 'Niv. associes = ' + CONVERT(varchar(2),subslevel) anomalie
75FROM oas_element
76WHERE
77 cmpcode = 'AUF'
78 AND elmlevel = 2
79 AND deldate IS NULL
80 AND endyear LIKE 0
81 AND code LIKE 'F%'
82 AND subslevel <> 0 -- 0 = Fournisseur est associes a un autre element
83
84UNION
85
86-- Pour les codes créés avant 2011
87
88SELECT
89 'DANS L''ONGLET LETTRAGE ET STATUTS ( PAIEMENTS AUTOMATIQUES,STATUTS PAR DEFAUT)'
90 AS categorie,
91 code,
92 [name],
93 adddate,
94 usrname,
95 CASE
96 WHEN matchable <> 76 THEN 'élém lettrage non coché'
97 WHEN summary <> 95 THEN 'niv. synthese <> 2 car créé avant 2011'
98 WHEN split <> 49 THEN 'niv. éclatement <> Néant'
99 WHEN
100 ltrim(rtrim(settle)) = ''
101 OR settle IS NULL
102 THEN 'Tiers payeur n''a pas été inscrit'
103 WHEN statpay <> 84 THEN 'statut paiement non disponible'
104 END anomalie
105FROM oas_element
106WHERE
107 cmpcode = 'AUF'
108 AND elmlevel = 2
109 AND deldate IS NULL
110 AND endyear LIKE 0
111 AND code LIKE 'F%'
112 AND code NOT IN (
113 'FCA1425', 'FCA1426', 'FCA1427', 'FCA1428', 'FCA1429', 'FCA1430',
114 'FCA1431', 'FVN1B23', 'FVN1B24', 'FVN4948', 'FVN4949', 'FVU1125'
115 )
116 AND (
117 matchable <> 76
118 OR summary <> 95
119 OR split <> 49
120 OR ltrim(rtrim(settle)) = ''
121 OR settle IS NULL
122 OR statpay <> 84
123 )
124 AND year(adddate)<2011
125
126UNION
127
128-- Pour les codes créés à partir de 2011
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é'
139 WHEN summary <> 102 THEN 'niv. synthese <> compte car créé à partir de 2011'
140 WHEN split <> 49 THEN 'niv. éclatement <> Néant'
141 WHEN (settle IS NOT NULL AND settle <> '')
142 THEN 'Tiers payeur ne doit pas être inscrit'
143 WHEN statpay <> 84 THEN 'statut paiement non disponible'
144 END anomalie
145FROM oas_element
146WHERE
147 cmpcode = 'AUF'
148 AND elmlevel = 2
149 AND deldate IS NULL
150 AND endyear LIKE 0
151 AND code LIKE 'F%'
152 AND code NOT IN (
153 'FBE1190', 'FBE1191', 'FBE1192', 'FBE1193', 'FBE1194', 'FBE1195',
154 'FBG1252', 'FBG1253', 'FBG1254', 'FBG1255', 'FBG1256', 'FCA1436',
155 'FCA1438', 'FCM1319', 'FCM1320', 'FCM1321', 'FCM1322', 'FFR11182',
156 'FFR11183', 'FFR11184', 'FFR11185', 'FFR11187', 'FFR11188', 'FFR11189',
157 'FGA1077', 'FGE1008', 'FHT1323', 'FHT1324', 'FKH1408', 'FKH1409',
158 'FKH1410', 'FKH1411', 'FKH1412', 'FKH1413', 'FKH1414', 'FKH1415',
159 'FKH1416', 'FKH1417', 'FKH1418', 'FKH1419', 'FKH1420', 'FKH1421',
160 'FKH1422', 'FLA1530', 'FLA1531', 'FLA1532', 'FLA1533', 'FLA2231',
161 'FLB1389', 'FLB1390', 'FLB1391', 'FLB1392', 'FLB1393', 'FMD1311',
162 'FMD1312', 'FMU1107', 'FRO1215', 'FRO1216', 'FRO1217', 'FSN1146',
163 'FSN1147', 'FSN1148', 'FVN1B34', 'FVN1B35', 'FVN1B36', 'FVN1B38',
164 'FVN1B39', 'FVN2465', 'FVN2466', 'FVN2467', 'FVN4960', 'FVN4961',
165 'FVN4962', 'FVN4963', 'FVN4964', 'FVN4965', 'FVN4966', 'FVN4967',
166 'FVN4968', 'FVU1128', 'FVU1129', 'FVU1130', 'FVU1131', 'FVU1132',
167 'FVU1133', 'FVU1134', 'FVU1135', 'FVU1136', 'FKH1423', 'FKH1424',
168 'FKH1425', 'FLB1394', 'FSN1149', 'FVN1B40', 'FVN1B41', 'FVN1B42',
169 'FRO1218'
170 )
171 AND (
172 matchable <> 76
173 OR summary <> 102
174 OR split <> 49
175 OR (settle IS NOT NULL AND settle <> '')
176 OR statpay <> 84
177 )
178 AND year(adddate)>= 2011
179
180UNION
181
182SELECT
183 'ONGLET CONTROLE DES PAIEMENTS' categorie,
184 code,
185 [name],
186 adddate,
187 usrname,
188 CASE
189 WHEN paper <> 76 AND elec <> 76 THEN 'Cheque et virement n''a pas été selectionné'
190 WHEN enablepay = 0 THEN 'Activer paiement non cochée'
191 END anomalie
192FROM oas_element
193WHERE
194 cmpcode = 'AUF'
195 AND elmlevel = 2
196 AND deldate IS NULL
197 AND endyear LIKE 0
198 AND code LIKE 'F%'
199 AND code NOT IN ('FVU1125')
200 AND ((paper <> 76 AND elec <> 76 ) OR enablepay = 0)
201
202UNION
203
204SELECT
205 'ONGLET LISTE D''ADRESSES, UNE ADRESSE EST DIFFERENTE DE "PAIEMENT" ET "COMMANDE'
206 AS categorie,
207 code,
208 [name],
209 adddate,
210 usrname,
211 'categorie =' + catagory anomalie
212FROM
213 oas_element,
214 (
215 SELECT DISTINCT elmcode,catagory
216 FROM oas_elmaddrlist
217 WHERE
218 cmpcode = 'AUF'
219 AND elmcode LIKE 'F%'
0a9469c5 220 AND catagory NOT IN ('COMMANDE', 'PAIEMENT', 'LIVRAISON')
22ab1788
EMS
221 AND elmlevel= 2
222 ) adresse
223WHERE
224 cmpcode = 'AUF'
225 AND elmlevel = 2
226 AND deldate IS NULL
227 AND endyear LIKE 0
228 AND code LIKE 'F%'
229 AND code = elmcode
230
231UNION
232
233SELECT
234 'ONGLET CONDITIONS, LA CASE EST-CE UN ELEMENT CLIENT/FOURNISSEUR N''A PAS ETE COCHE'
235 AS categorie,
236 code,
237 [name],
238 adddate,
239 usrname,
240 '' anomalie
241FROM oas_element
242WHERE
243 cmpcode = 'AUF'
244 AND elmlevel = 2
245 AND deldate IS NULL
246 AND endyear LIKE 0
247 AND code LIKE 'F%'
248 AND custsuppacc <> 76
249
250UNION
251
252SELECT
253 'ONGLET CONDITIONS, ESCOMPTE AUTORISE, LA CASE "NON" N''A PAS ETE COCHE' categorie,
254 code,
255 [name],
256 adddate,
257 usrname,
258 '' anomalie
259FROM oas_element
260WHERE
261 cmpcode = 'AUF'
262 AND elmlevel = 2
263 AND deldate IS NULL
264 AND endyear LIKE 0
265 AND code LIKE 'F%'
266 AND discenable <> 46
22ab1788 267GO