|
1
|
CREATE PROCEDURE PRPTBUMONTHLY
|
|
2
|
(
|
|
3
|
@year VARCHAR(8)
|
|
4
|
,@year1 VARCHAR(8)
|
|
5
|
,@costcenter VARCHAR(20)
|
|
6
|
,@scenarioid VARCHAR(20)
|
|
7
|
,@employeeid VARCHAR(20)
|
|
8
|
,@costobjecttype VARCHAR(20)
|
|
9
|
)
|
|
10
|
AS
|
|
11
|
|
|
12
|
--DECLARE @year VARCHAR(8) = '2023'
|
|
13
|
--DECLARE @year1 VARCHAR(8) = '2022'
|
|
14
|
--DECLARE @costcenter VARCHAR(20) = ''
|
|
15
|
--DECLARE @scenarioid VARCHAR(20) = '0000000004'
|
|
16
|
--DECLARE @employeeid VARCHAR(20) = ''
|
|
17
|
--DECLARE @costobjecttype VARCHAR(20) = ''
|
|
18
|
|
|
19
|
DECLARE @year2 VARCHAR(4)
|
|
20
|
SET @year2 = CAST((CAST(@year1 AS INT) - 1) AS VARCHAR(4))
|
|
21
|
|
|
22
|
SELECT
|
|
23
|
dat.year2,
|
|
24
|
eg.ObjectDescription AS ce_group_desc,
|
|
25
|
ce.ObjectDescription AS ce_desc,
|
|
26
|
dat.ce_group, dat.CostElement,
|
|
27
|
SUM(dat.amount_2) amount2,
|
|
28
|
SUM(dat.amount_1) amount1,
|
|
29
|
SUM(dat.total_budget) total_budget,
|
|
30
|
SUM(dat.amount01) amount01,
|
|
31
|
SUM(dat.amount02) amount02,
|
|
32
|
SUM(dat.amount03) amount03,
|
|
33
|
SUM(dat.amount04) amount04,
|
|
34
|
SUM(dat.amount05) amount05,
|
|
35
|
SUM(dat.amount06) amount06,
|
|
36
|
SUM(dat.amount07) amount07,
|
|
37
|
SUM(dat.amount08) amount08,
|
|
38
|
SUM(dat.amount09) amount09,
|
|
39
|
SUM(dat.amount10) amount10,
|
|
40
|
SUM(dat.amount11) amount11,
|
|
41
|
SUM(dat.amount12) amount12
|
|
42
|
FROM dbo.Product_GetBudgetResultCompare(@year, @year1, @year2 ,@scenarioid, @costcenter, @employeeid, @costobjecttype) dat
|
|
43
|
LEFT JOIN dbo.PHROM0001 AS eg
|
|
44
|
ON eg.ObjectClass = 'EG' AND eg.ObjectID = dat.ce_group
|
|
45
|
AND eg.StartDate <= (@year + '0101') AND eg.EndDate >= (@year + '0101')
|
|
46
|
LEFT JOIN dbo.PHROM0001 AS ce
|
|
47
|
ON ce.ObjectClass = 'CE' AND ce.ObjectID = dat.CostElement
|
|
48
|
AND ce.StartDate <= (@year + '0101') AND ce.EndDate >= (@year + '0101')
|
|
49
|
WHERE (dat.CostCenter = @costcenter OR @costcenter = '')
|
|
50
|
GROUP BY dat.year2, dat.ce_group, dat.cc_group, dat.CostElement, eg.ObjectDescription, ce.ObjectDescription
|