10b471f8a6444b9650d97fda38497c0861927188
[auf_coda_additions.git] / db / procedures / auf_p_etat_resultats_p4_ex_precedent_2011.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_etat_resultats_p4_ex_precedent_2011]
10 @exercice int,
11 @periode int,
12 @code varchar(14),
13 @retraitement varchar(3)
14
15 -- @code
16 -- 1-AUF : global AUF
17 -- 2-GRI : groupé par implantation
18 -- 3-GRB : groupé par bureau
19 -- 4-BXX : Bureau BXX
20 -- 5-XXX : implantation XXX
21 -- @retraitement: Oui/Non
22
23 AS
24
25 DECLARE @lib_periode varchar(100)
26 DECLARE @categorie varchar(1)
27 DECLARE @publication varchar(3)
28
29 SET NOCOUNT ON
30
31 --- extraire la catégorie de 'état
32
33 SELECT @categorie = LEFT(ltrim(@code),1)
34
35 IF ( @exercice = 0 ) BEGIN
36 -- pour permettre aux états sur IWR de récuperer l'éxercice et la période courants
37 SELECT @publication = 'oui'
38
39 SELECT
40 @exercice = num1,
41 @periode = num2,
42 @lib_periode = texte4
43 FROM auf_iwrconfig
44 WHERE code_etat = 'ICG'
45 END
46 ELSE BEGIN
47 SELECT @publication = 'non'
48
49 SELECT @lib_periode = texte4
50 FROM auf_iwrconfig
51 WHERE code_etat = 'ICG'
52 END
53
54
55 -- activer pour génération des états PDF
56 -- SELECT @publication = 'oui'
57
58 CREATE TABLE #cumul_ecritures (
59 imp_bur varchar(50),
60 yr int,
61 period int ,
62 balcode varchar(12),
63 el1 varchar(20),
64 el3 varchar(20),
65 full_value money
66 )
67
68 IF ltrim(rtrim(@code)) = '1-AUF' BEGIN
69 -- état global AUF
70
71 INSERT INTO #cumul_ecritures
72 SELECT
73 g.grpcode ,
74 yr,
75 period,
76 balcode,
77 el1,
78 el3,
79 full_value
80 FROM oas_balance, oas_grplist g
81 WHERE
82 oas_balance.cmpcode = 'AUF'
83 AND (
84 (yr = @exercice AND period BETWEEN 1 AND @periode)
85 OR (yr = @exercice-1 AND period BETWEEN 1 AND @periode)
86 )
87 AND curcode = 'EUR'
88 AND repbasis = 5
89 AND full_value <> 0
90 AND balcode IN ('BUDGET','BUDGET-MENS','REEL')
91 AND oas_balance.el3 = g.code
92 AND g.grpcode LIKE 'I%'
93 AND g.elmlevel = 3
94 AND g.cmpcode ='AUF'
95 END
96
97 IF ( ltrim(rtrim(@code)) = '3-GRB') BEGIN
98 -- groupé par Bureau
99 INSERT INTO #cumul_ecritures
100 SELECT
101 bur,
102 yr,
103 period,
104 balcode,
105 el1,
106 el3,
107 full_value
108 FROM
109 oas_balance b,
110 oas_grplist g,
111 (
112 SELECT DISTINCT codbur, bur
113 FROM auf_v_gen_implantations
114 ) bureau
115 WHERE
116 b.cmpcode = 'AUF'
117 AND (
118 (b.yr = @exercice AND b.period BETWEEN 1 AND @periode)
119 OR (b.yr = @exercice-1 AND b.period BETWEEN 1 AND @periode)
120 )
121 AND b.curcode = 'EUR'
122 AND b.repbasis = 5
123 AND b.full_value <> 0
124 AND b.balcode IN ('BUDGET','BUDGET-MENS','REEL')
125 AND b.el3 = g.code
126 AND g.grpcode LIKE 'B%'
127 AND g.elmlevel = 3
128 AND g.cmpcode ='AUF'
129 AND g.grpcode = bureau.codbur
130
131 IF @exercice = 2005
132 INSERT INTO #cumul_ecritures
133 SELECT 'Services centraux', 2004, 12, 'REEL', '61850', '4101KU', -23595.4800
134 END
135
136 IF (LEFT(LTRIM(@code),1) = '4') BEGIN
137 -- état pour le Bureau @code
138 SELECT @code = rtrim(substring(ltrim(@code),3,5))
139
140 INSERT INTO #cumul_ecritures
141 SELECT bur, yr, period, balcode, el1, el3, full_value
142 FROM
143 oas_balance b,
144 oas_grplist g,
145 (
146 SELECT DISTINCT codbur, bur
147 FROM auf_v_gen_implantations
148 ) bureau
149 WHERE
150 b.cmpcode = 'AUF'
151 AND (
152 (b.yr = @exercice AND b.period BETWEEN 1 AND @periode)
153 OR (b.yr = @exercice-1 AND b.period BETWEEN 1 AND @periode)
154 )
155 AND b.curcode = 'EUR'
156 AND b.repbasis = 5
157 AND b.full_value <> 0
158 AND b.balcode IN ('BUDGET','BUDGET-MENS','REEL')
159 AND b.el3 = g.code
160 AND g.grpcode = @code
161 AND g.elmlevel = 3
162 AND g.cmpcode ='AUF'
163 AND g.grpcode = bureau.codbur
164
165 IF (@code = 'BSC' AND ( @exercice = 2004 OR @exercice = 2005))
166 INSERT INTO #cumul_ecritures
167 SELECT 'Services centraux', 2004, 12, 'REEL', '61850', '4101KU', -23595.4800
168 END
169
170 IF (LTRIM(RTRIM(@code)) = '2-GRI') BEGIN
171
172 -- groupé par Implantation
173 INSERT INTO #cumul_ecritures
174 SELECT
175 CASE
176 WHEN bureau.codbur = 'BAN' THEN 'BA' + '-'+ bureau.name
177 ELSE bureau.codbur + '-'+ bureau.name
178 END [name],
179 yr,
180 period,
181 balcode,
182 el1,
183 el3,
184 full_value
185 FROM
186 oas_balance b,
187 oas_grplist g,
188 auf_v_gen_implantations bureau
189 WHERE
190 b.cmpcode = 'AUF'
191 AND (
192 (b.yr = @exercice AND b.period BETWEEN 1 AND @periode)
193 OR (b.yr = @exercice-1 AND b.period BETWEEN 1 AND @periode)
194 )
195 AND b.curcode = 'EUR'
196 AND b.repbasis = 5
197 AND b.full_value <> 0
198 AND b.balcode in ('BUDGET', 'BUDGET-MENS', 'REEL')
199 AND b.el3 = g.code
200 AND g.grpcode LIKE 'I%'
201 AND g.elmlevel = 3
202 AND g.cmpcode ='AUF'
203 AND bureau.code = right(rtrim(g.grpcode),3)
204
205 IF (@exercice = 2004 OR @exercice = 2005)
206 INSERT INTO #cumul_ecritures
207 SELECT 'Centrale Paris', 2004, 12, 'REEL', '61850', '4101KU', -23595.4800
208 END
209
210 IF (LEFT(ltrim(@code),1) = '5') BEGIN
211
212 -- pour l'implantation @code
213 SELECT @code = rtrim(substring(ltrim(@code),3,3))
214
215 INSERT INTO #cumul_ecritures
216 SELECT
217 bureau.name,
218 yr,
219 period,
220 balcode,
221 el1,
222 el3,
223 full_value
224 FROM
225 oas_balance b,
226 oas_grplist g,
227 auf_v_gen_implantations bureau
228 WHERE
229 b.cmpcode = 'AUF'
230 AND (
231 (b.yr = @exercice AND b.period BETWEEN 1 AND @periode)
232 OR (b.yr = @exercice-1 AND b.period BETWEEN 1 AND @periode)
233 )
234 AND b.curcode = 'EUR'
235 AND b.repbasis = 5
236 AND b.full_value <> 0
237 AND b.balcode in ('BUDGET','BUDGET-MENS','REEL')
238 AND b.el3 = g.code
239 AND g.grpcode = 'I'+@code
240 AND g.elmlevel = 3
241 AND g.cmpcode ='AUF'
242 AND bureau.code = @code
243
244 IF (@code = 'FR1' AND ( @exercice = 2004 OR @exercice = 2005 ))
245 INSERT INTO #cumul_ecritures
246 SELECT 'Centrale Paris', 2004, 12, 'REEL','61850','4101KU',-23595.4800
247 END
248
249 CREATE TABLE #etat_section (
250 imp_bur varchar(50),
251 exercice int,
252 periode int,
253 cumul varchar(20),
254 groupe varchar(100),
255 sgroupe varchar(20),
256 montant money,
257 libelle varchar(100)
258 )
259
260 -- section PRODUITS
261
262 INSERT INTO #etat_section
263 SELECT
264 imp_bur,
265 b.yr AS exercice,
266 b.period AS periode,
267 b.balcode AS cumul,
268 '010-Excédents des produits (charges) avant retraitements' AS groupe1,
269 '000-PRODUITS' AS groupe2,
270 b.full_value * -1 AS solde, -- Corriger pour debit / crédit !!!!!!!!!!
271 CASE
272 WHEN
273 b.el1 IN ('75100', '70500')
274 OR b.el1 LIKE '756%'
275 OR b.el1 LIKE '708%'
276 THEN '110-Ressources propres'
277 WHEN b.el1 LIKE '740%' THEN '120-Contributions gouvernementales'
278 WHEN
279 b.el1 LIKE '745%'
280 OR b.el1 = '74998' OR b.el1 = '74999'
281 THEN '130-Contributions contractuelles'
282 WHEN
283 b.el1 LIKE '78%'
284 OR b.el1='75700'
285 OR (
286 b.el1='11100'
287 AND rtrim(ltrim(el3)) <> ''
288 AND el3 NOT IN (
289 '90003GR', '91022GR', '91203GR',
290 '91024GR','91025GR','91032GR', '9003GR', '9122GR', '9123GR',
291 '9124GR','9125GR','9132GR'
292 )
293 )
294 OR b.el3 IN (
295 '91022GR', '91024GR', '91023GR', '91032GR','91025GR', '9122GR',
296 '9124GR', '9123GR', '9132GR','9125GR'
297 )
298 THEN '140-Apports, autres recettes, provisions'
299 WHEN
300 b.el1 LIKE '76%'
301 OR b.el1 LIKE '77%'
302 THEN '150-Produits financiers et exceptionnels'
303 WHEN b.el1 = '75800' THEN '160-Exploitation'
304 WHEN b.el1 LIKE '87%' THEN '170-Valeurs des biens et services à titre gratuit'
305 END AS sgroupe
306 FROM #cumul_ecritures b
307 WHERE -- Filtres globaux
308 (
309 b.el1 LIKE '7%'
310 OR b.el1 LIKE '87%'
311 OR (
312 b.el1='11100'
313 AND rtrim(ltrim(el3)) <> ''
314 AND el3 NOT IN (
315 '90003GR', '91022GR', '91023GR', '91024GR','9125GR','9132GR',
316 '9003GR', '9122GR', '9123GR', '9124GR','9125GR','9132GR'
317 )
318 )
319 OR b.el3 IN (
320 '91022GR', '91024GR', '91023GR', '91032GR','91025GR', '9122GR',
321 '9124GR', '9123GR', '9132GR','9125GR'
322 )
323 )
324 AND b.el1 <> '77200'
325
326 -- section CHARGES
327
328 INSERT INTO #etat_section
329 SELECT
330 imp_bur,
331 b.yr AS exercice,
332 b.period AS periode,
333 b.balcode AS cumul,
334 '010-Excédents des produits (charges) avant retraitements' AS groupe1,
335 '001-CHARGES' AS groupe2,
336 b.full_value * -1 AS solde,
337 CASE
338 -- Attention séquence
339 WHEN b.el1 LIKE '861%' THEN '190-Valeurs des biens et services à titre gratuit'
340 WHEN grp1.grpcode = 'HX' OR grpG.grpcode = 'GX' THEN
341 -- Attention séquence
342 CASE
343 WHEN b.el3 LIKE '95%' THEN '010-Frais de personnel'
344 WHEN b.el3 LIKE '96%' THEN '020-Frais généraux'
345 WHEN
346 b.el1 LIKE '9%'
347 AND (b.el3 LIKE '99099%' OR b.el3 LIKE '9999%')
348 THEN '120-Actions Réserve'
349 WHEN
350 b.el1 LIKE '666%'
351 AND (b.el3 LIKE '91006%' OR b.el3 LIKE '9106%')
352 THEN '180-Perte (gain) de change'
353 WHEN
354 b.el3 LIKE '91006%'
355 OR b.el3 LIKE '9106%'
356 THEN '160-Charges financières'
357 WHEN
358 b.el1 LIKE '67%'
359 AND b.el1 <> '67500'
360 AND (b.el3 LIKE '91007%' OR b.el3 LIKE '9107%')
361 THEN '170-Charges exceptionnelles'
362 WHEN
363 b.el1 LIKE '6817%'
364 AND b.el3 LIKE '91002GR%'
365 THEN '155-Charges cotisations'
366 ELSE '150-Autres charges, provisions'
367 END
368 WHEN grp1.grpcode = 'H1' THEN '03A-' + space(8) + lg.[name] + '-' + lg.code
369 WHEN grp1.grpcode='H2' THEN '04B-' +space(8)+ lg.[name]+'-'+lg.code
370 WHEN grp1.grpcode='H3' THEN '05C-' +space(8)+ lg.[name]+'-'+lg.code
371 WHEN grp1.grpcode='H4' THEN '06D-' +space(8)+ lg.[name]+'-'+lg.code
372 WHEN grp1.grpcode='H5' THEN '07E-' +space(8)+ lg.[name]+'-'+lg.code
373 WHEN grp1.grpcode='H6' THEN '08V-' +space(8)+ lg.[name]+'-'+lg.code
374 WHEN grp1.grpcode='H7' THEN '09W-' +space(8)+ lg.[name]+'-'+lg.code
375 WHEN grp1.grpcode='H8' THEN '10X-' +space(8)+ lg.[name]+'-'+lg.code
376 WHEN grpG.grpcode='G1' THEN '03A-' +space(8)+ lg.[name]
377 WHEN grpG.grpcode='G2' THEN '04B-' +space(8)+ lg.[name]
378 WHEN grpG.grpcode='G3' THEN '05C-' +space(8)+ lg.[name]
379 WHEN grpG.grpcode='G4' THEN '06D-' +space(8)+ lg.[name]
380 WHEN grpG.grpcode='G5' THEN '07E-' +space(8)+ lg.[name]
381 WHEN grpG.grpcode='G6' THEN '08V-' +space(8)+ lg.[name]
382 WHEN grp1.grpcode='H0' OR grpG.grpcode='G0' THEN '130-Actions institutionnelles'
383 WHEN grp1.grpcode='HS' OR grpG.grpcode='GS' THEN '140-Actions administratives'
384 END AS sgroupe
385 FROM
386 oas_group lg,
387 #cumul_ecritures b
388 LEFT OUTER JOIN oas_grplist grp1
389 ON grp1.grpcode LIKE 'H%'
390 AND grp1.elmlevel = 3
391 AND grp1.cmpcode = 'AUF'
392 AND b.el3 = grp1.code
393 LEFT OUTER JOIN oas_grplist grp2
394 ON grp2.grpcode LIKE 'L%'
395 AND grp2.elmlevel = 3
396 AND grp2.cmpcode = 'AUF'
397 AND b.el3 = grp2.code
398 LEFT OUTER JOIN oas_grplist grpG
399 ON grpG.grpcode LIKE 'G%'
400 AND grpG.elmlevel = 3
401 AND grpG.cmpcode = 'AUF'
402 AND b.el3 = grpG.code
403 WHERE -- Filtres globaux
404 (
405 b.el1 LIKE '6%'
406 OR b.el1 LIKE '86%'
407 OR b.el1 LIKE '2%'
408 OR b.el1 LIKE '1%'
409 OR b.el1 LIKE '9%'
410 )
411 AND b.el1 NOT LIKE '96%'
412 AND b.el1 NOT LIKE '97%'
413 AND b.el1 NOT LIKE '98%'
414 AND b.el3 NOT IN (
415 '91022GR', '91023GR', '91024GR', '91032GR','91025GR', '9122GR',
416 '9123GR', '9124GR', '9132GR','9125GR'
417 )
418 AND NOT (
419 b.el1='11100'
420 AND rtrim(ltrim(el3)) <> ''
421 AND el3 NOT IN (
422 '90003GR', '91022GR', '91023GR', '91024GR','91025GR','91032GR',
423 '9003GR', '9122GR', '9123GR', '9124GR','9125GR','9132GR'
424 )
425 )
426 AND b.el3 NOT LIKE '91009%'
427 AND b.el3 NOT LIKE '9109%'
428 AND (grp2.grpcode = lg.code OR grpG.grpcode = lg.code)
429 AND lg.groupwhat = 3
430 AND lg.cmpcode = 'AUF'
431 AND b.el3 NOT LIKE '91008%'
432 AND b.el3 NOT LIKE '9108%'
433
434
435 -- section RETRAITEMENTS
436 -- mettre @publication <> 'oui' dans prod
437
438 IF @retraitement = 'Oui' BEGIN
439 INSERT INTO #etat_section
440 SELECT
441 imp_bur,
442 yr AS exercice,
443 period AS periode,
444 balcode AS cumul,
445 '020-E' AS groupe1,
446 '002-RETRAITEMENTS' AS groupe2,
447 CASE
448 WHEN (el1 LIKE '6%' AND el3 != '9003GR') OR el1 LIKE '77200' THEN full_value *-1
449 ELSE full_value
450 END AS Solde,
451 CASE
452 WHEN
453 (el1 LIKE '11%' AND el3 <> '')
454 AND NOT (el1 LIKE '11%' AND el3 IN ('90003GR', '91031GR', '91025GR'))
455 THEN '510-Moins : apports'
456 WHEN el1 LIKE '2%' THEN '020-Plus : investissements en immobilisations'
457 WHEN
458 el3 IN ('90003GR', '91031GR', '91025GR')
459 THEN '010-Plus : affectation AG et au fonds de réserve'
460 WHEN el1 LIKE '16%' THEN '030-Plus : remboursement de la dette à long terme'
461 ELSE '550-Moins : amortis. immob. et pertes sur disposition'
462 END AS sgroupe
463 FROM #cumul_ecritures
464 WHERE -- Filtres globaux
465 (
466 (el1 = '67500' AND el3 LIKE '91008%')
467 OR (el1 = '68152')
468 OR el1 LIKE '6811%'
469 OR el1 LIKE '1%'
470 OR el1 LIKE '2%'
471 OR (el1 = '77200' AND el3 LIKE '91008%')
472 )
473 AND el1 NOT LIKE '28%'
474 AND el1 NOT LIKE '96%'
475 AND el1 NOT LIKE '97%'
476 AND el1 NOT LIKE '98%'
477 AND NOT ( el1 LIKE '2%' AND el3 LIKE '91008%' )
478 AND NOT ( el1 = '21820' AND el3 LIKE '91%')
479 AND NOT ( el1 = '68114' AND el3 LIKE '96%')
480 AND el3 NOT LIKE '91009%'
481 AND NOT ( el3 = '91033GR' AND yr =2005 )
482 AND NOT (
483 yr = 2004 AND
484 period = 12 AND
485 imp_bur in ('BXX','IXX1') AND
486 el1 LIKE '21%'
487 )
488 AND NOT ( el1 = '67500' AND ( el3 LIKE '96%' OR el3 ='30040QI'))
489 END
490
491 -- construire les colonne de l'état du résultats IWR
492
493 IF (ltrim(rtrim(@code)) = '1-AUF')
494 -- si catégorie d'état = global AUF
495 UPDATE #etat_section SET imp_bur = ''
496
497 SELECT
498 @categorie categorie,
499 @exercice exercice,
500 @lib_periode titre,
501 @periode periode,
502 Sec.imp_bur,
503 Sec.groupe,
504 Sec.sgroupe,
505 Sec.libelle,
506 CASE
507 WHEN Sec.sgroupe = '002-RETRAITEMENTS' THEN 0
508 ELSE ISNULL(budget.budget,0)
509 END AS budget, -- (1)
510 ISNULL(budget_mens.mensuel,0) AS mensuel, -- (2)
511 CASE
512 WHEN
513 budget.budget IS NULL OR
514 budget.budget = 0 OR
515 budget_mens.mensuel IS NULL
516 THEN 0
517 ELSE
518 CONVERT(numeric(18,5),budget_mens.mensuel)/ CONVERT(numeric(18,5),budget.budget)
519 -- budget_mens.mensuel/budget.budget
520 END AS pourc_mens, -- (2)/(1)
521 ISNULL(reel_cour.reel,0) reel , -- (3)
522 ISNULL(reel_cumul.reel_cumul,0) AS reel_cumul, -- (4)
523 CASE
524 WHEN
525 budget.budget IS NULL
526 OR budget.budget = 0
527 OR reel_cumul.reel_cumul IS NULL
528 THEN 0
529 ELSE CONVERT(numeric(18,5),reel_cumul.reel_cumul)/ CONVERT(numeric(18,5),budget.budget)
530 END AS pourc_r_cour, -- (4)/(1)
531 ISNULL(budget_mens.mensuel,0)- isnull(reel_cumul.reel_cumul,0) AS ecart_planif, -- (2)-(4)
532 CASE
533 WHEN Sec.sgroupe = '002-RETRAITEMENTS' THEN 0
534 ELSE ISNULL(budget.budget,0)- isnull(reel_cumul.reel_cumul,0)
535 END AS solde_total, --(1)-(4)
536 CASE
537 WHEN Sec.sgroupe = '002-RETRAITEMENTS' THEN 0
538 WHEN ( budget.budget IS NULL OR budget.budget = 0 ) THEN 0
539 ELSE
540 CONVERT(numeric(18,5),(budget.budget- ISNULL(reel_cumul.reel_cumul,0))) /
541 CONVERT(numeric(18,5),budget.budget)
542 END AS pourc_r_total, -- [(1)-(4)]/(1)
543 ISNULL(budget_dern.budget_dern,0) budget_dern, -- (5)
544 ISNULL(reel_dern.reel_dern,0) reel_dern, -- (6)
545 CASE
546 WHEN
547 budget_dern.budget_dern IS NULL
548 OR budget_dern.budget_dern = 0
549 OR reel_dern.reel_dern IS NULL
550 THEN 0
551 ELSE
552 CONVERT(numeric(18,5),reel_dern.reel_dern) /
553 CONVERT(numeric(18,5),budget_dern.budget_dern)
554 END AS pourc_r_dern -- (6)/(5)
555 INTO #etat
556 FROM
557 (
558 SELECT DISTINCT imp_bur, groupe, sgroupe, libelle
559 FROM #etat_section
560 ) Sec
561
562 -- budget année en cours
563 LEFT JOIN (
564 SELECT imp_bur, sgroupe, libelle, SUM(montant) budget
565 FROM #etat_section
566 WHERE exercice = @exercice AND cumul = 'BUDGET'
567 GROUP BY imp_bur, sgroupe, libelle
568 ) budget
569 ON sec.imp_bur = budget.imp_bur
570 AND sec.sgroupe = budget.sgroupe
571 AND sec.libelle = budget.libelle
572
573 -- planification mensuel
574 LEFT JOIN (
575 SELECT imp_bur, sgroupe, libelle, SUM(montant) mensuel
576 FROM #etat_section
577 WHERE exercice = @exercice AND cumul = 'BUDGET-MENS'
578 GROUP BY imp_bur, sgroupe, libelle
579 ) budget_mens
580 ON sec.imp_bur = budget_mens.imp_bur
581 AND sec.sgroupe = budget_mens.sgroupe
582 AND sec.libelle = budget_mens.libelle
583
584 -- budget réalisé période en cours
585 LEFT JOIN (
586 SELECT imp_bur, sgroupe, libelle, SUM(montant) reel
587 FROM #etat_section
588 WHERE exercice = @exercice AND periode = @periode AND cumul = 'REEL'
589 GROUP BY imp_bur, sgroupe, libelle
590 ) reel_cour
591 ON sec.imp_bur = reel_cour.imp_bur
592 AND sec.sgroupe = reel_cour.sgroupe
593 AND sec.libelle = reel_cour.libelle
594
595 -- budget réalisé cumulé en cours
596 LEFT JOIN (
597 SELECT imp_bur, sgroupe, libelle, SUM(montant) reel_cumul
598 FROM #etat_section
599 WHERE exercice = @exercice AND cumul = 'REEL'
600 GROUP BY imp_bur, sgroupe, libelle
601 ) reel_cumul
602 ON sec.imp_bur = reel_cumul.imp_bur
603 AND sec.sgroupe = reel_cumul.sgroupe
604 AND sec.libelle = reel_cumul.libelle
605
606 -- budget année dernière
607 LEFT JOIN (
608 SELECT imp_bur, sgroupe, libelle, SUM(montant) budget_dern
609 FROM #etat_section
610 WHERE exercice = @exercice-1 AND cumul = 'BUDGET'
611 GROUP BY imp_bur, sgroupe, libelle
612 ) budget_dern
613 ON sec.imp_bur = budget_dern.imp_bur
614 AND sec.sgroupe = budget_dern.sgroupe
615 AND sec.libelle = budget_dern.libelle
616
617 -- réalisé cumulé année dernière
618 LEFT JOIN (
619 SELECT imp_bur, sgroupe, libelle, SUM(montant) reel_dern
620 FROM #etat_section
621 WHERE exercice = @exercice-1 AND cumul = 'REEL' AND periode <= @periode
622 GROUP BY imp_bur, sgroupe, libelle
623 ) reel_dern
624 ON sec.imp_bur = reel_dern.imp_bur
625 AND sec.sgroupe = reel_dern.sgroupe
626 AND sec.libelle = reel_dern.libelle
627
628 -- requete final
629
630 SELECT
631 categorie, exercice execice, titre, periode, imp_bur, groupe, sgroupe,
632 libelle, budget, mensuel, pourc_mens, reel, reel_cumul, pourc_r_cour,
633 ecart_planif, solde_total, pourc_r_total, budget_dern, reel_dern,
634 pourc_r_dern
635 INTO #requete
636 FROM (
637 SELECT
638 categorie, exercice, titre, periode, imp_bur, groupe, sgroupe, libelle,
639 budget, mensuel, pourc_mens, reel, reel_cumul, pourc_r_cour,
640 ecart_planif, solde_total, pourc_r_total, budget_dern, reel_dern,
641 pourc_r_dern
642 FROM #etat
643 UNION
644 SELECT
645 categorie,
646 exercice,
647 titre,
648 periode,
649 imp_bur,
650 groupe,
651 sgroupe,
652 CASE
653 WHEN substring(libelle,3,1) = 'A' THEN '031-Projets institutnnels des établismts'
654 WHEN substring(libelle,3,1) = 'B' THEN '041-Renforcemt capacités des établismts'
655 WHEN substring(libelle,3,1) = 'C' THEN '051-Renforcemt coopération scientifique'
656 WHEN substring(libelle,3,1) = 'D' THEN '061-Langue Fr et dévlpmt Francophonie'
657 WHEN substring(libelle,3,1) = 'E' THEN '071-Coop univ pour form° et/ou recherche'
658 WHEN substring(libelle,3,1) = 'V' THEN '081-Valo ressources pédagogiques et sc'
659 WHEN substring(libelle,3,1) = 'W' THEN '091-Renforcmt capacités individuelles'
660 WHEN substring(libelle,3,1) = 'X' THEN '101-Valo bénéficiaires Intégration pro'
661 END libelle,
662 SUM(budget) budget,
663 SUM(mensuel) mensuel,
664 CASE
665 WHEN
666 SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(mensuel) IS NULL
667 THEN 0
668 ELSE CONVERT(numeric(18,5),SUM(mensuel))/ CONVERT(numeric(18,5),SUM(budget))
669 END AS pourc_mens, -- (2)/(1)
670 SUM(reel) reel,
671 SUM(reel_cumul) reel_cumul,
672 CASE
673 WHEN
674 SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(reel_cumul) IS NULL
675 THEN 0
676 ELSE CONVERT(numeric(18,5),SUM(reel_cumul))/ CONVERT(numeric(18,5),SUM(budget))
677 END AS pourc_r_cour, -- (4)/(1)
678 SUM(ecart_planif) ecart_planif,
679 SUM(solde_total) solde_total,
680 CASE
681 WHEN sgroupe = '002-RETRAITEMENTS' THEN 0
682 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 THEN 0
683 ELSE
684 CONVERT(numeric(18,5),(SUM(budget)- ISNULL(SUM(reel_cumul),0))) /
685 CONVERT(numeric(18,5),SUM(budget))
686 END AS pourc_r_total, -- [(1)-(4)]/(1)
687 SUM(budget_dern) budget_dern,
688 SUM(reel_dern) reel_dern,
689 CASE
690 WHEN
691 SUM(budget_dern) IS NULL OR SUM(budget_dern) = 0 OR SUM(reel_dern) IS NULL
692 THEN 0
693 ELSE
694 CONVERT(numeric(18,5),SUM(reel_dern)) /
695 CONVERT(numeric(18,5),SUM(budget_dern))
696 END AS pourc_r_dern -- (6)/(5)
697 FROM #etat
698 WHERE substring(libelle,3,1) in ('A','B','C','D','E','V', 'W', 'X')
699 GROUP BY
700 categorie,
701 exercice,
702 titre,
703 periode,
704 imp_bur,
705 groupe,
706 sgroupe,
707 CASE
708 WHEN substring(libelle,3,1) = 'A' THEN '031-Projets institutnnels des établismts'
709 WHEN substring(libelle,3,1) = 'B' THEN '041-Renforcemt capacités des établismts'
710 WHEN substring(libelle,3,1) = 'C' THEN '051-Renforcemt coopération scientifique'
711 WHEN substring(libelle,3,1) = 'D' THEN '061-Langue Fr et dévlpmt Francophonie'
712 WHEN substring(libelle,3,1) = 'E' THEN '071-Coop univ pour form° et/ou recherche'
713 WHEN substring(libelle,3,1) = 'V' THEN '081-Valo ressources pédagogiques et sc'
714 WHEN substring(libelle,3,1) = 'W' THEN '091-Renforcmt capacités individuelles'
715 WHEN substring(libelle,3,1) = 'X' THEN '101-Valo bénéficiaires Intégration pro'
716 END
717 UNION
718 SELECT
719 categorie,
720 exercice,
721 titre,
722 periode,
723 imp_bur,
724 groupe,
725 sgroupe,
726 CASE
727 WHEN sgroupe ='000-PRODUITS' THEN '151-Total des produits hors BSTG'
728 ELSE '181-Total des charges hors BSTG'
729 END libelle,
730 SUM(budget) budget,
731 SUM(mensuel) mensuel,
732 CASE
733 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(mensuel) IS NULL THEN 0
734 ELSE CONVERT(numeric(18,5),SUM(mensuel))/ CONVERT(numeric(18,5),SUM(budget))
735 END AS pourc_mens, -- (2)/(1)
736 SUM(reel) reel,
737 SUM(reel_cumul) reel_cumul,
738 CASE
739 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(reel_cumul) IS NULL THEN 0
740 ELSE CONVERT(numeric(18,5),SUM(reel_cumul))/ CONVERT(numeric(18,5),SUM(budget))
741 END AS pourc_r_cour, -- (4)/(1)
742 SUM(ecart_planif) ecart_planif,
743 SUM(solde_total) solde_total,
744 CASE
745 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 THEN 0
746 ELSE
747 CONVERT(numeric(18,5),(SUM(budget)- ISNULL(SUM(reel_cumul),0))) /
748 CONVERT(numeric(18,5),SUM(budget))
749 END AS pourc_r_total, -- [(1)-(4)]/(1)
750 SUM(budget_dern) budget_dern,
751 SUM(reel_dern) reel_dern,
752 CASE
753 WHEN
754 SUM(budget_dern) IS NULL OR SUM(budget_dern) = 0 OR SUM(reel_dern) IS NULL
755 THEN 0
756 ELSE
757 CONVERT(numeric(18,5),SUM(reel_dern)) /
758 CONVERT(numeric(18,5),SUM(budget_dern))
759 END AS pourc_r_dern -- (6)/(5)
760 FROM #etat
761 WHERE
762 sgroupe IN ( '000-PRODUITS','001-CHARGES')
763 AND NOT ( sgroupe = '000-PRODUITS' AND substring(libelle,1,3) = '160')
764 AND NOT ( sgroupe = '001-CHARGES' AND substring(libelle,1,3) = '190')
765 GROUP BY
766 categorie,
767 exercice,
768 titre,
769 periode,
770 imp_bur,
771 groupe,
772 sgroupe,
773 CASE
774 WHEN sgroupe ='000-PRODUITS' THEN '151-Total des produits hors BSTG'
775 ELSE '181-Total des charges hors BSTG'
776 END
777 UNION
778 SELECT
779 categorie,
780 exercice,
781 titre,
782 periode,
783 imp_bur,
784 groupe,
785 sgroupe,
786 '021-Actions des programmes' AS libelle,
787 SUM(budget) budget,
788 SUM(mensuel) mensuel,
789 CASE
790 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(mensuel) IS NULL THEN 0
791 ELSE CONVERT(numeric(18,5),SUM(mensuel))/ CONVERT(numeric(18,5),SUM(budget))
792 END AS pourc_mens, -- (2)/(1)
793 SUM(reel) reel,
794 SUM(reel_cumul) reel_cumul,
795 CASE
796 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(reel_cumul) IS NULL THEN 0
797 ELSE CONVERT(numeric(18,5),SUM(reel_cumul))/ CONVERT(numeric(18,5),SUM(budget))
798 END AS pourc_r_cour, -- (4)/(1)
799 SUM(ecart_planif) ecart_planif,
800 SUM(solde_total) solde_total,
801 CASE
802 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 THEN 0
803 ELSE
804 CONVERT(numeric(18,5),(SUM(budget)- ISNULL(SUM(reel_cumul),0))) /
805 CONVERT(numeric(18,5),SUM(budget))
806 END AS pourc_r_total, -- [(1)-(4)]/(1)
807 SUM(budget_dern) budget_dern,
808 SUM(reel_dern) reel_dern,
809 CASE
810 WHEN
811 SUM(budget_dern) IS NULL OR SUM(budget_dern) = 0 OR SUM(reel_dern) IS NULL
812 THEN 0
813 ELSE
814 CONVERT(numeric(18,5),SUM(reel_dern))/ CONVERT(numeric(18,5),SUM(budget_dern))
815 END AS pourc_r_dern -- (6)/(5)
816 FROM #etat
817 WHERE
818 sgroupe = '001-CHARGES'
819 AND substring(libelle,3,1) in ('A','B','C','D','E','V', 'W', 'X')
820 GROUP BY categorie, exercice, titre, periode, imp_bur, groupe, sgroupe
821 ) etat
822
823 SELECT
824 r.*,
825 CASE
826 WHEN LEFT(r.libelle,2) IN ('03','04','05','06','07','08', '09', '10') THEN 0
827 WHEN LEFT(r.libelle,3) IN ('151','181') THEN 0
828 ELSE 1
829 END AS calc_total
830 FROM #requete r
831 ORDER BY imp_bur,groupe,sgroupe,libelle,reel_dern
832
833 DROP TABLE #etat_section
834 DROP TABLE #cumul_ecritures
835 DROP TABLE #etat
836 DROP TABLE #requete
837 GO