USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_doc_ed_perm]    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: 18-12-2023
-- Description:	Checks whether the connected user has permission
-- to edit the metadata of a given document. 
-- Also returns fail if the id does not exist.
-- Input is a the id of the document to be checked.  
-- Output is a status string of 'Pass' or 'Fail'.

  -- To have edit permission for a document the user must first have view permission for that document.
  -- Viewing restrictions override editing permissions.  
  -- Edit permissions work differently from view permissions.  With view permissions a document
  -- is viewable by default.  With edit permissions the document is restricted by default. 
  -- A user has edit permission for a document if there is a chain from the document ID through
  -- either a function list or a people list to a sid in the sid list which is the sid for the user.
  -- If user is a controller then they have editing rights to any document they have viewing rights to
  -- except if the document is linked to a controller document group which is not linked to the connected user. 
  -- If the document is linked to a controller group then if the connected user is not part of that group
  -- then the user is excluded from editing rights.
  -- There are two layers of editing permissions.  At one level, controllers are granted exclusive editing
  -- permissions by an authoriser, referred to as controller level permission. Other controllers cannot alter those permissions.
  -- At the other level controllers grant editing permissions to editors, referred to as editor level permission.  Controller level permissions
  -- exclude and override any editor level permission.  A controller cannot delegate permission for a controller level permission. 


/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

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

	@doc_id_to_check_ep nvarchar(50) = NULL,
	@user_authentication_result_dep nvarchar(10) = NULL 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
		@view_permission  nvarchar(10)     = NULL,
		@iscontroller nvarchar(5)          = NULL,
		@dociscontrollergroup nvarchar(5)  = NULL,
		@sid_id bigint                     = NULL,
		@failure_type nvarchar(1000)       = NULL,
		@now datetime2(7)                  = SYSDATETIME(),
		@privilege_name nvarchar(50)       = '';

  -- Parameters which have been initialised at declaration but not explicitly set might be output as null to calling functions.
  SET @user_authentication_result_dep  = 'Fail'

  SET @usersid = SUSER_SID(ORIGINAL_LOGIN()); -- Read the SID of the connected user

  		SELECT @sid_id = sid_id
          FROM user_restr.sid_list
         WHERE sid = @usersid;
	


  -- Check if the user has viewing permission for the document.
  EXEC [internal].[usp_AUTHENTICATE_user_doc_id]
         @doc_id_to_check = @doc_id_to_check_ep,
		 @user_authentication_result = @view_permission OUTPUT;

  IF @view_permission = 'Fail' SET @failure_type = 'Document does not exist or user does not have access to it';

  IF @view_permission = 'Pass' -- The user has viewing permission for the document
    BEGIN 
	  EXEC [internal].[usp_AUTHENTICATE_user_role] 
	  		 @role_to_check = 'Controller',
			 @user_authentication_result = @iscontroller OUTPUT;
      -- If user is a controller then they have editing rights to any document they have viewing rights to
	  -- except if the document is linked to a controller document group which is not linked to the connected user. 

	  IF EXISTS -- Is the doc id linked to a controller group?
	    (SELECT cdgl.doc_id 
		   FROM xref.controller_doc_group_links AS cdgl
		  WHERE cdgl.doc_id = @doc_id_to_check_ep)
	  SET @dociscontrollergroup = 'Yes'
	  ELSE SET @dociscontrollergroup = 'No';


	  IF @dociscontrollergroup = 'Yes' -- If the document is linked to a controller group, test if the user is a member of that group and is also a controller
	  BEGIN 
		IF (EXISTS 
			  (SELECT sl.sid_id
				 FROM user_restr.sid_list AS sl
			LEFT JOIN user_restr.controller_doc_group_sid_links AS cdgsl
				   ON      sl.sid_id
					  = cdgsl.sid_id
			LEFT JOIN xref.controller_doc_group_names AS cdgn 
				   ON  cdgsl.controller_doc_group_name_id
					  = cdgn.controller_doc_group_name_id
			LEFT JOIN xref.controller_doc_group_links AS cdgl
				   ON   cdgn.controller_doc_group_name_id
					  = cdgl.controller_doc_group_name_id
	 			WHERE sl.sid = @usersid   			      
				  AND cdgl.doc_id = @doc_id_to_check_ep
			      AND (cdgsl.valid_from IS NULL OR cdgsl.valid_from <= @now)
				  AND (cdgsl.valid_until IS NULL OR cdgsl.valid_until >= @now)                  
                  ))
		 AND @iscontroller = 'Pass'
		 SET @user_authentication_result_dep = 'Pass';
		 ELSE SET @user_authentication_result_dep = 'Fail';
	 END
	 ELSE SET @user_authentication_result_dep = 'Pass';
		
	  -- If the document is not linked to a controller group then if the user is a controller then they have editing
	  -- rights to any document they have viewing rights to. 


	  IF @dociscontrollergroup = 'No' AND @user_authentication_result_dep = 'Pass' -- The user is not excluded by controller level restrictions
	  -- If the user is not excluded by controller level permissions but the document is not part of a controller group then permission will be determined by this segment.
		 BEGIN -- Check if the user is linked to the document group through either a function list or a people list
		   IF @iscontroller = 'Pass' SET @user_authentication_result_dep = 'Pass'  -- If the user is a controller at this step then they are granted permission
		   ELSE -- User is not a controller and is not excluded by controller level permissions
		    BEGIN
			  IF  
			        (EXISTS (SELECT df.function_id AS dfid
					           FROM base.document_id_list AS dil
						  LEFT JOIN xref.doc_group_links AS dgl
						            ON   dil.doc_id
									   = dgl.doc_id
						  LEFT JOIN xref.doc_group_names AS dgn
						            ON   dgl.doc_group_id
									   = dgn.doc_group_id
						  LEFT JOIN user_restr.doc_group_edit_perm_funct_lst AS dgepfl -- Check for membership of a duty function list
						            ON      dgn.doc_group_id
									   = dgepfl.doc_group_id
						  LEFT JOIN people.function_list_names AS fln
						            ON dgepfl.function_list_id
									   =  fln.function_list_id
						  LEFT JOIN people.function_lists AS fl
						            ON  fln.function_list_id
									   = fl.function_list_id
						  LEFT JOIN people.duty_functions AS df
						            ON   fl.function_id
									   = df.function_id
						  LEFT JOIN people.duty_function_sid_links AS dfsl
						            ON  dfsl.function_id
									   =  df.function_id
						  LEFT JOIN user_restr.sid_list AS sl
									ON dfsl.sid_id
									   = sl.sid_id
							  WHERE (dil.doc_id = @doc_id_to_check_ep
									 AND sl.sid = @usersid)
									 -- Check time-bound restrictions
									 AND (dgepfl.valid_from IS NULL OR dgepfl.valid_from <= @now)
									 AND (dgepfl.valid_until IS NULL OR dgepfl.valid_until >= @now)
									 AND (fl.valid_from IS NULL OR fl.valid_from <= @now)
									 AND (fl.valid_until IS NULL OR fl.valid_until >= @now)
									 AND (dfsl.valid_from IS NULL OR dfsl.valid_from <= @now)
									 AND (dfsl.valid_until IS NULL OR dfsl.valid_until >= @now)
  
                     UNION ALL

					        (SELECT pl.sid_id AS sid
					           FROM base.document_id_list AS dil
						  LEFT JOIN xref.doc_group_links AS dgl
						            ON   dil.doc_id
									   = dgl.doc_id
						  LEFT JOIN xref.doc_group_names AS dgn
						            ON   dgl.doc_group_id
									   = dgn.doc_group_id
						  LEFT JOIN user_restr.doc_group_edit_perm_people_lst AS dgeppl -- Check for membership of a people list
						            ON      dgn.doc_group_id
									   = dgeppl.doc_group_id
						  LEFT JOIN people.people_list_names AS pln
						            ON dgeppl.people_list_id
									   =  pln.people_list_id
						  LEFT JOIN people.people_lists AS pl
						            ON  pln.people_list_id
									   = pl.people_list_id
						  LEFT JOIN user_restr.sid_list AS sl
									ON   pl.sid_id
									   = sl.sid_id
							  WHERE dil.doc_id = @doc_id_to_check_ep
									 AND sl.sid = @usersid
									 -- Check time-bound restrictions
									 AND (dgeppl.valid_from IS NULL OR dgeppl.valid_from <= @now)
									 AND (dgeppl.valid_until IS NULL OR dgeppl.valid_until >= @now)
									 AND (pl.valid_from IS NULL OR pl.valid_from <= @now)
									 AND (pl.valid_until IS NULL OR pl.valid_until >= @now)
  							         ))) 

					BEGIN  -- The user has permission for this action
					  SET @user_authentication_result_dep = 'Pass';
					END
				ELSE
				 BEGIN 
				  SET @user_authentication_result_dep = 'Fail'; -- The user is not a Controller and does not have edit permission.
				    BEGIN 
--  ============================================================================================
                   -- Authorisation failure diagnostics
                    -- ============================================================================================
                   --
                   -- The user has already been established as having view permission.
                   -- The user is not a Controller.
                   --
                   -- Edit permission can therefore be obtained through either:
                   --   1. Function List path
                   --   2. People List path
                   --
                   -- Both paths have failed at this point, so diagnose each path separately.
                   -- ============================================================================================

                   DECLARE
                       @function_failure nvarchar(1000) = NULL,
                       @people_failure   nvarchar(1000) = NULL;


                   -- ============================================================================================
                   -- FUNCTION LIST PATH
                   --
                   -- Document
                   --   -> Document Group
                   --   -> Function List Edit Authorisation
                   --   -> Function List
                   --   -> Duty Function
                   --   -> User SID
                   --
                   -- Time-bound restrictions:
                   --   dgepfl.valid_from / valid_until
                   --   fl.valid_from / valid_until
                   --   dfsl.valid_from / valid_until
                   -- ============================================================================================

                   -- Check that the document is linked to a document group
                   IF NOT EXISTS
                   (
                       SELECT 1
                         FROM base.document_id_list AS dil
                        INNER JOIN xref.doc_group_links AS dgl
                                ON dil.doc_id = dgl.doc_id
                        WHERE dil.doc_id = @doc_id_to_check_ep
                   )
                   BEGIN
                       SET @function_failure =
                           CONCAT_WS('; ',
                               @function_failure,
                               'Function Path: Document Group Link Not Found'
                           );
                   END
                   ELSE
                   BEGIN

                       -- Check for Function List edit authorisation
                       IF NOT EXISTS
                       (
                           SELECT 1
                             FROM base.document_id_list AS dil
                            INNER JOIN xref.doc_group_links AS dgl
                                    ON dil.doc_id = dgl.doc_id
                            INNER JOIN user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                    ON dgl.doc_group_id = dgepfl.doc_group_id
                            WHERE dil.doc_id = @doc_id_to_check_ep
                       )
                       BEGIN
                           SET @function_failure =
                               CONCAT_WS('; ',
                                   @function_failure,
                                   'Function Path: Edit Authorisation Not Found'
                               );
                       END
                       ELSE
                       BEGIN

                           -- Check validity of the Document Group / Function List
                           -- edit authorisation
                           IF NOT EXISTS
                           (
                               SELECT 1
                                 FROM base.document_id_list AS dil
                                INNER JOIN xref.doc_group_links AS dgl
                                        ON dil.doc_id = dgl.doc_id
                                INNER JOIN user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                        ON dgl.doc_group_id = dgepfl.doc_group_id
                                WHERE dil.doc_id = @doc_id_to_check_ep
                                  AND (dgepfl.valid_from IS NULL
                                       OR dgepfl.valid_from <= @now)
                                  AND (dgepfl.valid_until IS NULL
                                       OR dgepfl.valid_until >= @now)
                           )
                           BEGIN
                               SET @function_failure =
                                   CONCAT_WS('; ',
                                       @function_failure,
                                       'Function Path: Edit Authorisation Not Currently Valid'
                                   );
                           END;


                           -- Check that the Function List exists
                           IF NOT EXISTS
                           (
                               SELECT 1
                                 FROM base.document_id_list AS dil
                                INNER JOIN xref.doc_group_links AS dgl
                                        ON dil.doc_id = dgl.doc_id
                                INNER JOIN user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                        ON dgl.doc_group_id = dgepfl.doc_group_id
                                INNER JOIN people.function_list_names AS fln
                                        ON dgepfl.function_list_id = fln.function_list_id
                                INNER JOIN people.function_lists AS fl
                                        ON fln.function_list_id = fl.function_list_id
                                WHERE dil.doc_id = @doc_id_to_check_ep
                           )
                           BEGIN
                               SET @function_failure =
                                   CONCAT_WS('; ',
                                       @function_failure,
                                       'Function Path: Function List Not Found'
                                   );
                           END
                           ELSE
                           BEGIN

                               -- Check Function List validity
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM base.document_id_list AS dil
                                    INNER JOIN xref.doc_group_links AS dgl
                                            ON dil.doc_id = dgl.doc_id
                                    INNER JOIN user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                            ON dgl.doc_group_id = dgepfl.doc_group_id
                                    INNER JOIN people.function_list_names AS fln
                                            ON dgepfl.function_list_id = fln.function_list_id
                                    INNER JOIN people.function_lists AS fl
                                            ON fln.function_list_id = fl.function_list_id
                                    WHERE dil.doc_id = @doc_id_to_check_ep
                                      AND (fl.valid_from IS NULL
                                           OR fl.valid_from <= @now)
                                      AND (fl.valid_until IS NULL
                                           OR fl.valid_until >= @now)
                               )
                               BEGIN
                                   SET @function_failure =
                                       CONCAT_WS('; ',
                                           @function_failure,
                                           'Function Path: Function List Not Currently Valid'
                                       );
                               END;


                               -- Check whether the Function List ultimately
                               -- provides a SID link for this user
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM base.document_id_list AS dil
                                    INNER JOIN xref.doc_group_links AS dgl
                                            ON dil.doc_id = dgl.doc_id
                                    INNER JOIN user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                            ON dgl.doc_group_id = dgepfl.doc_group_id
                                    INNER JOIN people.function_list_names AS fln
                                            ON dgepfl.function_list_id = fln.function_list_id
                                    INNER JOIN people.function_lists AS fl
                                            ON fln.function_list_id = fl.function_list_id
                                    INNER JOIN people.duty_functions AS df
                                            ON fl.function_id = df.function_id
                                    INNER JOIN people.duty_function_sid_links AS dfsl
                                            ON df.function_id = dfsl.function_id
                                    INNER JOIN user_restr.sid_list AS sl
                                            ON dfsl.sid_id = sl.sid_id
                                    WHERE dil.doc_id = @doc_id_to_check_ep
                                      AND sl.sid = @usersid
                               )
                               BEGIN
                                   SET @function_failure =
                                       CONCAT_WS('; ',
                                           @function_failure,
                                           'Function Path: User Not Linked Through Duty Function'
                                       );
                               END
                               ELSE
                               BEGIN

                                   -- Check validity of the complete
                                   -- Function List -> Duty Function -> User path
                                   IF NOT EXISTS
                                   (
                                       SELECT 1
                                         FROM base.document_id_list AS dil
                                        INNER JOIN xref.doc_group_links AS dgl
                                                ON dil.doc_id = dgl.doc_id
                                        INNER JOIN user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                                ON dgl.doc_group_id = dgepfl.doc_group_id
                                        INNER JOIN people.function_list_names AS fln
                                                ON dgepfl.function_list_id = fln.function_list_id
                                        INNER JOIN people.function_lists AS fl
                                                ON fln.function_list_id = fl.function_list_id
                                        INNER JOIN people.duty_functions AS df
                                                ON fl.function_id = df.function_id
                                        INNER JOIN people.duty_function_sid_links AS dfsl
                                                ON df.function_id = dfsl.function_id
                                        INNER JOIN user_restr.sid_list AS sl
                                                ON dfsl.sid_id = sl.sid_id
                                        WHERE dil.doc_id = @doc_id_to_check_ep
                                          AND sl.sid = @usersid

                                          -- Document Group / Function List authorisation
                                          AND (dgepfl.valid_from IS NULL
                                               OR dgepfl.valid_from <= @now)
                                          AND (dgepfl.valid_until IS NULL
                                               OR dgepfl.valid_until >= @now)

                                          -- Function List
                                          AND (fl.valid_from IS NULL
                                               OR fl.valid_from <= @now)
                                          AND (fl.valid_until IS NULL
                                               OR fl.valid_until >= @now)

                                          -- Duty Function / User link
                                          AND (dfsl.valid_from IS NULL
                                               OR dfsl.valid_from <= @now)
                                          AND (dfsl.valid_until IS NULL
                                               OR dfsl.valid_until >= @now)
                                   )
                                   BEGIN
                                       SET @function_failure =
                                           CONCAT_WS('; ',
                                               @function_failure,
                                               'Function Path: One or More Authorisation Links Not Currently Valid'
                                           );
                                   END;

                               END;
                           END;
                       END;
                   END;


                   -- ============================================================================================
                   -- PEOPLE LIST PATH
                   --
                   -- Document
                   --   -> Document Group
                   --   -> People List Edit Authorisation
                   --   -> People List
                   --   -> User SID
                   --
                   -- Time-bound restrictions:
                   --   dgeppl.valid_from / valid_until
                   --   pl.valid_from / valid_until
                   -- ============================================================================================

                   -- Check that the document is linked to a document group
                   IF NOT EXISTS
                   (
                       SELECT 1
                         FROM base.document_id_list AS dil
                        INNER JOIN xref.doc_group_links AS dgl
                                ON dil.doc_id = dgl.doc_id
                        WHERE dil.doc_id = @doc_id_to_check_ep
                   )
                   BEGIN
                       SET @people_failure =
                           CONCAT_WS('; ',
                               @people_failure,
                               'People Path: Document Group Link Not Found'
                           );
                   END
                   ELSE
                   BEGIN

                       -- Check for People List edit authorisation
                       IF NOT EXISTS
                       (
                           SELECT 1
                             FROM base.document_id_list AS dil
                            INNER JOIN xref.doc_group_links AS dgl
                                    ON dil.doc_id = dgl.doc_id
                            INNER JOIN user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                    ON dgl.doc_group_id = dgeppl.doc_group_id
                            WHERE dil.doc_id = @doc_id_to_check_ep
                       )
                       BEGIN
                           SET @people_failure =
                               CONCAT_WS('; ',
                                   @people_failure,
                                   'People Path: Edit Authorisation Not Found'
                               );
                       END
                       ELSE
                       BEGIN

                           -- Check validity of the Document Group / People List
                           -- edit authorisation
                           IF NOT EXISTS
                           (
                               SELECT 1
                                 FROM base.document_id_list AS dil
                                INNER JOIN xref.doc_group_links AS dgl
                                        ON dil.doc_id = dgl.doc_id
                                INNER JOIN user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                        ON dgl.doc_group_id = dgeppl.doc_group_id
                                WHERE dil.doc_id = @doc_id_to_check_ep
                                  AND (dgeppl.valid_from IS NULL
                                       OR dgeppl.valid_from <= @now)
                                  AND (dgeppl.valid_until IS NULL
                                       OR dgeppl.valid_until >= @now)
                           )
                           BEGIN
                               SET @people_failure =
                                   CONCAT_WS('; ',
                                       @people_failure,
                                       'People Path: Edit Authorisation Not Currently Valid'
                                   );
                           END;


                           -- Check that the People List exists
                           IF NOT EXISTS
                           (
                               SELECT 1
                                 FROM base.document_id_list AS dil
                                INNER JOIN xref.doc_group_links AS dgl
                                        ON dil.doc_id = dgl.doc_id
                                INNER JOIN user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                        ON dgl.doc_group_id = dgeppl.doc_group_id
                                INNER JOIN people.people_list_names AS pln
                                        ON dgeppl.people_list_id = pln.people_list_id
                                INNER JOIN people.people_lists AS pl
                                        ON pln.people_list_id = pl.people_list_id
                                WHERE dil.doc_id = @doc_id_to_check_ep
                           )
                           BEGIN
                               SET @people_failure =
                                   CONCAT_WS('; ',
                                       @people_failure,
                                       'People Path: People List Not Found'
                                   );
                           END
                           ELSE
                           BEGIN

                               -- Check People List validity
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM base.document_id_list AS dil
                                    INNER JOIN xref.doc_group_links AS dgl
                                            ON dil.doc_id = dgl.doc_id
                                    INNER JOIN user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                            ON dgl.doc_group_id = dgeppl.doc_group_id
                                    INNER JOIN people.people_list_names AS pln
                                            ON dgeppl.people_list_id = pln.people_list_id
                                    INNER JOIN people.people_lists AS pl
                                            ON pln.people_list_id = pl.people_list_id
                                    WHERE dil.doc_id = @doc_id_to_check_ep
                                      AND (pl.valid_from IS NULL
                                           OR pl.valid_from <= @now)
                                      AND (pl.valid_until IS NULL
                                           OR pl.valid_until >= @now)
                               )
                               BEGIN
                                   SET @people_failure =
                                       CONCAT_WS('; ',
                                           @people_failure,
                                           'People Path: People List Not Currently Valid'
                                       );
                               END;


                               -- Check whether the People List contains
                               -- the connected user's SID
                               IF NOT EXISTS
                               (
                                   SELECT 1
                                     FROM base.document_id_list AS dil
                                    INNER JOIN xref.doc_group_links AS dgl
                                            ON dil.doc_id = dgl.doc_id
                                    INNER JOIN user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                            ON dgl.doc_group_id = dgeppl.doc_group_id
                                    INNER JOIN people.people_list_names AS pln
                                            ON dgeppl.people_list_id = pln.people_list_id
                                    INNER JOIN people.people_lists AS pl
                                            ON pln.people_list_id = pl.people_list_id
                                    INNER JOIN user_restr.sid_list AS sl
                                            ON pl.sid_id = sl.sid_id
                                    WHERE dil.doc_id = @doc_id_to_check_ep
                                      AND sl.sid = @usersid
                               )
                               BEGIN

                                   -- Distinguish a missing People List from
                                   -- a People List which exists but does not
                                   -- contain the user.
                                   IF NOT EXISTS
                                   (
                                       SELECT 1
                                         FROM base.document_id_list AS dil
                                        INNER JOIN xref.doc_group_links AS dgl
                                                ON dil.doc_id = dgl.doc_id
                                        INNER JOIN user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                                ON dgl.doc_group_id = dgeppl.doc_group_id
                                        INNER JOIN people.people_list_names AS pln
                                                ON dgeppl.people_list_id = pln.people_list_id
                                        INNER JOIN people.people_lists AS pl
                                                ON pln.people_list_id = pl.people_list_id
                                        WHERE dil.doc_id = @doc_id_to_check_ep
                                   )
                                   BEGIN
                                       SET @people_failure =
                                           CONCAT_WS('; ',
                                               @people_failure,
                                               'People Path: People List Not Found'
                                           );
                                   END
                                   ELSE
                                   BEGIN
                                       SET @people_failure =
                                           CONCAT_WS('; ',
                                               @people_failure,
                                               'People Path: User Not a Member of People List'
                                           );
                                   END;

                               END
                               ELSE
                               BEGIN

                                   -- Check validity of the complete
                                   -- People List -> User path
                                   IF NOT EXISTS
                                   (
                                       SELECT 1
                                         FROM base.document_id_list AS dil
                                        INNER JOIN xref.doc_group_links AS dgl
                                                ON dil.doc_id = dgl.doc_id
                                        INNER JOIN user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                                ON dgl.doc_group_id = dgeppl.doc_group_id
                                        INNER JOIN people.people_list_names AS pln
                                                ON dgeppl.people_list_id = pln.people_list_id
                                        INNER JOIN people.people_lists AS pl
                                                ON pln.people_list_id = pl.people_list_id
                                        INNER JOIN user_restr.sid_list AS sl
                                                ON pl.sid_id = sl.sid_id
                                        WHERE dil.doc_id = @doc_id_to_check_ep
                                          AND sl.sid = @usersid

                                          -- Document Group / People List authorisation
                                          AND (dgeppl.valid_from IS NULL
                                               OR dgeppl.valid_from <= @now)
                                          AND (dgeppl.valid_until IS NULL
                                               OR dgeppl.valid_until >= @now)

                                          -- People List
                                          AND (pl.valid_from IS NULL
                                               OR pl.valid_from <= @now)
                                          AND (pl.valid_until IS NULL
                                               OR pl.valid_until >= @now)
                                   )
                                   BEGIN
                                       SET @people_failure =
                                           CONCAT_WS('; ',
                                               @people_failure,
                                               'People Path: One or More Authorisation Links Not Currently Valid'
                                           );
                                   END;

                               END;
                           END;
                       END;
                   END;


                   -- ============================================================================================
                   -- COMBINE THE TWO ALTERNATIVE PATHS
                   --
                   -- Function Path OR People Path
                   --
                   -- Both paths have failed because this diagnostic block is only
                   -- reached after the main EXISTS test returned false.
                   -- ============================================================================================

                   SET @failure_type =
                       CONCAT_WS('; ',
                           @function_failure,
                           @people_failure
                       );


				   -- End authorisation failure diagnostics 
--  ============================================================================================

				   -- Write to authorisation fail log

					  INSERT INTO user_restr.authorisation_fail_log
								  (sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
						   VALUES (
									@sid_id,
									'Document Edit Permission',
									@doc_id_to_check_ep ,
									@privilege_name,
									'[internal].[usp_AUTHENTICATE_doc_ed_perm]',
									@failure_type
									)
			       END -- END Authorisation failure diagnostics
				 END  -- END The user is not a Controller and does not have edit permission.


		END -- END ELSE IF @dociscontrollergroup = 'Yes'
	 END -- END IF @dociscontrollergroup = 'No' AND @user_authentication_result_dep = 'Pass' 
  END  --IF @view_permission = 'Pass' -- The user has viewing permission for the document
   
END
GO