Ajouter le pré-engagement dans l'interro budgétaire
[auf_coda_additions.git] / db / procedures / auf_p_intero_cumul_dep_rec.sql
CommitLineData
22ab1788
EMS
1USE [coda-prod]
2GO
3
4SET ANSI_NULLS OFF
5GO
6SET QUOTED_IDENTIFIER OFF
7GO
8
9ALTER PROCEDURE [dbo].[auf_p_intero_cumul_dep_rec]
10 @projet varchar(6),
11 @exercice int,
12 @groupe varchar(10)
13AS
14SET NOCOUNT ON
15DECLARE @periode int
16DECLARE @like_el3 varchar(20)
17
18SELECT @periode = 12
19
20IF isnumeric(@projet) = 1 OR len(@projet) > 2
21 SELECT @like_el3 = @projet+'%'
22ELSE
23 SELECT @like_el3 = '%'+@projet
24
25CREATE TABLE #cumuls (
26 el3 varchar(12),
27 typecumul varchar(12) ,
28 montant money null
29)
30
31-- DEPENSES BUDGET
32INSERT INTO #cumuls
33SELECT
34 el3,
35 'DEP-BUDGET' typecumul,
36 SUM(full_value) mont
37FROM oas_balance, oas_grplist imp
38WHERE
39 oas_balance.cmpcode = 'AUF'
40 AND imp.cmpcode = 'AUF'
41 AND yr = @exercice
42 AND period BETWEEN 1 AND @periode
43 AND curcode = 'EUR'
44 AND repbasis = 5
45 AND balcode ='BUDGET'
46 AND (el1 LIKE '2%' OR el1 LIKE '6%' OR el1 LIKE '9%')
47 -- on exclut les repartitions --
48 AND el1 NOT LIKE '96%'
49 AND el1 NOT LIKE '97%'
50 AND el1 NOT LIKE '98%'
51 AND el3 LIKE @like_el3
52 AND el3 = imp.code
53 AND imp.elmlevel = 3
54 AND imp.grpcode LIKE @groupe
55GROUP BY el3
56
57-- DEPENSES REEL
58INSERT INTO #cumuls
59SELECT
60 el3,
61 'DEP-REEL' typecumul,
62 SUM(full_value) mont
63FROM oas_balance, oas_grplist imp
64WHERE
65 oas_balance.cmpcode = 'AUF'
66 AND imp.cmpcode = 'AUF'
67 AND yr = @exercice
68 AND period BETWEEN 1 AND @periode
69 AND curcode = 'EUR'
70 AND repbasis = 5
71 AND balcode ='REEL'
72 AND (el1 LIKE '2%' OR el1 LIKE '6%' OR el1 LIKE '9%')
73 -- on exclut les repartitions
74 AND el1 NOT LIKE '96%'
75 AND el1 NOT LIKE '97%'
76 AND el1 NOT LIKE '98%'
77 AND el3 LIKE @like_el3
78 AND el3 = imp.code
79 AND imp.elmlevel = 3
80 AND imp.grpcode LIKE @groupe
81GROUP BY el3
82
83-- RECETTES BUDGET
84INSERT INTO #cumuls
85SELECT
86 el3,
87 'REC-BUDGET' typecumul,
88 SUM(full_value) mont
89FROM oas_balance, oas_grplist imp
90WHERE
91 oas_balance.cmpcode = 'AUF'
92 AND imp.cmpcode = 'AUF'
93 AND yr = @exercice
94 AND period BETWEEN 1 AND @periode
95 AND curcode = 'EUR'
96 AND repbasis = 5
97 AND balcode ='BUDGET'
98 AND (el1 LIKE '1%' OR el1 LIKE '7%')
99 AND el3 LIKE @like_el3
100 AND el3 = imp.code
101 AND imp.elmlevel = 3
102 AND imp.grpcode LIKE @groupe
103GROUP BY el3
104
105-- RECETTES REEL
106INSERT INTO #cumuls
107SELECT
108 el3,
109 'REC-REEL' typecumul,
110 SUM(full_value) mont
111FROM oas_balance, oas_grplist imp
112WHERE
113 oas_balance.cmpcode = 'AUF'
114 AND imp.cmpcode = 'AUF'
115 AND yr = @exercice
116 AND period BETWEEN 1 AND @periode
117 AND curcode = 'EUR'
118 AND repbasis = 5
119 AND balcode ='REEL'
120 AND (el1 LIKE '1%' OR el1 LIKE '7%')
121 AND el3 LIKE @like_el3
122 AND el3 = imp.code
123 AND imp.elmlevel = 3
124 AND imp.grpcode LIKE @groupe
125GROUP BY el3
126
3a0cb4f5
EMS
127-- PRE-ENGAGEMENT --
128INSERT INTO #cumuls
129SELECT
130 el3,
131 'PRENGAGEMENT' typecumul,
132 SUM(full_value) mont
133FROM oas_balance, oas_grplist imp
134WHERE
135 oas_balance.cmpcode = 'AUF'
136 AND imp.cmpcode = 'AUF'
137 AND curcode = 'EUR'
138 AND repbasis = 5
139 AND balcode = 'PRENGAGEMENT'
140 AND el1 NOT LIKE '4%'
141 AND el1 NOT LIKE '96%'
142 AND el1 NOT LIKE '97%'
143 AND el1 NOT LIKE '98%'
144 AND el3 LIKE @like_el3
145 AND el3 = imp.code
146 AND imp.elmlevel = 3
147 AND imp.grpcode LIKE @groupe
148GROUP BY el3
149
f449635c 150-- ENGAGEMENT --
22ab1788
EMS
151INSERT INTO #cumuls
152SELECT
153 el3,
f449635c 154 'ENGAGEMENT' typecumul,
22ab1788
EMS
155 SUM(full_value) mont
156FROM oas_balance, oas_grplist imp
157WHERE
158 oas_balance.cmpcode = 'AUF'
159 AND imp.cmpcode = 'AUF'
22ab1788 160 AND curcode = 'EUR'
fc6fc44e 161 AND repbasis = 5
f449635c 162 AND balcode = 'ENGAGEMENT'
22ab1788
EMS
163 AND el1 NOT LIKE '4%'
164 AND el1 NOT LIKE '96%'
165 AND el1 NOT LIKE '97%'
166 AND el1 NOT LIKE '98%'
22ab1788
EMS
167 AND el3 LIKE @like_el3
168 AND el3 = imp.code
169 AND imp.elmlevel = 3
170 AND imp.grpcode LIKE @groupe
171GROUP BY el3
172
173SELECT
174 cumul.el3 projetPoste,
175 e.sname nomProjet,
176 e.[name] nomPoste,
177 SUM(ISNULL(budget_depenses.montant,0)) budget_depense,
178 SUM(ISNULL(budget_recettes.montant,0)) budget_recette,
179 SUM(ISNULL(reel_recettes.montant,0)) reel_recette,
180 SUM(ISNULL(reel_depenses.montant,0)) reel_depense,
3a0cb4f5
EMS
181 SUM(ISNULL(engages.montant,0)) engage,
182 SUM(ISNULL(preengagement.montant,0)) preengagement
22ab1788
EMS
183FROM
184 (
185 SELECT DISTINCT el3
186 FROM #cumuls
187 ) cumul
188 LEFT JOIN (
189 SELECT el3, typecumul, montant
190 FROM #cumuls
191 ) budget_depenses
192 ON cumul.el3 = budget_depenses.el3
193 AND budget_depenses.typecumul = 'DEP-BUDGET'
194 LEFT JOIN (
195 SELECT el3, typecumul, montant
196 FROM #cumuls
197 ) budget_recettes
198 ON cumul.el3 = budget_recettes.el3
199 AND budget_recettes.typecumul = 'REC-BUDGET'
200 LEFT JOIN (
201 SELECT el3, typecumul, montant
202 FROM #cumuls
203 ) reel_recettes
204 ON cumul.el3 = reel_recettes.el3
205 AND reel_recettes.typecumul = 'REC-REEL'
206 LEFT JOIN (
207 SELECT el3, typecumul, montant
208 FROM #cumuls
209 ) reel_depenses
210 ON cumul.el3 = reel_depenses.el3
211 AND reel_depenses.typecumul = 'DEP-REEL'
212 LEFT JOIN (
213 SELECT el3, typecumul, montant
214 FROM #cumuls
215 ) engages
216 ON cumul.el3 = engages.el3
f449635c 217 AND engages.typecumul = 'ENGAGEMENT'
3a0cb4f5
EMS
218 LEFT JOIN (
219 SELECT el3, typecumul, montant
220 FROM #cumuls
221 ) preengagement
222 ON cumul.el3 = preengagement.el3
223 AND preengagement.typecumul = 'PRENGAGEMENT'
22ab1788
EMS
224 LEFT JOIN oas_element e
225 ON cumul.el3 = e.code
226 AND e.cmpcode = 'AUF'
227 AND e.elmlevel = 3
4e57e206
EMS
228WHERE
229 (@exercice < 2014 AND LEN(cumul.el3) = 7 AND LEFT(cumul.el3,2) <> '95')
230 OR (@exercice >= 2014 AND LEN(cumul.el3) = 8 AND LEFT(cumul.el3,1) <> 'P')
22ab1788
EMS
231GROUP BY cumul.el3, e.sname, e.[name]
232ORDER BY cumul.el3
233GO