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 
)