7430ee311e7e283341a6b1898981a76800c478c6
[auf_coda_additions.git] / 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