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].[RemoteTypist] [RT]
                                       ON [RT].[Code] = [DIA].[FNCODE]
                               INNER JOIN [dbo].[RemoteTypistStartDate] [RTS]
                                       ON [RTS].[StartDate] < [DIA].[DATE]
                            ON [DIA].[ACTIONID] = [DAT].[DiaryID]
                           AND [DIA].[STATUS] = 0
                 ON [DAT].[TrackReference] = [ED].[TrackReference]
   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

SELECT [DAS].[TrackReference]
  FROM [dbo].[DiaryAttachmentSelector] [DAS]
       INNER JOIN [dbo].[diary] [DIA]
                  INNER JOIN [dbo].[SearchMatters] [SM]
                          ON [SM].[Code] = [DIA].[CASECODE]
                         AND [SM].[Closed] = 0
               ON [DIA].[ACTIONID] = [DAS].[ActionID]
              AND [DIA].[STATUS] = 0
 WHERE [DAS].[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'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