[#2359] Ajouté des colonnes à l'interrogation de cumul avancé
[auf_coda_additions.git] / auf_p_p4_intero_cumul.sql
CommitLineData
9c0c19e6
EMS
1CREATE 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
10AS
11
12SET NOCOUNT ON;
13
14SELECT @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,
f7493cc2
EMS
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,
9c0c19e6
EMS
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
94FROM auf_v_p4_projets_groupes g
95 INNER JOIN oas_balance b ON b.el3 = g.code_budgetaire
f7493cc2
EMS
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
9c0c19e6
EMS
99WHERE 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)) + '%'
109GROUP BY b.el1, b.el3, g.nom_court, g.nom, g.region, g.implantation
110ORDER BY b.el1, b.el3;
2295d186 111GO