Changé l'interrogation de cumul avancé pour un simple SELECT

[auf_coda_additions.git] / auf_p_p4_intero_cumul.sql

CREATE PROCEDURE auf_p_p4_intero_cumul
    @annee int,
    @periodeD int,
    @periodeF int,
    @projet varchar(7),
    @region varchar(12),
    @implantation varchar(12),
    @el1 varchar(6)

AS

SET NOCOUNT ON;

SELECT @annee AS exercice,
       b.el1,
       b.el3,
       g.nom_court,
       g.nom,
       CASE WHEN b.el1 LIKE '97%' OR
                 (b.el1 LIKE '1%' AND b.el3 NOT LIKE '9003%') OR
                 b.el1 LIKE '7%'
            THEN 'Recette'
            WHEN b.el1 LIKE '86%' OR b.el1 LIKE '986%' THEN 'Dépense BSTG'
            WHEN b.el1 LIKE '87%' OR b.el1 LIKE '987%' THEN 'Recette BSTG'
            ELSE 'Dépense'
       END AS nature,
       g.region,
       g.implantation,
       SUM(CASE WHEN b.balcode = 'BUDGET' THEN b.full_value ELSE 0 END) AS budget,
       SUM(CASE WHEN b.balcode = 'ESTIME' THEN b.full_value ELSE 0 END) AS estime,
       SUM(CASE WHEN b.balcode = 'BUDGET-MENS' THEN b.full_value ELSE 0 END) AS mensul,
       SUM(CASE WHEN b.balcode = 'REEL' THEN b.full_value ELSE 0 END) AS reel,
       SUM(CASE WHEN b.balcode = 'ENGAGE' THEN b.full_value ELSE 0 END) AS engage
FROM auf_v_p4_projets_groupes g
     INNER JOIN oas_balance b ON b.el3 = g.code_budgetaire
WHERE b.yr = @annee AND
      b.period BETWEEN @periodeD AND @periodeF AND
      b.curcode = 'EUR' AND
      b.repbasis = 5 AND
      b.balcode NOT LIKE 'DBM%' AND
      b.el3 LIKE @projet + '%' AND
      b.el1 NOT LIKE '4%' AND
      b.el1 LIKE @el1 + '%' AND
      g.region LIKE @region + '%' AND
      g.implantation LIKE 'I' + RTRIM(LTRIM(@implantation)) + '%'
GROUP BY b.el1, b.el3, g.nom_court, g.nom, g.region, g.implantation
ORDER BY b.el1, b.el3;
GO