USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [authorising].[usp_DEL_contr_doc_group_name]    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:	Initial creation
-- Deletes a controller level document edit group name  
-- Output is a status message and a transaction status.  
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2024
*/

-- =============================================
CREATE PROCEDURE [authorising].[usp_DEL_contr_doc_group_name] 

     @contr_doc_ed_grp_name_id  bigint   = NULL,
	 @message nvarchar(1000)			 = NULL OUTPUT,
	 @transaction_status nvarchar(50)	 = NULL OUTPUT

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

  DECLARE 
        @tempmessage nvarchar(300)           = '',
		@username nvarchar(150)              = ORIGINAL_LOGIN(),     -- The username 
		@connectedusersid varbinary(100)     = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
		@userauthentication_status nchar(10) = 'Fail', -- The outcome of the authentication check of the user 
		@transaction_ready nchar(10)         = 'Ready',
		@data_validation_status nchar(10)    = 'Pass';


  -- Parameters which have not been explicitly set might be output as null to calling functions
  SET @transaction_status = 'Transaction not attempted';  


  -- Connected user authentication
  -- Authenticate the connected user as an authoriser
  EXEC [internal].[usp_AUTHENTICATE_authoriser] 
		@user_authentication_result = @userauthentication_status OUTPUT;
  IF @userauthentication_status = 'Fail'
    BEGIN  -- The user does not have permission for this action
		SET @transaction_ready      = 'Fail';
	    EXEC internal.usp_SEL_message 
            @message_id   = 'NoPermission', 
			@message_text = @tempmessage OUTPUT;
	    IF (@tempmessage IS NOT NULL) 
  	       SET @message = CONCAT(@message, ' | ', ISNULL(@username, ''), '  ', @tempmessage);
	    ELSE 
		   SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoPermission');
	END

  IF @userauthentication_status = 'Pass' -- Don't do anything if the user is not authorised.
    BEGIN

     -- Check if a matching record does not exist
			 IF NOT EXISTS (SELECT controller_doc_group_name_id
				   	          FROM xref.controller_doc_group_names 
						     WHERE    controller_doc_group_name_id 
							       = @contr_doc_ed_grp_name_id)
			   BEGIN
				 EXEC internal.usp_SEL_message 
					  @message_id   = 'NotExist', 
					  @message_text = @tempmessage OUTPUT;
				 IF (@tempmessage IS NOT NULL) 
  					SET @message = CONCAT_WS(' | ', @message, @tempmessage);
				 ELSE 
					SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NotExist');
				 SET @data_validation_status = 'Fail';
				 SET @transaction_ready      = 'Fail';
			   END

	  -- Output the data validation status failed message
	  IF @data_validation_status = 'Fail'
		BEGIN
		  EXEC internal.usp_SEL_message 
			   @message_id   = 'FailedDataValidation', 
			   @message_text = @tempmessage OUTPUT;
		  IF (@tempmessage IS NOT NULL) 
			 SET @message = CONCAT_WS(' | ', @message, @tempmessage);
		  ELSE 
			 SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FailedDataValidation.');
		END
	  -- End data validation



-- Execute the delete query
  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY

	  BEGIN TRANSACTION;
	  
	     DELETE 
		   FROM xref.controller_doc_group_names  
		  WHERE   controller_doc_group_name_id 
		        = @contr_doc_ed_grp_name_id;

	   COMMIT TRANSACTION;

         EXEC internal.usp_SEL_message 
              @message_id   = 'Success', 
              @message_text = @tempmessage OUTPUT;
  	     IF (@tempmessage IS NOT NULL) 
	        SET @message = CONCAT_WS(' | ', @message, @tempmessage);
	     ELSE 
		    SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on Success.');
		 SET @transaction_status = 'Good';

   	  END TRY
	  BEGIN CATCH


	     IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;


	     SET @transaction_status = 'Bad';
         EXEC internal.usp_SEL_message 
              @message_id   = 'DeleteError', 
              @message_text = @tempmessage OUTPUT;
		 IF (@tempmessage IS NOT NULL) 
		    SET @message = CONCAT(@message, ' | ', @tempmessage, ' | ', 
		    CONVERT(nvarchar(10),ERROR_NUMBER()), ' | ', ERROR_MESSAGE());
		 ELSE 
		    SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on DeleteError.');
	  END CATCH
    END
 END -- END IF @userauthentication_status = 'Pass' 
END
GO