Modifications 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
4e57e206 24 AND code LIKE '_____________'
22ab1788
EMS
25 AND subanal <> 76 -- 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
4e57e206 45 AND code LIKE '_____________'
22ab1788
EMS
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
4e57e206 63 AND code LIKE '_____________'
22ab1788
EMS
64 AND descr <> 76 -- 76 veut dire oui obligatoire
65
66UNION
67
68SELECT
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
76FROM oas_element
77WHERE
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()))
4e57e206 83 AND code LIKE '_____________'
22ab1788
EMS
84 AND accountsummary <> 'OPERATION'
85
86UNION
87
88SELECT
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
95FROM oas_element
96WHERE
97 cmpcode = 'AUF'
98 AND elmlevel = 2
99 AND deldate IS NULL
100 AND endyear LIKE 0
4e57e206 101 AND code LIKE '_____________'
22ab1788
EMS
102 AND subslevel <> 3 -- 3 = operation est associes a un element 3
103
104UNION
105
106SELECT
4e57e206 107 'L''ELEMENT 3 ASSOCIE NE CORRESPOND PAS AUX 8 PREMIERS CARACTERES DE L''ELEMENT 2'
22ab1788
EMS
108 AS categorie,
109 code,
110 [name],
111 adddate,
112 usrname,
113 'élément 3 associe = ' + subselm anomalie
114FROM oas_element
115WHERE
116 cmpcode = 'AUF'
117 AND elmlevel = 2
118 AND deldate IS NULL
119 AND endyear LIKE 0
4e57e206
EMS
120 AND code LIKE '_____________'
121 AND LEFT(code,8) <> rtrim(subselm)
22ab1788 122GO