[#2359] Ajouté des colonnes à l'interrogation de cumul avancé
[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 AS code_implantation,
29 gi.sname AS nom_implantation,
30 CASE g.implantation
31 WHEN 'ICM1' THEN '10'
32 WHEN 'IGA1' THEN '11'
33 WHEN 'ITD1' THEN '12'
34 WHEN 'ICD1' THEN '13'
35 WHEN 'ICF1' THEN '14'
36 WHEN 'IBI1' THEN '15'
37 WHEN 'ICG1' THEN '16'
38 WHEN 'ICD2' THEN '18'
39 WHEN 'ICM2' THEN '19'
40 WHEN 'ISN1' THEN '20'
41 WHEN 'ISN2' THEN '21'
42 WHEN 'IGN1' THEN '22'
43 WHEN 'IMR1' THEN '23'
44 WHEN 'IML1' THEN '24'
45 WHEN 'IBF1' THEN '25'
46 WHEN 'INE1' THEN '26'
47 WHEN 'ICI1' THEN '27'
48 WHEN 'ITG1' THEN '28'
49 WHEN 'IBJ1' THEN '29'
50 WHEN 'IHT1' THEN '35'
51 WHEN 'IHT2' THEN '36'
52 WHEN 'IVN1' THEN '40'
53 WHEN 'IVN2' THEN '41'
54 WHEN 'IVN3' THEN '42'
55 WHEN 'IVN4' THEN '43'
56 WHEN 'ILA1' THEN '45'
57 WHEN 'ILA2' THEN '46'
58 WHEN 'IKH1' THEN '47'
59 WHEN 'IVU1' THEN '49'
60 WHEN 'ILB1' THEN '55'
61 WHEN 'ILB3' THEN '59'
62 WHEN 'ISY1' THEN '57'
63 WHEN 'ISY2' THEN '58'
64 WHEN 'IEG1' THEN '52'
65 WHEN 'IRO1' THEN '65'
66 WHEN 'IAM1' THEN '62'
67 WHEN 'IGE1' THEN '64'
68 WHEN 'IBG1' THEN '66'
69 WHEN 'IMD1' THEN '67'
70 WHEN 'IAL1' THEN '68'
71 WHEN 'IBG2' THEN '69'
72 WHEN 'IMG1' THEN '70'
73 WHEN 'IMG2' THEN '71'
74 WHEN 'IMU1' THEN '72'
75 WHEN 'IMU2' THEN '73'
76 WHEN 'IKM1' THEN '74'
77 WHEN 'ICA2' THEN '81'
78 WHEN 'IBR1' THEN '85'
79 WHEN 'IBE1' THEN '93'
80 WHEN 'IMA1' THEN '90'
81 WHEN 'ITN1' THEN '91'
82 WHEN 'IDZ1' THEN '94'
83 WHEN 'IDZ2' THEN '95'
84 WHEN 'IDZ3' THEN '96'
85 WHEN 'ICA1' THEN '01'
86 WHEN 'ICA2' THEN '02'
87 END AS no_implantation,
88 gr.name AS rubrique,
89 SUM(CASE WHEN b.balcode = 'BUDGET' THEN b.full_value ELSE 0 END) AS budget,
90 SUM(CASE WHEN b.balcode = 'ESTIME' THEN b.full_value ELSE 0 END) AS estime,
91 SUM(CASE WHEN b.balcode = 'BUDGET-MENS' THEN b.full_value ELSE 0 END) AS mensul,
92 SUM(CASE WHEN b.balcode = 'REEL' THEN b.full_value ELSE 0 END) AS reel,
93 SUM(CASE WHEN b.balcode = 'ENGAGE' THEN b.full_value ELSE 0 END) AS engage
94 FROM auf_v_p4_projets_groupes g
95 INNER JOIN oas_balance b ON b.el3 = g.code_budgetaire
96 LEFT JOIN oas_group gi ON gi.code = g.implantation
97 LEFT JOIN oas_group glr ON glr.code = b.el3
98 LEFT JOIN oas_group gr ON gr.code = glr.grpcode
99 WHERE b.yr = @annee AND
100 b.period BETWEEN @periodeD AND @periodeF AND
101 b.curcode = 'EUR' AND
102 b.repbasis = 5 AND
103 b.balcode NOT LIKE 'DBM%' AND
104 b.el3 LIKE @projet + '%' AND
105 b.el1 NOT LIKE '4%' AND
106 b.el1 LIKE @el1 + '%' AND
107 g.region LIKE @region + '%' AND
108 g.implantation LIKE 'I' + RTRIM(LTRIM(@implantation)) + '%'
109 GROUP BY b.el1, b.el3, g.nom_court, g.nom, g.region, g.implantation
110 ORDER BY b.el1, b.el3;
111 GO