6 SET QUOTED_IDENTIFIER
OFF
9 ALTER PROCEDURE [dbo
].
[auf_p_intero_cumul_dep_rec
]
16 DECLARE @like_el3
varchar(20)
20 IF isnumeric(@projet
) = 1 OR len(@projet
) > 2
21 SELECT @like_el3
= @projet
+'%'
23 SELECT @like_el3
= '%'+@projet
25 CREATE TABLE #
cumuls (
27 typecumul
varchar(12) ,
35 'DEP-BUDGET' typecumul
,
37 FROM oas_balance
, oas_grplist imp
39 oas_balance.cmpcode
= 'AUF'
40 AND imp.cmpcode
= 'AUF'
42 AND period
BETWEEN 1 AND @periode
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
54 AND imp.grpcode
LIKE @groupe
63 FROM oas_balance
, oas_grplist imp
65 oas_balance.cmpcode
= 'AUF'
66 AND imp.cmpcode
= 'AUF'
68 AND period
BETWEEN 1 AND @periode
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
80 AND imp.grpcode
LIKE @groupe
87 'REC-BUDGET' typecumul
,
89 FROM oas_balance
, oas_grplist imp
91 oas_balance.cmpcode
= 'AUF'
92 AND imp.cmpcode
= 'AUF'
94 AND period
BETWEEN 1 AND @periode
98 AND (el1
LIKE '1%' OR el1
LIKE '7%')
99 AND el3
LIKE @like_el3
102 AND imp.grpcode
LIKE @groupe
109 'REC-REEL' typecumul
,
111 FROM oas_balance
, oas_grplist imp
113 oas_balance.cmpcode
= 'AUF'
114 AND imp.cmpcode
= 'AUF'
116 AND period
BETWEEN 1 AND @periode
120 AND (el1
LIKE '1%' OR el1
LIKE '7%')
121 AND el3
LIKE @like_el3
124 AND imp.grpcode
LIKE @groupe
131 'ENGAGEMENT' typecumul
,
133 FROM oas_balance
, oas_grplist imp
135 oas_balance.cmpcode
= 'AUF'
136 AND imp.cmpcode
= 'AUF'
138 AND period
BETWEEN 1 AND @periode
141 AND balcode
= 'ENGAGEMENT'
142 AND el1
NOT LIKE '4%'
143 AND el1
NOT LIKE '96%'
144 AND el1
NOT LIKE '97%'
145 AND el1
NOT LIKE '98%'
146 AND len(rtrim(el3
)) <> 8
147 AND LEFT(el3
,2) <> '95'
148 AND el3
LIKE @like_el3
151 AND imp.grpcode
LIKE @groupe
155 cumul.el3 projetPoste
,
158 SUM(ISNULL(budget_depenses.montant
,0)) budget_depense
,
159 SUM(ISNULL(budget_recettes.montant
,0)) budget_recette
,
160 SUM(ISNULL(reel_recettes.montant
,0)) reel_recette
,
161 SUM(ISNULL(reel_depenses.montant
,0)) reel_depense
,
162 SUM(ISNULL(engages.montant
,0)) engage
169 SELECT el3
, typecumul
, montant
172 ON cumul.el3
= budget_depenses.el3
173 AND budget_depenses.typecumul
= 'DEP-BUDGET'
175 SELECT el3
, typecumul
, montant
178 ON cumul.el3
= budget_recettes.el3
179 AND budget_recettes.typecumul
= 'REC-BUDGET'
181 SELECT el3
, typecumul
, montant
184 ON cumul.el3
= reel_recettes.el3
185 AND reel_recettes.typecumul
= 'REC-REEL'
187 SELECT el3
, typecumul
, montant
190 ON cumul.el3
= reel_depenses.el3
191 AND reel_depenses.typecumul
= 'DEP-REEL'
193 SELECT el3
, typecumul
, montant
196 ON cumul.el3
= engages.el3
197 AND engages.typecumul
= 'ENGAGEMENT'
198 LEFT JOIN oas_element e
199 ON cumul.el3
= e.code
200 AND e.cmpcode
= 'AUF'
202 WHERE LEFT(cumul.el3
,2) <> '95'
203 GROUP BY cumul.el3
, e.sname
, e.
[name]