Commit | Line | Data |
---|---|---|
9c0c19e6 EMS |
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; | |
2295d186 | 48 | GO |