1 CREATE PROCEDURE auf_p_p4_intero_cumul
7 @implantation
varchar(12),
14 SELECT @annee
AS exercice
,
19 CASE WHEN b.el1
LIKE '97%' OR
20 (b.el1
LIKE '1%' AND b.el3
NOT LIKE '9003%') OR
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'
29 SUM(CASE WHEN b.balcode
= 'BUDGET' THEN b.full_value
ELSE 0 END) AS budget
,
30 SUM(CASE WHEN b.balcode
= 'ESTIME' THEN b.full_value
ELSE 0 END) AS estime
,
31 SUM(CASE WHEN b.balcode
= 'BUDGET-MENS' THEN b.full_value
ELSE 0 END) AS mensul
,
32 SUM(CASE WHEN b.balcode
= 'REEL' THEN b.full_value
ELSE 0 END) AS reel
,
33 SUM(CASE WHEN b.balcode
= 'ENGAGE' THEN b.full_value
ELSE 0 END) AS engage
34 FROM auf_v_p4_projets_groupes g
35 INNER JOIN oas_balance b
ON b.el3
= g.code_budgetaire
36 WHERE b.yr
= @annee
AND
37 b.period
BETWEEN @periodeD
AND @periodeF
AND
40 b.balcode
NOT LIKE 'DBM%' AND
41 b.el3
LIKE @projet
+ '%' AND
42 b.el1
NOT LIKE '4%' AND
43 b.el1
LIKE @el1
+ '%' AND
44 g.region
LIKE @region
+ '%' AND
45 g.implantation
LIKE 'I' + RTRIM(LTRIM(@implantation
)) + '%'
46 GROUP BY b.el1
, b.el3
, g.nom_court
, g.nom
, g.region
, g.implantation
47 ORDER BY b.el1
, b.el3
;