BANK KALTENG » Bank Kalteng (Phase 2)

??USE [MinovaES_Bank_Kalteng_Dev]

GO

/****** Object:  UserDefinedFunction [dbo].[Product_GetBudgetActualMonthly]    Script Date: 21/06/2022 10.10.05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[Product_GetBudgetActualMonthly] -- SELECT * FROM dbo.[GetBudgetActualMonthly]('100', '2010', '01', '12', 'scriberion', 'triadblank')

(	

	-- Add the parameters for the function here

	@year VARCHAR(4),

	@month_awal VARCHAR(2),

	@month_akhir VARCHAR(2),

	@CostCenter VARCHAR(20),

	@EmployeeID VARCHAR(20)

)

RETURNS TABLE

AS

RETURN 

(

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

	--DECLARE @month_awal VARCHAR(2) = '01'

	--DECLARE @month_akhir VARCHAR(2) = '12'

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

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

	-- Add the SELECT statement with parameter references here

	SELECT header.CostCenter

			, wt_def.BudgetAcc 

			,cg.RelationshipObject AS cc_group	

			,eg.RelationshipObject AS ce_group

			,CASE

			WHEN wt_def.Regular <> 'Y'

				THEN (SUM( CAST(dbo.GetPEN(pay_month.Amount) AS DECIMAL) + 

					  CAST(ISNULL(dbo.GetPEN(pay_month_retro.Amount), '0') AS DECIMAL) )) 

			ELSE (SUM(CAST(dbo.GetPEN(pay_month.Amount) AS DECIMAL) + 

					  CAST(ISNULL(dbo.GetPEN(pay_month_retro.Amount), '0') AS DECIMAL) ) * (12/MAX(header.RunPeriodMonth)))

			END

			AS 'amount'

	FROM dbo.PHRPYTR0300 header

	INNER JOIN dbo.PHRPYTR0301 pay_month 

		ON header.EmployeeID = pay_month.EmployeeID

		   AND header.PayPeriodMonth = pay_month.PayPeriodMonth

		   AND header.PayPeriodYear = pay_month.PayPeriodYear

		   AND header.RunPeriodMonth = pay_month.RunPeriodMonth

		   AND header.RunPeriodYear = pay_month.RunPeriodYear

		   AND header.SplitIndicator = pay_month.SplitIndicator

	LEFT OUTER JOIN dbo.PHRPYTR0301RET pay_month_retro

		ON pay_month_retro.EmployeeID = pay_month.EmployeeID

		   AND pay_month_retro.PayPeriodMonth = pay_month.PayPeriodMonth

		   AND pay_month_retro.PayPeriodYear = pay_month.PayPeriodYear

		   AND pay_month_retro.RunPeriodMonth = pay_month.RunPeriodMonth

		   AND pay_month_retro.RunPeriodYear = pay_month.RunPeriodYear

		   AND pay_month_retro.SplitIndicator = pay_month.SplitIndicator

		   AND pay_month_retro.WageType = pay_month.WageType

	INNER JOIN dbo.PHRPYCU0300 wt_def

		ON pay_month.WageType = wt_def.WageTypeDefinition

		LEFT OUTER JOIN

		(SELECT assignment.EmployeeID, om_p.CostElement

		FROM dbo.PHRPA0002 assignment

		INNER JOIN dbo.PHROM0006 om_p 

			ON om_p.ObjectID = assignment.Position

		WHERE (om_p.StartDate <= (@year + '1231') AND om_p.EndDate >= (@year + '1231')) AND

			  (assignment.StartDate <= (@year + '1231') AND assignment.EndDate >= (@year + '1231'))

		)ce_on_emp

		ON header.EmployeeID = ce_on_emp.EmployeeID

	LEFT JOIN dbo.PHROM0002 AS cg

		ON cg.ObjectClass = 'CC' AND cg.RelationshipClass = 'CG'

		AND cg.ObjectID = header.CostCenter

		AND cg.StartDate <= (@year + '1231') AND cg.EndDate >= (@year + '1231')

	LEFT JOIN dbo.PHROM0002 AS eg

		ON eg.ObjectClass = 'CE' AND eg.RelationshipClass = 'EG'

		AND eg.ObjectID = wt_def.BudgetAcc

		AND eg.StartDate <= (@year + '1231') AND eg.EndDate >= (@year + '1231') 

	WHERE header.PayPeriodYear = @year

		AND header.RunPeriodYear = @year

		AND wt_def.StartDate <= (@year + '1231')

		AND wt_def.EndDate >= (@year + '1231')

		--AND wt_def.IncludeToBudget = '1'

		AND wt_def.BudgetAcc <> ''

		AND header.PayPeriodMonth = header.RunPeriodMonth

		AND header.RunPeriodMonth >= @month_awal

		AND header.RunPeriodMonth <= @month_akhir

		AND (header.CostCenter = @CostCenter OR @CostCenter = '')

		AND (header.EmployeeID = @EmployeeID OR @EmployeeID = '')

	GROUP BY header.CostCenter, wt_def.BudgetAcc, wt_def.Regular ,cg.RelationshipObject ,eg.RelationshipObject 

)