3f0f2384edb27c955d8702ae92edf1f5cd679f82
[auf_coda_additions.git] / db / procedures / auf_p_intero_cumul_dep_rec.sql
1 USE [coda-prod]
2 GO
3
4 SET ANSI_NULLS OFF
5 GO
6 SET QUOTED_IDENTIFIER OFF
7 GO
8
9 ALTER PROCEDURE [dbo].[auf_p_intero_cumul_dep_rec]
10 @projet varchar(6),
11 @exercice int,
12 @groupe varchar(10)
13 AS
14 SET NOCOUNT ON
15 DECLARE @periode int
16 DECLARE @like_el3 varchar(20)
17
18 SELECT @periode = 12
19
20 IF isnumeric(@projet) = 1 OR len(@projet) > 2
21 SELECT @like_el3 = @projet+'%'
22 ELSE
23 SELECT @like_el3 = '%'+@projet
24
25 CREATE TABLE #cumuls (
26 el3 varchar(12),
27 typecumul varchar(12) ,
28 montant money null
29 )
30
31 -- DEPENSES BUDGET
32 INSERT INTO #cumuls
33 SELECT
34 el3,
35 'DEP-BUDGET' typecumul,
36 SUM(full_value) mont
37 FROM oas_balance, oas_grplist imp
38 WHERE
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
55 GROUP BY el3
56
57 -- DEPENSES REEL
58 INSERT INTO #cumuls
59 SELECT
60 el3,
61 'DEP-REEL' typecumul,
62 SUM(full_value) mont
63 FROM oas_balance, oas_grplist imp
64 WHERE
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
81 GROUP BY el3
82
83 -- RECETTES BUDGET
84 INSERT INTO #cumuls
85 SELECT
86 el3,
87 'REC-BUDGET' typecumul,
88 SUM(full_value) mont
89 FROM oas_balance, oas_grplist imp
90 WHERE
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
103 GROUP BY el3
104
105 -- RECETTES REEL
106 INSERT INTO #cumuls
107 SELECT
108 el3,
109 'REC-REEL' typecumul,
110 SUM(full_value) mont
111 FROM oas_balance, oas_grplist imp
112 WHERE
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
125 GROUP BY el3
126
127 -- ENGAGEMENT --
128 INSERT INTO #cumuls
129 SELECT
130 el3,
131 'ENGAGEMENT' typecumul,
132 SUM(full_value) mont
133 FROM oas_balance, oas_grplist imp
134 WHERE
135 oas_balance.cmpcode = 'AUF'
136 AND imp.cmpcode = 'AUF'
137 AND curcode = 'EUR'
138 AND repbasis = 5
139 AND balcode = 'ENGAGEMENT'
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
148 GROUP BY el3
149
150 SELECT
151 cumul.el3 projetPoste,
152 e.sname nomProjet,
153 e.[name] nomPoste,
154 SUM(ISNULL(budget_depenses.montant,0)) budget_depense,
155 SUM(ISNULL(budget_recettes.montant,0)) budget_recette,
156 SUM(ISNULL(reel_recettes.montant,0)) reel_recette,
157 SUM(ISNULL(reel_depenses.montant,0)) reel_depense,
158 SUM(ISNULL(engages.montant,0)) engage
159 FROM
160 (
161 SELECT DISTINCT el3
162 FROM #cumuls
163 ) cumul
164 LEFT JOIN (
165 SELECT el3, typecumul, montant
166 FROM #cumuls
167 ) budget_depenses
168 ON cumul.el3 = budget_depenses.el3
169 AND budget_depenses.typecumul = 'DEP-BUDGET'
170 LEFT JOIN (
171 SELECT el3, typecumul, montant
172 FROM #cumuls
173 ) budget_recettes
174 ON cumul.el3 = budget_recettes.el3
175 AND budget_recettes.typecumul = 'REC-BUDGET'
176 LEFT JOIN (
177 SELECT el3, typecumul, montant
178 FROM #cumuls
179 ) reel_recettes
180 ON cumul.el3 = reel_recettes.el3
181 AND reel_recettes.typecumul = 'REC-REEL'
182 LEFT JOIN (
183 SELECT el3, typecumul, montant
184 FROM #cumuls
185 ) reel_depenses
186 ON cumul.el3 = reel_depenses.el3
187 AND reel_depenses.typecumul = 'DEP-REEL'
188 LEFT JOIN (
189 SELECT el3, typecumul, montant
190 FROM #cumuls
191 ) engages
192 ON cumul.el3 = engages.el3
193 AND engages.typecumul = 'ENGAGEMENT'
194 LEFT JOIN oas_element e
195 ON cumul.el3 = e.code
196 AND e.cmpcode = 'AUF'
197 AND e.elmlevel = 3
198 WHERE
199 (@exercice < 2014 AND LEN(cumul.el3) = 7 AND LEFT(cumul.el3,2) <> '95')
200 OR (@exercice >= 2014 AND LEN(cumul.el3) = 8 AND LEFT(cumul.el3,1) <> 'P')
201 GROUP BY cumul.el3, e.sname, e.[name]
202 ORDER BY cumul.el3
203 GO