|
1
|
CREATE PROCEDURE PRPTBUACTCOM
|
|
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
|
DECLARE @monthnow INT
|
|
21
|
|
|
22
|
SET @year2 = CAST((CAST(@year1 AS INT) - 1) AS VARCHAR(4))
|
|
23
|
SET @monthnow = MONTH(GETDATE())
|
|
24
|
|
|
25
|
SELECT
|
|
26
|
dat.year2,
|
|
27
|
eg.ObjectDescription AS ce_group_desc,
|
|
28
|
ce.ObjectDescription AS ce_desc,
|
|
29
|
dat.ce_group, dat.CostElement,
|
|
30
|
SUM(dat.amount_2) amount2,
|
|
31
|
SUM(dat.amount_1) amount1,
|
|
32
|
SUM(dat.actual_budgetyear) actual_budgetyear,
|
|
33
|
SUM(dat.total_budget) total_budget
|
|
34
|
FROM dbo.Product_GetBudgetActualCompare(@year, @year1, @year2, @scenarioid, @costobjecttype, @costcenter, @employeeid) dat
|
|
35
|
LEFT JOIN dbo.PHROM0001 AS eg
|
|
36
|
ON eg.ObjectClass = 'EG' AND eg.ObjectID = dat.ce_group
|
|
37
|
AND eg.StartDate <= (@year + '0101') AND eg.EndDate >= (@year + '0101')
|
|
38
|
LEFT JOIN dbo.PHROM0001 AS ce
|
|
39
|
ON ce.ObjectClass = 'CE' AND ce.ObjectID = dat.CostElement
|
|
40
|
AND ce.StartDate <= (@year + '0101') AND ce.EndDate >= (@year + '0101')
|
|
41
|
WHERE (dat.CostCenter = @costcenter OR @costcenter = '')
|
|
42
|
GROUP BY dat.year2, dat.ce_group, dat.cc_group, dat.CostElement, eg.ObjectDescription, ce.ObjectDescription
|