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