Modifications diverses
[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 LIKE '_____________'
25 AND subanal <> 76 -- 76 = oui et 46 = non
26
27 UNION
28
29 SELECT
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
39 FROM oas_element
40 WHERE
41 cmpcode = 'AUF'
42 AND elmlevel = 2
43 AND deldate IS NULL
44 AND endyear LIKE 0
45 AND code LIKE '_____________'
46 AND accounttype <> 40 -- 40 veut dire element de gauche
47
48 UNION
49
50 SELECT
51 'DANS L''ONGLET IMPUTATION,LA DESCRIPTION OBLIGATOIRE N''A PAS ETE COCHE' categorie,
52 code,
53 [name],
54 adddate,
55 usrname,
56 '' anomalie
57 FROM oas_element
58 WHERE
59 cmpcode = 'AUF'
60 AND elmlevel = 2
61 AND deldate IS NULL
62 AND endyear LIKE 0
63 AND code LIKE '_____________'
64 AND descr <> 76 -- 76 veut dire oui obligatoire
65
66 UNION
67
68 SELECT
69 'DANS L''ONGLET IMPUTATION, L''OPTION SYNTHESE DE COMPTE DOIT ETRE A "OPERATION"'
70 AS categorie,
71 code,
72 [name],
73 adddate,
74 usrname,
75 'accountsummary = ' + accountsummary anomalie
76 FROM oas_element
77 WHERE
78 cmpcode = 'AUF'
79 AND elmlevel = 2
80 AND deldate IS NULL
81 -- elements actif et on enleve tout ce qui est plus ancien que l'annee en cours
82 AND (endyear LIKE 0 OR YEAR(adddate) >= YEAR(GETDATE()))
83 AND code LIKE '_____________'
84 AND accountsummary <> 'OPERATION'
85
86 UNION
87
88 SELECT
89 'DANS L''ONGLET IMPUTATION, L''OPTION ELEMENT ASSOCIE PAR DEFAULT A ETE COCHE' categorie,
90 code,
91 [name],
92 adddate,
93 usrname,
94 'Niv. associes = ' + CONVERT(varchar(2),subslevel) anomalie
95 FROM oas_element
96 WHERE
97 cmpcode = 'AUF'
98 AND elmlevel = 2
99 AND deldate IS NULL
100 AND endyear LIKE 0
101 AND code LIKE '_____________'
102 AND subslevel <> 3 -- 3 = operation est associes a un element 3
103
104 UNION
105
106 SELECT
107 'L''ELEMENT 3 ASSOCIE NE CORRESPOND PAS AUX 8 PREMIERS CARACTERES DE L''ELEMENT 2'
108 AS categorie,
109 code,
110 [name],
111 adddate,
112 usrname,
113 'élément 3 associe = ' + subselm anomalie
114 FROM oas_element
115 WHERE
116 cmpcode = 'AUF'
117 AND elmlevel = 2
118 AND deldate IS NULL
119 AND endyear LIKE 0
120 AND code LIKE '_____________'
121 AND LEFT(code,8) <> rtrim(subselm)
122 GO