Ajouter le pré-engagement dans l'interro budgétaire
[auf_coda_additions.git] / db / procedures / auf_p_intero_cumul_dep_rec.sql
1 USE [coda-prod]
2 GO
3
4 SET ANSI_NULLS OFF
5 GO
6 SET QUOTED_IDENTIFIER OFF
7 GO
8
9 ALTER PROCEDURE [dbo].[auf_p_intero_cumul_dep_rec]
10 @projet varchar(6),
11 @exercice int,
12 @groupe varchar(10)
13 AS
14 SET NOCOUNT ON
15 DECLARE @periode int
16 DECLARE @like_el3 varchar(20)
17
18 SELECT @periode = 12
19
20 IF isnumeric(@projet) = 1 OR len(@projet) > 2
21 SELECT @like_el3 = @projet+'%'
22 ELSE
23 SELECT @like_el3 = '%'+@projet
24
25 CREATE TABLE #cumuls (
26 el3 varchar(12),
27 typecumul varchar(12) ,
28 montant money null
29 )
30
31 -- DEPENSES BUDGET
32 INSERT INTO #cumuls
33 SELECT
34 el3,
35 'DEP-BUDGET' typecumul,
36 SUM(full_value) mont
37 FROM oas_balance, oas_grplist imp
38 WHERE
39 oas_balance.cmpcode = 'AUF'
40 AND imp.cmpcode = 'AUF'
41 AND yr = @exercice
42 AND period BETWEEN 1 AND @periode
43 AND curcode = 'EUR'
44 AND repbasis = 5
45 AND balcode ='BUDGET'
46 AND (el1 LIKE '2%' OR el1 LIKE '6%' OR el1 LIKE '9%')
47 -- on exclut les repartitions --
48 AND el1 NOT LIKE '96%'
49 AND el1 NOT LIKE '97%'
50 AND el1 NOT LIKE '98%'
51 AND el3 LIKE @like_el3
52 AND el3 = imp.code
53 AND imp.elmlevel = 3
54 AND imp.grpcode LIKE @groupe
55 GROUP BY el3
56
57 -- DEPENSES REEL
58 INSERT INTO #cumuls
59 SELECT
60 el3,
61 'DEP-REEL' typecumul,
62 SUM(full_value) mont
63 FROM oas_balance, oas_grplist imp
64 WHERE
65 oas_balance.cmpcode = 'AUF'
66 AND imp.cmpcode = 'AUF'
67 AND yr = @exercice
68 AND period BETWEEN 1 AND @periode
69 AND curcode = 'EUR'
70 AND repbasis = 5
71 AND balcode ='REEL'
72 AND (el1 LIKE '2%' OR el1 LIKE '6%' OR el1 LIKE '9%')
73 -- on exclut les repartitions
74 AND el1 NOT LIKE '96%'
75 AND el1 NOT LIKE '97%'
76 AND el1 NOT LIKE '98%'
77 AND el3 LIKE @like_el3
78 AND el3 = imp.code
79 AND imp.elmlevel = 3
80 AND imp.grpcode LIKE @groupe
81 GROUP BY el3
82
83 -- RECETTES BUDGET
84 INSERT INTO #cumuls
85 SELECT
86 el3,
87 'REC-BUDGET' typecumul,
88 SUM(full_value) mont
89 FROM oas_balance, oas_grplist imp
90 WHERE
91 oas_balance.cmpcode = 'AUF'
92 AND imp.cmpcode = 'AUF'
93 AND yr = @exercice
94 AND period BETWEEN 1 AND @periode
95 AND curcode = 'EUR'
96 AND repbasis = 5
97 AND balcode ='BUDGET'
98 AND (el1 LIKE '1%' OR el1 LIKE '7%')
99 AND el3 LIKE @like_el3
100 AND el3 = imp.code
101 AND imp.elmlevel = 3
102 AND imp.grpcode LIKE @groupe
103 GROUP BY el3
104
105 -- RECETTES REEL
106 INSERT INTO #cumuls
107 SELECT
108 el3,
109 'REC-REEL' typecumul,
110 SUM(full_value) mont
111 FROM oas_balance, oas_grplist imp
112 WHERE
113 oas_balance.cmpcode = 'AUF'
114 AND imp.cmpcode = 'AUF'
115 AND yr = @exercice
116 AND period BETWEEN 1 AND @periode
117 AND curcode = 'EUR'
118 AND repbasis = 5
119 AND balcode ='REEL'
120 AND (el1 LIKE '1%' OR el1 LIKE '7%')
121 AND el3 LIKE @like_el3
122 AND el3 = imp.code
123 AND imp.elmlevel = 3
124 AND imp.grpcode LIKE @groupe
125 GROUP BY el3
126
127 -- PRE-ENGAGEMENT --
128 INSERT INTO #cumuls
129 SELECT
130 el3,
131 'PRENGAGEMENT' typecumul,
132 SUM(full_value) mont
133 FROM oas_balance, oas_grplist imp
134 WHERE
135 oas_balance.cmpcode = 'AUF'
136 AND imp.cmpcode = 'AUF'
137 AND curcode = 'EUR'
138 AND repbasis = 5
139 AND balcode = 'PRENGAGEMENT'
140 AND el1 NOT LIKE '4%'
141 AND el1 NOT LIKE '96%'
142 AND el1 NOT LIKE '97%'
143 AND el1 NOT LIKE '98%'
144 AND el3 LIKE @like_el3
145 AND el3 = imp.code
146 AND imp.elmlevel = 3
147 AND imp.grpcode LIKE @groupe
148 GROUP BY el3
149
150 -- ENGAGEMENT --
151 INSERT INTO #cumuls
152 SELECT
153 el3,
154 'ENGAGEMENT' typecumul,
155 SUM(full_value) mont
156 FROM oas_balance, oas_grplist imp
157 WHERE
158 oas_balance.cmpcode = 'AUF'
159 AND imp.cmpcode = 'AUF'
160 AND curcode = 'EUR'
161 AND repbasis = 5
162 AND balcode = 'ENGAGEMENT'
163 AND el1 NOT LIKE '4%'
164 AND el1 NOT LIKE '96%'
165 AND el1 NOT LIKE '97%'
166 AND el1 NOT LIKE '98%'
167 AND el3 LIKE @like_el3
168 AND el3 = imp.code
169 AND imp.elmlevel = 3
170 AND imp.grpcode LIKE @groupe
171 GROUP BY el3
172
173 SELECT
174 cumul.el3 projetPoste,
175 e.sname nomProjet,
176 e.[name] nomPoste,
177 SUM(ISNULL(budget_depenses.montant,0)) budget_depense,
178 SUM(ISNULL(budget_recettes.montant,0)) budget_recette,
179 SUM(ISNULL(reel_recettes.montant,0)) reel_recette,
180 SUM(ISNULL(reel_depenses.montant,0)) reel_depense,
181 SUM(ISNULL(engages.montant,0)) engage,
182 SUM(ISNULL(preengagement.montant,0)) preengagement
183 FROM
184 (
185 SELECT DISTINCT el3
186 FROM #cumuls
187 ) cumul
188 LEFT JOIN (
189 SELECT el3, typecumul, montant
190 FROM #cumuls
191 ) budget_depenses
192 ON cumul.el3 = budget_depenses.el3
193 AND budget_depenses.typecumul = 'DEP-BUDGET'
194 LEFT JOIN (
195 SELECT el3, typecumul, montant
196 FROM #cumuls
197 ) budget_recettes
198 ON cumul.el3 = budget_recettes.el3
199 AND budget_recettes.typecumul = 'REC-BUDGET'
200 LEFT JOIN (
201 SELECT el3, typecumul, montant
202 FROM #cumuls
203 ) reel_recettes
204 ON cumul.el3 = reel_recettes.el3
205 AND reel_recettes.typecumul = 'REC-REEL'
206 LEFT JOIN (
207 SELECT el3, typecumul, montant
208 FROM #cumuls
209 ) reel_depenses
210 ON cumul.el3 = reel_depenses.el3
211 AND reel_depenses.typecumul = 'DEP-REEL'
212 LEFT JOIN (
213 SELECT el3, typecumul, montant
214 FROM #cumuls
215 ) engages
216 ON cumul.el3 = engages.el3
217 AND engages.typecumul = 'ENGAGEMENT'
218 LEFT JOIN (
219 SELECT el3, typecumul, montant
220 FROM #cumuls
221 ) preengagement
222 ON cumul.el3 = preengagement.el3
223 AND preengagement.typecumul = 'PRENGAGEMENT'
224 LEFT JOIN oas_element e
225 ON cumul.el3 = e.code
226 AND e.cmpcode = 'AUF'
227 AND e.elmlevel = 3
228 WHERE
229 (@exercice < 2014 AND LEN(cumul.el3) = 7 AND LEFT(cumul.el3,2) <> '95')
230 OR (@exercice >= 2014 AND LEN(cumul.el3) = 8 AND LEFT(cumul.el3,1) <> 'P')
231 GROUP BY cumul.el3, e.sname, e.[name]
232 ORDER BY cumul.el3
233 GO