BANK KALTENG » Bank Kalteng (Phase 2)

??USE [MinovaES_Bank_Kalteng_Dev]

GO

/****** Object:  UserDefinedFunction [dbo].[Product_GetBudgetResultCompare]    Script Date: 21/06/2022 10.17.14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[Product_GetBudgetResultCompare] -- SELECT * FROM dbo.GetBudgetResultCompare('100','2011','2010','2009','scriberion','triadblank')

(	

	-- Add the parameters for the function here

	@year VARCHAR(4),

	@year1 VARCHAR(4),

    @year2 VARCHAR(4),

	@scenarioid varchar(20),

	@CostCenter VARCHAR(20),

	@EmployeeID VARCHAR(20),

	@CostObjectType VARCHAR(20)

)	

RETURNS TABLE 

AS

RETURN 

(

	--DECLARE @year VARCHAR(4) = '2023'

	--DECLARE @year1 VARCHAR(4) = '2023'

	--DECLARE @year2 VARCHAR(4) = '2023'

	--DECLARE @scenarioid varchar(20) = '0000000004'

	--DECLARE @CostCenter VARCHAR(20) = ''

	--DECLARE @EmployeeID VARCHAR(20) = ''

	--DECLARE @CostObjectType VARCHAR(20) = ''

	-- Add the SELECT statement with parameter references here

	SELECT @year2 year2, 

	bud_0.cc_group, bud_0.ce_group,

	bud_0.CostCenter, bud_0.CostElement,

	ISNULL(bud_2.amount, 0) amount_2, ISNULL(bud_1.amount, 0) amount_1,

	bud_0.total_budget,

	bud_0.amount01,

	bud_0.amount02,

	bud_0.amount03,

	bud_0.amount04,

	bud_0.amount05,

	bud_0.amount06,

	bud_0.amount07,

	bud_0.amount08,

	bud_0.amount09,

	bud_0.amount10,

	bud_0.amount11,

	bud_0.amount12

	FROM dbo.Product_GetBudgetSimulasi(@year, @scenarioid, @CostObjectType, @CostCenter, @EmployeeID) bud_0

	LEFT OUTER JOIN dbo.Product_GetBudgetActual(@year1, @CostCenter, @EmployeeID) bud_1

	ON bud_0.CostCenter = bud_1.CostCenter

	   AND bud_0.CostElement = bud_1.BudgetAcc

	LEFT OUTER JOIN dbo.Product_GetBudgetActual(@year2, @CostCenter, @EmployeeID) bud_2

	ON bud_0.CostCenter = bud_2.CostCenter

	   AND bud_0.CostElement = bud_2.BudgetAcc

)