auf_p_p4_intero_cumul.sql

   1 CREATE PROCEDURE auf_p_p4_intero_cumul
   2     @annee int,
   3     @periodeD int,
   4     @periodeF int,
   5     @projet varchar(7),
   6     @region varchar(12),
   7     @implantation varchar(12),
   8     @el1 varchar(6)
   9
  10 AS
  11
  12 SET NOCOUNT ON;
  13
  14 SELECT @annee AS exercice,
  15        b.el1,
  16        b.el3,
  17        g.nom_court,
  18        g.nom,
  19        CASE WHEN b.el1 LIKE '97%' OR
  20                  (b.el1 LIKE '1%' AND b.el3 NOT LIKE '9003%') OR
  21                  b.el1 LIKE '7%'
  22             THEN 'Recette'
  23             WHEN b.el1 LIKE '86%' OR b.el1 LIKE '986%' THEN 'Dépense BSTG'
  24             WHEN b.el1 LIKE '87%' OR b.el1 LIKE '987%' THEN 'Recette BSTG'
  25             ELSE 'Dépense'
  26        END AS nature,
  27        g.region,
  28        g.implantation,
  29        SUM(CASE WHEN b.balcode = 'BUDGET' THEN b.full_value ELSE 0 END) AS budget,
  30        SUM(CASE WHEN b.balcode = 'ESTIME' THEN b.full_value ELSE 0 END) AS estime,
  31        SUM(CASE WHEN b.balcode = 'BUDGET-MENS' THEN b.full_value ELSE 0 END) AS mensul,
  32        SUM(CASE WHEN b.balcode = 'REEL' THEN b.full_value ELSE 0 END) AS reel,
  33        SUM(CASE WHEN b.balcode = 'ENGAGE' THEN b.full_value ELSE 0 END) AS engage
  34 FROM auf_v_p4_projets_groupes g
  35      INNER JOIN oas_balance b ON b.el3 = g.code_budgetaire
  36 WHERE b.yr = @annee AND
  37       b.period BETWEEN @periodeD AND @periodeF AND
  38       b.curcode = 'EUR' AND
  39       b.repbasis = 5 AND
  40       b.balcode NOT LIKE 'DBM%' AND
  41       b.el3 LIKE @projet + '%' AND
  42       b.el1 NOT LIKE '4%' AND
  43       b.el1 LIKE @el1 + '%' AND
  44       g.region LIKE @region + '%' AND
  45       g.implantation LIKE 'I' + RTRIM(LTRIM(@implantation)) + '%'
  46 GROUP BY b.el1, b.el3, g.nom_court, g.nom, g.region, g.implantation
  47 ORDER BY b.el1, b.el3;
  48 GO