ALTER FUNCTION [dbo].[Product_GetBudgetSimulasi] -- SELECT * FROM dbo.GetBudgetSimulasi('100', '2011', 'scriberion', 'triadblank')
(	
	-- Add the parameters for the function here
	@year VARCHAR(4),
	@scenario_id VARCHAR(20),
	@CostObjectType VARCHAR(20),
	@CostCenter VARCHAR(20),
	@EmployeeID VARCHAR(20)
)
RETURNS TABLE 
AS
RETURN 
(

	--DECLARE @year varchar(8) = '2023'
	--DECLARE @scenario_id varchar(20) = '0000000004'
	--DECLARE @CostObjectType VARCHAR(20) = ''
	--DECLARE @CostCenter VARCHAR(20) = ''
	--DECLARE @EmployeeID VARCHAR(20) = ''

	----DECLARE @budget_status varchar(20)
	----SELECT @budget_status = MAX(budget_status) FROM hr_tr0702 WHERE (scenario_id = @scenario_id OR @scenario_id = '')

	-- Add the SELECT statement with parameter references here
	SELECT bud0.CostCenter, bud0.CostElement,
	cg.RelationshipObject AS cc_group,
	eg.RelationshipObject AS ce_group,
	SUM(TotalAdjustment) 'total_budget',
	SUM(Adjustment01) 'amount01',
	SUM(Adjustment02) 'amount02',
	SUM(Adjustment03) 'amount03',
	SUM(Adjustment04) 'amount04',
	SUM(Adjustment05) 'amount05',
	SUM(Adjustment06) 'amount06',
	SUM(Adjustment07) 'amount07',
	SUM(Adjustment08) 'amount08',
	SUM(Adjustment09) 'amount09',
	SUM(Adjustment10) 'amount10',
	SUM(Adjustment11) 'amount11',
	SUM(Adjustment12) 'amount12'
	, bud0.NewTariff 
	FROM dbo.PHRBU0001 bud0
	LEFT JOIN dbo.PHROM0002 AS cg
		ON cg.ObjectClass = 'CC' AND cg.RelationshipClass = 'CG'
		AND cg.StartDate <= (@year + '1231') AND cg.EndDate >= (@year + '1231')
		AND cg.ObjectID = bud0.CostCenter
	LEFT JOIN dbo.PHROM0002 AS eg
		ON eg.ObjectClass = 'CE' AND eg.RelationshipClass = 'EG'
		AND eg.StartDate <= (@year + '1231') AND eg.EndDate >= (@year + '1231')
		AND eg.ObjectID = bud0.CostElement
	WHERE LEFT(bud0.StartDate, 4) =  @year
		  AND bud0.NewTariff = ( SELECT MAX(NewTariff) FROM PHRBU0001 WHERE (Scenario = @scenario_id OR @scenario_id = ''))
		  AND (bud0.Scenario = @scenario_id OR @scenario_id = '')
		  AND (bud0.CostObjectType = @CostObjectType OR @CostObjectType = '')
		  AND (bud0.CostCenter = @CostCenter OR @CostCenter = '')
		  AND (bud0.CostObjectID = @EmployeeID OR @EmployeeID = '')
	GROUP BY bud0.CostCenter, bud0.CostElement , bud0.NewTariff, cg.RelationshipObject, eg.RelationshipObject
)