Project

General

Profile

Download (4.53 KB)

Feature #1374 » Product_GetBudgetSimulasi_20220713.sql

Tri Rizqiaty, 07/21/2022 02:54 PM

 
1 
??ALTER FUNCTION [dbo].[Product_GetBudgetSimulasi] -- SELECT * FROM dbo.GetBudgetSimulasi('100', '2011', 'scriberion', 'triadblank')
2
3 
(
4
5 
	-- Add the parameters for the function here
6
7 
	@year VARCHAR(4),
8
9 
	@scenario_id VARCHAR(20),
10
11 
	@CostObjectType VARCHAR(20),
12
13 
	@CostCenter VARCHAR(20),
14
15 
	@EmployeeID VARCHAR(20)
16
17 
)
18
19 
RETURNS TABLE
20
21 
AS
22
23 
RETURN
24
25 
(
26
27
28
29 
	--DECLARE @year varchar(8) = '2023'
30
31 
	--DECLARE @scenario_id varchar(20) = '0000000004'
32
33 
	--DECLARE @CostObjectType VARCHAR(20) = ''
34
35 
	--DECLARE @CostCenter VARCHAR(20) = ''
36
37 
	--DECLARE @EmployeeID VARCHAR(20) = ''
38
39
40
41 
	----DECLARE @budget_status varchar(20)
42
43 
	----SELECT @budget_status = MAX(budget_status) FROM hr_tr0702 WHERE (scenario_id = @scenario_id OR @scenario_id = '')
44
45
46
47 
	-- Add the SELECT statement with parameter references here
48
49 
	SELECT bud0.CostCenter, bud0.CostElement,
50
51 
	cg.RelationshipObject AS cc_group,
52
53 
	eg.RelationshipObject AS ce_group,
54
55 
	SUM(TotalAdjustment) 'total_budget',
56
57 
	SUM(Adjustment01) 'amount01',
58
59 
	SUM(Adjustment02) 'amount02',
60
61 
	SUM(Adjustment03) 'amount03',
62
63 
	SUM(Adjustment04) 'amount04',
64
65 
	SUM(Adjustment05) 'amount05',
66
67 
	SUM(Adjustment06) 'amount06',
68
69 
	SUM(Adjustment07) 'amount07',
70
71 
	SUM(Adjustment08) 'amount08',
72
73 
	SUM(Adjustment09) 'amount09',
74
75 
	SUM(Adjustment10) 'amount10',
76
77 
	SUM(Adjustment11) 'amount11',
78
79 
	SUM(Adjustment12) 'amount12'
80
81 
	, bud0.NewTariff
82
83 
	FROM dbo.PHRBU0001 bud0
84
85 
	LEFT JOIN dbo.PHROM0002 AS cg
86
87 
		ON cg.ObjectClass = 'CC' AND cg.RelationshipClass = 'CG'
88
89 
		AND cg.StartDate <= (@year + '1231') AND cg.EndDate >= (@year + '1231')
90
91 
		AND cg.ObjectID = bud0.CostCenter
92
93 
	LEFT JOIN dbo.PHROM0002 AS eg
94
95 
		ON eg.ObjectClass = 'CE' AND eg.RelationshipClass = 'EG'
96
97 
		AND eg.StartDate <= (@year + '1231') AND eg.EndDate >= (@year + '1231')
98
99 
		AND eg.ObjectID = bud0.CostElement
100
101 
	WHERE LEFT(bud0.StartDate, 4) =  @year
102
103 
		  AND bud0.NewTariff = ( SELECT MAX(NewTariff) FROM PHRBU0001 WHERE (Scenario = @scenario_id OR @scenario_id = ''))
104
105 
		  AND (bud0.Scenario = @scenario_id OR @scenario_id = '')
106
107 
		  AND (bud0.CostObjectType = @CostObjectType OR @CostObjectType = '')
108
109 
		  AND (bud0.CostCenter = @CostCenter OR @CostCenter = '')
110
111 
		  AND (bud0.CostObjectID = @EmployeeID OR @EmployeeID = '')
112
113 
	GROUP BY bud0.CostCenter, bud0.CostElement , bud0.NewTariff, cg.RelationshipObject, eg.RelationshipObject
114
115 
)
116
117
(6-6/10)