49c9b828a83d3cb0cb42764291d0857467052f7b
[auf_coda_additions.git] / auf_p_p4_intero_cumul.sql
1
2
3
4
5 --exec auf_p_p4_intero_cumul 2011,1,12,'91060PJ','','',''
6
7
8 CREATE procedure auf_p_p4_intero_cumul
9
10 @annee int ,
11 @periodeD int,
12 @periodeF int,
13 @projet varchar(7),
14 @region varchar(12),
15 @implantation varchar(12),
16 @el1 varchar(6)
17
18
19 as
20
21
22 SET NOCOUNT ON
23
24
25 create table #cumuls (el1 varchar(10),
26 el3 varchar(12),
27 balcode varchar(12),
28 nature varchar(30),
29 montant money null)
30
31
32 insert into #cumuls
33
34 select
35 el1,
36 el3,
37 balcode ,
38 case when el1 like '97%' then
39 'Recette'
40 when el1 like '2%' then
41 'Dépense'
42 when (el1 like '1%' AND el3 like '9003%') then
43 'Dépense'
44 when (el1 like '1%' AND el3 not like '9003%') then
45 'Recette'
46 when el1 like '6%' then
47 'Dépense'
48 when el1 like '7%' then
49 'Recette'
50 when el1 like '86%' then
51 'Dépense BSTG'
52 when el1 like '87%' then
53 'Recette BSTG'
54 when el1 like '987%' then
55 'Recette BSTG'
56 when el1 like '986%' then
57 'Dépense BSTG'
58 else
59 'Dépense'
60 end ,
61 sum(full_value) mont
62
63
64 from oas_balance
65
66 where yr = @annee and
67 period between @periodeD and @periodeF and
68 curcode = 'EUR' and
69 repbasis = 5 and
70 balcode not like 'DBM%' and
71 el3 like @projet + '%' and
72 el1 not like '4%' and
73 el1 like @el1 + '%'
74 -------------------------------------
75
76 group by el1,el3, balcode
77
78
79
80 select @annee exercice,
81 cumul.el1,
82 cumul.el3,
83 groupe.nom_court,
84 groupe.nom,
85 cumul.nature,
86 groupe.region,
87 groupe.implantation,
88 --groupe.implantation_nc,
89
90 sum(isnull(budget.montant,0)) budget,
91 sum(isnull(estime.montant,0)) estime,
92 sum(isnull(mensul.montant,0)) mensul,
93 sum(isnull(reel.montant,0)) reel ,
94 sum(isnull(engage.montant,0)) engage
95
96 from auf_v_p4_projets_groupes groupe,
97
98 ( select distinct el1,el3,nature
99 from #cumuls ) cumul left join
100 ( select el1,
101 el3,
102 balcode,
103 montant
104 from #cumuls) budget
105
106 on ( cumul.el1 = budget.el1 and
107 cumul.el3 = budget.el3 and
108 budget.balcode = 'BUDGET' )
109
110 left join
111 ( select el1,
112 el3,
113 balcode,
114 montant
115
116 from #cumuls) estime
117
118 on ( cumul.el1 = estime.el1 and
119 cumul.el3 = estime.el3 and
120 estime.balcode = 'ESTIME' )
121
122 left join
123 ( select el1,
124 el3,
125 balcode,
126 montant
127
128 from #cumuls) mensul
129
130 on ( cumul.el1 = mensul.el1 and
131 cumul.el3 = mensul.el3 and
132 mensul.balcode = 'BUDGET-MENS' )
133
134 left join
135 ( select el1,
136 el3,
137 balcode,
138 montant
139
140 from #cumuls) engage
141
142 on ( cumul.el1 = engage.el1 and
143 cumul.el3 = engage.el3 and
144 engage.balcode = 'ENGAGE' )
145
146 left join
147 ( select el1,
148 el3,
149 balcode,
150 montant
151 from #cumuls) reel
152
153 on ( cumul.el1 = reel.el1 and
154 cumul.el3 = reel.el3 and
155 reel.balcode = 'REEL' )
156
157 where cumul.el3 = groupe.code_budgetaire and
158 groupe.region like @region + '%' and
159 groupe.implantation like 'I' + rtrim(ltrim(@implantation)) + '%'
160
161 group by cumul.el1,
162 cumul.el3,
163 groupe.nom_court,
164 groupe.nom,
165 cumul.nature,
166 groupe.region ,
167 groupe.implantation
168 --groupe.implantation_nc
169 GO