IF NOT EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
    WHERE [SO].[name] = 'DiaryAttachmentOriginalMatterLinks'
      AND [SO].[type] = 'U')
  BEGIN
    EXEC ('CREATE TABLE
       [dbo].[DiaryAttachmentOriginalMatterLinks]
      ([TrackReference]      INT            NOT NULL,
       [OriginalUniqueID]    INT            NOT NULL,
       CONSTRAINT [PK_DAOML]
                  PRIMARY KEY CLUSTERED
                 ([TrackReference]))
    ON [PRIMARY]')
  END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.indexes [SI]
                       ON [SI].object_id = [SO].object_id
                      AND [SI].[name] = 'IDX_UniqueID'
    WHERE [SO].[name] = 'DiaryAttachmentOriginalMatterLinks'
      AND [SO].[type] = 'U'
      AND [SI].[index_id] IS NULL)
  BEGIN
    EXEC ('    CREATE NONCLUSTERED
     INDEX [IDX_UniqueID]
        ON [dbo].[DiaryAttachmentOriginalMatterLinks]
          ([OriginalUniqueID])
           INCLUDE ([TrackReference])')
  END
GO

DELETE [DAT]
  FROM [dbo].[DiaryAttachments] [DAT]
       LEFT OUTER JOIN [dbo].[Diary] [DIA]
                    ON [DIA].[ActionID] = [DAT].[DiaryID]
 WHERE [DIA].[ActionID] IS NULL
GO

INSERT
  INTO [dbo].[DiaryAttachmentOriginalMatterLinks]
      ([TrackReference],
       [OriginalUniqueID])
SELECT [DAT].[TrackReference],
       [MAT].[UniqueID]
  FROM [dbo].[DiaryAttachments] [DAT]
       INNER JOIN [dbo].[matters] [MAT]
               ON [MAT].[Code] = [DAT].[CASECODE]
       LEFT OUTER JOIN [dbo].[DiaryAttachmentOriginalMatterLinks] [DAOML]
                    ON [DAOML].[TrackReference] = [DAT].[TrackReference]
 WHERE [DAOML].[TrackReference] IS NULL
GO




IF OBJECT_ID('DocumentPopups','U') IS NOT NULL
BEGIN
	IF NOT EXISTS(
		SELECT  TOP 1 1 
		FROM    SYS.columns 
		WHERE   OBJECT_NAME(object_id) = 'DocumentPopups' AND
				[name] = 'Title')
	BEGIN
		ALTER TABLE DocumentPopups
		ADD [Title] VARCHAR(100)
	END	
END

GO



IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_GetFileName'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KAAS_GetFileName]
  END
GO

CREATE PROCEDURE
       [dbo].[KAAS_GetFileName]
       (@TrackReference    INT)
AS

/*******************************************************************************************************
* Fetches the file path using Track Reference No.                                                      *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_GetFileName]											           *
*                                                                                                      *
* Modification History	:                                                                              *
* 2020-06-19	Arun V		Created                                                                *
********************************************************************************************************/

BEGIN
    SELECT
	    [Document]
	FROM
	    [DiaryAttachments]
	WHERE
	    [TrackReference] = @TrackReference
END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KEYHM_GetFileName'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KEYHM_GetFileName]
  END
GO

CREATE PROCEDURE
       [dbo].[KEYHM_GetFileName]
       (@TrackReference    INT)
AS

/*******************************************************************************************************
* Fetches the file path using Track Reference No.                                                      *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KEYHM_GetFileName]											           *
*                                                                                                      *
* Modification History	:                                                                              *
* 2020-06-19	Arun V		Created                                                                *
********************************************************************************************************/

BEGIN
    SELECT
	    [Document]
	FROM
	    [DiaryAttachments]
	WHERE
	    [TrackReference] = @TrackReference
END
GO



IF OBJECT_ID(N'KAAS_GetAPIVersion',N'P')IS NOT NULL
	DROP PROCEDURE KAAS_GetAPIVersion
GO
CREATE PROCEDURE KAAS_GetAPIVersion
AS
/**********************************************************
KAAS_GetAPIVersion

This procedure is used to fetch the latest version of the script

Modification History:
---------------------

DATE		NAME		DESCRIPTION
--------------------------------------------------------------
2019AUG22	ARUN		Created	
2019JUN25   Arun V      Added SQL Server
***********************************************************/
BEGIN
	DECLARE @SqlServerVersion NVARCHAR(500)

	SELECT @SqlServerVersion = @@VERSION
	SELECT TOP 1 
		[Version] as DBVersion, 
		[ReleaseDate] as ReleaseDate, 
		@SqlServerVersion as SQLVersion
	FROM 
		[Common].[Release] 
	ORDER BY 
		ReleaseID DESC
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetSPHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[KAAS_GetSPHistory]
GO

CREATE PROCEDURE [dbo].[KAAS_GetSPHistory] (
                @SPTYPE   VARCHAR(20)
)
AS
/*************************************************************************************
*	
*	Stored Procedure Name : [dbo].[KAAS_GetSPHistory]						   
*
*	To get list of all stored procedures belonging to a category provided
*	Categories - KEYSAAS : Keyhouse web app
*				 KEYHM: Keyhouse Mobile
*	
*	Modifications: 
2020-06-15      Aakif M      Created KAAS_GetSPHistory
2020-06-25      Arun V       To get all stored procedure from DB using like search.
******************************************************************************************/ 
BEGIN
DECLARE @SEARCHTEXT VARCHAR(20)
	
IF (@SPTYPE IS NOT NULL AND @SPTYPE != '')
	BEGIN
	  SET @SEARCHTEXT = @SPTYPE + '%';
	  SELECT ROW_NUMBER()  OVER (ORDER BY [create_date]) as SerialNo,
			 [NAME] AS [Name],
			 [type_desc] as [Description],
		     [create_date] as [DateOfCreation],
			 [modify_date] as [ModificationDate]
	  FROM sys.objects 
	  WHERE 
		[NAME] LIKE @SEARCHTEXT
	  ORDER BY 
		[create_date],
		[modify_date],
		[type_desc],
		[Name]
	END
END

GO

IF EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO WHERE SO.[name] = 'KAAS_InsertDiaryAttachment' AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KAAS_InsertDiaryAttachment]
  END
GO

CREATE PROCEDURE 
       [dbo].[KAAS_InsertDiaryAttachment]
      (@DIARYID            INT,
       @TRACKREFERENCE     INT,
       @CASECODE           VARCHAR(20),
       @NAME               VARCHAR(10),
       @DOCUMENT           VARCHAR(100),
       @FILEPATH           VARCHAR(255),
       @TYPE               VARCHAR(5),
       @DOCCLASS           VARCHAR(3),
       @DATEENTERED        DATETIME,
       @ENTEREDBY          VARCHAR(8),
       @LASTACCESSDATE     DATETIME,
       @LASTACCESSBY       VARCHAR(8),
       @SYSTEM             VARCHAR(1),
       @DICTATIONFILE      VARCHAR(255),
       @Source             VARCHAR(20),
       @Fees               INT,
       @Outlay             INT,
       @Result             VARCHAR(255) OUTPUT)    
AS
/*******************************************************************************************************
* This stored procedure is used to insert or update the Client Case Action                             *
* Attachment information.IF ID is already exist then it will update that record. otherwise             *
* new record will insert.                                                                              *    
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_InsertDiaryAttachment]                                           *
* Copied from			: [dbo].[ky_NETAAInsertDiaryAttchement]                                        *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-10	Vinodhan K		Created                                                                *
* 2020-05-26    Arun V          Modified the type extenaion size from 3 to 5                           *
 *******************************************************************************************************/
BEGIN

	SET NOCOUNT OFF

	IF NOT EXISTS
	(SELECT 1 
		FROM [dbo].[DiaryAttachments] DAT
		WHERE DAT.[TRACKREFERENCE] = @TRACKREFERENCE) 
	BEGIN
	SET @FILEPATH = dbo.KAAS_FN_GETFILEPATH (SUBSTRING(@FILEPATH,LEN(@FILEPATH)-CHARINDEX ('\',REVERSE(@FILEPATH))+2,LEN(@FILEPATH)) ,@FILEPATH )  
	IF NOT(@FILEPATH = 'NOCHANGE')  
		BEGIN
		INSERT 
		INTO [dbo].[DiaryAttachments]
			([DIARYID],
				[TRACKREFERENCE],
				[CASECODE],
				[NAME],
				[DOCUMENT],
				[FILEPATH],
				[TYPE],
				[DOCCLASS],
				[DATEENTERED],
				[ENTEREDBY],
				[LASTACCESSDATE],
				[LASTACCESSBY],
				[SYSTEM],
				[DICTATIONFILE],
				[Source],
				[Fees],
				[Outlay])
		VALUES(@DIARYID,
				@TRACKREFERENCE,
				@CASECODE,
				@NAME,
				@DOCUMENT,
				@FILEPATH,
				@TYPE,
				@DOCCLASS,
				@DATEENTERED,
				@ENTEREDBY,
				@LASTACCESSDATE,
				@LASTACCESSBY,
				@SYSTEM,
				@DICTATIONFILE,
				@Source,
				@Fees,
				@Outlay)
		IF EXISTS(SELECT * FROM diary WHERE ACTIONID = @DIARYID AND ACTIONTYPE = 'U')
		BEGIN
			DECLARE @UserName VARCHAR(20)
			DECLARE @Description VARCHAR(500)
			SET @UserName = (SELECT NAME FROM Handlers WHERE Code = @ENTEREDBY)
			SET @Description = 'Attachment Added: ' + CHAR(13) + CHAR(10)
							+  'Date:  ' + CONVERT(VARCHAR(11),CONVERT(DATETIME,@DATEENTERED),106) + CHAR(13) + CHAR(10)
						+ 'Document Name: ' + @DOCUMENT +CHAR(13) + CHAR(10)
						+ 'Document Class: ' + @DOCCLASS + CHAR(13) + CHAR(10)
			INSERT INTO
				[dbo].[UndertakingLog]
				([UndertakingID],
				[FieldChanges],
				[FromValue],
				[ToValue],
				[ByWho],
				[ModifiedDate])
			VALUES(@DIARYID,
				'New Documents Added',
				@Description,
				' - NIL - ',
				@UserName,
				CONVERT(DATETIME,GETDATE(),112))
		END
               
		SET @Result = @FILEPATH 
		END
		ELSE  
		BEGIN  
			SET @Result = 'NOCHANGE'  
		END  
	END 
	--ELSE 
	--  BEGIN
	--    UPDATE DAT
	--       SET DAT.[TRACKREFERENCE]           =    @TRACKREFERENCE,
	--           DAT.[CASECODE]                 =    @CASECODE,
	--           DAT.[NAME]                     =    @NAME,
	--           DAT.[DOCUMENT]                 =    @DOCUMENT,
	--           DAT.[FILEPATH]                 =    @FILEPATH,
	--           DAT.[TYPE]                     =    @TYPE,
	--           DAT.[DOCCLASS]                 =    @DOCCLASS,
	--           DAT.[DATEENTERED]              =    @DATEENTERED,
	--           DAT.[ENTEREDBY]                =    @ENTEREDBY,
	--           DAT.[LASTACCESSDATE]           =    @LASTACCESSDATE,
	--           DAT.[LASTACCESSBY]             =    @LASTACCESSBY,
	--           DAT.[SYSTEM]                   =    @SYSTEM,
	--           DAT.[DICTATIONFILE]            =    @DICTATIONFILE,
	--           DAT.[Source]                   =    @Source,
	--           DAT.[Fees]                     =    @Fees,
	--           DAT.[Outlay]                   =    @Outlay
	--      FROM [dbo].[DiaryAttachments] DAT
	--     WHERE DAT.[TRACKREFERENCE]           =    @TRACKREFERENCE
	--  END

END
GO

IF EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO WHERE SO.[name] = 'KEYHM_InsertDiaryAttachment' AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KEYHM_InsertDiaryAttachment]
  END
GO

CREATE PROCEDURE 
       [dbo].[KEYHM_InsertDiaryAttachment]
      (@DIARYID            INT,
       @TRACKREFERENCE     INT,
       @CASECODE           VARCHAR(20),
       @NAME               VARCHAR(10),
       @DOCUMENT           VARCHAR(100),
       @FILEPATH           VARCHAR(255),
       @TYPE               VARCHAR(5),
       @DOCCLASS           VARCHAR(3),
       @DATEENTERED        DATETIME,
       @ENTEREDBY          VARCHAR(8),
       @LASTACCESSDATE     DATETIME,
       @LASTACCESSBY       VARCHAR(8),
       @SYSTEM             VARCHAR(1),
       @DICTATIONFILE      VARCHAR(255),
       @Source             VARCHAR(20),
       @Fees               INT,
       @Outlay             INT,
       @Result             VARCHAR(255) OUTPUT)    
AS
/*******************************************************************************************************
* This stored procedure is used to insert or update the Client Case Action                             *
* Attachment information.IF ID is already exist then it will update that record. otherwise             *
* new record will insert.                                                                              *    
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KEYHM_InsertDiaryAttachment]                                          *
* Copied from			: [dbo].[ky_NETAAInsertDiaryAttchement]                                        *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-10	Vinodhan K		Created                                                                *
* 2020-05-26    Arun V          Modified the type extension size from 3 to 5                           *
 *******************************************************************************************************/
BEGIN

	SET NOCOUNT OFF

	IF NOT EXISTS
	(SELECT 1 
		FROM [dbo].[DiaryAttachments] DAT
		WHERE DAT.[TRACKREFERENCE] = @TRACKREFERENCE) 
	BEGIN
	SET @FILEPATH = dbo.KEYHM_FN_GETFILEPATH (SUBSTRING(@FILEPATH,LEN(@FILEPATH)-CHARINDEX ('\',REVERSE(@FILEPATH))+2,LEN(@FILEPATH)) ,@FILEPATH )  
	IF NOT(@FILEPATH = 'NOCHANGE')  
		BEGIN
		INSERT 
		INTO [dbo].[DiaryAttachments]
			([DIARYID],
				[TRACKREFERENCE],
				[CASECODE],
				[NAME],
				[DOCUMENT],
				[FILEPATH],
				[TYPE],
				[DOCCLASS],
				[DATEENTERED],
				[ENTEREDBY],
				[LASTACCESSDATE],
				[LASTACCESSBY],
				[SYSTEM],
				[DICTATIONFILE],
				[Source],
				[Fees],
				[Outlay])
		VALUES(@DIARYID,
				@TRACKREFERENCE,
				@CASECODE,
				@NAME,
				@DOCUMENT,
				@FILEPATH,
				@TYPE,
				@DOCCLASS,
				@DATEENTERED,
				@ENTEREDBY,
				@LASTACCESSDATE,
				@LASTACCESSBY,
				@SYSTEM,
				@DICTATIONFILE,
				@Source,
				@Fees,
				@Outlay)
		IF EXISTS(SELECT * FROM diary WHERE ACTIONID = @DIARYID AND ACTIONTYPE = 'U')
		BEGIN
			DECLARE @UserName VARCHAR(20)
			DECLARE @Description VARCHAR(500)
			SET @UserName = (SELECT NAME FROM Handlers WHERE Code = @ENTEREDBY)
			SET @Description = 'Attachment Added: ' + CHAR(13) + CHAR(10)
							+  'Date:  ' + CONVERT(VARCHAR(11),CONVERT(DATETIME,@DATEENTERED),106) + CHAR(13) + CHAR(10)
						+ 'Document Name: ' + @DOCUMENT +CHAR(13) + CHAR(10)
						+ 'Document Class: ' + @DOCCLASS + CHAR(13) + CHAR(10)
			INSERT INTO
				[dbo].[UndertakingLog]
				([UndertakingID],
				[FieldChanges],
				[FromValue],
				[ToValue],
				[ByWho],
				[ModifiedDate])
			VALUES(@DIARYID,
				'New Documents Added',
				@Description,
				' - NIL - ',
				@UserName,
				CONVERT(DATETIME,GETDATE(),112))
		END
               
		SET @Result = @FILEPATH 
		END
		ELSE  
		BEGIN  
			SET @Result = 'NOCHANGE'  
		END  
	END 
	--ELSE 
	--  BEGIN
	--    UPDATE DAT
	--       SET DAT.[TRACKREFERENCE]           =    @TRACKREFERENCE,
	--           DAT.[CASECODE]                 =    @CASECODE,
	--           DAT.[NAME]                     =    @NAME,
	--           DAT.[DOCUMENT]                 =    @DOCUMENT,
	--           DAT.[FILEPATH]                 =    @FILEPATH,
	--           DAT.[TYPE]                     =    @TYPE,
	--           DAT.[DOCCLASS]                 =    @DOCCLASS,
	--           DAT.[DATEENTERED]              =    @DATEENTERED,
	--           DAT.[ENTEREDBY]                =    @ENTEREDBY,
	--           DAT.[LASTACCESSDATE]           =    @LASTACCESSDATE,
	--           DAT.[LASTACCESSBY]             =    @LASTACCESSBY,
	--           DAT.[SYSTEM]                   =    @SYSTEM,
	--           DAT.[DICTATIONFILE]            =    @DICTATIONFILE,
	--           DAT.[Source]                   =    @Source,
	--           DAT.[Fees]                     =    @Fees,
	--           DAT.[Outlay]                   =    @Outlay
	--      FROM [dbo].[DiaryAttachments] DAT
	--     WHERE DAT.[TRACKREFERENCE]           =    @TRACKREFERENCE
	--  END

END
GO


DECLARE @NCOMMAND NVARCHAR(MAX)

IF NOT((SELECT SERVERPROPERTY('Edition') AS	[Edition]) = 'SQL Azure')
BEGIN
    IF EXISTS
       (SELECT TOP 1 1
        FROM SYS.[objects] SO
        WHERE SO.[name] = N'KAAS_LogDocumentAccess'
        AND SO.[type] = 'P')
    BEGIN
        DROP PROCEDURE [dbo].[KAAS_LogDocumentAccess]
    END
	
	SET @NCOMMAND = 
	N'
	CREATE PROCEDURE
       [dbo].[KAAS_LogDocumentAccess]
      (@TrackReference          INT,
       @Handler                 VARCHAR(10),
       @Comment                 VARCHAR(2000))
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[ky_NETSPDocumentAccess]                                                                            *
 *                                                                                                           *
 * Log Document Access                                                                                       *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                5.5.1.2                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2017-10-02   Pino Carafa       Created                                                                    *
 * 2018-08-09   Pino Carafa       Performance Improvement                                                    *
 * 2019-03-20   Pino Carafa       Azure Compatibility                                                        *
 * 2019-05-05	Arun V			  Copied from ky_NETSPDocumentAccess                                         *
 * 2020-06-25	Rajesh P		  Changed the [sysprocess] access from [master].[dbo]                        *
 *************************************************************************************************************/
BEGIN

SET @TrackReference = ISNULL(@TrackReference, 0)

IF @TrackReference = 0
  BEGIN
    RETURN
  END

IF (SELECT TOP 1 1 FROM [dbo].[DiaryAttachments] [DAT] WITH (NOLOCK) WHERE [DAT].[TrackReference] = @TrackReference) IS NULL
  BEGIN
    RETURN
  END

IF EXISTS
  (SELECT TOP 1 1
     FROM [dbo].[Handlers] [HAN] WITH (NOLOCK)
    WHERE [HAN].[CODE] = RTRIM(ISNULL(@Handler, '''')))
  BEGIN
    SELECT @Handler     = RTRIM(ISNULL([HAN].[Code], ''''))
      FROM [dbo].[Handlers] [HAN] WITH (NOLOCK)
     WHERE [HAN].[CODE] = RTRIM(ISNULL(@Handler, ''''))
  END
ELSE
  BEGIN
    SET @Handler = ''''
  END

IF RTRIM(ISNULL(@Handler, '''')) = ''''
  BEGIN
    SELECT @Handler = CONVERT(VARCHAR(10),
                              CASE WHEN CHARINDEX(CHAR(0),
                                                  CONVERT(VARCHAR(MAX),
                                                          [P].[context_info])) > 0
                                        THEN SUBSTRING(CONVERT(VARCHAR(MAX),
                                                               [P].[context_info]),
                                                       1,
                                                       CHARINDEX(CHAR(0),
                                                                 CONVERT(VARCHAR(MAX),
                                                                 [P].[context_info])) - 1)
                                   ELSE CONVERT(VARCHAR(MAX),
                                                [P].[context_info]) END)
      FROM [master].[dbo].[sysprocesses] P
     WHERE P.[spid] = @@SPID
  END

SET @Handler = RTRIM(ISNULL(@Handler, ''ADM''))

SET @Comment        = CASE WHEN RTRIM(ISNULL(@Comment, '''')) = ''''
                                THEN ''No reason given''
                           ELSE RTRIM(ISNULL(@Comment, '''')) END

  INSERT
    INTO [dbo].[DiaryAttachmentAccess]
        ([TrackReference],
         [Handler],
         [Comment])
  VALUES(@TrackReference,
         @Handler,
         @Comment)

END
	'

	EXECUTE SP_EXECUTESQL @NCOMMAND
END
ELSE
BEGIN
   IF EXISTS
       (SELECT TOP 1 1
        FROM SYS.[objects] SO
        WHERE SO.[name] = N'KAAS_LogDocumentAccess'
        AND SO.[type] = 'P')
    BEGIN
        DROP PROCEDURE [dbo].[KAAS_LogDocumentAccess]
    END

	
	SET @NCOMMAND = 
	N'
	CREATE PROCEDURE
       [dbo].[KAAS_LogDocumentAccess]
      (@TrackReference          INT,
       @Handler                 VARCHAR(10),
       @Comment                 VARCHAR(2000))
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[ky_NETSPDocumentAccess]                                                                            *
 *                                                                                                           *
 * Log Document Access                                                                                       *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                5.5.1.2                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2017-10-02   Pino Carafa       Created                                                                    *
 * 2018-08-09   Pino Carafa       Performance Improvement                                                    *
 * 2019-03-20   Pino Carafa       Azure Compatibility                                                        *
 * 2019-05-05	Arun V			  Copied from ky_NETSPDocumentAccess                                         *
 * 2020-06-25	Rajesh P		  Changed the [sysprocess] access from [master].[dbo]                        *
 *************************************************************************************************************/
BEGIN

SET @TrackReference = ISNULL(@TrackReference, 0)

IF @TrackReference = 0
  BEGIN
    RETURN
  END

IF (SELECT TOP 1 1 FROM [dbo].[DiaryAttachments] [DAT] WITH (NOLOCK) WHERE [DAT].[TrackReference] = @TrackReference) IS NULL
  BEGIN
    RETURN
  END

IF EXISTS
  (SELECT TOP 1 1
     FROM [dbo].[Handlers] [HAN] WITH (NOLOCK)
    WHERE [HAN].[CODE] = RTRIM(ISNULL(@Handler, '''')))
  BEGIN
    SELECT @Handler     = RTRIM(ISNULL([HAN].[Code], ''''))
      FROM [dbo].[Handlers] [HAN] WITH (NOLOCK)
     WHERE [HAN].[CODE] = RTRIM(ISNULL(@Handler, ''''))
  END
ELSE
  BEGIN
    SET @Handler = ''''
  END

IF RTRIM(ISNULL(@Handler, '''')) = ''''
  BEGIN
    SELECT @Handler = CONVERT(VARCHAR(10),
                              CASE WHEN CHARINDEX(CHAR(0),
                                                  CONVERT(VARCHAR(MAX),
                                                          [P].[context_info])) > 0
                                        THEN SUBSTRING(CONVERT(VARCHAR(MAX),
                                                               [P].[context_info]),
                                                       1,
                                                       CHARINDEX(CHAR(0),
                                                                 CONVERT(VARCHAR(MAX),
                                                                 [P].[context_info])) - 1)
                                   ELSE CONVERT(VARCHAR(MAX),
                                                [P].[context_info]) END)
      FROM [dbo].[sysprocesses] P
     WHERE P.[spid] = @@SPID
  END

SET @Handler = RTRIM(ISNULL(@Handler, ''ADM''))

SET @Comment        = CASE WHEN RTRIM(ISNULL(@Comment, '''')) = ''''
                                THEN ''No reason given''
                           ELSE RTRIM(ISNULL(@Comment, '''')) END

  INSERT
    INTO [dbo].[DiaryAttachmentAccess]
        ([TrackReference],
         [Handler],
         [Comment])
  VALUES(@TrackReference,
         @Handler,
         @Comment)

END
	'

	EXECUTE SP_EXECUTESQL @NCOMMAND
END


GO