rapports/consommation.sql

   1 -- Engagements non-rapprochés par numéro de commande et el3  {{{1
   2
   3 DECLARE @engagements_non_rapproches TABLE (
   4     code_commande VARCHAR(12) NOT NULL,
   5     num_commande VARCHAR(32) NOT NULL,
   6     el3 VARCHAR(8),
   7     exercice SMALLINT NOT NULL,
   8     montant MONEY NOT NULL,
   9     UNIQUE (code_commande, num_commande, el3)
  10 );
  11
  12 INSERT INTO @engagements_non_rapproches
  13 SELECT
  14     odoccode AS code_commande,
  15     odocnum AS num_commande,
  16     el3,
  17     balyp_yr AS exercice,
  18     SUM(homevalue) AS montant
  19 FROM pop_commitments
  20 WHERE
  21     doctype = 31997  -- Engagements
  22     AND balyp_yr IN (2014, 2015)
  23     AND matchid IS NULL
  24 GROUP BY odoccode, odocnum, el3, balyp_yr
  25 HAVING SUM(homevalue) != 0;
  26
  27 -- Engagements rapprochés par matchid et el3  {{{1
  28
  29 DECLARE @engagements_rapproches TABLE (
  30     matchid NUMERIC(28, 0) NOT NULL,
  31     el3 VARCHAR(8),
  32     exercice SMALLINT NOT NULL,
  33     exercice_reception SMALLINT NOT NULL,
  34     montant MONEY NOT NULL,
  35     UNIQUE (matchid, el3)
  36 );
  37
  38 INSERT INTO @engagements_rapproches
  39 SELECT
  40     c.matchid,
  41     c.el3,
  42     c.balyp_yr AS exercice,
  43     gh.docyearperiod_yr AS exercice_reception,
  44     SUM(c.homevalue) AS montant
  45 FROM
  46     pop_commitments c
  47     INNER JOIN pim_mline ml ON ml.mlineid = c.matchid
  48     INNER JOIN pim_line l ON l.lineid = ml.lrnlnlineid
  49     INNER JOIN pim_lrnhead h ON h.documentid = l.docdocumentid
  50     INNER JOIN pop_grnhead gh
  51         ON gh.doccode = h.documentcode
  52         AND gh.docnum = h.documentnumber
  53 WHERE
  54     c.doctype = 31997
  55     AND c.balyp_yr IN (2014, 2015)
  56     AND c.matchid IS NOT NULL
  57 GROUP BY c.matchid, c.el3, c.balyp_yr, gh.docyearperiod_yr
  58 HAVING SUM(homevalue) != 0
  59
  60 -- Montants réceptionnés par commande et el3  {{{1
  61
  62 DECLARE @receptions TABLE (
  63     code_commande VARCHAR(12) NOT NULL,
  64     num_commande VARCHAR(32) NOT NULL,
  65     el3 VARCHAR(8),
  66     montant_2014 MONEY NOT NULL,
  67     montant_2015 MONEY NOT NULL,
  68     UNIQUE (code_commande, num_commande, el3)
  69 );
  70
  71 INSERT INTO @receptions
  72 SELECT
  73     ll.orderdocumentcode AS code_commande,
  74     ll.ordernumber AS num_commande,
  75     LEFT(ll.departmentmastercode, 8) AS el3,
  76     SUM(
  77         CASE
  78             WHEN h.yearperiod_yr = 2014
  79                 THEN ROUND(
  80                     ll.unmatchedvaluevalue /
  81                     (ll.documentrate_rate / 10000000) *
  82                     (
  83                         1 +
  84                         (COALESCE(t.rate, 0) / 1000000000) *
  85                         (1 - COALESCE(t.rec_percent, 0) / 1000000000)
  86                     )
  87                 , 2)
  88             ELSE 0
  89         END
  90     ) AS montant_2014,
  91     SUM(
  92         CASE
  93             WHEN h.yearperiod_yr = 2015
  94                 THEN ROUND(
  95                     ll.unmatchedvaluevalue /
  96                     (ll.documentrate_rate / 10000000) *
  97                     (
  98                         1 +
  99                         (COALESCE(t.rate, 0) / 1000000000) *
 100                         (1 - COALESCE(t.rec_percent, 0) / 1000000000)
 101                     )
 102                 , 2)
 103             ELSE 0
 104         END
 105     ) AS montant_2015
 106 FROM pim_lrnline ll
 107     INNER JOIN pim_line l ON l.lineid = ll.lineid
 108     INNER JOIN pim_dochead h ON h.documentid = l.docdocumentid
 109     LEFT JOIN oas_taxlist t
 110         ON t.cmpcode = 'AUF'
 111         AND t.taxcode = l.linetaxcode
 112         AND t.effect_date = (
 113             SELECT MAX(effect_date)
 114             FROM oas_taxlist
 115             WHERE cmpcode = 'AUF' AND taxcode = l.linetaxcode
 116         )
 117 WHERE
 118     ll.orderdocumentcode IS NOT NULL
 119     AND ll.ordernumber IS NOT NULL
 120 GROUP BY
 121     ll.orderdocumentcode,
 122     ll.ordernumber,
 123     LEFT(ll.departmentmastercode, 8);
 124
 125 -- Engagements non rapprochés par el3  {{{1
 126
 127 DECLARE @engagements_non_rapproches_el3 TABLE (
 128     el3 VARCHAR(8),
 129     eng2014_rec2014 MONEY NOT NULL,
 130     eng2014_rec2015 MONEY NOT NULL,
 131     eng2015_rec2015 MONEY NOT NULL,
 132     eng2014_non_receptionnes MONEY NOT NULL,
 133     eng2015_non_receptionnes MONEY NOT NULL,
 134     UNIQUE (el3)
 135 );
 136
 137 INSERT INTO @engagements_non_rapproches_el3
 138 SELECT
 139     e.el3,
 140     SUM(
 141         CASE
 142             WHEN e.exercice = 2014 THEN COALESCE(r.montant_2014, 0)
 143             ELSE 0
 144         END
 145     ) AS eng2014_rec2014,
 146     SUM(
 147         CASE
 148             WHEN e.exercice = 2014 THEN COALESCE(r.montant_2015, 0)
 149             ELSE 0
 150         END
 151     ) AS eng2014_rec2015,
 152     SUM(
 153         CASE
 154             WHEN e.exercice = 2015 THEN COALESCE(r.montant_2015, 0)
 155             ELSE 0
 156         END
 157     ) AS eng2015_rec2015,
 158     SUM(
 159         CASE
 160             WHEN e.exercice = 2014
 161                 THEN e.montant - COALESCE(r.montant_2014 + r.montant_2015, 0)
 162             ELSE 0
 163         END
 164     ) AS eng2014_non_receptionnes,
 165     SUM(
 166         CASE
 167             WHEN e.exercice = 2015
 168                 THEN e.montant - COALESCE(r.montant_2014 + r.montant_2015, 0)
 169             ELSE 0
 170         END
 171     ) AS eng2015_non_receptionnes
 172 FROM
 173     @engagements_non_rapproches e
 174     LEFT JOIN @receptions r
 175         ON e.code_commande = r.code_commande
 176         AND e.num_commande = r.num_commande
 177         and e.el3 = r.el3
 178 GROUP BY e.el3;
 179
 180 -- Engagements rapprochés par el3  {{{1
 181
 182 DECLARE @engagements_rapproches_el3 TABLE (
 183     el3 VARCHAR(8),
 184     eng2014_rec2014 MONEY NOT NULL,
 185     eng2014_rec2015 MONEY NOT NULL,
 186     eng2015_rec2015 MONEY NOT NULL,
 187     UNIQUE (el3)
 188 );
 189
 190 INSERT INTO @engagements_rapproches_el3
 191 SELECT
 192     el3,
 193     SUM(
 194         CASE
 195             WHEN exercice = 2014 AND exercice_reception = 2014 THEN montant
 196             ELSE 0
 197         END
 198     ) AS eng2014_rec2014,
 199     SUM(
 200         CASE
 201             WHEN exercice = 2014 AND exercice_reception = 2015 THEN montant
 202             ELSE 0
 203         END
 204     ) AS eng2014_rec2015,
 205     SUM(
 206         CASE WHEN exercice = 2015 THEN montant ELSE 0 END
 207     ) AS eng2015_rec2015
 208 FROM @engagements_rapproches
 209 GROUP BY el3;
 210
 211 -- Budget par el3  {{{1
 212
 213 DECLARE @budget_el3 TABLE (
 214     el3 VARCHAR(8) NOT NULL,
 215     exercice SMALLINT NOT NULL,
 216     montant MONEY NOT NULL,
 217     UNIQUE (el3, exercice)
 218 );
 219
 220 INSERT INTO @budget_el3
 221 SELECT
 222     el3,
 223     yr AS exercice,
 224     SUM(debit_value) AS montant
 225 FROM oas_balance
 226 WHERE
 227     cmpcode = 'AUF'
 228     AND yr IN (2014, 2015)
 229     AND balcode = 'BUDGET'
 230     AND repbasis = 5
 231     AND curcode = 'EUR'
 232 GROUP BY el3, yr;
 233
 234 -- Budget sans BSTG par el3
 235
 236 DECLARE @budget_sans_bstg_el3 TABLE (
 237     el3 VARCHAR(8) NOT NULL,
 238     exercice SMALLINT NOT NULL,
 239     montant MONEY NOT NULL,
 240     UNIQUE (el3, exercice)
 241 );
 242
 243 INSERT INTO @budget_sans_bstg_el3
 244 SELECT
 245     el3,
 246     yr AS exercice,
 247     SUM(debit_value) AS montant
 248 FROM oas_balance
 249 WHERE
 250     cmpcode = 'AUF'
 251     AND yr IN (2014, 2015)
 252     AND balcode = 'BUDGET'
 253     AND repbasis = 5
 254     AND curcode = 'EUR'
 255     AND el1 NOT LIKE '8%'
 256 GROUP BY el3, yr;
 257
 258 -- Préengagement par el3  {{{1
 259
 260 DECLARE @preengagement_el3 TABLE (
 261     el3 VARCHAR(8) NOT NULL,
 262     montant MONEY NOT NULL,
 263     UNIQUE (el3)
 264 );
 265
 266 INSERT INTO @preengagement_el3
 267 SELECT el3, SUM(full_value)
 268 FROM oas_balance
 269 WHERE
 270     cmpcode = 'AUF'
 271     AND yr = 2015
 272     AND balcode = 'PRENGAGEMENT'
 273     AND repbasis = 5
 274     AND curcode = 'EUR'
 275 GROUP BY el3;
 276
 277 -- Réel par el3   {{{1
 278
 279 DECLARE @reel_el3 TABLE (
 280     el3 VARCHAR(8) NOT NULL,
 281     exercice SMALLINT NOT NULL,
 282     montant MONEY NOT NULL,
 283     UNIQUE (el3, exercice)
 284 );
 285
 286 INSERT INTO @reel_el3
 287 SELECT el3, yr, SUM(full_value)
 288 FROM oas_balance
 289 WHERE
 290     cmpcode = 'AUF'
 291     AND yr IN (2014, 2015)
 292     AND balcode = 'REEL'
 293     AND repbasis = 5
 294     AND curcode = 'EUR'
 295     AND el1 LIKE '6%'
 296 GROUP BY el3, yr;
 297
 298 -- Provisions par el3  {{{1
 299
 300 DECLARE @provisions_el3 TABLE (
 301     el3 VARCHAR(8) NOT NULL,
 302     montant MONEY NOT NULL,
 303     UNIQUE (el3)
 304 );
 305
 306 INSERT INTO @provisions_el3
 307 SELECT l.el3, SUM(l.valuehome)
 308 FROM oas_docline l, oas_dochead h
 309 WHERE
 310     l.cmpcode = 'AUF'
 311     AND h.cmpcode = 'AUF'
 312     AND l.doccode = h.doccode
 313     AND l.docnum = h.docnum
 314     AND (
 315         h.doccode = 'F-CTP'
 316         OR (h.doccode = 'G-ODD-WEB' AND h.docnum BETWEEN 699 AND 702)
 317         OR (h.doccode IN ('F-ANN', 'G-ANN') AND h.origdoccode = 'F-CTP')
 318         OR (
 319             h.doccode IN ('F-ANN', 'G-ANN')
 320             AND h.origdoccode = 'G-ODD-WEB'
 321             AND h.origdocnum BETWEEN 699 AND 702
 322         )
 323     )
 324     AND h.yr = 2014
 325     AND l.el3 IS NOT NULL AND l.el3 != ''
 326 GROUP BY l.el3;
 327
 328 -- Engagement par el3  {{{1
 329
 330 DECLARE @engagement_el3 TABLE (
 331     el3 VARCHAR(8),
 332     montant MONEY NOT NULL,
 333     UNIQUE (el3)
 334 );
 335
 336 INSERT INTO @engagement_el3
 337 SELECT el3, SUM(full_value)
 338 FROM oas_balance
 339 WHERE
 340     cmpcode = 'AUF'
 341     AND balcode = 'ENGAGEMENT'
 342     AND repbasis = 5
 343     AND curcode = 'EUR'
 344 GROUP BY el3;
 345
 346 -- Sommaire par el3  {{{1
 347
 348 DECLARE @sommaire_el3 TABLE (
 349     el3 VARCHAR(8),
 350     budget_2014 MONEY NOT NULL,
 351     budget_2014_sans_bstg MONEY NOT NULL,
 352     budget_2015 MONEY NOT NULL,
 353     budget_2015_sans_bstg MONEY NOT NULL,
 354     preengagement MONEY NOT NULL,
 355     eng2014_rec2014 MONEY NOT NULL,
 356     eng2014_rec2015 MONEY NOT NULL,
 357     eng2015_rec2015 MONEY NOT NULL,
 358     eng2014_non_receptionnes MONEY NOT NULL,
 359     eng2015_non_receptionnes MONEY NOT NULL,
 360     reel_2014 MONEY NOT NULL,
 361     reel_2015 MONEY NOT NULL,
 362     provisions MONEY NOT NULL,
 363     engagement MONEY NOT NULL,
 364     UNIQUE (el3)
 365 );
 366
 367 INSERT INTO @sommaire_el3
 368 SELECT
 369     el3,
 370     SUM(budget_2014),
 371     SUM(budget_2014_sans_bstg),
 372     SUM(budget_2015),
 373     SUM(budget_2015_sans_bstg),
 374     SUM(preengagement),
 375     SUM(eng2014_rec2014),
 376     SUM(eng2014_rec2015),
 377     SUM(eng2015_rec2015),
 378     SUM(eng2014_non_receptionnes),
 379     SUM(eng2015_non_receptionnes),
 380     SUM(reel_2014),
 381     SUM(reel_2015),
 382     SUM(provisions),
 383     SUM(engagement)
 384 FROM (
 385     SELECT
 386         el3,
 387         0 AS budget_2014,
 388         0 AS budget_2014_sans_bstg,
 389         0 AS budget_2015,
 390         0 AS budget_2015_sans_bstg,
 391         0 AS preengagement,
 392         eng2014_rec2014,
 393         eng2014_rec2015,
 394         eng2015_rec2015,
 395         eng2014_non_receptionnes,
 396         eng2015_non_receptionnes,
 397         0 AS reel_2014,
 398         0 AS reel_2015,
 399         0 AS provisions,
 400         0 AS engagement
 401     FROM @engagements_non_rapproches_el3
 402     UNION ALL
 403     SELECT
 404         el3,
 405         0 AS budget_2014,
 406         0 AS budget_2014_sans_bstg,
 407         0 AS budget_2015,
 408         0 AS budget_2015_sans_bstg,
 409         0 AS preengagement,
 410         eng2014_rec2014,
 411         eng2014_rec2015,
 412         eng2015_rec2015,
 413         0 AS eng2014_non_receptionnes,
 414         0 AS eng2015_non_receptionnes,
 415         0 AS reel_2014,
 416         0 AS reel_2015,
 417         0 AS provisions,
 418         0 AS engagement
 419     FROM @engagements_rapproches_el3
 420     UNION ALL
 421     SELECT
 422         el3,
 423         CASE WHEN exercice = 2014 THEN montant ELSE 0 END AS budget_2014,
 424         0 AS budget_2014_sans_bstg,
 425         CASE WHEN exercice = 2015 THEN montant ELSE 0 END AS budget_2015,
 426         0 AS budget_2015_sans_bstg,
 427         0 AS preengagement,
 428         0 AS eng2014_rec2014,
 429         0 AS eng2014_rec2015,
 430         0 AS eng2015_rec2015,
 431         0 AS eng2014_non_receptionnes,
 432         0 AS eng2015_non_receptionnes,
 433         0 AS reel_2014,
 434         0 AS reel_2015,
 435         0 AS provisions,
 436         0 AS engagement
 437     FROM @budget_el3
 438     UNION ALL
 439     SELECT
 440         el3,
 441         0 AS budget_2014,
 442         CASE WHEN exercice = 2014 THEN montant ELSE 0 END AS budget_2014_sans_bstg,
 443         0 AS budget_2015,
 444         CASE WHEN exercice = 2015 THEN montant ELSE 0 END AS budget_2015_sans_bstg,
 445         0 AS preengagement,
 446         0 AS eng2014_rec2014,
 447         0 AS eng2014_rec2015,
 448         0 AS eng2015_rec2015,
 449         0 AS eng2014_non_receptionnes,
 450         0 AS eng2015_non_receptionnes,
 451         0 AS reel_2014,
 452         0 AS reel_2015,
 453         0 AS provisions,
 454         0 AS engagement
 455     FROM @budget_sans_bstg_el3
 456     UNION ALL
 457     SELECT
 458         el3,
 459         0 AS budget_2014,
 460         0 AS budget_2014_sans_bstg,
 461         0 AS budget_2015,
 462         0 AS budget_2015_sans_bstg,
 463         montant AS preengagement,
 464         0 AS eng2014_rec2014,
 465         0 AS eng2014_rec2015,
 466         0 AS eng2015_rec2015,
 467         0 AS eng2014_non_receptionnes,
 468         0 AS eng2015_non_receptionnes,
 469         0 AS reel_2014,
 470         0 AS reel_2015,
 471         0 AS provisions,
 472         0 AS engagement
 473     FROM @preengagement_el3
 474     UNION ALL
 475     SELECT
 476         el3,
 477         0 AS budget_2014,
 478         0 AS budget_2014_sans_bstg,
 479         0 AS budget_2015,
 480         0 AS budget_2015_sans_bstg,
 481         0 AS preengagement,
 482         0 AS eng2014_rec2014,
 483         0 AS eng2014_rec2015,
 484         0 AS eng2015_rec2015,
 485         0 AS eng2014_non_receptionnes,
 486         0 AS eng2015_non_receptionnes,
 487         CASE WHEN exercice = 2014 THEN montant ELSE 0 END AS reel_2014,
 488         CASE WHEN exercice = 2015 THEN montant ELSE 0 END AS reel_2015,
 489         0 AS provisions,
 490         0 AS engagement
 491     FROM @reel_el3
 492     UNION ALL
 493     SELECT
 494         el3,
 495         0 AS budget_2014,
 496         0 AS budget_2014_sans_bstg,
 497         0 AS budget_2015,
 498         0 AS budget_2015_sans_bstg,
 499         0 AS preengagement,
 500         0 AS eng2014_rec2014,
 501         0 AS eng2014_rec2015,
 502         0 AS eng2015_rec2015,
 503         0 AS eng2014_non_receptionnes,
 504         0 AS eng2015_non_receptionnes,
 505         0 AS reel_2014,
 506         0 AS reel_2015,
 507         montant AS provisions,
 508         0 AS engagement
 509     FROM @provisions_el3
 510     UNION ALL
 511     SELECT
 512         el3,
 513         0 AS budget_2014,
 514         0 AS budget_2014_sans_bstg,
 515         0 AS budget_2015,
 516         0 AS budget_2015_sans_bstg,
 517         0 AS preengagement,
 518         0 AS eng2014_rec2014,
 519         0 AS eng2014_rec2015,
 520         0 AS eng2015_rec2015,
 521         0 AS eng2014_non_receptionnes,
 522         0 AS eng2015_non_receptionnes,
 523         0 AS reel_2014,
 524         0 AS reel_2015,
 525         0 AS provisions,
 526         montant AS engagement
 527     FROM @engagement_el3
 528 ) t
 529 GROUP BY t.el3;
 530
 531 -- Consommation 2015  {{{1
 532
 533 SELECT
 534     el3.code AS el3,
 535     el3.sname AS Projet,
 536     el3.name AS POB,
 537     grpI.code AS [Code implantation],
 538     grpI.name AS Implantation,
 539     grpN.code AS [Code objectif],
 540     grpN.name AS Objectif,
 541     grpR.code AS [Code rubrique],
 542     grpR.name AS Rubrique,
 543     budget_2015 AS Budget,
 544     budget_2015_sans_bstg AS [Budget sans BSTG],
 545     eng2014_rec2015 AS [Engagements 2014 reçus en 2015],
 546     eng2015_rec2015 AS [Engagements 2015 reçus en 2015],
 547     preengagement AS [Pré-engagement],
 548     eng2014_non_receptionnes AS [Engagements 2014 non reçus],
 549     eng2015_non_receptionnes AS [Engagements 2015 non reçus],
 550     (
 551         eng2014_rec2015 + eng2015_rec2015 + preengagement +
 552         eng2014_non_receptionnes + eng2015_non_receptionnes
 553     ) AS [Total engagé et pré-engagé],
 554     CASE
 555         WHEN budget_2015_sans_bstg != 0
 556             THEN (
 557                 eng2014_rec2015 + eng2015_rec2015 + preengagement +
 558                 eng2014_non_receptionnes + eng2015_non_receptionnes
 559             ) / budget_2015_sans_bstg
 560     END AS [Taux de consommation 1],
 561     reel_2015 AS [Réel 2015],
 562     -provisions AS [Provisions 2014],
 563     engagement AS [Engagement],
 564     CASE
 565         WHEN budget_2015_sans_bstg != 0
 566             THEN (reel_2015 + engagement) / budget_2015_sans_bstg
 567     END AS [Taux de consommation 2]
 568 FROM
 569     @sommaire_el3 e
 570     INNER JOIN oas_element el3
 571         ON el3.cmpcode = 'AUF'
 572         AND el3.elmlevel = 3
 573         AND el3.code = e.el3
 574     LEFT JOIN oas_grplist glI
 575         ON glI.cmpcode = 'AUF'
 576         AND glI.elmlevel = 3
 577         AND glI.code = e.el3
 578         AND glI.grpcode LIKE 'I%'
 579     LEFT JOIN oas_group grpI
 580         ON grpI.cmpcode = 'AUF'
 581         AND grpI.groupwhat = 3
 582         AND grpI.code = glI.grpcode
 583     LEFT JOIN oas_grplist glN
 584         ON glN.cmpcode = 'AUF'
 585         AND glN.elmlevel = 3
 586         AND glN.code = e.el3
 587         AND glN.grpcode LIKE 'N%'
 588     LEFT JOIN oas_group grpN
 589         ON grpN.cmpcode = 'AUF'
 590         AND grpN.groupwhat = 3
 591         AND grpN.code = glN.grpcode
 592     LEFT JOIN oas_grplist glR
 593         ON glR.cmpcode = 'AUF'
 594         AND glR.elmlevel = 3
 595         AND glR.code = e.el3
 596         AND glR.grpcode LIKE 'R%'
 597     LEFT JOIN oas_group grpR
 598         ON grpR.cmpcode = 'AUF'
 599         AND grpR.groupwhat = 3
 600         AND grpR.code = glR.grpcode
 601 WHERE
 602     budget_2015 != 0
 603     OR budget_2015_sans_bstg != 0
 604     OR preengagement != 0
 605     OR eng2014_non_receptionnes != 0
 606     OR eng2015_non_receptionnes != 0
 607     OR eng2014_rec2015 != 0
 608     OR eng2015_rec2015 != 0
 609     OR reel_2015 != 0
 610     OR provisions != 0
 611     OR engagement != 0
 612 ORDER BY e.el3
 613
 614 -- Consommation 2014  {{{1
 615
 616 SELECT
 617     el3.code AS el3,
 618     el3.sname AS Projet,
 619     el3.name AS POB,
 620     grpI.code AS [Code implantation],
 621     grpI.name AS Implantation,
 622     grpN.code AS [Code objectif],
 623     grpN.name AS Objectif,
 624     grpR.code AS [Code rubrique],
 625     grpR.name AS Rubrique,
 626     budget_2014 AS Budget,
 627     budget_2014_sans_bstg AS [Budget sans BSTG],
 628     eng2014_rec2014 AS [Engagements 2014 reçus en 2014],
 629     eng2014_non_receptionnes + eng2014_rec2015
 630         AS [Engagements 2014 non reçus en 2014],
 631     CASE
 632         WHEN budget_2014_sans_bstg != 0
 633             THEN eng2014_rec2014 / budget_2014_sans_bstg
 634     END AS [Taux exécution],
 635     CASE
 636         WHEN budget_2014_sans_bstg != 0
 637             THEN (eng2014_rec2014 + eng2014_rec2015 + eng2014_non_receptionnes)
 638                 / budget_2014_sans_bstg
 639     END AS [Taux engagement],
 640     reel_2014 AS [Réel],
 641     provisions AS Provisions,
 642     CASE
 643         WHEN budget_2014_sans_bstg != 0
 644             THEN reel_2014 / budget_2014_sans_bstg
 645     END AS [Taux de consommation]
 646 FROM
 647     @sommaire_el3 e
 648     INNER JOIN oas_element el3
 649         ON el3.cmpcode = 'AUF'
 650         AND el3.elmlevel = 3
 651         AND el3.code = e.el3
 652     LEFT JOIN oas_grplist glI
 653         ON glI.cmpcode = 'AUF'
 654         AND glI.elmlevel = 3
 655         AND glI.code = e.el3
 656         AND glI.grpcode LIKE 'I%'
 657     LEFT JOIN oas_group grpI
 658         ON grpI.cmpcode = 'AUF'
 659         AND grpI.groupwhat = 3
 660         AND grpI.code = glI.grpcode
 661     LEFT JOIN oas_grplist glN
 662         ON glN.cmpcode = 'AUF'
 663         AND glN.elmlevel = 3
 664         AND glN.code = e.el3
 665         AND glN.grpcode LIKE 'N%'
 666     LEFT JOIN oas_group grpN
 667         ON grpN.cmpcode = 'AUF'
 668         AND grpN.groupwhat = 3
 669         AND grpN.code = glN.grpcode
 670     LEFT JOIN oas_grplist glR
 671         ON glR.cmpcode = 'AUF'
 672         AND glR.elmlevel = 3
 673         AND glR.code = e.el3
 674         AND glR.grpcode LIKE 'R%'
 675     LEFT JOIN oas_group grpR
 676         ON grpR.cmpcode = 'AUF'
 677         AND grpR.groupwhat = 3
 678         AND grpR.code = glR.grpcode
 679 WHERE
 680     budget_2014 != 0
 681     OR budget_2014_sans_bstg != 0
 682     OR eng2014_non_receptionnes != 0
 683     OR eng2014_rec2014 != 0
 684     OR eng2014_rec2015 != 0
 685     OR reel_2014 != 0
 686     OR provisions != 0
 687 ORDER BY e.el3
 688
 689 -- vim: foldmethod=marker