SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

create procedure TMT_TranslationWorkflowReminder
as
begin
select LD.Language_desc, WF.WF_Id, WF.P_Code, PN.Manager_code, AL.Action_Desc, KL.Kit_desc, CL.Course_desc, OL.Object_desc, AVL.Activity_desc, TL.Test_desc, TQL.Quest_desc, TPL.Prop_desc, WF.Number_of_reminders
from tmt_workflow WF
	LEFT JOIN TMT_ActionLang AL ON AL.Action_id=WF.Action_id AND AL.Language_Code=WF.Language_code
	LEFT JOIN TMT_KitLang KL ON KL.Kit_id=WF.Kit_id AND KL.Language_Code=WF.Language_code
	LEFT JOIN TMT_CourseLang CL ON CL.Course_id=WF.Course_id AND CL.Language_Code=WF.Language_code
	LEFT JOIN TMT_ObjectLang OL ON OL.[Object_id]=WF.[Object_id] AND OL.Language_Code=WF.Language_code
	LEFT JOIN TMT_ActivityLang AVL ON AVL.Activity_id=WF.Activity_id AND AVL.Language_Code=WF.Language_code
	LEFT JOIN TMT_TestLang TL ON TL.Test_id=WF.Test_id AND TL.Language_Code=WF.Language_code
	LEFT JOIN TMT_TestQuestLang TQL ON TQL.Quest_id=WF.Quest_id AND TQL.Language_Code=WF.Language_code
	LEFT JOIN TMT_TestProposalLang TPL ON TPL.Prop_id=WF.Prop_id AND TPL.Language_Code=WF.Language_code
	LEFT JOIN TMT_Person PN ON PN.P_Code=WF.P_Code
	LEFT JOIN gen_language_desc LD ON LD.Language_Code=WF.To_Lang_code and LD.Translate_code='E'
where 	wf.Drop_flag <> 'X'
	and WF.wf_type=1  -- Translate
	and WF.wf_status=1 -- Todo
	and (WF.Date_required<GetDate() or WF.Date_required is null) 
	and ( dateAdd( day, 4, WF.Date_Last_reminder) > GetDate() or WF.Date_Last_reminder is null)
order by WF.P_code
end

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