-------------
ALTER PROCEDURE [dbo].[USP_IsThisStepOKToAdd] 
	@ModId int,
	@ModuleName varchar(50),
	@Step int,
	@DueDate DateTime,
	@Result bit output,
	@Message Int OUTPUT
AS
BEGIN
	SET @Message = ''

	DECLARE @MaxStepClosed Int, @MinStepOpen Int, @MinDueDateForMinStep DateTime, @MinDateForNextStep DateTime

	SET @MaxStepClosed = 0
	SET @MinStepOpen = 0
	SET @MinDueDateForMinStep = GetDate()

	IF EXISTS(SELECT 1 FROM dbo.Tab_WorkflowActivity WHERE ModuleRecordID = @ModId AND ModuleName = @ModuleName)
		BEGIN
			IF NOT EXISTS(SELECT 1 FROM dbo.Tab_WorkflowActivity WHERE ModuleRecordID = @ModId AND ModuleName = @ModuleName AND (Step = (@Step - 1) OR @Step = 1))
				BEGIN
					SET @Message = 4 --'No record was found for a Step prior to the one that you are trying to add. Please add an activity for a previous step first and then try this again.' 
					SET @Result = 0
					RETURN
				END

				SELECT @MaxStepClosed = MAX(Step) FROM Tab_WorkflowActivity WHERE ModuleRecordID = @ModId AND ModuleName = @ModuleName AND DoneDate IS NOT NULL;
				SELECT @MinStepOpen = MIN(Step), @MinDueDateForMinStep = MIN(DueDate) FROM Tab_WorkflowActivity WHERE ModuleRecordID = @ModId AND ModuleName = @ModuleName AND DoneDate IS NULL;
				SELECT @MinDateForNextStep = MIN(DueDate) FROM Tab_WorkflowActivity WHERE ModuleRecordID = @ModId AND ModuleName = @ModuleName AND Step > @Step;

				IF @Step < @MaxStepClosed 				
					BEGIN
						SET @Message =  1 --'An activity in the Step greater then this Step chosen by you has been completed. So you will not be able to add a new activity in this step.' 
						SET @Result = 0
						RETURN
					END
				IF @Step > @MinStepOpen AND @DueDate < @MinDueDateForMinStep
					BEGIN
						SET @Message = 2 --'An activity in the Step prior then this Step chosen by you has a Due Date ahead of the Due Date chosen by you for this step. So you will not be able to add a new activity in this step.' 
						SET @Result = 0
						RETURN
					END
				IF @DueDate > @MinDateForNextStep 
					BEGIN
						SET @Message = 3 --'An activity in a step higher then the one you were trying to save, has a due date prior to this date. You can't this activity.' 
						SET @Result = 0
						RETURN
					END
		END
	ELSE IF @Step <> 1
		BEGIN
			SET @Message = 5 --'Workflow doesn't have activity defined yet. Please start with Step 1 first and then go on to higher steps.' 
			SET @Result = 0
			RETURN
		END

		SET @Result = 1
		
	
END
print @Result