USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_wf_inst_step]    Script Date: Sat 05-09-2026 7:01:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Silkwood Software
-- Create date: 04-09-2024
-- Description:	Checks whether the connected user has permission
-- to access a given workflow instance step ID according to the associated document ID. 
-- Input is a the id of the step to be checked.  
-- Output is a status string of 'Pass' or 'Fail'.
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

-- =============================================
CREATE PROCEDURE [internal].[usp_AUTHENTICATE_wf_inst_step] 

	@instance_step_to_check bigint,
	@step_authentication_result nvarchar(10) OUTPUT


AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  DECLARE 
  		@usersid varbinary(100)   = NULL, -- The SID of the connected user
		@documentid nvarchar(50);



  -- Select the document ID associated with the step
     SELECT @documentid = wid.doc_id
       FROM workflow_instances.workflow_instance_steps AS wis
 INNER JOIN workflow_instances.workflow_instance_definitions as wid
	     ON   wis.workflow_instance_id
		    = wid.workflow_instance_id
	  WHERE wis.workflow_instance_step_id
	       = @instance_step_to_check;

  IF @documentid IS NULL
   SET @step_authentication_result = 'Pass';
  ELSE -- A doc id exists
     BEGIN   -- Check if the document ID exists and the user has permission to access the document
			EXEC [internal].[usp_AUTHENTICATE_user_doc_id] 
 				@doc_id_to_check =  @documentid,
				@user_authentication_result = @step_authentication_result OUTPUT;
	 END

END
GO