|
1
|
ALTER FUNCTION [dbo].[Product_GetBudgetActualCompare] -- SELECT * FROM dbo.GetBudgetActualCompare('100','2011','2010','2009','scriberion','triadblank')
|
|
2
|
(
|
|
3
|
-- Add the parameters for the function here
|
|
4
|
@year VARCHAR(4),
|
|
5
|
@year_1 VARCHAR(4),
|
|
6
|
@year_2 VARCHAR(4),
|
|
7
|
@scenario_id varchar(20),
|
|
8
|
@costobjecttype VARCHAR(20),
|
|
9
|
@costcenter VARCHAR(20),
|
|
10
|
@employeeid VARCHAR(20)
|
|
11
|
|
|
12
|
)
|
|
13
|
RETURNS TABLE
|
|
14
|
AS
|
|
15
|
RETURN
|
|
16
|
(
|
|
17
|
--DECLARE @year VARCHAR(4) = '2023'
|
|
18
|
--DECLARE @year_1 VARCHAR(4) = '2022'
|
|
19
|
--DECLARE @year_2 VARCHAR(4) = '2021'
|
|
20
|
--DECLARE @scenario_id varchar(20) = '0000000004'
|
|
21
|
--DECLARE @costobjecttype VARCHAR(20) = ''
|
|
22
|
--DECLARE @costcenter VARCHAR(20) = ''
|
|
23
|
--DECLARE @employeeid VARCHAR(20) = ''
|
|
24
|
|
|
25
|
-- Add the SELECT statement with parameter references here
|
|
26
|
SELECT @year_2 year2,
|
|
27
|
bud.cc_group, bud.ce_group,
|
|
28
|
bud.CostCenter, bud.CostElement,
|
|
29
|
ISNULL(bud_2.amount, 0) amount_2,
|
|
30
|
ISNULL(bud_1.amount, 0) amount_1,
|
|
31
|
ISNULL(bud_0.amount, 0) actual_budgetyear,
|
|
32
|
bud.total_budget
|
|
33
|
FROM dbo.Product_GetBudgetSimulasi(@year, @scenario_id, @costobjecttype, @costcenter, @employeeid) bud
|
|
34
|
LEFT OUTER JOIN dbo.Product_GetBudgetActual(@year, @costcenter, @employeeid) bud_0
|
|
35
|
ON bud.CostCenter = bud_0.CostCenter
|
|
36
|
AND bud.CostElement = bud_0.BudgetAcc
|
|
37
|
LEFT OUTER JOIN dbo.Product_GetBudgetActual(@year_1, @costcenter, @employeeid) bud_1
|
|
38
|
ON bud.CostCenter = bud_1.CostCenter
|
|
39
|
AND bud.CostElement = bud_1.BudgetAcc
|
|
40
|
LEFT OUTER JOIN dbo.Product_GetBudgetActual(@year_2, @costcenter, @employeeid) bud_2
|
|
41
|
ON bud.CostCenter = bud_2.CostCenter
|
|
42
|
AND bud.CostElement = bud_2.BudgetAcc
|
|
43
|
)
|