IF OBJECT_ID(N'BalanceSheet',N'V')IS NOT NULL
	DROP VIEW [dbo].[BalanceSheet]
GO

CREATE VIEW [dbo].[BalanceSheet]
AS
/************************************************************************************
 *
 *    Balance Sheet System View - This is not to be changed for clients
 *
 *    Modification History
 *    2016-06-16   Catherine Ievers   Added in additonal Month fields
 *    2019-08-12   Catherine Ievers   Added in Branching
 *    2020-04-29   John Ginnane       KP-48 - Rewrote view to match the Clarion report in
 *                                    terms of behaviour and to be up to standard
 *    2022-08-19   John Ginnane       15087 - Branch details will be appear as blank
 *                                    if branching is disabled
 *
 ************************************************************************************/
/* Get the sum of the accruals for each nominal */
WITH [CTE_Accruals] ([Code],
                     [Value]) AS (
SELECT [T].[Code],
       SUM([T].[Value])
  FROM (SELECT [ACC].[NOMINALFROM] AS [Code],
               [ACC].[VALUE] AS [Value]
          FROM [dbo].[Accruals] AS [ACC]
        UNION ALL
        SELECT [ACC].[NOMINALTO],
               [ACC].[VALUE] * -1
          FROM [dbo].[Accruals] AS [ACC]) AS [T]
       GROUP BY [T].[Code]), 
/* Get a list of the nominals, their header and sub-header codes and the row number
   This row number is used later to get the last valid sub-header code in case a
   nominal has an invalid code. This is to copy how the original Clarion report behaved */
      [CTE_NominalBase] ([Code],
                         [AccountName],
                         [Type],
                         [PB],
                         [HeaderCode],
                         [SubHeaderCode],
                         [ActualSubHeaderCode],
                         [Row]) AS (
SELECT [NOM].[CODE],
       [NOM].[DESC],
       [NOM].[TYPE],
       [NOM].[PB],
       LEFT([NOM].[SEQ], 1) + ' 00',
       LEFT([NOM].[SEQ], 2),
       [NOM].[SEQ],
       ROW_NUMBER() OVER(ORDER BY LEFT([NOM].[SEQ], 1) + ' 00' ASC,
                                  LEFT([NOM].[SEQ], 2) ASC,
                                  [NOM].[SEQ] ASC,
                                  [NOM].[CODE] ASC)
  FROM [dbo].[Nominal] AS [NOM]
 WHERE [NOM].[PB] = 'B'),
/* Get the descriptions of the header and sub-headers */
      [CTE_Nominal] ([Code],
                     [AccountName],
                     [Type],
                     [HeaderCode],
                     [Header],
                     [SubHeaderCode],
                     [SubHeader],
                     [ActualSubHeaderCode],
                     [LastValidSubHeaderCode],
                     [LastValidSubHeader]) AS (
SELECT [NOM].[Code],
       [NOM].[AccountName],
       [NOM].[Type],
       [NOM].[HeaderCode],
       [Header].[DESC],
       [NOM].[SubHeaderCode],
       ISNULL([SubHeader].[DESC], ''),
       [NOM].[ActualSubHeaderCode],
       ISNULL([LastValidSubHeader].[Code], ''),
       ISNULL([LastValidSubHeader].[Description], '')
  FROM [CTE_NominalBase] AS [NOM]
       LEFT OUTER JOIN [dbo].[NominalReportSeq] AS [Header]
                    ON [Header].[CODE] = [NOM].[HeaderCode]
                   AND [Header].[PB] = [NOM].[PB]
       LEFT OUTER JOIN [dbo].[NominalReportSeq] AS [SubHeader]
                    ON [SubHeader].[CODE] = [NOM].[ActualSubHeaderCode]
                   AND [SubHeader].[PB] = [NOM].[PB]
       -- Try to get the last VALID subheader and use that header
       OUTER APPLY (SELECT TOP 1
                           [ActualSubHeaderCode] AS [Code],
                           [NRS].[DESC] AS [Description]
                      FROM [CTE_NominalBase] AS [Previous]
                           INNER JOIN [NominalReportSeq] AS [NRS]
                                   ON [Previous].[ActualSubHeaderCode] = [NRS].[CODE]
                                  AND [Previous].[SubHeaderCode] = [NOM].[SubHeaderCode]
                     WHERE [NOM].[ActualSubHeaderCode] >= [Previous].[ActualSubHeaderCode]
                           ORDER BY [Previous].[HeaderCode] DESC,
                                    [Previous].[SubHeaderCode] DESC,
                                    [Previous].[ActualSubHeaderCode] DESC) AS [LastValidSubHeader])
/* Now get the nominal ledger details and output */
SELECT ISNULL([NOM].[HeaderCode], '')          AS [HeaderCode],
       ISNULL([NOM].[Header], '')              AS [Header],
       ISNULL([NOM].[SubHeaderCode], '')       AS [SubHeaderCode],
       ISNULL([NOM].[SubHeader], '')           AS [SubHeader],
       ISNULL([NOM].[ActualSubHeaderCode], '') AS [ActualSubHeaderCode],
       ISNULL([NOM].[LastValidSubHeaderCode], '') AS [LastValidSubHeaderCode],
       ISNULL([NOM].[LastValidSubHeader], '')  AS [LastValidSubHeader],
       [NOM].[Code]                            AS [Code],
       [NOM].[AccountName]                     AS [AccountName],
       [NOM].[Type]                            AS [Type],
       ISNULL([ACC].[Value], 0)                AS [Accruals],
       ISNULL([NML].[BATCHNO], 0)              AS [BatchNo],
       ISNULL([NML].[PREF], 0)      AS [PRef],
       [NML].[DATE]                            AS [Date],
       [NML].[ENTRYDATE]                       AS [EntryDate],
       ISNULL([NML].[NARR], '')                AS [Narrative],
       ISNULL([NML].[VALUE], 0)                AS [Value],
       [NML].[PER]                             AS [Period],
       [NML].[YEAR]                            AS [Year],
       CASE WHEN [CON].[IsBranchingEnabled] = 1
                 THEN ISNULL([NML].[BRANCH], '')
            ELSE '' END                        AS [Branch],
       CASE WHEN [CON].[IsBranchingEnabled] = 1
                 THEN ISNULL([BRA].[DESCRIPTION], '')
            ELSE '' END                        AS [BranchName],
       [NML].[RECORDID]                        AS [RecordID]
  FROM [CTE_Nominal] AS [NOM]
       OUTER APPLY (SELECT CASE WHEN ISNULL([CON].[Indicator22], '') = 'Y' THEN 1 ELSE 0 END AS [IsBranchingEnabled]
                      FROM [control] AS [CON]) AS [CON]
       LEFT OUTER JOIN [CTE_Accruals] AS [ACC]
                    ON [NOM].[Code] = [ACC].[Code]
       LEFT OUTER JOIN [dbo].[NominalLedger] AS [NML]
                    ON [NOM].[Code] = [NML].[CODE]
       LEFT OUTER JOIN [dbo].[Branch] AS [BRA]
                    ON [NML].[BRANCH] = [BRA].[BRANCHCODE]
GO

IF OBJECT_ID(N'NewExternalDictations',N'V')IS NOT NULL
	DROP VIEW [dbo].[NewExternalDictations]
GO


CREATE
  VIEW [dbo].[NewExternalDictations]
  WITH SCHEMABINDING
AS

  SELECT [DIA].[CASECODE]                AS [CaseCode],
         [DAT].[DiaryID]                 AS [ActionId],
         [ED].[TrackReference]           AS [TrackReference],
         [DAT].[DOCUMENT]                AS [DOCUMENT],
         [ED].[RecordId]                 AS [RecordId],
         [ED].[Status]                   AS [Status],
         [ED].[DateField]                AS [DateField]
    FROM [dbo].[ExternalDictations] [ED]
         INNER JOIN [dbo].[DiaryAttachments] [DAT]
                    INNER JOIN [dbo].[diary] [DIA]
                               INNER JOIN [dbo].[SearchMatters] [SM]
                                       ON [SM].[Code] = [DIA].[CASECODE]
                                      AND [SM].[Closed] = 0
                               INNER JOIN [dbo].[DiaryDelegations] [DD]
                                          INNER JOIN [dbo].[RemoteTypist] [RT]
                                                  ON [RT].[Code] = [DD].[HANDLER]
                                       ON [DD].[ACTIONID] = [DIA].[ACTIONID]
                                      AND [DD].[STATUS] = 0
                                      AND [DD].[DelType] IN ('Delegated', 'Released')
                               INNER JOIN [dbo].[RemoteTypistStartDate] [RTS]
                                       ON [RTS].[StartDate] < [DIA].[DATE]
                            ON [DIA].[ACTIONID] = [DAT].[DiaryID]
                           AND [DIA].[ACTIONTYPE] = 'D'
                           AND [DIA].[STATUS] = 0
                 ON [DAT].[TrackReference] = [ED].[TrackReference]
                AND [DAT].[Type] IN ('AAC', 'AC3', 'FLAC', 'M4A', 'MP3', 'OGG', 'WAV', 'WMA')
   WHERE [ED].[Status] = 0
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] = 'PK_NewExternalDictations'
    WHERE [SO].[name] = 'NewExternalDictations'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN

CREATE UNIQUE CLUSTERED INDEX
       [PK_NewExternalDictations]
    ON [dbo].[NewExternalDictations]
      ([TrackReference])
    ON [PRIMARY]

END
GO

IF OBJECT_ID(N'OpenDictations',N'V')IS NOT NULL
	DROP VIEW [dbo].[OpenDictations]
GO

CREATE 
  VIEW [dbo].[OpenDictations]
  WITH SCHEMABINDING
AS
/************************************************************************************
 *
 *    View Name: OpenDictations
 *	  It returns list of all dictations which were created after RemoteTypistStartDate
 *
 *    Modification History
 *    2024-05-21   Pino				Created
 *    2024-09-13   Aakif			Action type should be checked against diary table
 *    2024-11-27   Ponni M			Returns only Open dictation logs
 ************************************************************************************/

SELECT [DAS].[TrackReference]
  FROM [dbo].[DiaryAttachmentSelector] [DAS]
	    INNER JOIN [dbo].[DiaryAttachments] [DAT]
                    INNER JOIN [dbo].[diary] [DIA]
                               INNER JOIN [dbo].[SearchMatters] [SM]
                                       ON [SM].[Code] = [DIA].[CASECODE]
                                      AND [SM].[Closed] = 0
                               INNER JOIN [dbo].[DiaryDelegations] [DD]
                                          INNER JOIN [dbo].[RemoteTypist] [RT]
                                                  ON [RT].[Code] = [DD].[HANDLER]
                                       ON [DD].[ACTIONID] = [DIA].[ACTIONID]
                                      AND [DD].[STATUS] = 0
                                      AND [DD].[DelType] IN ('Delegated', 'Released')
                               INNER JOIN [dbo].[RemoteTypistStartDate] [RTS]
                                       ON [RTS].[StartDate] < [DIA].[DATE]
                            ON [DIA].[ACTIONID] = [DAT].[DiaryID]
                           AND [DIA].[ACTIONTYPE] = 'D'
                           AND [DIA].[STATUS] = 0
                 ON [DAS].[TrackReference] = [DAT].[TrackReference]
                AND [DAT].[Type] IN ('AAC', 'AC3', 'FLAC', 'M4A', 'MP3', 'OGG', 'WAV', 'WMA')
 WHERE [DIA].[ACTIONTYPE] = 'D'
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] = 'PK_OpenDictations'
    WHERE [SO].[name] = 'OpenDictations'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE UNIQUE CLUSTERED INDEX
       [PK_OpenDictations]
    ON [dbo].[OpenDictations]
      ([TrackReference])
    ON [PRIMARY]

END
GO



IF OBJECT_ID(N'PublishedActions',N'V')IS NOT NULL
	DROP VIEW [dbo].[PublishedActions]
GO

CREATE VIEW
       [dbo].[PublishedActions]
  WITH SCHEMABINDING
AS
SELECT [DIA].[ACTIONID],
       [DIA].[CASECODE],
       [DIA].[DATE],
       [DIA].[DisplayText],
       CASE WHEN [DIA].[STATUS] IS NULL
                 THEN CONVERT(BIT, 0)
            WHEN [DIA].[STATUS] = 1
                 THEN CONVERT(BIT, 1)
            ELSE CONVERT(BIT, 0) END          AS [STATUS],
       CASE WHEN [DIA].[HIGHLIGHTED] IS NULL
                 THEN CONVERT(BIT, 0)
            WHEN [DIA].[HIGHLIGHTED] = 'Y'
                 THEN CONVERT(BIT, 1)
            ELSE CONVERT(BIT, 0) END          AS [HIGHLIGHTED]
  FROM [dbo].[diary] [DIA]
 WHERE [DIA].[PUBLISH] = 'P'

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] = 'PK_PublishedActions'
    WHERE [SO].[name] = 'PublishedActions'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE UNIQUE CLUSTERED INDEX 
		[PK_PublishedActions]
	ON [dbo].[PublishedActions]
	([ACTIONID] ASC)
	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_NextActions'
    WHERE [SO].[name] = 'PublishedActions'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE NONCLUSTERED INDEX
		[IDX_NextActions]
	ON [dbo].[PublishedActions]
	([CASECODE] DESC,
	 [STATUS] ASC,
	 [DATE] ASC)
	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_LastActions'
    WHERE [SO].[name] = 'PublishedActions'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE NONCLUSTERED INDEX 
		[IDX_LastActions]
	ON [dbo].[PublishedActions]
	([CASECODE] DESC,
	 [STATUS] DESC,
	 [DATE] DESC)
	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_Highlighted'
    WHERE [SO].[name] = 'PublishedActions'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE NONCLUSTERED INDEX
		[IDX_Highlighted]
	ON [dbo].[PublishedActions]
	([CASECODE] DESC,
	 [HIGHLIGHTED] DESC,
	 [DATE] DESC)
	ON [PRIMARY]
END
GO


IF OBJECT_ID(N'PublishedMatters',N'V')IS NOT NULL
	DROP VIEW [dbo].[PublishedMatters]
GO

CREATE VIEW
       [dbo].[PublishedMatters]
  WITH SCHEMABINDING
AS
/************************************************************************************
 *
 *    View Name: PublishedMatters
 *	  It returns Matters details which is published and Has RemoteAccess
 *
 *    Modification History
 *    2024-07-11   Pino				Created
 *    2024-08-20   Aakif			Include 'RemoteAccess' check
 ************************************************************************************/


SELECT [MAT].[Code],
       [SM].[Name],
       [SM].[ClientCode],
       [SM].[FECode] + '-' + [SM].[HandlerName]              AS [FECode],
       [SM].[PhoneNumber]                                    AS [Telephone],
       [SM].[NCAddress]                                      AS [ClientAddress],
       [MAT].[Code]                                          AS [MatterCode],
       [SM].[Description]                                    AS [MatterDescription],
       [SM].[HandlerName]                                    AS [CaseHandler],
       [CTL].[USERPROMPT1]                                   AS [User1Label],
       [SM].[User1]                                          AS [User1],
       [CTL].[USERPROMPT2]                                   AS [User2Label],
       [SM].[User2]                                          AS [User2],
       [CTL].[USERPROMPT3]                                   AS [User3Label],
       [SM].[User3]                                          AS [User3],
       [CTL].[YourRef]                                       AS [YourRefLabel],
       [SM].[YourRef]                                        AS [YourRef],
       [SM].[Started]                                        AS [MatterStartDate],
       [SM].[closedate]                                      AS [MatterCloseDate],
       [SM].[MatterStatus]                                   AS [MatterStatusCode],
       [SM].[MatterStatusDescription]                        AS [MatterStatus],
       [MAT].[OriginalDebt]                                  AS [MatterOriginalDebt],
       RTRIM(ISNULL([MAT].[Comment], ''))                    AS [MatterComments],
       [CTC].[DisallowNewMatter]                             AS [DisallowNewMatter],
       [SM].[Closed]                                         AS [Closed],
       CASE WHEN [SM].[Closed] = 1
                 THEN 'Y'
            ELSE 'N' END                                     AS [IsMatterClosed],
       CONVERT(DECIMAL(19, 2), 
                 (ISNULL([MAT].[OriginalDebt], 0) + 
                  ISNULL([MAT].[DebtInterest], 0) +
                  ISNULL([MAT].[RecoverableCosts], 0)) - 
                  ISNULL([MAT].[DebtCollected], 0))          AS [OutstandingAmount],
       [SM].[Started]                                        AS [Started]
  FROM [dbo].[matters] [MAT]
       INNER JOIN [dbo].[SearchMatters] [SM]
               ON [SM].[Code] = [MAT].[Code]
       INNER JOIN [dbo].[Contacts] [CTC]
               ON [CTC].[Code] = [MAT].[ClientCode]
       INNER JOIN [dbo].[control] [CTL]
               ON ISNULL([CTL].[NAME], '') IS NOT NULL
 WHERE [MAT].[Publish] = 'P'
   AND [CTC].[RemoteAccess] = 'Y'
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] = 'PK_PublishedMatters'
    WHERE [SO].[name] = 'PublishedMatters'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE UNIQUE CLUSTERED INDEX 
		[PK_PublishedMatters]
	ON [dbo].[PublishedMatters]
	([Code] ASC)
	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_ClosedClient'
    WHERE [SO].[name] = 'PublishedMatters'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE NONCLUSTERED INDEX 
		[IDX_ClosedClient]
	ON [dbo].[PublishedMatters]
	([ClientCode] ASC,
	 [Closed] ASC,
	 [Code] ASC)
	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_Closed'
    WHERE [SO].[name] = 'PublishedMatters'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE NONCLUSTERED INDEX 
		[IDX_Closed]
	ON [dbo].[PublishedMatters]
	([Closed] ASC,
	 [Code] ASC)
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_Client'
    WHERE [SO].[name] = 'PublishedMatters'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN
CREATE NONCLUSTERED INDEX 
		[IDX_Client] 
	ON [dbo].[PublishedMatters]
	([ClientCode] ASC,
	 [Code] ASC)
	ON [PRIMARY]
END
GO
IF OBJECT_ID(N'ReturnedExternalDictations',N'V')IS NOT NULL
	DROP VIEW [dbo].[ReturnedExternalDictations]
GO

CREATE
  VIEW [dbo].[ReturnedExternalDictations]
  WITH SCHEMABINDING
AS

  SELECT [DIA].[CASECODE]                AS [CaseCode],
         [DAT].[DiaryID]                 AS [ActionId],
         [ED].[TrackReference]           AS [TrackReference],
         [DAT].[DOCUMENT]                AS [DOCUMENT],
         [ED].[RecordId]                 AS [RecordId],
         [ED].[Status]                   AS [Status],
         [ED].[DateField]                AS [DateField]
    FROM [dbo].[ExternalDictations] [ED]
         INNER JOIN [dbo].[DiaryAttachments] [DAT]
                    INNER JOIN [dbo].[diary] [DIA]
                               INNER JOIN [dbo].[SearchMatters] [SM]
                                       ON [SM].[Code] = [DIA].[CASECODE]
                                      AND [SM].[Closed] = 0
                            ON [DIA].[ACTIONID] = [DAT].[DiaryID]
                           AND [DIA].[STATUS] = 0
                 ON [DAT].[TrackReference] = [ED].[TrackReference]
   WHERE [ED].[Status] = 2
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] = 'PK_ReturnedExternalDictations'
    WHERE [SO].[name] = 'ReturnedExternalDictations'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN

CREATE UNIQUE CLUSTERED INDEX
       [PK_ReturnedExternalDictations]
    ON [dbo].[ReturnedExternalDictations]
      ([TrackReference])
    ON [PRIMARY]

END
GO

IF OBJECT_ID(N'SentExternalDictations',N'V')IS NOT NULL
	DROP VIEW [dbo].[SentExternalDictations]
GO

CREATE
  VIEW [dbo].[SentExternalDictations]
  WITH SCHEMABINDING
AS

  SELECT [DIA].[CASECODE]                AS [CaseCode],
         [DAT].[DiaryID]                 AS [ActionId],
         [ED].[TrackReference]           AS [TrackReference],
         [DAT].[DOCUMENT]                AS [DOCUMENT],
         [ED].[RecordId]                 AS [RecordId],
         [ED].[Status]                   AS [Status],
         [ED].[DateField]                AS [DateField]
    FROM [dbo].[ExternalDictations] [ED]
         INNER JOIN [dbo].[DiaryAttachments] [DAT]
                    INNER JOIN [dbo].[diary] [DIA]
                               INNER JOIN [dbo].[SearchMatters] [SM]
                                       ON [SM].[Code] = [DIA].[CASECODE]
                                      AND [SM].[Closed] = 0
                            ON [DIA].[ACTIONID] = [DAT].[DiaryID]
                           AND [DIA].[STATUS] = 0
                 ON [DAT].[TrackReference] = [ED].[TrackReference]
   WHERE [ED].[Status] = 1
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] = 'PK_SentExternalDictations'
    WHERE [SO].[name] = 'SentExternalDictations'
      AND [SO].[type] = 'V'
      AND [SI].[index_id] IS NULL)
BEGIN

CREATE UNIQUE CLUSTERED INDEX
       [PK_SentExternalDictations]
    ON [dbo].[SentExternalDictations]
      ([TrackReference])
    ON [PRIMARY]

END
GO