SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET ANSI_NULLS OFF
GO

ALTER FUNCTION [dbo].[fn_OSP_RequestData] (@EntityID as int, @dtDate as datetime)  
RETURNS @retTable TABLE (Ndx int,
	Entity_Id int,
	Surname sql_variant,
	Forename sql_variant,
	Employment_Start_Date datetime,
	Continuous_Service_Date datetime,
	Hours numeric(4,2),
	Days_Worked numeric(5,4),
	Public_Holiday_Pattern sql_variant,
	Working_Pattern sql_variant,
	PrimaryContract int,
	CurrencySymbol nvarchar(250),
	HourlyRate money,
	LeaveDate datetime,
	Valid_Until datetime) 
AS
BEGIN 
	
	insert @retTable

	SELECT TOP 1	dbo.DT_CONTRACT.Ndx,
		dbo.DT_CONTRACT.Entity_Id, 
		dbo.DT_PERSON.Surname AS Surname,
                      	CAST(dbo.DT_PERSON.Forename as nvarchar(128))  + ' (' + (SELECT TOP 1 CAST(Title as nvarchar(128)) FROM DT_JOB WHERE Entity_ID = CAST(DT_CONTRACT.Job as int) AND DT_JOB.Entity_Start_Date <= @dtDate AND (DT_JOB.Entity_End_Date IS NULL OR DT_JOB.Entity_End_Date >= @dtDate)) + ')' AS Forename,
		(SELECT TOP 1 Entity_Start_Date FROM DT_EMPLOYEE EMP WHERE EMP.Entity_ID = DTE.Entity_Id ORDER BY Entity_Start_Date ASC) AS Employment_Start_Date,
		CAST(DTE.Continuous_Service_Date AS datetime) AS Continuous_Service_Date,
		CAST(dbo.DT_CONTRACT.Hours AS numeric(4,2)) AS Hours,
		CAST(dbo.DT_CONTRACT.Days_Worked AS numeric(5,4)) AS Days_Worked,
		dbo.DT_CONTRACT.Public_Holiday_Pattern as Public_Holiday_Pattern,
		dbo.DT_CONTRACT.Working_Pattern as Working_Pattern,
		CAST(dbo.DT_CONTRACT.PrimaryContract AS int)  AS PrimaryContract,
		(SELECT     Description
                              FROM         LKP_COMBO_VALUES
                              WHERE     Ndx = cast(DT_PAY.Currency AS int)) AS CurrencySymbol,
		            CAST(CASE WHEN Pay_Hourly IS NULL THEN 
				ROUND(CASE basiscode.Code
				WHEN 'A' THEN CAST(Amount as money) / g.[WeeksinYear] / nullif(CAST(DT_CONTRACT.Hours as Numeric(8,2)), 0)
				WHEN 'M' THEN CAST(Amount as money) * 12 / g.[WeeksinYear] / nullif(CAST(DT_CONTRACT.Hours as Numeric(8,2)), 0)
				WHEN 'W' THEN CAST(Amount as money) / nullif(CAST(DT_CONTRACT.Hours as Numeric(8,2)), 0)
				WHEN 'D' THEN CAST(Amount as money) * CAST(Days_Worked as Numeric(5, 4)) / nullif(CAST(DT_CONTRACT.Hours as Numeric(8,2)), 0)
				WHEN 'H' THEN CAST(Amount as money) 
				ELSE 0 
				END, 2)
			ELSE
				Pay_Hourly
			END as money) AS HourlyRate,
		COALESCE((SELECT TOP 1 Entity_End_Date FROM DT_EMPLOYEE EMP WHERE EMP.Entity_ID = DTE.Entity_Id ORDER BY Entity_Start_Date DESC),
				(SELECT TOP 1 CAST(Fixed_Term_End_Date as datetime) FROM DT_CONTRACT CON WHERE CON.Entity_ID = LNK_EMPLOYEE_CONTRACT.Right_Join ORDER BY Entity_Start_Date DESC)
				) AS LeaveDate,
		dbo.MinDate(DT_CONTRACT.Entity_End_Date, DT_PAY.Entity_End_Date) AS Valid_Until

	FROM DT_PERSON
	
	INNER JOIN dbo.LNK_EMPLOYEE_PERSON
		ON dbo.LNK_EMPLOYEE_PERSON.Right_Join = dbo.DT_PERSON.Entity_Id
		AND LNK_EMPLOYEE_PERSON.Start_Date <= @dtDate AND (LNK_EMPLOYEE_PERSON.End_Date IS NULL OR LNK_EMPLOYEE_PERSON.End_Date >= @dtDate)
		AND DT_PERSON.Entity_Start_Date <= @dtDate AND (DT_PERSON.Entity_End_Date IS NULL OR DT_PERSON.Entity_End_Date >= @dtDate)
	
		
	INNER JOIN DT_EMPLOYEE DTE
		ON dbo.LNK_EMPLOYEE_PERSON.Left_Join = DTE.Entity_Id
		AND DTE.Entity_Start_Date <= @dtDate AND (DTE.Entity_End_Date IS NULL OR DTE.Entity_End_Date >= @dtDate)
	
		
	INNER JOIN dbo.LNK_EMPLOYEE_CONTRACT
		ON DTE.Entity_Id = dbo.LNK_EMPLOYEE_CONTRACT.Left_Join
		
	INNER JOIN DT_CONTRACT
		ON dbo.LNK_EMPLOYEE_CONTRACT.Right_Join = dbo.DT_CONTRACT.Entity_Id
		AND LNK_EMPLOYEE_CONTRACT.Start_Date <= @dtDate AND (LNK_EMPLOYEE_CONTRACT.End_Date IS NULL OR LNK_EMPLOYEE_CONTRACT.End_Date >= @dtDate)
		AND DT_CONTRACT.Entity_Start_Date <= @dtDate AND (DT_CONTRACT.Entity_End_Date IS NULL OR DT_CONTRACT.Entity_End_Date >= @dtDate)
		
	LEFT JOIN dbo.LNK_PAY_CONTRACT
		ON dbo.DT_CONTRACT.Entity_Id = dbo.LNK_PAY_CONTRACT.Right_Join
		AND LNK_PAY_CONTRACT.Start_Date <= @dtDate AND (LNK_PAY_CONTRACT.End_Date IS NULL OR LNK_PAY_CONTRACT.End_Date >= @dtDate)
		
	LEFT JOIN dbo.DT_PAY 
		ON dbo.LNK_PAY_CONTRACT.Left_Join = dbo.DT_PAY.Entity_Id
		AND DT_PAY.Entity_Start_Date <= @dtDate AND (DT_PAY.Entity_End_Date IS NULL OR DT_PAY.Entity_End_Date >= @dtDate)

	--For period figures etc
	INNER JOIN (SELECT AppTitle, ISNULL(WeeksinYear, 52) AS WeeksinYear FROM REF_GLOBALS) g
	ON g.AppTitle = 'HREvolution+'

	LEFT OUTER JOIN LKP_COMBO_VALUES basiscode
	ON basiscode.Ndx = CAST(DT_PAY.Basis as int)
						
	WHERE DT_CONTRACT.Entity_Id = @EntityID

	ORDER BY DT_CONTRACT.PrimaryContract DESC,  DT_CONTRACT.Hours DESC

RETURN
END





GO
UPDATE [dbo].[REF_SQLFIELDS] SET [Code]=N'--AccrualDate Emp Leave date or PIT if NULL)
COALESCE((SELECT TOP 1 ISNULL(Entity_End_Date, CAST(C.Fixed_Term_End_Date as datetime)) FROM DT_CONTRACT C WHERE C.Entity_Id = RT.Master_Id ORDER BY C.Entity_Start_Date DESC) , ''[PIT]'')' WHERE [Table_Name] = N'VW_ADV_ABSENCE_PLAN' AND [Field_Name] = N'AccrualDate' AND [CustomisationID] = 0