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