Conversion des fichiers de UTF-16 à UTF-8
[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 b.el1 LIKE '745%' THEN '130-Contributions contractuelles'
279 WHEN (
280 b.el1 LIKE '78%'
281 OR b.el1='75700'
282 OR (
283 b.el1='11100'
284 AND rtrim(ltrim(el3)) <> ''
285 AND el3 NOT IN (
286 '90003GR', '91022GR', '91203GR',
287 '91024GR','91025GR','91032GR', '9003GR', '9122GR', '9123GR',
288 '9124GR','9125GR','9132GR'
289 )
290 )
291 OR b.el3 IN (
292 '91022GR', '91024GR', '91023GR', '91032GR','91025GR', '9122GR',
293 '9124GR', '9123GR', '9132GR','9125GR'
294 )
295 ) THEN '140-Apports, autres recettes, provisions'
296 WHEN (
297 b.el1 LIKE '76%'
298 OR b.el1 LIKE '77%'
299 ) THEN '150-Produits financiers et exceptionnels'
300 WHEN b.el1 LIKE '87%' THEN '160-Valeurs des biens et services à titre gratuit'
301 END AS sgroupe
302 FROM #cumul_ecritures b
303 WHERE -- Filtres globaux
304 (
305 b.el1 LIKE '7%'
306 OR b.el1 LIKE '87%'
307 OR (
308 b.el1='11100'
309 AND rtrim(ltrim(el3)) <> ''
310 AND el3 NOT IN (
311 '90003GR', '91022GR', '91023GR', '91024GR','9125GR','9132GR',
312 '9003GR', '9122GR', '9123GR', '9124GR','9125GR','9132GR'
313 )
314 )
315 OR b.el3 IN (
316 '91022GR', '91024GR', '91023GR', '91032GR','91025GR', '9122GR',
317 '9124GR', '9123GR', '9132GR','9125GR'
318 )
319 )
320 AND b.el1 <> '77200'
321
322 -- section CHARGES
323
324 INSERT INTO #etat_section
325 SELECT
326 imp_bur,
327 b.yr AS exercice,
328 b.period AS periode,
329 b.balcode AS cumul,
330 '010-Excédents des produits (charges) avant retraitements' AS groupe1,
331 '001-CHARGES' AS groupe2,
332 b.full_value * -1 AS solde,
333 CASE
334 -- Attention séquence
335 WHEN b.el1 LIKE '861%' THEN '190-Valeurs des biens et services à titre gratuit'
336 WHEN grp1.grpcode = 'HX' OR grpG.grpcode = 'GX' THEN
337 -- Attention séquence
338 CASE
339 WHEN b.el3 LIKE '95%' THEN '010-Frais de personnel'
340 WHEN b.el3 LIKE '96%' THEN '020-Frais généraux'
341 WHEN
342 b.el1 LIKE '9%'
343 AND (b.el3 LIKE '99099%' OR b.el3 LIKE '9999%')
344 THEN '120-Actions Réserve'
345 WHEN
346 b.el1 LIKE '666%'
347 AND (b.el3 LIKE '91006%' OR b.el3 LIKE '9106%')
348 THEN '180-Perte (gain) de change'
349 WHEN
350 b.el3 LIKE '91006%'
351 OR b.el3 LIKE '9106%'
352 THEN '160-Charges financières'
353 WHEN
354 b.el1 LIKE '67%'
355 AND b.el1 <> '67500'
356 AND (b.el3 LIKE '91007%' OR b.el3 LIKE '9107%')
357 THEN '170-Charges exceptionnelles'
358 ELSE '150-Autres charges, provisions'
359 END
360 WHEN grp1.grpcode = 'H1' THEN '03A-' + space(8) + lg.[name] + '-' + lg.code
361 WHEN grp1.grpcode='H2' THEN '04B-' +space(8)+ lg.[name]+'-'+lg.code
362 WHEN grp1.grpcode='H3' THEN '05C-' +space(8)+ lg.[name]+'-'+lg.code
363 WHEN grp1.grpcode='H4' THEN '06D-' +space(8)+ lg.[name]+'-'+lg.code
364 WHEN grp1.grpcode='H5' THEN '07E-' +space(8)+ lg.[name]+'-'+lg.code
365 WHEN grp1.grpcode='H6' THEN '08V-' +space(8)+ lg.[name]+'-'+lg.code
366 WHEN grp1.grpcode='H7' THEN '09W-' +space(8)+ lg.[name]+'-'+lg.code
367 WHEN grp1.grpcode='H8' THEN '10X-' +space(8)+ lg.[name]+'-'+lg.code
368 WHEN grpG.grpcode='G1' THEN '03A-' +space(8)+ lg.[name]
369 WHEN grpG.grpcode='G2' THEN '04B-' +space(8)+ lg.[name]
370 WHEN grpG.grpcode='G3' THEN '05C-' +space(8)+ lg.[name]
371 WHEN grpG.grpcode='G4' THEN '06D-' +space(8)+ lg.[name]
372 WHEN grpG.grpcode='G5' THEN '07E-' +space(8)+ lg.[name]
373 WHEN grpG.grpcode='G6' THEN '08V-' +space(8)+ lg.[name]
374 WHEN grp1.grpcode='H0' OR grpG.grpcode='G0' THEN '130-Actions institutionnelles'
375 WHEN grp1.grpcode='HS' OR grpG.grpcode='GS' THEN '140-Actions administratives'
376 END AS sgroupe
377 FROM
378 oas_group lg,
379 #cumul_ecritures b
380 LEFT OUTER JOIN oas_grplist grp1
381 ON grp1.grpcode LIKE 'H%'
382 AND grp1.elmlevel = 3
383 AND grp1.cmpcode = 'AUF'
384 AND b.el3 = grp1.code
385 LEFT OUTER JOIN oas_grplist grp2
386 ON grp2.grpcode LIKE 'L%'
387 AND grp2.elmlevel = 3
388 AND grp2.cmpcode = 'AUF'
389 AND b.el3 = grp2.code
390 LEFT OUTER JOIN oas_grplist grpG
391 ON grpG.grpcode LIKE 'G%'
392 AND grpG.elmlevel = 3
393 AND grpG.cmpcode = 'AUF'
394 AND b.el3 = grpG.code
395 WHERE -- Filtres globaux
396 (
397 b.el1 LIKE '6%'
398 OR b.el1 LIKE '86%'
399 OR b.el1 LIKE '2%'
400 OR b.el1 LIKE '1%'
401 OR b.el1 LIKE '9%'
402 )
403 AND b.el1 NOT LIKE '96%'
404 AND b.el1 NOT LIKE '97%'
405 AND b.el1 NOT LIKE '98%'
406 AND b.el3 NOT IN (
407 '91022GR', '91023GR', '91024GR', '91032GR','91025GR', '9122GR',
408 '9123GR', '9124GR', '9132GR','9125GR'
409 )
410 AND NOT (
411 b.el1='11100'
412 AND rtrim(ltrim(el3)) <> ''
413 AND el3 NOT IN (
414 '90003GR', '91022GR', '91023GR', '91024GR','91025GR','91032GR',
415 '9003GR', '9122GR', '9123GR', '9124GR','9125GR','9132GR'
416 )
417 )
418 AND b.el3 NOT LIKE '91009%'
419 AND b.el3 NOT LIKE '9109%'
420 AND (grp2.grpcode = lg.code OR grpG.grpcode = lg.code)
421 AND lg.groupwhat = 3
422 AND lg.cmpcode = 'AUF'
423 AND b.el3 NOT LIKE '91008%'
424 AND b.el3 NOT LIKE '9108%'
425
426
427 -- section RETRAITEMENTS
428 -- mettre @publication <> 'oui' dans prod
429
430 IF @retraitement = 'Oui' BEGIN
431 INSERT INTO #etat_section
432 SELECT
433 imp_bur,
434 yr AS exercice,
435 period AS periode,
436 balcode AS cumul,
437 '020-E' AS groupe1,
438 '002-RETRAITEMENTS' AS groupe2,
439 CASE
440 WHEN (el1 LIKE '6%' AND el3 != '9003GR') OR el1 LIKE '77200' THEN full_value *-1
441 ELSE full_value
442 END AS Solde,
443 CASE
444 WHEN
445 (el1 LIKE '11%' AND el3 <> '')
446 AND NOT (el1 LIKE '11%' AND el3 IN ('90003GR', '91031GR', '91025GR'))
447 THEN '510-Moins : apports'
448 WHEN el1 LIKE '2%' THEN '020-Plus : investissements en immobilisations'
449 WHEN
450 el3 IN ('90003GR', '91031GR', '91025GR')
451 THEN '010-Plus : affectation AG et au fonds de réserve'
452 WHEN el1 LIKE '16%' THEN '030-Plus : remboursement de la dette à long terme'
453 ELSE '550-Moins : amortis. immob. et pertes sur disposition'
454 END AS sgroupe
455 FROM #cumul_ecritures
456 WHERE -- Filtres globaux
457 (
458 (el1 = '67500' AND el3 LIKE '91008%')
459 OR (el1 = '68152')
460 OR el1 LIKE '6811%'
461 OR el1 LIKE '1%'
462 OR el1 LIKE '2%'
463 OR (el1 = '77200' AND el3 LIKE '91008%')
464 )
465 AND el1 NOT LIKE '28%'
466 AND el1 NOT LIKE '96%'
467 AND el1 NOT LIKE '97%'
468 AND el1 NOT LIKE '98%'
469 AND NOT ( el1 LIKE '2%' AND el3 LIKE '91008%' )
470 AND NOT ( el1 = '21820' AND el3 LIKE '91%')
471 AND NOT ( el1 = '68114' AND el3 LIKE '96%')
472 AND el3 NOT LIKE '91009%'
473 AND NOT ( el3 = '91033GR' AND yr =2005 )
474 AND NOT (
475 yr = 2004 AND
476 period = 12 AND
477 imp_bur in ('BXX','IXX1') AND
478 el1 LIKE '21%'
479 )
480 AND NOT ( el1 = '67500' AND ( el3 LIKE '96%' OR el3 ='30040QI'))
481 END
482
483 -- construire les colonne de l'état du résultats IWR
484
485 IF (ltrim(rtrim(@code)) = '1-AUF')
486 -- si catégorie d'état = global AUF
487 UPDATE #etat_section SET imp_bur = ''
488
489 SELECT
490 @categorie categorie,
491 @exercice exercice,
492 @lib_periode titre,
493 @periode periode,
494 Sec.imp_bur,
495 Sec.groupe,
496 Sec.sgroupe,
497 Sec.libelle,
498 CASE
499 WHEN Sec.sgroupe = '002-RETRAITEMENTS' THEN 0
500 ELSE ISNULL(budget.budget,0)
501 END AS budget, -- (1)
502 ISNULL(budget_mens.mensuel,0) AS mensuel, -- (2)
503 CASE
504 WHEN
505 budget.budget IS NULL OR
506 budget.budget = 0 OR
507 budget_mens.mensuel IS NULL
508 THEN 0
509 ELSE
510 CONVERT(numeric(18,5),budget_mens.mensuel)/ CONVERT(numeric(18,5),budget.budget)
511 -- budget_mens.mensuel/budget.budget
512 END AS pourc_mens, -- (2)/(1)
513 ISNULL(reel_cour.reel,0) reel , -- (3)
514 ISNULL(reel_cumul.reel_cumul,0) AS reel_cumul, -- (4)
515 CASE
516 WHEN
517 budget.budget IS NULL
518 OR budget.budget = 0
519 OR reel_cumul.reel_cumul IS NULL
520 THEN 0
521 ELSE CONVERT(numeric(18,5),reel_cumul.reel_cumul)/ CONVERT(numeric(18,5),budget.budget)
522 END AS pourc_r_cour, -- (4)/(1)
523 ISNULL(budget_mens.mensuel,0)- isnull(reel_cumul.reel_cumul,0) AS ecart_planif, -- (2)-(4)
524 CASE
525 WHEN Sec.sgroupe = '002-RETRAITEMENTS' THEN 0
526 ELSE ISNULL(budget.budget,0)- isnull(reel_cumul.reel_cumul,0)
527 END AS solde_total, --(1)-(4)
528 CASE
529 WHEN Sec.sgroupe = '002-RETRAITEMENTS' THEN 0
530 WHEN ( budget.budget IS NULL OR budget.budget = 0 ) THEN 0
531 ELSE
532 CONVERT(numeric(18,5),(budget.budget- ISNULL(reel_cumul.reel_cumul,0))) /
533 CONVERT(numeric(18,5),budget.budget)
534 END AS pourc_r_total, -- [(1)-(4)]/(1)
535 ISNULL(budget_dern.budget_dern,0) budget_dern, -- (5)
536 ISNULL(reel_dern.reel_dern,0) reel_dern, -- (6)
537 CASE
538 WHEN
539 budget_dern.budget_dern IS NULL
540 OR budget_dern.budget_dern = 0
541 OR reel_dern.reel_dern IS NULL
542 THEN 0
543 ELSE
544 CONVERT(numeric(18,5),reel_dern.reel_dern) /
545 CONVERT(numeric(18,5),budget_dern.budget_dern)
546 END AS pourc_r_dern -- (6)/(5)
547 INTO #etat
548 FROM
549 (
550 SELECT DISTINCT imp_bur, groupe, sgroupe, libelle
551 FROM #etat_section
552 ) Sec
553
554 -- budget année en cours
555 LEFT JOIN (
556 SELECT imp_bur, sgroupe, libelle, SUM(montant) budget
557 FROM #etat_section
558 WHERE exercice = @exercice AND cumul = 'BUDGET'
559 GROUP BY imp_bur, sgroupe, libelle
560 ) budget
561 ON sec.imp_bur = budget.imp_bur
562 AND sec.sgroupe = budget.sgroupe
563 AND sec.libelle = budget.libelle
564
565 -- planification mensuel
566 LEFT JOIN (
567 SELECT imp_bur, sgroupe, libelle, SUM(montant) mensuel
568 FROM #etat_section
569 WHERE exercice = @exercice AND cumul = 'BUDGET-MENS'
570 GROUP BY imp_bur, sgroupe, libelle
571 ) budget_mens
572 ON sec.imp_bur = budget_mens.imp_bur
573 AND sec.sgroupe = budget_mens.sgroupe
574 AND sec.libelle = budget_mens.libelle
575
576 -- budget réalisé période en cours
577 LEFT JOIN (
578 SELECT imp_bur, sgroupe, libelle, SUM(montant) reel
579 FROM #etat_section
580 WHERE exercice = @exercice AND periode = @periode AND cumul = 'REEL'
581 GROUP BY imp_bur, sgroupe, libelle
582 ) reel_cour
583 ON sec.imp_bur = reel_cour.imp_bur
584 AND sec.sgroupe = reel_cour.sgroupe
585 AND sec.libelle = reel_cour.libelle
586
587 -- budget réalisé cumulé en cours
588 LEFT JOIN (
589 SELECT imp_bur, sgroupe, libelle, SUM(montant) reel_cumul
590 FROM #etat_section
591 WHERE exercice = @exercice AND cumul = 'REEL'
592 GROUP BY imp_bur, sgroupe, libelle
593 ) reel_cumul
594 ON sec.imp_bur = reel_cumul.imp_bur
595 AND sec.sgroupe = reel_cumul.sgroupe
596 AND sec.libelle = reel_cumul.libelle
597
598 -- budget année dernière
599 LEFT JOIN (
600 SELECT imp_bur, sgroupe, libelle, SUM(montant) budget_dern
601 FROM #etat_section
602 WHERE exercice = @exercice-1 AND cumul = 'BUDGET'
603 GROUP BY imp_bur, sgroupe, libelle
604 ) budget_dern
605 ON sec.imp_bur = budget_dern.imp_bur
606 AND sec.sgroupe = budget_dern.sgroupe
607 AND sec.libelle = budget_dern.libelle
608
609 -- réalisé cumulé année dernière
610 LEFT JOIN (
611 SELECT imp_bur, sgroupe, libelle, SUM(montant) reel_dern
612 FROM #etat_section
613 WHERE exercice = @exercice-1 AND cumul = 'REEL' AND periode <= @periode
614 GROUP BY imp_bur, sgroupe, libelle
615 ) reel_dern
616 ON sec.imp_bur = reel_dern.imp_bur
617 AND sec.sgroupe = reel_dern.sgroupe
618 AND sec.libelle = reel_dern.libelle
619
620 -- requete final
621
622 SELECT
623 categorie, exercice execice, titre, periode, imp_bur, groupe, sgroupe,
624 libelle, budget, mensuel, pourc_mens, reel, reel_cumul, pourc_r_cour,
625 ecart_planif, solde_total, pourc_r_total, budget_dern, reel_dern,
626 pourc_r_dern
627 INTO #requete
628 FROM (
629 SELECT
630 categorie, exercice, titre, periode, imp_bur, groupe, sgroupe, libelle,
631 budget, mensuel, pourc_mens, reel, reel_cumul, pourc_r_cour,
632 ecart_planif, solde_total, pourc_r_total, budget_dern, reel_dern,
633 pourc_r_dern
634 FROM #etat
635 UNION
636 SELECT
637 categorie,
638 exercice,
639 titre,
640 periode,
641 imp_bur,
642 groupe,
643 sgroupe,
644 CASE
645 WHEN substring(libelle,3,1) = 'A' THEN '031-Projets institutnnels des établismts'
646 WHEN substring(libelle,3,1) = 'B' THEN '041-Renforcemt capacités des établismts'
647 WHEN substring(libelle,3,1) = 'C' THEN '051-Renforcemt coopération scientifique'
648 WHEN substring(libelle,3,1) = 'D' THEN '061-Langue Fr et dévlpmt Francophonie'
649 WHEN substring(libelle,3,1) = 'E' THEN '071-Coop univ pour form° et/ou recherche'
650 WHEN substring(libelle,3,1) = 'V' THEN '081-Valo ressources pédagogiques et sc'
651 WHEN substring(libelle,3,1) = 'W' THEN '091-Renforcmt capacités individuelles'
652 WHEN substring(libelle,3,1) = 'X' THEN '101-Valo bénéficiaires Intégration pro'
653 END libelle,
654 SUM(budget) budget,
655 SUM(mensuel) mensuel,
656 CASE
657 WHEN
658 SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(mensuel) IS NULL
659 THEN 0
660 ELSE CONVERT(numeric(18,5),SUM(mensuel))/ CONVERT(numeric(18,5),SUM(budget))
661 END AS pourc_mens, -- (2)/(1)
662 SUM(reel) reel,
663 SUM(reel_cumul) reel_cumul,
664 CASE
665 WHEN
666 SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(reel_cumul) IS NULL
667 THEN 0
668 ELSE CONVERT(numeric(18,5),SUM(reel_cumul))/ CONVERT(numeric(18,5),SUM(budget))
669 END AS pourc_r_cour, -- (4)/(1)
670 SUM(ecart_planif) ecart_planif,
671 SUM(solde_total) solde_total,
672 CASE
673 WHEN sgroupe = '002-RETRAITEMENTS' THEN 0
674 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 THEN 0
675 ELSE
676 CONVERT(numeric(18,5),(SUM(budget)- ISNULL(SUM(reel_cumul),0))) /
677 CONVERT(numeric(18,5),SUM(budget))
678 END AS pourc_r_total, -- [(1)-(4)]/(1)
679 SUM(budget_dern) budget_dern,
680 SUM(reel_dern) reel_dern,
681 CASE
682 WHEN
683 SUM(budget_dern) IS NULL OR SUM(budget_dern) = 0 OR SUM(reel_dern) IS NULL
684 THEN 0
685 ELSE
686 CONVERT(numeric(18,5),SUM(reel_dern)) /
687 CONVERT(numeric(18,5),SUM(budget_dern))
688 END AS pourc_r_dern -- (6)/(5)
689 FROM #etat
690 WHERE substring(libelle,3,1) in ('A','B','C','D','E','V', 'W', 'X')
691 GROUP BY
692 categorie,
693 exercice,
694 titre,
695 periode,
696 imp_bur,
697 groupe,
698 sgroupe,
699 CASE
700 WHEN substring(libelle,3,1) = 'A' THEN '031-Projets institutnnels des établismts'
701 WHEN substring(libelle,3,1) = 'B' THEN '041-Renforcemt capacités des établismts'
702 WHEN substring(libelle,3,1) = 'C' THEN '051-Renforcemt coopération scientifique'
703 WHEN substring(libelle,3,1) = 'D' THEN '061-Langue Fr et dévlpmt Francophonie'
704 WHEN substring(libelle,3,1) = 'E' THEN '071-Coop univ pour form° et/ou recherche'
705 WHEN substring(libelle,3,1) = 'V' THEN '081-Valo ressources pédagogiques et sc'
706 WHEN substring(libelle,3,1) = 'W' THEN '091-Renforcmt capacités individuelles'
707 WHEN substring(libelle,3,1) = 'X' THEN '101-Valo bénéficiaires Intégration pro'
708 END
709 UNION
710 SELECT
711 categorie,
712 exercice,
713 titre,
714 periode,
715 imp_bur,
716 groupe,
717 sgroupe,
718 CASE
719 WHEN sgroupe ='000-PRODUITS' THEN '151-Total des produits hors BSTG'
720 ELSE '181-Total des charges hors BSTG'
721 END libelle,
722 SUM(budget) budget,
723 SUM(mensuel) mensuel,
724 CASE
725 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(mensuel) IS NULL THEN 0
726 ELSE CONVERT(numeric(18,5),SUM(mensuel))/ CONVERT(numeric(18,5),SUM(budget))
727 END AS pourc_mens, -- (2)/(1)
728 SUM(reel) reel,
729 SUM(reel_cumul) reel_cumul,
730 CASE
731 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(reel_cumul) IS NULL THEN 0
732 ELSE CONVERT(numeric(18,5),SUM(reel_cumul))/ CONVERT(numeric(18,5),SUM(budget))
733 END AS pourc_r_cour, -- (4)/(1)
734 SUM(ecart_planif) ecart_planif,
735 SUM(solde_total) solde_total,
736 CASE
737 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 THEN 0
738 ELSE
739 CONVERT(numeric(18,5),(SUM(budget)- ISNULL(SUM(reel_cumul),0))) /
740 CONVERT(numeric(18,5),SUM(budget))
741 END AS pourc_r_total, -- [(1)-(4)]/(1)
742 SUM(budget_dern) budget_dern,
743 SUM(reel_dern) reel_dern,
744 CASE
745 WHEN
746 SUM(budget_dern) IS NULL OR SUM(budget_dern) = 0 OR SUM(reel_dern) IS NULL
747 THEN 0
748 ELSE
749 CONVERT(numeric(18,5),SUM(reel_dern)) /
750 CONVERT(numeric(18,5),SUM(budget_dern))
751 END AS pourc_r_dern -- (6)/(5)
752 FROM #etat
753 WHERE
754 sgroupe IN ( '000-PRODUITS','001-CHARGES')
755 AND NOT ( sgroupe = '000-PRODUITS' AND substring(libelle,1,3) = '160')
756 AND NOT ( sgroupe = '001-CHARGES' AND substring(libelle,1,3) = '190')
757 GROUP BY
758 categorie,
759 exercice,
760 titre,
761 periode,
762 imp_bur,
763 groupe,
764 sgroupe,
765 CASE
766 WHEN sgroupe ='000-PRODUITS' THEN '151-Total des produits hors BSTG'
767 ELSE '181-Total des charges hors BSTG'
768 END
769 UNION
770 SELECT
771 categorie,
772 exercice,
773 titre,
774 periode,
775 imp_bur,
776 groupe,
777 sgroupe,
778 '021-Actions des programmes' AS libelle,
779 SUM(budget) budget,
780 SUM(mensuel) mensuel,
781 CASE
782 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(mensuel) IS NULL THEN 0
783 ELSE CONVERT(numeric(18,5),SUM(mensuel))/ CONVERT(numeric(18,5),SUM(budget))
784 END AS pourc_mens, -- (2)/(1)
785 SUM(reel) reel,
786 SUM(reel_cumul) reel_cumul,
787 CASE
788 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 OR SUM(reel_cumul) IS NULL THEN 0
789 ELSE CONVERT(numeric(18,5),SUM(reel_cumul))/ CONVERT(numeric(18,5),SUM(budget))
790 END AS pourc_r_cour, -- (4)/(1)
791 SUM(ecart_planif) ecart_planif,
792 SUM(solde_total) solde_total,
793 CASE
794 WHEN SUM(budget) IS NULL OR SUM(budget) = 0 THEN 0
795 ELSE
796 CONVERT(numeric(18,5),(SUM(budget)- ISNULL(SUM(reel_cumul),0))) /
797 CONVERT(numeric(18,5),SUM(budget))
798 END AS pourc_r_total, -- [(1)-(4)]/(1)
799 SUM(budget_dern) budget_dern,
800 SUM(reel_dern) reel_dern,
801 CASE
802 WHEN
803 SUM(budget_dern) IS NULL OR SUM(budget_dern) = 0 OR SUM(reel_dern) IS NULL
804 THEN 0
805 ELSE
806 CONVERT(numeric(18,5),SUM(reel_dern))/ CONVERT(numeric(18,5),SUM(budget_dern))
807 END AS pourc_r_dern -- (6)/(5)
808 FROM #etat
809 WHERE
810 sgroupe = '001-CHARGES'
811 AND substring(libelle,3,1) in ('A','B','C','D','E','V', 'W', 'X')
812 GROUP BY categorie, exercice, titre, periode, imp_bur, groupe, sgroupe
813 ) etat
814
815 SELECT
816 r.*,
817 CASE
818 WHEN LEFT(r.libelle,2) IN ('03','04','05','06','07','08', '09', '10') THEN 0
819 WHEN LEFT(r.libelle,3) IN ('151','181') THEN 0
820 ELSE 1
821 END AS calc_total
822 FROM #requete r
823 ORDER BY imp_bur,groupe,sgroupe,libelle,reel_dern
824
825 DROP TABLE #etat_section
826 DROP TABLE #cumul_ecritures
827 DROP TABLE #etat
828 DROP TABLE #requete
829 GO