SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

create procedure TMT_OutstandingCourseReminder
as
begin
select WF.WF_Id, WF.Number_of_reminders, WF.Course_id, WF.CP_Id, WF.P_Code, GP.P_First_Name, GP.P_Name, PN.Manager_code, CL.Course_desc, CP.CP_Status, isnull(CP.Date_assigned, WF.Z_Creation) as Date_assigned, cp.Date_started
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_CourseLang CL ON CL.Course_id=WF.Course_id AND CL.Language_Code=SI.Spoken_language
	LEFT JOIN TMT_CourseProgram CP ON CP.CP_Id=WF.CP_Id
	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.Drop_flag <> 'X'
AND 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.Course_id IS NOT NULL and (CP.CP_Id is null or CP.CP_Status<>3 ) --Complete
order by WF.P_code, CP.CP_Status
end

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