SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TMT_OutstandingTestReminder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TMT_OutstandingTestReminder]
go

create procedure TMT_OutstandingTestReminder
as
begin
select WF.WF_Id, WF.Number_of_reminders, WF.Test_id, WF.P_Code, GP.P_First_Name, GP.P_Name, PN.Manager_code, TL.Test_desc, WF.Z_Creation
from tmt_workflow WF
	INNER JOIN GEN_Systems_Users GSU ON GSU.P_Code=WF.P_Code
		INNER JOIN Security_identity SI ON SI.Login_Name = GSU.U_Login_Name
			LEFT JOIN TMT_TestLang TL ON TL.Test_id=WF.Test_id AND TL.Language_Code=SI.Spoken_language
	LEFT JOIN TMT_Person PN ON PN.P_Code=WF.P_Code
	LEFT JOIN GEN_People GP ON GP.P_Code=WF.P_Code

where WF.wf_status=1  -- ToDo
	AND WF.wf_type=2  -- Undertake
	and ( dateAdd( day, 4, WF.Date_Last_reminder) < GetDate() or WF.Date_Last_reminder is null)
	and WF.Test_id IS NOT NULL
end

grant execute on [dbo].[TMT_OutstandingTestReminder] to [public]
go