Project

General

Profile

Download (4.81 KB)

Bug #1203 » Product_GetBudgetSimulasi_20220621.sql

Tri Rizqiaty, 06/29/2022 10:00 AM

 
1 
??USE [MinovaES_Bank_Kalteng_Dev]
2
3 
GO
4
5 
/****** Object:  UserDefinedFunction [dbo].[Product_GetBudgetSimulasi]    Script Date: 21/06/2022 10.24.59 ******/
6
7 
SET ANSI_NULLS ON
8
9 
GO
10
11 
SET QUOTED_IDENTIFIER ON
12
13 
GO
14
15 
ALTER FUNCTION [dbo].[Product_GetBudgetSimulasi] -- SELECT * FROM dbo.GetBudgetSimulasi('100', '2011', 'scriberion', 'triadblank')
16
17 
(
18
19 
	-- Add the parameters for the function here
20
21 
	@year VARCHAR(4),
22
23 
	@scenario_id VARCHAR(20),
24
25 
	@CostObjectType VARCHAR(20),
26
27 
	@CostCenter VARCHAR(20),
28
29 
	@EmployeeID VARCHAR(20)
30
31 
)
32
33 
RETURNS TABLE
34
35 
AS
36
37 
RETURN
38
39 
(
40
41
42
43 
	--DECLARE @year varchar(8) = '2023'
44
45 
	--DECLARE @scenario_id varchar(20) = '0000000004'
46
47 
	--DECLARE @CostObjectType VARCHAR(20) = ''
48
49 
	--DECLARE @CostCenter VARCHAR(20) = ''
50
51 
	--DECLARE @EmployeeID VARCHAR(20) = ''
52
53
54
55 
	----DECLARE @budget_status varchar(20)
56
57 
	----SELECT @budget_status = MAX(budget_status) FROM hr_tr0702 WHERE (scenario_id = @scenario_id OR @scenario_id = '')
58
59
60
61 
	-- Add the SELECT statement with parameter references here
62
63 
	SELECT bud0.CostCenter, bud0.CostElement,
64
65 
	cg.RelationshipObject AS cc_group,
66
67 
	eg.RelationshipObject AS ce_group,
68
69 
	SUM(TotalAmount) 'total_budget',
70
71 
	SUM(Month01) 'amount01',
72
73 
	SUM(Month02) 'amount02',
74
75 
	SUM(Month03) 'amount03',
76
77 
	SUM(Month04) 'amount04',
78
79 
	SUM(Month05) 'amount05',
80
81 
	SUM(Month06) 'amount06',
82
83 
	SUM(Month07) 'amount07',
84
85 
	SUM(Month08) 'amount08',
86
87 
	SUM(Month09) 'amount09',
88
89 
	SUM(Month10) 'amount10',
90
91 
	SUM(Month11) 'amount11',
92
93 
	SUM(Month12) 'amount12'
94
95 
	, bud0.NewTariff
96
97 
	FROM dbo.PHRBU0001 bud0
98
99 
	LEFT JOIN dbo.PHROM0002 AS cg
100
101 
		ON cg.ObjectClass = 'CC' AND cg.RelationshipClass = 'CG'
102
103 
		AND cg.StartDate <= (@year + '1231') AND cg.EndDate >= (@year + '1231')
104
105 
		AND cg.ObjectID = bud0.CostCenter
106
107 
	LEFT JOIN dbo.PHROM0002 AS eg
108
109 
		ON eg.ObjectClass = 'CE' AND eg.RelationshipClass = 'EG'
110
111 
		AND eg.StartDate <= (@year + '1231') AND eg.EndDate >= (@year + '1231')
112
113 
		AND eg.ObjectID = bud0.CostElement
114
115 
	WHERE LEFT(bud0.StartDate, 4) =  @year
116
117 
		  AND bud0.NewTariff = ( SELECT MAX(NewTariff) FROM PHRBU0001 WHERE (Scenario = @scenario_id OR @scenario_id = ''))
118
119 
		  AND (bud0.Scenario = @scenario_id OR @scenario_id = '')
120
121 
		  AND (bud0.CostObjectType = @CostObjectType OR @CostObjectType = '')
122
123 
		  AND (bud0.CostCenter = @CostCenter OR @CostCenter = '')
124
125 
		  AND (bud0.CostObjectID = @EmployeeID OR @EmployeeID = '')
126
127 
	GROUP BY bud0.CostCenter, bud0.CostElement , bud0.NewTariff, cg.RelationshipObject, eg.RelationshipObject
128
129 
)
130
131
(10-10/21)