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