Modifications diverses
[auf_coda_additions.git] / db / vues / auf_v_controleBoursiers.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_controleBoursiers]
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 (
25 endyear = 0
26 OR (
27 endyear = YEAR(GETDATE())
28 AND endperiod >= MONTH(GETDATE())
29 )
30 OR endyear > YEAR(GETDATE())
31 )
32 AND code LIKE '%L'
33 AND len(rtrim(ltrim(code))) IN (11, 14)
34 AND subanal <> 76 -- 76 = oui et 46 = non
35
36 UNION
37
38 SELECT
39 'DANS L''ONGLET IMPUTATION, N''A PAS ETE CREE COMME ELEMENT DE GAUCHE' categorie,
40 code,
41 [name],
42 adddate,
43 usrname,
44 CASE
45 WHEN accounttype = 9 THEN 'compte de bilan'
46 WHEN accounttype = 56 THEN 'compte de resultat'
47 END anomalie
48 FROM oas_element
49 WHERE
50 cmpcode = 'AUF'
51 AND elmlevel = 2
52 AND deldate IS NULL
53 AND (
54 endyear = 0
55 OR (
56 endyear = YEAR(GETDATE())
57 AND endperiod >= MONTH(GETDATE())
58 )
59 OR endyear > YEAR(GETDATE())
60 )
61 AND code LIKE '%L'
62 AND len(rtrim(ltrim(code))) IN (11, 14)
63 AND accounttype <> 40 -- 40 veut dire element de gauche
64
65 UNION
66
67 SELECT
68 'DANS L''ONGLET IMPUTATION,LA DESCRIPTION OBLIGATOIRE N''A PAS ETE COCHE' categorie,
69 code,
70 [name],
71 adddate,
72 usrname,
73 '' anomalie
74 FROM oas_element
75 WHERE
76 cmpcode = 'AUF'
77 AND elmlevel = 2
78 AND deldate IS NULL
79 AND (
80 endyear = 0
81 OR (
82 endyear = YEAR(GETDATE())
83 AND endperiod >= MONTH(GETDATE())
84 )
85 OR endyear > YEAR(GETDATE())
86 )
87 AND code LIKE '%L'
88 AND len(rtrim(ltrim(code))) IN (11, 14)
89 AND descr <> 76 -- 76 veut dire oui obligatoire
90
91 UNION
92
93 SELECT
94 'DANS L''ONGLET IMPUTATION, L''OPTION ELEMENT ASSOCIE PAR DEFAULT A ETE COCHE' categorie,
95 code,
96 [name],
97 adddate,
98 usrname,
99 'Niv. associes = ' + CONVERT(varchar(2),subslevel) anomalie
100 FROM oas_element
101 WHERE
102 cmpcode = 'AUF'
103 AND elmlevel = 2
104 AND deldate IS NULL
105 AND (
106 endyear = 0
107 OR (
108 endyear = YEAR(GETDATE())
109 AND endperiod >= MONTH(GETDATE())
110 ) OR endyear > YEAR(GETDATE())
111 )
112 AND code LIKE '%L'
113 AND len(rtrim(ltrim(code))) IN (11, 14)
114 AND subslevel <> 3 -- 3 = Fournisseur est associes a un autre element 3
115
116 UNION
117
118 SELECT
119 'DANS L''ONGLET LETTRAGE ET STATUTS ( PAIEMENTS AUTOMATIQUES,STATUTS PAR DEFAUT)'
120 AS categorie,
121 code,
122 [name],
123 adddate,
124 usrname,
125 CASE
126 WHEN matchable <> 76 THEN 'élém lettrage non coché'
127 WHEN statpay <> 84 THEN 'statut paiement non disponible'
128 END anomalie
129 FROM oas_element
130 WHERE
131 cmpcode = 'AUF'
132 AND elmlevel = 2
133 AND deldate IS NULL
134 AND (
135 endyear = 0
136 OR (
137 endyear = YEAR(GETDATE())
138 AND endperiod >= MONTH(GETDATE())
139 )
140 OR endyear > YEAR(GETDATE())
141 )
142 AND code LIKE '%L'
143 AND len(rtrim(ltrim(code))) IN (11, 14)
144 AND (matchable <> 76 OR statpay <> 84)
145
146 UNION
147
148 SELECT
149 'ONGLET CONTROLE DES PAIEMENTS' categorie,
150 code,
151 [name],
152 adddate,
153 usrname,
154 CASE
155 WHEN paper <> 76 AND elec <> 76 THEN 'cheque et virement n''a pas été selectionné'
156 END anomalie
157 FROM oas_element
158 WHERE
159 cmpcode = 'AUF'
160 AND elmlevel = 2
161 AND deldate IS NULL
162 AND (
163 endyear = 0
164 OR (
165 endyear = YEAR(GETDATE())
166 AND endperiod >= MONTH(GETDATE())
167 )
168 OR endyear > YEAR(GETDATE())
169 )
170 AND code LIKE '%L'
171 AND len(rtrim(ltrim(code))) IN (11, 14)
172 AND paper <> 76
173 AND elec <> 76
174
175 UNION
176
177 SELECT
178 'TAILLE DE L''ELEMENT' categorie,
179 code,
180 [name],
181 adddate,
182 usrname,
183 '' anomalie
184 FROM oas_element
185 WHERE
186 cmpcode = 'AUF'
187 AND elmlevel = 2
188 AND deldate IS NULL
189 AND code LIKE '%L'
190 AND (
191 endyear = 0
192 OR (
193 endyear = YEAR(GETDATE())
194 AND endperiod >= MONTH(GETDATE())
195 )
196 OR endyear > YEAR(GETDATE())
197 )
198 AND LEN(RTRIM(LTRIM(code))) NOT IN (11, 14)
199
200 GO