Modifs diverses
[auf_coda_additions.git] / db / vues / auf_v_controleTiersOperations.sql
CommitLineData
22ab1788
EMS
1USE [coda-prod]
2GO
3SET ANSI_NULLS ON
4GO
5SET QUOTED_IDENTIFIER ON
6GO
7
8ALTER VIEW [dbo].[auf_v_controleTiersOperations]
9AS
10
11SELECT
12 'DANS L''ONGLET OPTION, LE SOUS-ANALYSE DOIT ETRE COCHE A "OUI"' 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 NOT LIKE 'c%'
25 AND code NOT LIKE 'F%'
26 AND code NOT LIKE 'd%'
27 AND code NOT LIKE 'o%'
28 AND code NOT LIKE 's%'
29 AND code NOT LIKE 't%'
30 AND code NOT LIKE '%L'
0a9469c5 31 AND code NOT LIKE 'MODELE-%'
22ab1788
EMS
32 AND code <> '8888ZZ000'
33 AND subanal <> 76 -- 76 = oui et 46 = non
34
35UNION
36
37SELECT
38 'DANS L''ONGLET IMPUTATION, N''A PAS ETE CREE COMME ELEMENT DE GAUCHE' categorie,
39 code,
40 [name],
41 adddate,
42 usrname,
43 CASE
44 WHEN accounttype = 9 THEN 'compte de bilan'
45 WHEN accounttype = 56 THEN 'compte de resultat'
46 END anomalie
47FROM oas_element
48WHERE
49 cmpcode = 'AUF'
50 AND elmlevel = 2
51 AND deldate IS NULL
52 AND endyear LIKE 0
53 AND code NOT LIKE 'c%'
54 AND code NOT LIKE 'F%'
55 AND code NOT LIKE 'd%'
56 AND code NOT LIKE 'o%'
57 AND code NOT LIKE 's%'
58 AND code NOT LIKE 't%'
59 AND code NOT LIKE '%L'
0a9469c5 60 AND code NOT LIKE 'MODELE-%'
22ab1788
EMS
61 AND code <> '8888ZZ000'
62 AND accounttype <> 40 -- 40 veut dire element de gauche
63
64UNION
65
66SELECT
67 'DANS L''ONGLET IMPUTATION,LA DESCRIPTION OBLIGATOIRE N''A PAS ETE COCHE' categorie,
68 code,
69 [name],
70 adddate,
71 usrname,
72 '' anomalie
73FROM oas_element
74WHERE
75 cmpcode = 'AUF'
76 AND elmlevel = 2
77 AND deldate IS NULL
78 AND endyear LIKE 0
79 AND code NOT LIKE 'c%'
80 AND code NOT LIKE 'F%'
81 AND code NOT LIKE 'd%'
82 AND code NOT LIKE 'o%'
83 AND code NOT LIKE 's%'
84 AND code NOT LIKE 't%'
85 AND code NOT LIKE '%L'
0a9469c5 86 AND code NOT LIKE 'MODELE-%'
22ab1788
EMS
87 AND code <> '8888ZZ000'
88 AND descr <> 76 -- 76 veut dire oui obligatoire
89
90UNION
91
92SELECT
93 'DANS L''ONGLET IMPUTATION, L''OPTION SYNTHESE DE COMPTE DOIT ETRE A "OPERATION"'
94 AS categorie,
95 code,
96 [name],
97 adddate,
98 usrname,
99 'accountsummary = ' + accountsummary anomalie
100FROM oas_element
101WHERE
102 cmpcode = 'AUF'
103 AND elmlevel = 2
104 AND deldate IS NULL
105 -- elements actif et on enleve tout ce qui est plus ancien que l'annee en cours
106 AND (endyear LIKE 0 OR YEAR(adddate) >= YEAR(GETDATE()))
107 AND code NOT LIKE 'c%'
108 AND code NOT LIKE 'F%'
109 AND code NOT LIKE 'd%'
110 AND code NOT LIKE 'o%'
111 AND code NOT LIKE 's%'
112 AND code NOT LIKE 't%'
113 AND code NOT LIKE '%L'
0a9469c5 114 AND code NOT LIKE 'MODELE-%'
22ab1788
EMS
115 AND code <> '8888ZZ000'
116 AND accountsummary <> 'OPERATION'
117
118UNION
119
120SELECT
121 'DANS L''ONGLET IMPUTATION, L''OPTION ELEMENT ASSOCIE PAR DEFAULT A ETE COCHE' categorie,
122 code,
123 [name],
124 adddate,
125 usrname,
126 'Niv. associes = ' + CONVERT(varchar(2),subslevel) anomalie
127FROM oas_element
128WHERE
129 cmpcode = 'AUF'
130 AND elmlevel = 2
131 AND deldate IS NULL
132 AND endyear LIKE 0
133 AND code NOT LIKE 'c%'
134 AND code NOT LIKE 'F%'
135 AND code NOT LIKE 'd%'
136 AND code NOT LIKE 'o%'
137 AND code NOT LIKE 's%'
138 AND code NOT LIKE 't%'
139 AND code NOT LIKE '%L'
0a9469c5 140 AND code NOT LIKE 'MODELE-%'
22ab1788
EMS
141 AND code <> '8888ZZ000'
142 AND subslevel <> 3 -- 3 = operation est associes a un element 3
143
144UNION
145
146SELECT
147 'L''ELEMENT 3 ASSOCIE NE CORRESPOND PAS AUX 7 PREMIERS CARACTERES DE L''ELEMENT 2'
148 AS categorie,
149 code,
150 [name],
151 adddate,
152 usrname,
153 'élément 3 associe = ' + subselm anomalie
154FROM oas_element
155WHERE
156 cmpcode = 'AUF'
157 AND elmlevel = 2
158 AND deldate IS NULL
159 AND endyear LIKE 0
160 AND code NOT LIKE 'c%'
161 AND code NOT LIKE 'F%'
162 AND code NOT LIKE 'd%'
163 AND code NOT LIKE 'o%'
164 AND code NOT LIKE 's%'
165 AND code NOT LIKE 't%'
166 AND code NOT LIKE '%L'
0a9469c5 167 AND code NOT LIKE 'MODELE-%'
22ab1788
EMS
168 AND code <> '8888ZZ000'
169 AND LEFT(code,7) <> rtrim(subselm)
170 AND len(code) < 11
171GO