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'
 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