IF EXISTS
  (SELECT *
     FROM sys.objects SO
    WHERE SO.[name] = 'MoveClosed'
      AND SO.[type] = 'U')
BEGIN
  DROP TABLE [dbo].[MoveClosed]
END
GO

CREATE TABLE
       [dbo].[MoveClosed]
      ([REPORTMODE]             INT        NOT NULL,
       [STOP]                   INT        NOT NULL,
       [ROLLBACK]               INT        NOT NULL)
    ON [PRIMARY]
GO

/* SET REPORTMODE to 0 to actually run the query
   SET ROLLBACK to 0 to have the transaction committed at the end*/
INSERT
  INTO [dbo].[MoveClosed]
      ([REPORTMODE],
       [STOP],
       [ROLLBACK])
VALUES(1,
       0,
       1)
GO

BEGIN TRY
  /* Create reporting database */
  IF EXISTS
    (select *
       FROM [master].[sys].[databases] [DB]
      WHERE [DB].[name] = 'Keyhouse_MoveClosed')
  BEGIN
    IF EXISTS
      (SELECT *
         FROM [Keyhouse_MoveClosed].sys.objects SO
        WHERE SO.[name] = 'OrphanedCaseMasterRecords'
          AND SO.[type] = 'U')
      BEGIN
        DROP TABLE [Keyhouse_MoveClosed].[dbo].[OrphanedCaseMasterRecords]
      END

    IF EXISTS
      (SELECT *
         FROM [Keyhouse_MoveClosed].sys.objects SO
        WHERE SO.[name] = 'MatterRenumberingLog'
          AND SO.[type] = 'U')
      BEGIN
        DROP TABLE [Keyhouse_MoveClosed].[dbo].[MatterRenumberingLog]
      END

    DROP DATABASE [Keyhouse_MoveClosed]
  END
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 2
    FROM [dbo].[MoveClosed] CLO
END CATCH
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  DECLARE @DB NVARCHAR(100)
  DECLARE @Path NVARCHAR(4000)
  DECLARE @FN NVARCHAR(100)

  SET @DB = DB_NAME()

  SELECT @Path = [filename] FROM master..sysdatabases WHERE name = @DB

  IF CHARINDEX('\', @Path) > 0 
    BEGIN
      SET @FN = SUBSTRING(@Path, LEN(@Path) - CharIndex('\', Reverse(@Path)) + 2, CharIndex('\', Reverse(@Path)) - 1)
      SET @Path = SUBSTRING(@Path, 1, LEN(@Path) - CharIndex('\', Reverse(@Path)))
      IF CHARINDEX('.', @FN) > 0 
        BEGIN
          SET @FN = SUBSTRING(@FN, 1, LEN(@FN) - CharIndex('.', Reverse(@FN)))
        END
    END 

  DECLARE @COMMAND VARCHAR(MAX)

  SET @COMMAND = 'CREATE DATABASE [Keyhouse_MoveClosed]
 ON  PRIMARY 
( NAME = N''Keyhouse_MoveClosed'', FILENAME = N''' + @Path + '\' + @DB + '_MoveClosed.mdf'' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 20% )
 LOG ON 
( NAME = N''Keyhouse_MoveClosed_log'', FILENAME = N''' + @Path + '\' + @DB + '_MoveClosed_1.ldf'' , SIZE = 100MB , MAXSIZE = UNLIMITED , FILEGROWTH = 20%)'

  EXEC (@COMMAND)

  DECLARE @COLLATION VARCHAR(100)

  select @COLLATION = [DB].[collation_name]
    from [master].[sys].[databases] [DB]
   where [DB].[name] = @DB

  SET @COMMAND = 'ALTER DATABASE [Keyhouse_MoveClosed] COLLATE ' + @COLLATION

  EXEC (@COMMAND)
END
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET ANSI_NULL_DEFAULT OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET ANSI_NULLS OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET ANSI_PADDING OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET ANSI_WARNINGS OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET ARITHABORT OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET AUTO_CLOSE OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET AUTO_CREATE_STATISTICS ON 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET AUTO_SHRINK OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET AUTO_UPDATE_STATISTICS ON 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET CURSOR_DEFAULT  GLOBAL 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET NUMERIC_ROUNDABORT OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET QUOTED_IDENTIFIER OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET RECURSIVE_TRIGGERS OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET  DISABLE_BROKER 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET TRUSTWORTHY OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET PARAMETERIZATION SIMPLE 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET READ_COMMITTED_SNAPSHOT OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET RECOVERY FULL 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET  MULTI_USER 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET PAGE_VERIFY CHECKSUM  
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET DB_CHAINING OFF 
GO

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
ALTER DATABASE [Keyhouse_MoveClosed] SET  READ_WRITE 
GO

DECLARE @ORPHANEDCASEMASTER TABLE
       (CSCODE          VARCHAR(20))

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  INSERT
    INTO @ORPHANEDCASEMASTER
        ([CSCODE])
  SELECT CSM.[CSCODE]
    from [dbo].[CaseMaster] CSM
         LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
   WHERE MAT.[Code] IS NULL

  SELECT CSM.*
    INTO [Keyhouse_MoveClosed].[dbo].[OrphanedCaseMasterRecords]
    FROM @ORPHANEDCASEMASTER ORP
         INNER JOIN [dbo].[CaseMaster] CSM
                 ON CSM.[CSCODE] = ORP.[CSCODE]
END
GO

DECLARE @LOG TABLE
       ([ID]         INT              IDENTITY(1, 1),
        [ACTION]     VARCHAR(1000),
        [RESULT]     VARCHAR(7))

INSERT
  INTO @LOG
      ([ACTION])
SELECT 'RUN SETTINGS: ' + CASE WHEN CLO.[REPORTMODE] = 1 THEN 'Report only, data is not modified' ELSE 'Executing move' + ', ' + CASE WHEN CLO.[ROLLBACK] = 1 THEN 'TEST run, result will be rolled back' ELSE 'Actual run, result will be committed if successful' END END
  FROM [dbo].[MoveClosed] CLO

BEGIN TRANSACTION

/*****************************************************************************************
 *
 * Get rid of inconsistent data START
 *    for each [Code] in [dbo].[matters] there should be a corresponding [CSCODE] in
 *    [dbo].[CaseMaster]. If there's an orphaned [CSCODE] in [dbo].[CaseMaster], get rid
 *    of any references to his CSCODE in any of the live tables. This will avoid problems
 *    with Primary Key duplicate insertions further down the line.
 *
 *****************************************************************************************/
IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 2)
BEGIN
    INSERT
      INTO @LOG
          ([ACTION])
    VALUES('Cannot proceed. Logging database could not be dropped and recreated.')
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
BEGIN TRY
  DELETE DAT
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[diary] DIA
                    INNER JOIN [dbo].[DiaryAttachments] DAT
                            ON DAT.[DIARYID] = DIA.[ACTIONID]
                  ON DIA.[CASECODE] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL

  DELETE DAT
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[DiaryAttachments] DAT
                  ON DAT.[CASECODE] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL

  DELETE DEL
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[diary] DIA
                    INNER JOIN [dbo].[DiaryDelegations] DEL
                            ON DEL.[ACTIONID] = DIA.[ACTIONID]
                  ON DIA.[CASECODE] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL

  DELETE DIA
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[diary] DIA
                  ON DIA.[CASECODE] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL

  DELETE CUF
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[CaseUDFAnswers] CUF
                  ON CUF.[CASECODE] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL
 
  DELETE MAL
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[MatterLedger] MAL
                  ON MAL.[MATTER] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL
 
  DELETE TIE
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[TimeEntry] TIE
                  ON TIE.[MATTER] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL
 
  DELETE TDB
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[TimeDayBook] TDB
                  ON TDB.[MATTER] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL
 
  DELETE UND
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[Undertakings] UND
                  ON UND.[MATTER] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL
 
  DELETE BD
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[BillHeader] BH
                    INNER JOIN [dbo].[BillDetails] BD
                            ON BD.[DRAFTNO] = BH.[DRAFTNO]
                  ON BH.[MATTER] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL
 
  DELETE BL
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[BillHeader] BH
                    INNER JOIN [dbo].[BillLogging] BL
                            ON BL.[DRAFTNO] = BH.[DRAFTNO]
                  ON BH.[MATTER] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL
 
  DELETE BF
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[BillFeeBreakDown] BF
                  ON BF.[MATTER] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL
 
  DELETE BH
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[BillHeader] BH
                  ON BH.[MATTER] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL

  DELETE BD
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[BatchH] BH
                    INNER JOIN [dbo].[BatchDetails] BD
                            ON BD.[BATCHNO] = BH.[BATCHNO]
                  ON BH.[MATTER] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL

  DELETE BH
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
          INNER JOIN [dbo].[BatchH] BH
                  ON BH.[MATTER] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL

  DELETE CSM
    from [dbo].[CaseMaster] CSM
          LEFT OUTER JOIN [dbo].[matters] MAT
                      ON MAT.[Code] = CSM.[CSCODE]
    WHERE MAT.[Code] IS NULL

  DELETE CLS
    from [dbo].[Closed] CLS
          INNER JOIN [dbo].[matters] MAT
                  ON MAT.[Code] = CLS.[CODE]
    WHERE CLS.[CODE] = 'ZZZZZZ/ZZZZ'
      AND MAT.[Code] IS NOT NULL

  DELETE CLS
    from [dbo].[CaseMasterClosed] CLS
          INNER JOIN [dbo].[matters] MAT
                  ON MAT.[Code] = CLS.[CSCODE]
    WHERE CLS.[CSCODE] = 'ZZZZZZ/ZZZZ'
      AND MAT.[Code] IS NOT NULL

    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Removing orphaned Case Master Records',
           'Success')
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO

    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Removing orphaned Case Master Records',
           'Failure')
END CATCH
END

/*****************************************************************************************
 *
 * Get rid of inconsistent data END
 *
 *****************************************************************************************/

DECLARE @DUPS TABLE
       ([ClientCode]      VARCHAR(10),
        [Code]            VARCHAR(20),
        [Number]          int,
        [NextMatterNo]    VARCHAR(20))

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  BEGIN TRY
    INSERT
      INTO @DUPS
          ([ClientCode],
           [Code])
    SELECT RTRIM(ISNULL(CLS.[CLIENTCODE], '')) AS [CLIENTCODE],
           RTRIM(ISNULL(CLS.[CODE], '')) AS [CODE]
      FROM [dbo].[Closed] CLS
           INNER JOIN [dbo].[matters] MAT
                   ON MAT.[Code] = CLS.[CODE]

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Identifying Duplicate Case Codes',
             'Success')
  END TRY
  BEGIN CATCH
    UPDATE CLO
        SET CLO.[STOP] = 1
      FROM [dbo].[MoveClosed] CLO

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Identifying Duplicate Case Codes',
             'Failure')
  END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  BEGIN TRY
    UPDATE DUP
       SET DUP.[Number] = MD.[TOTAL]
      FROM @DUPS DUP
           CROSS APPLY (SELECT COUNT(1) AS [TOTAL]
                          FROM @DUPS D
                         WHERE D.[ClientCode] = DUP.[ClientCode]
                           AND D.[Code] < DUP.[Code]) MD

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Determining Number Of Cases for clients',
             'Success')
  END TRY
  BEGIN CATCH
    UPDATE CLO
        SET CLO.[STOP] = 1
      FROM [dbo].[MoveClosed] CLO

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Determining Number Of Cases for clients',
             'Failure')
  END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  BEGIN TRY
    UPDATE DUP
       SET DUP.[NextMatterNo] = DUP.[ClientCode] + '/' + SUBSTRING(CONVERT(VARCHAR(5), 10000 + EM2.[MATTERNO] + 1 + DUP.[Number]), 2, 4)
      FROM @DUPS DUP
           CROSS APPLY (SELECT ISNULL(MAX(CASE WHEN ISNUMERIC(EM.[MATTER]) = 0
                                                    THEN 0
                                               ELSE CONVERT(int, EM.[MATTER]) END), 0) AS [MATTERNO]
                          FROM (SELECT M1.[MATTER]
                                  FROM [dbo].[matters] M1
                                  WHERE M1.[ClientCode] = DUP.[ClientCode]
                                UNION
                                SELECT C1.[MATTER]
                                  FROM [dbo].[closed] C1
                                  WHERE C1.[ClientCode] = DUP.[ClientCode]) EM) EM2

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Calculating replacement Matter Codes',
             'Success')
  END TRY
  BEGIN CATCH
    UPDATE CLO
        SET CLO.[STOP] = 1
      FROM [dbo].[MoveClosed] CLO

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Calculating replacement Matter Codes',
             'Failure')
  END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  BEGIN TRY
    UPDATE CLS
       SET CLS.[MATTER] = DUP.[NextMatterNo]
      FROM @DUPS DUP
           INNER JOIN [dbo].[ClosedMatterLedger] CLS
                   ON CLS.[MATTER] = DUP.[Code]

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset ClosedMatterLedger.MATTER',
             'Success')
  END TRY
  BEGIN CATCH
    UPDATE CLO
        SET CLO.[STOP] = 1
      FROM [dbo].[MoveClosed] CLO

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset ClosedMatterLedger.MATTER',
             'Failure')
  END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  BEGIN TRY
    UPDATE CLS
       SET CLS.[CASECODE] = DUP.[NextMatterNo]
      FROM @DUPS DUP
           INNER JOIN [dbo].[CloseDiaryAttachments] CLS
                   ON CLS.[CASECODE] = DUP.[Code]

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset CloseDiaryAttachments.CASECODE',
             'Success')
  END TRY
  BEGIN CATCH
    UPDATE CLO
        SET CLO.[STOP] = 1
      FROM [dbo].[MoveClosed] CLO

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset CloseDiaryAttachments.CASECODE',
             'Failure')
  END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  BEGIN TRY
    UPDATE CLS
       SET CLS.[CASECODE] = DUP.[NextMatterNo]
      FROM @DUPS DUP
           INNER JOIN [dbo].[CloseDiary] CLS
                   ON CLS.[CASECODE] = DUP.[Code]

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset CloseDiary.CASECODE',
             'Success')
  END TRY
  BEGIN CATCH
    UPDATE CLO
        SET CLO.[STOP] = 1
      FROM [dbo].[MoveClosed] CLO

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset CloseDiary.CASECODE',
             'Failure')
  END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  BEGIN TRY
    UPDATE CLS
       SET CLS.[CASECODE] = DUP.[NextMatterNo]
      FROM @DUPS DUP
           INNER JOIN [dbo].[ClosedCaseUDFAnswers] CLS
                   ON CLS.[CASECODE] = DUP.[Code]

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset ClosedCaseUDFAnswers.CASECODE',
             'Success')
  END TRY
  BEGIN CATCH
    UPDATE CLO
        SET CLO.[STOP] = 1
      FROM [dbo].[MoveClosed] CLO

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset ClosedCaseUDFAnswers.CASECODE',
             'Failure')
  END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  BEGIN TRY
    UPDATE CLS
       SET CLS.[CASECODE] = DUP.[NextMatterNo]
      FROM @DUPS DUP
           INNER JOIN [dbo].[CloseCaseContacts] CLS
                   ON CLS.[CASECODE] = DUP.[Code]

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset CloseCaseContacts.CASECODE',
             'Success')
  END TRY
  BEGIN CATCH
    UPDATE CLO
        SET CLO.[STOP] = 1
      FROM [dbo].[MoveClosed] CLO

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset CloseCaseContacts.CASECODE',
             'Failure')
  END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  BEGIN TRY
    UPDATE CLS
       SET CLS.[CSCODE] = DUP.[NextMatterNo]
      FROM @DUPS DUP
           INNER JOIN [dbo].[CaseMasterClosed] CLS
                   ON CLS.[CSCODE] = DUP.[Code]

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset CaseMasterClosed.CSCODE',
             'Success')
  END TRY
  BEGIN CATCH
    UPDATE CLO
        SET CLO.[STOP] = 1
      FROM [dbo].[MoveClosed] CLO

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset CaseMasterClosed.CSCODE',
             'Failure')
  END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
  BEGIN TRY
    UPDATE CLS
       SET CLS.[Code] = DUP.[NextMatterNo] 
      FROM @DUPS DUP
           INNER JOIN [dbo].[Closed] CLS
                   ON CLS.[Code] = DUP.[Code]

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset Closed.Code',
             'Success')
  END TRY
  BEGIN CATCH
    UPDATE CLO
        SET CLO.[STOP] = 1
      FROM [dbo].[MoveClosed] CLO

      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Reset Closed.Code',
             'Failure')
  END CATCH
END

DECLARE @RESTOREDMATTERS TABLE
       ([ClientCode]      VARCHAR(10),
        [Code]            VARCHAR(20),
        [RestoredToCode]  VARCHAR(20))

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0)
BEGIN
BEGIN TRY
  INSERT
    INTO @RESTOREDMATTERS
        ([ClientCode],
         [Code],
         [RestoredToCode])
  SELECT RTRIM(ISNULL(CLS.[CLIENTCODE], ''))               AS [CLIENTCODE],
         ISNULL(DUP.[Code], CLS.[Code])                    AS [OriginalCode],
         CLS.[Code]                                        AS [RestoredToCode]
    FROM [dbo].[Closed] CLS
         LEFT OUTER JOIN @DUPS DUP
                      ON DUP.[NextMatterNo] = CLS.[CODE]
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO

    INSERT
      INTO @LOG
          ([ACTION],
            [RESULT])
    VALUES('Cannot log restored matters',
            'Failure')
END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [REPORTMODE] = 0
      AND [STOP] = 0)
BEGIN
BEGIN TRY
  INSERT
    INTO [dbo].[matters]
        ([Code],
         [ClientCode],
         [Matter],
         [Description],
         [FECode],
         [Partner],
         [Secretary],
         [Dept],
         [Started],
         [WType],
         [Branch],
         [EstFee],
         [OutlayBud],
         [ExpBillD],
         [ExpPayD],
         [S13],
         [Comment],
         [DebtBal],
         [OutlayBal],
         [ClientBal],
         [ClientCurBal],
         [ClientDepBal],
         [DebtBalFwd],
         [OutlayBalFwd],
         [ClientBalFwd],
         [ClientCurBalFwd],
         [ClientDepBalFwd],
         [PendingBal],
         [ClientMin],
         [FileNum],
         [ThirdPart],
         [ThPartSol],
         [Value],
         [Undertaking],
         [LastAct],
         [LastActType],
         [LastActWho],
         [StatuteLimits],
         [Status],
         [Section68],
         [FNL_Date],
         [FNL_Name],
         [FNL_Address],
         [FNL_Fees],
         [FNL_Outlay],
         [FNL_VAT],
         [FNL_Total],
         [OldRef],
         [YourRef],
         [PCODE],
         [PFECODE],
         [User1],
         [User2],
         [User3],
         [DepositName],
         [DepositType],
         [TimeBal],
         [ChargeBal],
         [CurDebtBal],
         [CurOutlayBal],
         [CurClientBal],
         [CurClientCBal],
         [CurClientDBal],
         [CurDebtBalFwd],
         [CurOutlayBalFwd],
         [CurClientBalFwd],
         [CurClientCBalFwd],
         [CurClientDBalFwd],
         [CurPendingBal],
         [Unallocated],
         [Statements],
         [ChargeLevel],
         [FileColour],
         [OpenInvoiceNo],
         [Password],
         [CompBillOnOff],
         [CompFrequency],
         [CompOutlayLimit],
         [CompFeesLimit],
         [CompTotalLimit],
         [CompBillingMatter],
         [CompBilling],
         [ClientName],
         [ClientAddress],
         [ClientSalutation],
         [ChargeArrangement],
         [Importance],
         [OriginalDebt],
         [DebtCollected],
         [RecoverableCosts],
         [DebtInterest],
         [BillingMethod],
         [CollectingBalance],
         [Publish],
         [Permissions],
         [OrgClosedDate],
         [OUTFileNo],
         [ChargeOutRateInd],
         [ReOpenedDate],
         [FixedFee],
         [ShowCommentInAlarm],
         [OtherRef],
         [Location],
         [DestroyDate],
         [Closed],
         [CloseDate])
  SELECT [Code]                                        AS [Code],
         RTRIM(ISNULL([CLIENTCODE], ''))               AS [CLIENTCODE],
         RTRIM(ISNULL([Matter], ''))                   AS [Matter],
         RTRIM(ISNULL([DESCRIPTION], ''))              AS [DESCRIPTION],
         RTRIM(ISNULL([FECode], ''))                   AS [FECode],
         NULL                                          AS [Partner],
         NULL                                          AS [Secretary],
         RTRIM(ISNULL([DEPT], ''))                     AS [Dept],
         [Started]                                     AS [Started],
         RTRIM(ISNULL([WTYPE], ''))                    AS [WType],
         [BRANCH]                                      AS [Branch],
         ISNULL([ESTFEE], 0)                           AS [EstFee],
         ISNULL([OUTLAYBUD], 0)                        AS [OutlayBud],
         [EXPBILLD]                                    AS [ExpBillD],
         [EXPPAYD]                                     AS [ExpPayD],
         [S13]                                         AS [S13],
         CONVERT(VARCHAR(3000), RTRIM([COMMENT]))      AS [Comment],
         ISNULL([DEBTBAL], 0)                          AS [DebtBal],
         ISNULL([OUTLAYBAL], 0)                        AS [OutlayBal],
         ISNULL([CLIENTBAL], 0)                        AS [ClientBal],
         ISNULL([CLIENTCURBAL], 0)                     AS [ClientCurBal],
         ISNULL([CLIENTDEPBAL], 0)                     AS [ClientDepBal],
         ISNULL([DEBTBALFWD], 0)                       AS [DebtBalFwd],
         ISNULL([OUTLAYBALFWD], 0)                     AS [OutlayBalFwd],
         ISNULL([CLIENTBALFWD], 0)                     AS [ClientBalFwd],
         ISNULL([CLIENTCURBALFWD], 0)                  AS [ClientCurBalFwd],
         ISNULL([CLIENTDEPBALFWD], 0)                  AS [ClientDepBalFwd],
         ISNULL([PENDINGBAL], 0)                       AS [PendingBal],
         ISNULL([CLIENTMIN], 0)                        AS [ClientMin],
         ISNULL([FILENUM], 0)                          AS [FileNum],
         [THIRDPART]                                   AS [ThirdPart],
         [THPARTSOL]                                   AS [ThPartSol],
         ISNULL([VALUE], 0)                            AS [Value],
         [UNDERTAKING]                                 AS [Undertaking],
         [LASTACT]                                     AS [LastAct],
         RTRIM([LASTACTTYPE])                          AS [LastActType],
         RTRIM([LASTACTWHO])                           AS [LastActWho],
         [STATUTELIMITS]                               AS [StatuteLimits],
         RTRIM([STATUS])                               AS [Status],
         [SECTION68]                                   AS [Section68],
         [FNL_DATE]                                    AS [FNL_Date],
         [FNL_NAME]                                    AS [FNL_Name],
         [FNL_ADDRESS]                                 AS [FNL_Address],
         ISNULL([FNL_FEES], 0)                         AS [FNL_Fees],
         ISNULL([FNL_OUTLAY], 0)                       AS [FNL_Outlay],
         ISNULL([FNL_VAT], 0)                          AS [FNL_VAT],
         ISNULL([FNL_TOTAL], 0)                        AS [FNL_Total],
         RTRIM([OLDREF])                               AS [OldRef],
         RTRIM([YOURREF])                              AS [YourRef],
         RTRIM([PCODE])                                AS [PCODE],
         RTRIM([PFECODE])                              AS [PFECODE],
         RTRIM([USER1])                                AS [User1],
         RTRIM([USER2])                                AS [User2],
         RTRIM([USER3])                                AS [User3],
         RTRIM([DEPOSITNAME])                          AS [DepositName],
         [DEPOSITTYPE]                                 AS [DepositType],
         ISNULL([TIMEBAL], 0)                          AS [TimeBal],
         ISNULL([CHARGEBAL], 0)                        AS [ChargeBal],
         ISNULL([CURDEBTBAL], 0)                       AS [CurDebtBal],
         ISNULL([CUROUTLAYBAL], 0)                     AS [CurOutlayBal],
         ISNULL([CURCLIENTBAL], 0)                     AS [CurClientBal],
         ISNULL([CURCLIENTCBAL], 0)                    AS [CurClientCBal],
         ISNULL([CURCLIENTDBAL], 0)                    AS [CurClientDBal],
         ISNULL([CURDEBTBALFWD], 0)                    AS [CurDebtBalFwd],
         ISNULL([CUROUTLAYBALFWD], 0)                  AS [CurOutlayBalFwd],
         ISNULL([CURCLIENTBALFWD], 0)                  AS [CurClientBalFwd],
         ISNULL([CURCLIENTCBALFWD], 0)                 AS [CurClientCBalFwd],
         ISNULL([CURCLIENTDBALFWD], 0)                 AS [CurClientDBalFwd],
         ISNULL([CURPENDINGBAL], 0)                    AS [CurPendingBal],
         ISNULL([UNALLOCATED], 0)                      AS [Unallocated],
         [STATEMENTS]                                  AS [Statements],
         [CHARGELEVEL]                                 AS [ChargeLevel],
         [FILECOLOUR]                                  AS [FileColour],
         [OPENINVOICENO]                               AS [OpenInvoiceNo],
         RTRIM([PASSWORD])                             AS [Password],
         [COMPBILLONOFF]                               AS [CompBillOnOff],
         [COMPFREQUENCY]                               AS [CompFrequency],
         ISNULL([COMPOUTLAYLIMIT], 0)                  AS [CompOutlayLimit],
         ISNULL([COMPFEESLIMIT], 0)                    AS [CompFeesLimit],
         ISNULL([COMPTOTALLIMIT], 0)                   AS [CompTotalLimit],
         RTRIM([CompBillingMatter])                    AS [CompBillingMatter],
         [CompBilling]                                 AS [CompBilling],
         RTRIM([ClientName])                           AS [ClientName],
         RTRIM([ClientAddress])                        AS [ClientAddress],
         RTRIM([ClientSalutation])                     AS [ClientSalutation],
         [ChargeArrangement]                           AS [ChargeArrangement],
         [Importance]                                  AS [Importance],
         ISNULL([OriginalDebt], 0)                     AS [OriginalDebt],
         ISNULL([DebtCollected], 0)                    AS [DebtCollected],
         ISNULL([RecoverableCosts], 0)                 AS [RecoverableCosts],
         ISNULL([DebtInterest], 0)                     AS [DebtInterest],
         [BillingMethod]                               AS [BillingMethod],
         ISNULL([CollectingBalance], 0)                AS [CollectingBalance],
         [Publish]                                     AS [Publish],
         ISNULL([Permissions], 0)                      AS [Permissions],
         [OrgClosedDate]                               AS [OrgClosedDate],
         NULL                                          AS [OUTFileNo],
         NULL                                          AS [ChargeOutRateInd],
         NULL                                          AS [ReOpenedDate],
         'N'                                           AS [FixedFee],
         'N'                                           AS [ShowCommentInAlarm],
         RTRIM([OTHERREF])                             AS [OtherRef],
         RTRIM([Location])                             AS [Location],
         [DestroyDate]                                 AS [DestroyDate],
         'Y'                                           AS [Closed],
         [DATECLOSED]                                  AS [CloseDate]
    FROM [dbo].[Closed]

    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy Closed to Matters',
           'Success')
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO

    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy Closed to Matters',
           'Failure')
END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [REPORTMODE] = 0
      AND [STOP] = 0)
BEGIN
BEGIN TRY
  INSERT
    INTO [dbo].[CaseMaster]
        ([CSCODE],
         [CSCLIENTCDE],
         [CSFNR],
         [CSDESC],
         [CSINSTYN],
         [CSDATEENTERED_DATE],
         [CSPLAINTNO],
         [CSWARRANT],
         [CSCLEXEYN],
         [CSCLBENYN],
         [CSLEDGREF],
         [CSPFILEREF],
         [CSWKTCODE],
         [CSCRTCODE],
         [CSAGYCODE],
         [CSGENAGYCODE],
         [CSBSCCODE],
         [CSBNKCODE],
         [CSLRGCODE],
         [CSDRGCODE],
         [CSHCTCODE],
         [CSSHFCODE],
         [CSLAUTHCODE],
         [CSIRTCODE],
         [CSSTACODE],
         [CSISSFEECODE],
         [CSJUDFEECODE],
         [CSINTADD],
         [CSCOMPTIME],
         [CSCOMPUNIT],
         [CSCONTRATE],
         [CSDAILYRATE],
         [CSNODTR],
         [CSNOGTR],
         [CSNOPTF],
         [CSNOPLC],
         [CSNODOC],
         [CSNOSPL],
         [CSNOBEN],
         [CSPREMISES],
         [CSSOLTRDNAME],
         [CSSOLTRDADDR],
         [CSSOLTEL],
         [CSSOLFAX],
         [CSSOLSALUTE],
         [CSSOLDXNO],
         [CSSOLEMAIL],
         [CSDATESERVICE_DATE],
         [CSDATEINSTSTART_DATE],
         [CSINSTTOTAL],
         [CSINSTAMT],
         [CSINSTFREQ],
         [CSINSTUNIT],
         [CSLASTACN],
         [CSDATELASTACN_DATE],
         [CSNEXTACN],
         [CSDATENEXTACN_DATE],
         [CSDATEISSUE_DATE],
         [CSDATERETURN_DATE],
         [CSDATEMOTION_DATE],
         [CSDATEHEARING_DATE],
         [CSDATEJDGT_DATE],
         [CSDATEEXCHG_DATE],
         [CSDATECLOSE_DATE],
         [CSDATECOMPLETION_DATE],
         [CSDATEREGIST_DATE],
         [CSDATEACCEPT_DATE],
         [CSDATESOL_DATE],
         [CSDATEFIRSTINV_DATE],
         [CSDATELASTINV_DATE],
         [CSDATELASTPAY_DATE],
         [CSFIRSTINVAMT],
         [CSORGDEBT],
         [CSBALDEBT],
         [CSBALCLIENT],
         [CSCOSTISSUE],
         [CSCOSTJDGT],
         [CSCOSTENF],
         [CSFEEISSUE],
         [CSFEEJDGT],
         [CSFEEENF],
         [CSINTISSUE],
         [CSINTJDGT],
         [CSINTENF],
         [CSCLAUSE1],
         [CSCLAUSE2],
         [CSCLAUSE3],
         [CSCLAUSE4],
         [CSCLAUSE5],
         [CSCLAUSE6],
         [CSCLAUSE7],
         [CSCLAUSE8],
         [CSCLAUSE9],
         [CSCLAUSE10],
         [CSSOLREF],
         [CSFILENUM],
         [FileNum])
  SELECT [CSCODE]                                      AS [CSCODE],
         [CSCLIENTCDE]                                 AS [CSCLIENTCDE],
         [CSFNR]                                       AS [CSFNR],
         [CSDESC]                                      AS [CSDESC],
         [CSINSTYN]                                    AS [CSINSTYN],
         [CSDATEENTERED]                               AS [CSDATEENTERED],
         [CSPLAINTNO]                                  AS [CSPLAINTNO],
         [CSWARRANT]                                   AS [CSWARRANT],
         [CSCLEXEYN]                                   AS [CSCLEXEYN],
         [CSCLBENYN]                                   AS [CSCLBENYN],
         [CSLEDGREF]                                   AS [CSLEDGREF],
         [CSPFILEREF]                                  AS [CSPFILEREF],
         [CSWKTCODE]                                   AS [CSWKTCODE],
         [CSCRTCODE]                                   AS [CSCRTCODE],
         [CSAGYCODE]                                   AS [CSAGYCODE],
         [CSGENAGYCODE]                                AS [CSGENAGYCODE],
         [CSBSCCODE]                                   AS [CSBSCCODE],
         [CSBNKCODE]                                   AS [CSBNKCODE],
         [CSLRGCODE]                                   AS [CSLRGCODE],
         [CSDRGCODE]                                   AS [CSDRGCODE],
         [CSHCTCODE]                                   AS [CSHCTCODE],
         [CSSHFCODE]                                   AS [CSSHFCODE],
         [CSLAUTHCODE]                                 AS [CSLAUTHCODE],
         [CSIRTCODE]                                   AS [CSIRTCODE],
         [CSSTACODE]                                   AS [CSSTACODE],
         [CSISSFEECODE]                                AS [CSISSFEECODE],
         [CSJUDFEECODE]                                AS [CSJUDFEECODE],
         [CSINTADD]                                    AS [CSINTADD],
         [CSCOMPTIME]                                  AS [CSCOMPTIME],
         [CSCOMPUNIT]                                  AS [CSCOMPUNIT],
         [CSCONTRATE]                                  AS [CSCONTRATE],
         [CSDAILYRATE]                                 AS [CSDAILYRATE],
         [CSNODTR]                                     AS [CSNODTR],
         [CSNOGTR]                                     AS [CSNOGTR],
         [CSNOPTF]                                     AS [CSNOPTF],
         [CSNOPLC]                                     AS [CSNOPLC],
         [CSNODOC]                                     AS [CSNODOC],
         [CSNOSPL]                                     AS [CSNOSPL],
         [CSNOBEN]                                     AS [CSNOBEN],
         [CSPREMISES]                                  AS [CSPREMISES],
         [CSSOLTRDNAME]                                AS [CSSOLTRDNAME],
         [CSSOLTRDADDR]                                AS [CSSOLTRDADDR],
         [CSSOLTEL]                                    AS [CSSOLTEL],
         [CSSOLFAX]                                    AS [CSSOLFAX],
         [CSSOLSALUTE]                                 AS [CSSOLSALUTE],
         [CSSOLDXNO]                                   AS [CSSOLDXNO],
         [CSSOLEMAIL]                                  AS [CSSOLEMAIL],
         [CSDATESERVICE]                               AS [CSDATESERVICE_DATE],
         [CSDATEINSTSTART]                             AS [CSDATEINSTSTART_DATE],
         [CSINSTTOTAL]                                 AS [CSINSTTOTAL],
         [CSINSTAMT]                                   AS [CSINSTAMT],
         [CSINSTFREQ]                                  AS [CSINSTFREQ],
         [CSINSTUNIT]                                  AS [CSINSTUNIT],
         [CSLASTACN]                                   AS [CSLASTACN],
         [CSDATELASTACN]                               AS [CSDATELASTACN_DATE],
         [CSNEXTACN]                                   AS [CSNEXTACN],
         [CSDATENEXTACN]                               AS [CSDATENEXTACN_DATE],
         [CSDATEISSUE]                                 AS [CSDATEISSUE_DATE],
         [CSDATERETURN]                                AS [CSDATERETURN_DATE],
         [CSDATEMOTION]                                AS [CSDATEMOTION_DATE],
         [CSDATEHEARING]                               AS [CSDATEHEARING_DATE],
         [CSDATEJDGT]                                  AS [CSDATEJDGT_DATE],
         [CSDATEEXCHG]                                 AS [CSDATEEXCHG_DATE],
         [CSDATECLOSE]                                 AS [CSDATECLOSE_DATE],
         [CSDATECOMPLETION]                            AS [CSDATECOMPLETION_DATE],
         [CSDATEREGIST]                                AS [CSDATEREGIST_DATE],
         [CSDATEACCEPT]                                AS [CSDATEACCEPT_DATE],
         [CSDATESOL]                                   AS [CSDATESOL_DATE],
         [CSDATEFIRSTINV]                              AS [CSDATEFIRSTINV_DATE],
         [CSDATELASTINV]                               AS [CSDATELASTINV_DATE],
         [CSDATELASTPAY]                               AS [CSDATELASTPAY_DATE],
         [CSFIRSTINVAMT]                               AS [CSFIRSTINVAMT],
         [CSORGDEBT]                                   AS [CSORGDEBT],
         [CSBALDEBT]                                   AS [CSBALDEBT],
         [CSBALCLIENT]                                 AS [CSBALCLIENT],
         [CSCOSTISSUE]                                 AS [CSCOSTISSUE],
         [CSCOSTJDGT]                                  AS [CSCOSTJDGT],
         [CSCOSTENF]                                   AS [CSCOSTENF],
         [CSFEEISSUE]                                  AS [CSFEEISSUE],
         [CSFEEJDGT]                                   AS [CSFEEJDGT],
         [CSFEEENF]                                    AS [CSFEEENF],
         [CSINTISSUE]                                  AS [CSINTISSUE],
         [CSINTJDGT]                                   AS [CSINTJDGT],
         CONVERT(datetime, [CSINTENF])                 AS [CSINTENF],
         [CSCLAUSE1]                                   AS [CSCLAUSE1],
         [CSCLAUSE2]                                   AS [CSCLAUSE2],
         [CSCLAUSE3]                                   AS [CSCLAUSE3],
         [CSCLAUSE4]                                   AS [CSCLAUSE4],
         [CSCLAUSE5]                                   AS [CSCLAUSE5],
         [CSCLAUSE6]                                   AS [CSCLAUSE6],
         [CSCLAUSE7]                                   AS [CSCLAUSE7],
         [CSCLAUSE8]                                   AS [CSCLAUSE8],
         [CSCLAUSE9]                                   AS [CSCLAUSE9],
         [CSCLAUSE10]                                  AS [CSCLAUSE10],
         [CSSOLREF]                                    AS [CSSOLREF],
         [CSFILENUM]                                   AS [CSFILENUM],
         [FILENUM]                                     AS [FileNum]
    FROM [dbo].[CaseMasterClosed]
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy CaseMasterClosed to CaseMaster',
           'Success')
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO
      
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy CaseMasterClosed to CaseMaster',
           'Failure')
END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [REPORTMODE] = 0
      AND [STOP] = 0)
BEGIN
BEGIN TRY
  INSERT
    INTO [dbo].[CaseUDFAnswers]
        ([CASECODE],
         [UDFFILE],
         [UDFNAME],
         [SEQNO],
         [DateValue],
         [NumberValue],
         [ForCopy],
         [TEXT1])
  SELECT RTRIM(ISNULL(TO1.[CASECODE], ''))              AS [CASECODE],
         RTRIM(ISNULL(TO1.[UDFFILE], ''))               AS [UDFFILE],
         RTRIM(ISNULL(TO1.[UDFNAME], ''))               AS [UDFNAME],
         CONVERT(int, TO1.[SEQNO])                      AS [SEQNO],
         TO1.[DateValue]                                AS [DateValue],
         CONVERT(decimal(20, 2), TO1.[NumberValue])     AS [NumberValue],
         NULL                                           AS [ForCopy],
         CONVERT(VARCHAR(MAX), TO1.[TEXT1])             AS [TEXT1]
    FROM (select distinct
               [casecode],
               [udffile],
               [udfname]
          from [dbo].[ClosedCaseUDFAnswers]) UGH
          CROSS APPLY (  SELECT TOP 1 *
                           FROM [dbo].[ClosedCaseUDFAnswers] CUF
                          WHERE CUF.[CASECODE] = UGH.[CASECODE]
                            AND CUF.[UDFFILE] = UGH.[UDFFILE]
                            AND CUF.[UDFNAME] = UGH.[UDFNAME]
                       ORDER BY CUF.[SEQNO] DESC) TO1
         INNER JOIN [dbo].[matters] MAT
                 ON MAT.[code] = TO1.[CASECODE]
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy ClosedCaseUDFAnswers to CaseUDFAnswers',
           'Success')
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy ClosedCaseUDFAnswers to CaseUDFAnswers',
           'Failure')
END CATCH
END

DECLARE @DUPDIA TABLE
       ([id]                  INT      IDENTITY(1, 1),
        [ActionID]            INT,
        [NewActionID]         INT)

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [REPORTMODE] = 0
      AND [STOP] = 0)
BEGIN
BEGIN TRY
  INSERT
    INTO @DUPDIA
        ([ActionID])
  SELECT [cls].[ActionID]
    FROM [dbo].[CloseDiary] cls
         INNER JOIN [dbo].[diary] DIA
                 ON DIA.[actionid] = CLS.[actionid]
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Determine Duplicate Action IDs',
           'Success')
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Could not determine Duplicate Action IDs',
           'Failure')
END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [REPORTMODE] = 0
      AND [STOP] = 0)
BEGIN
BEGIN TRY
  UPDATE DUP
     SET DUP.[NewActionID] = D.[NewActionID] + DUP.[id]
    FROM @DUPDIA DUP
         CROSS APPLY (SELECT ISNULL(MAX(DIA.[ActionID]), 0) + 1 AS [NewActionID]
                        FROM [dbo].[diary] DIA) D
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Determine Replacement Action IDs',
           'Success')
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Could not determine Replacement Action IDs',
           'Failure')
END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [REPORTMODE] = 0
      AND [STOP] = 0)
BEGIN
BEGIN TRY
INSERT
  INTO [dbo].[diary]
      ([ACTIONID],
       [CASECODE],
       [PRIORITY],
       [HIGHLIGHTED],
       [PUBLISH],
       [DATE],
       [STATUS],
       [ACTIONCODE],
       [ACTIONSTATUS],
       [ACTIONTYPE],
       [PROCESSTYPE],
       [FNCODE],
       [TEAMCODE],
       [TEXT1],
       [TEXT2],
       [DELEGATEDFNR],
       [DELEGATEDDATE],
       [DELEGATEDBACKDATE],
       [DEFERRED],
       [DUEDATE],
       [IMAGENO],
       [DYSTARTTIME],
       [DYENDTIME],
       [DURATION],
       [ORGINALACTIONID],
       [MILESTEONETYPE],
       [ATTACHMENTS],
       [PROCESSSTATUS],
       [WORKPROCESS],
       [BILLABLE],
       [BILLDESCRIPTION],
       [EMAILADDRESS],
       [EMAIL],
       [SUBJECT],
       [ADDRESSTO],
       [DELEGATIONSTATUS],
       [DRAFTBILLNO],
       [CHEQUEREQNO],
       [TxmSent],
       [Location],
       [HearingType],
       [ForCopy],
       [TxmDate],
       [TxmSeqNo],
       [DisplayText],
       [Flag],
       [CCTo],
       [BCCTo],
       [ClientContactID],
       [ContactCategory],
       [Campaign],
       [FileNum])
SELECT ISNULL(DUP.[NewActionID], CLS.[ActionID])                                                   AS [ActionID],
       RTRIM(ISNULL(CLS.[CASECODE], ''))                                                           AS [CASECODE],
       CASE WHEN RTRIM(ISNULL(CLS.[PRIORITY], 'N')) IN ('H', 'L', 'N')
                 THEN RTRIM(ISNULL(CLS.[PRIORITY], 'N'))
            ELSE 'N' END                                                                           AS [PRIORITY],
       CASE WHEN RTRIM(ISNULL(CLS.[HIGHLIGHTED], 'N')) IN ('Y', 'N')
                 THEN RTRIM(ISNULL(CLS.[HIGHLIGHTED], 'N'))
            ELSE 'N' END                                                                           AS [HIGHLIGHTED],
       CASE WHEN RTRIM(ISNULL(CLS.[PUBLISH], 'N')) IN ('A', 'P', 'N')
                 THEN RTRIM(ISNULL(CLS.[PUBLISH], 'N'))
            ELSE 'N' END                                                                           AS [PUBLISH],
       CLS.[DATE]                                                                                  AS [DATE],
       CLS.[STATUS]                                                                                AS [STATUS],
       RTRIM(CLS.[ACTIONCODE])                                                                     AS [ACTIONCODE],
       RTRIM(CLS.[ACTIONSTATUS])                                                                   AS [ACTIONSTATUS],
       RTRIM(CLS.[ACTIONTYPE])                                                                     AS [ACTIONTYPE],
       RTRIM(CLS.[PROCESSTYPE])                                                                    AS [PROCESSTYPE],
       RTRIM(CLS.[FNCODE])                                                                         AS [FNCODE],
       RTRIM(CLS.[TEAMCODE])                                                                       AS [TEAMCODE],
       ISNULL(CONVERT(VARCHAR(MAX), CLS.[TEXT1]), '')                                              AS [TEXT1],
       ISNULL(CONVERT(VARCHAR(MAX), CLS.[TEXT2]), '')                                              AS [TEXT2],
       RTRIM(CLS.[DELEGATEDFNR])                                                                   AS [DELEGATEDFNR],
       CLS.[DELEGATEDDATE]                                                                         AS [DELEGATEDDATE],
       CLS.[DELEGATEDBACKDATE]                                                                     AS [DELEGATEDBACKDATE],
       RTRIM(CLS.[DEFERRED])                                                                       AS [DEFERRED],
       CLS.[DUEDATE]                                                                               AS [DUEDATE],
       CLS.[IMAGENO]                                                                               AS [IMAGENO],
       RTRIM(CLS.[DYSTARTTIME])                                                                    AS [DYSTARTTIME],
       RTRIM(CLS.[DYENDTIME])                                                                      AS [DYENDTIME],
       CLS.[DURATION]                                                                              AS [DURATION],
       ISNULL(CLS.[ORIGINALACTIONID], 0)                                                           AS [ORIGINALACTIONID],
       RTRIM(CLS.[MILESTEONETYPE])                                                                 AS [MILESTEONETYPE],
       RTRIM(CLS.[ATTACHMENTS])                                                                    AS [ATTACHMENTS],
       CLS.[PROCESSSTATUS]                                                                         AS [PROCESSSTATUS],
       CLS.[WORKPROCESS]                                                                           AS [WORKPROCESS],
       CLS.[BILLABLE]                                                                              AS [BILLABLE],
       RTRIM(CLS.[BILLDESCRIPTION])                                                                AS [BILLDESCRIPTION],
       RTRIM(CLS.[EMAILADDRESS])                                                                   AS [EMAILADDRESS],
       RTRIM(CLS.[EMAIL])                                                                          AS [EMAIL],
       RTRIM(CONVERT(VARCHAR(500), CLS.[SUBJECT]))                                                 AS [SUBJECT],
       RTRIM(CONVERT(VARCHAR(2000), CLS.[ADDRESSTO]))                                              AS [ADDRESSTO],
       ISNULL(CONVERT(INT, CLS.[DELEGATIONSTATUS]), 0)                                             AS [DELEGATIONSTATUS],
       CLS.[DRAFTBILLNO]                                                                           AS [DRAFTBILLNO],
       CLS.[CHEQUEREQNO]                                                                           AS [CHEQUEREQNO],
       0                                                                                           AS [TxmSent],
       RTRIM(CLS.[Location])                                                                       AS [Location],
       RTRIM(CLS.[HearingType])                                                                    AS [HearingType],
       NULL                                                                                        AS [ForCopy],
       CLS.[TxmDate]                                                                               AS [TxmDate],
       CLS.[TxmSeqNo]                                                                              AS [TxmSeqNo],
       [dbo].[ky_RemoveSpuriousWhitespace2](ISNULL(CONVERT(VARCHAR(MAX), CLS.[TEXT1]), ''), 200)   AS [DisplayText],
       NULL                                                                                        AS [Flag],
       NULL                                                                                        AS [CCTo],
       NULL                                                                                        AS [BCCTo],
       NULL                                                                                        AS [ClientContactID],
       NULL                                                                                        AS [ContactCategory],
       NULL                                                                                        AS [Campaign],
       ISNULL(CLS.[FileNum], 0)                                                                    AS [FileNum]
  FROM [dbo].[CloseDiary] CLS
       LEFT OUTER JOIN @DUPDIA DUP
                    ON DUP.[ActionID] = CLS.[ACTIONID]
       INNER JOIN [dbo].[matters] MAT
               ON MAT.[Code] = CLS.[CASECODE]
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy CloseDiary to Diary',
           'Success')
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy CloseDiary to Diary',
           'Failure')
END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [REPORTMODE] = 0
      AND [STOP] = 0)
BEGIN
BEGIN TRY
INSERT
  INTO [dbo].[DiaryAttachments]
      ([DIARYID],
       [TRACKREFERENCE],
       [CASECODE],
       [NAME],
       [DOCUMENT],
       [FILEPATH],
       [TYPE],
       [DOCCLASS],
       [DATEENTERED],
       [ENTEREDBY],
       [LASTACCESSDATE],
       [LASTACCESSBY],
       [SYSTEM],
       [DICTATIONFILE],
       [SOURCE],
       [Fees],
       [Outlay],
       [ARCHPATH])
SELECT ISNULL(DUP.[NewActionID], CLS.[DIARYID])                                                   AS [DIARYID],
       CLS.[TRACKREFERENCE]                                                                       AS [TRACKREFERENCE],
       RTRIM(ISNULL(CLS.[CASECODE], ''))                                                          AS [CASECODE],
       RTRIM(ISNULL(CLS.[NAME], ''))                                                              AS [NAME],
       RTRIM(ISNULL(CLS.[DOCUMENT], ''))                                                          AS [DOCUMENT],
       RTRIM(ISNULL(CLS.[FILEPATH], ''))                                                          AS [FILEPATH],
       RTRIM(ISNULL(CLS.[TYPE], ''))                                                              AS [TYPE],
       RTRIM(ISNULL(CLS.[DOCCLASS], ''))                                                          AS [DOCCLASS],
       CLS.[DATEENTERED]                                                                          AS [DATEENTERED],
       RTRIM(ISNULL(CLS.[ENTEREDBY], ''))                                                         AS [ENTEREDBY],
       CLS.[LASTACCESSDATE]                                                                       AS [LASTACCESSDATE],
       RTRIM(ISNULL(CLS.[LASTACCESSBY], ''))                                                      AS [LASTACCESSBY],
       CLS.[SYSTEM]                                                                               AS [SYSTEM],
       RTRIM(CLS.[DICTATIONFILE])                                                                 AS [DICTATIONFILE],
       ''                                                                                         AS [SOURCE],
       0                                                                                          AS [Fees],
       0                                                                                          AS [Outlay],
       RTRIM(ISNULL(CLS.[ARCHPATH], ''))                                                          AS [ARCHPATH]
  FROM [dbo].[CloseDiaryAttachments] CLS
       LEFT OUTER JOIN @DUPDIA DUP
                    ON DUP.[ActionID] = CLS.[DIARYID]
       INNER JOIN [dbo].[matters] MAT
               ON MAT.[Code] = CLS.[CASECODE]
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy CloseDiaryAttachments to DiaryAttachments',
           'Success')
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy CloseDiaryAttachments to DiaryAttachments',
           'Failure')
END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [REPORTMODE] = 0
      AND [STOP] = 0)
BEGIN
BEGIN TRY
SET IDENTITY_INSERT [dbo].[MatterLedger] ON

INSERT
  INTO [dbo].[MatterLedger]
      ([MATTER],
       [BATCHNO],
       [PREF],
       [DATE],
       [REF],
       [NARR],
       [VALUED],
       [VALUEO],
       [VALUEC],
       [FEE],
       [PER],
       [YEAR],
       [ENTRYDATE],
       [CLIENT],
       [VALUECC],
       [VALUECD],
       [DEPOSITTYPE],
       [CURVALUED],
       [CURVALUEO],
       [CURVALUEC],
       [CURVALUECC],
       [CURVALUECD],
       [LREF],
       [BILLED],
       [BILLINGYEAR],
       [BILLINGPERIOD],
       [BILLINGDATE],
       [BilledOutlayValue],
       [InvoiceNo],
       [IncludeInBill])
SELECT RTRIM(ISNULL(ML.[MATTER], ''))                                                                            AS [MATTER],
       ISNULL(ML.[BATCHNO], 0)                                                                                   AS [BATCHNO],
       ML.[PREF]                                                                                                 AS [PREF],
       ISNULL(ML.[DATE], ISNULL(MAT.[started], CONVERT(DATETIME, '19000101')))                                   AS [DATE],
       RTRIM(ML.[REF])                                                                                           AS [REF],
       RTRIM(ML.[NARR])                                                                                          AS [NARR],
       ISNULL(ML.[VALUED], 0)                                                                                    AS [VALUED],
       ISNULL(ML.[VALUEO], 0)                                                                                    AS [VALUEO],
       ISNULL(ML.[VALUEC], 0)                                                                                    AS [VALUEC],
       RTRIM(ISNULL(ML.[FEE], ''))                                                                               AS [FEE],
       ML.[PER]                                                                                                  AS [PER],
       ML.[YEAR]                                                                                                 AS [YEAR],
       ML.[ENTRYDATE]                                                                                            AS [ENTRYDATE],
       RTRIM(ISNULL(ML.[CLIENT], ''))                                                                            AS [CLIENT],
       ISNULL(ML.[VALUECC], 0)                                                                                   AS [VALUECC],
       ISNULL(ML.[VALUECD], 0)                                                                                   AS [VALUECD],
       ML.[DEPOSITTYPE]                                                                                          AS [DEPOSITTYPE],
       ISNULL(ML.[CURVALUED], 0)                                                                                 AS [CURVALUED],
       ISNULL(ML.[CURVALUEO], 0)                                                                                 AS [CURVALUEO],
       ISNULL(ML.[CURVALUEC], 0)                                                                                 AS [CURVALUEC],
       ISNULL(ML.[CURVALUECC], 0)                                                                                AS [CURVALUECC],
       ISNULL(ML.[CURVALUECD], 0)                                                                                AS [CURVALUECD],
       ML.[LREF]                                                                                                 AS [LREF],
       ML.[BILLED]                                                                                               AS [BILLED],
       NULL                                                                                                      AS [BILLINGYEAR],
       NULL                                                                                                      AS [BILLINGPERIOD],
       NULL                                                                                                      AS [BILLINGDATE],
       ISNULL(ML.[VALUEO], 0)                                                                                    AS [BilledOutlayValue],
       NULL                                                                                                      AS [InvoiceNo],
       2                                                                                                         AS [IncludeInBill]
  FROM [dbo].[ClosedMatterLedger] ML
       INNER JOIN [dbo].[matters] MAT
               ON MAT.[CODE] = ML.[MATTER]
 WHERE ISNULL(ML.[LREF], 0) <> 0

  SET IDENTITY_INSERT [dbo].[MatterLedger] OFF

  DECLARE @maxVal INT
  SELECT @maxVal = ISNULL(max(LREF),0)+1 from [dbo].[MatterLedger]
  DBCC CHECKIDENT('MatterLedger', RESEED, @maxVal)

INSERT
  INTO [dbo].[MatterLedger]
      ([MATTER],
       [BATCHNO],
       [PREF],
       [DATE],
       [REF],
       [NARR],
       [VALUED],
       [VALUEO],
       [VALUEC],
       [FEE],
       [PER],
       [YEAR],
       [ENTRYDATE],
       [CLIENT],
       [VALUECC],
       [VALUECD],
       [DEPOSITTYPE],
       [CURVALUED],
       [CURVALUEO],
       [CURVALUEC],
       [CURVALUECC],
       [CURVALUECD],
       [BILLED],
       [BILLINGYEAR],
       [BILLINGPERIOD],
       [BILLINGDATE],
       [BilledOutlayValue],
       [InvoiceNo],
       [IncludeInBill])
SELECT RTRIM(ISNULL(ML.[MATTER], ''))                                                                            AS [MATTER],
       ISNULL(ML.[BATCHNO], 0)                                                                                   AS [BATCHNO],
       ML.[PREF]                                                                                                 AS [PREF],
       ISNULL(ML.[DATE], ISNULL(MAT.[started], CONVERT(DATETIME, '19000101')))                                   AS [DATE],
       RTRIM(ML.[REF])                                                                                           AS [REF],
       RTRIM(ML.[NARR])                                                                                          AS [NARR],
       ISNULL(ML.[VALUED], 0)                                                                                    AS [VALUED],
       ISNULL(ML.[VALUEO], 0)                                                                                    AS [VALUEO],
       ISNULL(ML.[VALUEC], 0)                                                                                    AS [VALUEC],
       RTRIM(ISNULL(ML.[FEE], ''))                                                                               AS [FEE],
       ML.[PER]                                                                                                  AS [PER],
       ML.[YEAR]                                                                                                 AS [YEAR],
       ML.[ENTRYDATE]                                                                                            AS [ENTRYDATE],
       RTRIM(ISNULL(ML.[CLIENT], ''))                                                                            AS [CLIENT],
       ISNULL(ML.[VALUECC], 0)                                                                                   AS [VALUECC],
       ISNULL(ML.[VALUECD], 0)                                                                                   AS [VALUECD],
       ML.[DEPOSITTYPE]                                                                                          AS [DEPOSITTYPE],
       ISNULL(ML.[CURVALUED], 0)                                                                                 AS [CURVALUED],
       ISNULL(ML.[CURVALUEO], 0)                                                                                 AS [CURVALUEO],
       ISNULL(ML.[CURVALUEC], 0)                                                                                 AS [CURVALUEC],
       ISNULL(ML.[CURVALUECC], 0)                                                                                AS [CURVALUECC],
       ISNULL(ML.[CURVALUECD], 0)                                                                                AS [CURVALUECD],
       ML.[BILLED]                                                                                               AS [BILLED],
       NULL                                                                                                      AS [BILLINGYEAR],
       NULL                                                                                                      AS [BILLINGPERIOD],
       NULL                                                                                                      AS [BILLINGDATE],
       0                                                                                                         AS [BilledOutlayValue],
       NULL                                                                                                      AS [InvoiceNo],
       NULL                                                                                                      AS [IncludeInBill]
  FROM [dbo].[ClosedMatterLedger] ML
       INNER JOIN [dbo].[matters] MAT
               ON MAT.[CODE] = ML.[MATTER]
 WHERE ISNULL(ML.[LREF], 0) = 0
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy ClosedMatterLedger to MatterLedger',
           'Success')
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy ClosedMatterLedger to MatterLedger',
           'Failure')
END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [REPORTMODE] = 0
      AND [STOP] = 0)
BEGIN
BEGIN TRY
INSERT
  INTO [dbo].[CaseContacts]
      ([CASECODE],
       [CONTYPE],
       [CONNUM],
       [NAMECODE],
       [SOLCODE],
       [SOLREF],
       [INSCODE],
       [INSREF],
       [COURTRECORDNO],
       [HEARINGDATE],
       [TIME],
       [Relationship],
       [Nominated],
       [AssignedContact],
       [ForCopy],
       [CivilSumSDate],
       [CivilBillSDate],
       [SSumSDate],
       [InstOrdSDate],
       [ComOrdSDate],
       [WarrantIssDate],
       [SumAttSerDate],
       [FileNum])
SELECT RTRIM(ISNULL(CLS.[CASECODE], ''))                                                          AS [CASECODE],
       RTRIM(ISNULL(CLS.[CONTYPE], ''))                                                           AS [CONTYPE],
       ISNULL(CLS.[CONNUM], 0)                                                                    AS [CONNUM],
       RTRIM(CLS.[NAMECODE])                                                                      AS [NAMECODE],
       RTRIM(CLS.[SOLCODE])                                                                       AS [SOLCODE],
       RTRIM(CLS.[SOLREF])                                                                        AS [SOLREF],
       RTRIM(CLS.[INSCODE])                                                                       AS [INSCODE],
       RTRIM(CLS.[INSREF])                                                                        AS [INSREF],
       RTRIM(CLS.[COURTRECORDNO])                                                                 AS [COURTRECORDNO],
       CLS.[HEARINGDATE]                                                                          AS [HEARINGDATE],
       RTRIM(CLS.[TIME])                                                                          AS [TIME],
       NULL                                                                                       AS [Relationship],
       NULL                                                                                       AS [Nominated],
       NULL                                                                                       AS [AssignedContact],
       NULL                                                                                       AS [ForCopy],
       NULL                                                                                       AS [CivilSumSDate],
       NULL                                                                                       AS [CivilBillSDate],
       NULL                                                                                       AS [SSumSDate],
       NULL                                                                                       AS [InstOrdSDate],
       NULL                                                                                       AS [ComOrdSDate],
       NULL                                                                                       AS [WarrantIssDate],
       NULL                                                                                       AS [SumAttSerDate],
       ISNULL(CLS.[FILENUM], 0)                                                                   AS [FileNum]
  FROM [dbo].[CloseCaseContacts] CLS
       INNER JOIN [dbo].[matters] MAT
               ON MAT.[Code] = CLS.[CASECODE]
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy CloseCaseContacts to CaseContacts',
           'Success')
END TRY
BEGIN CATCH
  UPDATE CLO
      SET CLO.[STOP] = 1
    FROM [dbo].[MoveClosed] CLO
    
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Copy CloseDiaryAttachments to DiaryAttachments',
           'Failure')
END CATCH
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [ROLLBACK] = 1
       OR [STOP] = 1
       OR [REPORTMODE] = 1)
BEGIN
  IF EXISTS
    (SELECT *
       FROM [dbo].[MoveClosed]
      WHERE [STOP] = 1)
    BEGIN
      INSERT
        INTO @LOG
            ([ACTION],
             [RESULT])
      VALUES('Rollback due to errors. See preceding errors',
             'Failure')
    END
  ELSE
    BEGIN
      IF EXISTS
        (SELECT *
           FROM [dbo].[MoveClosed]
          WHERE [REPORTMODE] = 1)
        BEGIN
          INSERT
            INTO @LOG
                ([ACTION],
                 [RESULT])
          VALUES('Report run complete. Rollback.',
                 'Success')
        END
      ELSE
        BEGIN
          INSERT
            INTO @LOG
                ([ACTION],
                 [RESULT])
          VALUES('TEST run complete. Rollback.',
                 'Success')
        END
    END
  ROLLBACK
END
ELSE
  BEGIN
    INSERT
      INTO @LOG
          ([ACTION],
           [RESULT])
    VALUES('Process has completed. Changes have been committed.',
           'Success')

    COMMIT
  END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 2)
BEGIN
    SELECT * FROM @LOG
END

IF NOT EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 2)
BEGIN
SELECT RST.[ClientCode] AS [ClientCode],
       RST.[Code] AS [OriginalMatterCode],
       RST.[RestoredToCode] AS [RestoredToCode] 
  INTO [Keyhouse_MoveClosed].[dbo].[RestoredMatters]
  FROM @RESTOREDMATTERS RST

SELECT DUP.[ClientCode] AS [ClientCode],
       DUP.[Code] AS [OriginalMatterCode],
       DUP.[NextMatterNo] AS [NewMatterCode] 
  INTO [Keyhouse_MoveClosed].[dbo].[MatterRenumberingLog]
  FROM @DUPS DUP

SELECT *
  INTO [Keyhouse_MoveClosed].[dbo].[ExecutionLog]
  FROM @LOG
END

IF EXISTS
  (SELECT *
     FROM [dbo].[MoveClosed]
    WHERE [STOP] = 0
      AND [ROLLBACK] = 0
      AND [REPORTMODE] = 0)
BEGIN
  SELECT *
    INTO [Keyhouse_MoveClosed].[dbo].[ClosedMatterLedger]
    FROM [dbo].[ClosedMatterLedger]

  TRUNCATE TABLE [dbo].[ClosedMatterLedger]

  SELECT *
    INTO [Keyhouse_MoveClosed].[dbo].[CloseDiaryAttachments]
    FROM [dbo].[CloseDiaryAttachments]

  TRUNCATE TABLE [dbo].[CloseDiaryAttachments]

  SELECT *
    INTO [Keyhouse_MoveClosed].[dbo].[CloseDiary]
    FROM [dbo].[CloseDiary]

  TRUNCATE TABLE [dbo].[CloseDiary]

  SELECT *
    INTO [Keyhouse_MoveClosed].[dbo].[ClosedCaseUDFAnswers]
    FROM [dbo].[ClosedCaseUDFAnswers]

  TRUNCATE TABLE [dbo].[ClosedCaseUDFAnswers]

  SELECT *
    INTO [Keyhouse_MoveClosed].[dbo].[CloseCaseContacts]
    FROM [dbo].[CloseCaseContacts]

  TRUNCATE TABLE [dbo].[CloseCaseContacts]

  SELECT *
    INTO [Keyhouse_MoveClosed].[dbo].[CaseMasterClosed]
    FROM [dbo].[CaseMasterClosed]

  TRUNCATE TABLE [dbo].[CaseMasterClosed]

  SELECT *
    INTO [Keyhouse_MoveClosed].[dbo].[Closed]
    FROM [dbo].[Closed]

  TRUNCATE TABLE [dbo].[Closed]
END

DROP TABLE [dbo].[MoveClosed]

/* Change OpenClosedMatters and MatterLedger Views based on the current state of [dbo].[closed] */
IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'OpenClosedMatters'
      AND SO.[type] = 'V')
BEGIN
  DROP VIEW [dbo].[OpenClosedMatters]
END
GO

-- Function is obsolete. Delete here, do not re-create.
IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'ky_NETOpenClosedMatters'
      AND SO.[type] = 'TF')
BEGIN
  DROP FUNCTION [dbo].[ky_NETOpenClosedMatters]
END
GO

DECLARE @COMMAND VARCHAR(MAX)

IF EXISTS
  (SELECT *
     FROM [dbo].[closed])
  BEGIN
    -- there are still "closed" matters in [dbo].[closed]. Use the old views.
    SET @COMMAND = 'CREATE VIEW [dbo].[OpenClosedMatters]
AS

SELECT     TOP (100) PERCENT 
LTRIM(ISNULL(dbo.matters.Code, '''') + ISNULL(dbo.Closed.CODE, '''')) AS Code,
LTRIM(ISNULL(dbo.matters.ClientCode, '''')      + ISNULL(dbo.Closed.CLIENTCODE, '''')) AS ClientCode,
(SELECT TOP 1 name FROM contacts 
where CODE = (LTRIM(ISNULL(dbo.matters.ClientCode,'''')+ISNULL(dbo.Closed.CLIENTCODE,'''')))
ORDER BY code ) AS ClientName,
LTRIM(ISNULL(dbo.matters.Matter, '''') + ISNULL(dbo.Closed.MATTER, '''')) AS Matter, 
LTRIM(ISNULL(dbo.matters.Description, '''') + ISNULL(dbo.Closed.DESCRIPTION, '''')) AS Description,
LTRIM(ISNULL(dbo.matters.Dept, '''') + ISNULL(dbo.Closed.DEPT, ''''))  AS Dept, 
LTRIM(ISNULL(dbo.matters.FECode, '''') + ISNULL(dbo.Closed.FECODE, '''')) AS Fecode,
LTRIM(ISNULL(dbo.matters.Started, '''') + ISNULL(dbo.Closed.STARTED,'''')) AS Started, 
ISNULL(dbo.matters.Started, '''') + ISNULL(dbo.Closed.STARTED ,'''') AS StartDate, 
LTRIM(ISNULL(dbo.matters.WType, '''') + ISNULL(dbo.Closed.WTYPE, '''')) AS Wtype, 
LTRIM(ISNULL(dbo.matters.User1, '''') + ISNULL(dbo.Closed.USER1, '''')) AS User1, 
LTRIM(ISNULL(dbo.matters.User2, '''') + ISNULL(dbo.Closed.USER2, '''')) AS User2,
LTRIM(ISNULL(dbo.matters.User3, '''') + ISNULL(dbo.Closed.USER3, '''')) AS User3,
LTRIM(ISNULL(dbo.matters.OldRef, '''') + ISNULL(dbo.Closed.OLDREF, '''')) AS OldRef, 
LTRIM(ISNULL(dbo.matters.FileNum, '''') + ISNULL(dbo.Closed.FILENUM,  '''')) AS FileNum,
dbo.matters.Code AS OpenCode, 
dbo.Closed.CODE AS ClosedCode,
(CASE WHEN dbo.matters.Code IS NULL THEN ''Closed''
ELSE (CASE WHEN dbo.closed.Code IS NULL THEN ''Open''
ELSE ''Both'' END)END) As OpenClosed 
FROM         dbo.matters FULL OUTER JOIN
                      dbo.Closed ON dbo.matters.Code = dbo.Closed.CODE
ORDER BY LTRIM(ISNULL(dbo.matters.Code, '''') + ISNULL(dbo.Closed.CODE, ''''))'

    EXEC (@COMMAND)
  END
ELSE
  BEGIN
    -- no more "closed" matters in [dbo].[closed]. Use the new views!
    SET @COMMAND = '    CREATE VIEW
           [dbo].[OpenClosedMatters]
      WITH SCHEMABINDING
    AS
      SELECT RTRIM(ISNULL(MAT.[Code], ''''))                                               AS [Code] ,
             RTRIM(ISNULL(MAT.[ClientCode], ''''))                                         AS [ClientCode],
             RTRIM(ISNULL(CON.[Name], ''''))                                               AS [ClientName],
             RTRIM(ISNULL(MAT.[matter], ''''))                                             AS [Matter], 
             RTRIM(ISNULL(MAT.[Description], ''''))                                        AS [Description],
             RTRIM(ISNULL(MAT.[Dept], ''''))                                               AS [Dept], 
             RTRIM(ISNULL(MAT.[FECode], ''''))                                             AS [FECode],
             CASE SUBSTRING(CONVERT(VARCHAR,
                                      MAT.[Started],
                                      103), 4, 2)
                    WHEN ''01'' THEN ''Jan'' --Necessary to create an "unambiguous" conversion,
                    WHEN ''02'' THEN ''Feb'' --so that we can create indexes over this view
                    WHEN ''03'' THEN ''Mar''
                    WHEN ''04'' THEN ''Apr''
                    WHEN ''05'' THEN ''May''
                    WHEN ''06'' THEN ''Jun''
                    WHEN ''07'' THEN ''Jul''
                    WHEN ''08'' THEN ''Aug''
                    WHEN ''09'' THEN ''Sep''
                    WHEN ''10'' THEN ''Oct''
                    WHEN ''11'' THEN ''Nov''
                    WHEN ''12'' THEN ''Dec'' END
             + '' ''
             + CONVERT(VARCHAR(2),
                       CONVERT(INT,
                               SUBSTRING(CONVERT(VARCHAR,
                                                 MAT.[Started],
                                                 103), 1, 2)))
             + '' ''
             + SUBSTRING(CONVERT(VARCHAR,
                                 MAT.[Started],
                                 112), 1, 4)
             + ''12:00AM'' AS [Started],
             MAT.[Started]                                                               AS [StartDate], 
             RTRIM(ISNULL(MAT.[WType], ''''))                                              AS [WType], 
             RTRIM(ISNULL(MAT.[User1], ''''))                                              AS [User1], 
             RTRIM(ISNULL(MAT.[User2], ''''))                                              AS [User2], 
             RTRIM(ISNULL(MAT.[User3], ''''))                                              AS [User3], 
             RTRIM(ISNULL(MAT.[OldRef], ''''))                                             AS [OldRef], 
             ISNULL(MAT.[FileNum], 0)                                                    AS [FileNum], 
             CASE WHEN MAT.[Closed] <> ''Y'' THEN RTRIM(ISNULL(MAT.[Code], '''')) ELSE '''' END AS [OpenCode], 
             CASE WHEN MAT.[Closed] = ''Y'' THEN RTRIM(ISNULL(MAT.[Code], '''')) ELSE '''' END  AS [ClosedCode],
             CASE WHEN MAT.[Closed] <> ''Y'' THEN ''Open'' ELSE ''Closed'' END                  AS [OpenClosed]
        FROM [dbo].[matters] MAT
             INNER JOIN [dbo].[Contacts] CON
                     ON CON.[Code] = MAT.[CLIENTCODE]'
    
    EXEC (@COMMAND)

    SET @COMMAND = '    CREATE UNIQUE CLUSTERED INDEX
           [IX_Code]
        ON [dbo].[OpenClosedMatters]
          ([Code])'
    
    EXEC (@COMMAND)

    SET @COMMAND = '    CREATE UNIQUE NONCLUSTERED INDEX
           [IX_Client]
        ON [dbo].[OpenClosedMatters]
          ([ClientCode],
           [Code])'

    EXEC (@COMMAND)
  END
GO

IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'OpenClosedMatterLedger'
      AND SO.[type] = 'V')
BEGIN
  DROP VIEW [dbo].[OpenClosedMatterLedger]
END
GO

IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'OpenClosedMattersLedger'
      AND SO.[type] = 'V')
BEGIN
  DROP VIEW [dbo].[OpenClosedMattersLedger]
END
GO

-- Function is obsolete. Delete, don't recreate
IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'ky_NETOpenClosedMatterLedger'
      AND SO.[type] = 'TF')
BEGIN
  DROP FUNCTION [dbo].[ky_NETOpenClosedMatterLedger]
END
GO

DECLARE @COMMAND VARCHAR(MAX)

IF EXISTS
  (SELECT *
     FROM [dbo].[closed])
  BEGIN
    SET @COMMAND = 'CREATE VIEW [dbo].[OpenClosedMatterLedger]
AS
SELECT     ISNULL(dbo.MatterLedger.MATTER, dbo.ClosedMatterLedger.MATTER) AS Matter, ISNULL(dbo.MatterLedger.BATCHNO, 
                      dbo.ClosedMatterLedger.BATCHNO) AS BatchNo, ISNULL(dbo.MatterLedger.PREF, dbo.ClosedMatterLedger.PREF) AS Pref, 
                      ISNULL(dbo.MatterLedger.[DATE], dbo.ClosedMatterLedger.[DATE]) AS TransDate, ISNULL(dbo.MatterLedger.REF, dbo.ClosedMatterLedger.REF) 
                      AS Ref, ISNULL(dbo.MatterLedger.NARR, dbo.ClosedMatterLedger.NARR) AS Narr, ISNULL(dbo.MatterLedger.VALUED, 
                      dbo.ClosedMatterLedger.VALUED) AS ValueD, ISNULL(dbo.MatterLedger.VALUEO, dbo.ClosedMatterLedger.VALUEO) AS ValueO, 
                      ISNULL(dbo.MatterLedger.VALUEC, dbo.ClosedMatterLedger.VALUEC) AS ValueC, ISNULL(dbo.MatterLedger.FEE, dbo.ClosedMatterLedger.FEE) AS Fee, 
                      ISNULL(dbo.MatterLedger.PER, dbo.ClosedMatterLedger.PER) AS Per, ISNULL(dbo.MatterLedger.[YEAR], dbo.ClosedMatterLedger.[YEAR]) AS TranYear,
                       ISNULL(dbo.MatterLedger.ENTRYDATE, dbo.ClosedMatterLedger.ENTRYDATE) AS EntryDate, ISNULL(dbo.MatterLedger.CLIENT, 
                      dbo.ClosedMatterLedger.CLIENT) AS Client, ISNULL(dbo.MatterLedger.VALUECC, dbo.ClosedMatterLedger.VALUECC) AS ValueCC, 
                      ISNULL(dbo.MatterLedger.VALUECD, dbo.ClosedMatterLedger.VALUECD) AS ValueCD, ISNULL(dbo.MatterLedger.DEPOSITTYPE, 
                      dbo.ClosedMatterLedger.DEPOSITTYPE) AS Deposittype, ISNULL(dbo.MatterLedger.LREF, dbo.ClosedMatterLedger.LREF) AS lref
FROM         dbo.MatterLedger FULL OUTER JOIN
                      dbo.ClosedMatterLedger ON dbo.MatterLedger.MATTER = dbo.ClosedMatterLedger.MATTER AND 
                      dbo.MatterLedger.LREF = dbo.ClosedMatterLedger.LREF'

    EXEC (@COMMAND)

    SET @COMMAND = 'CREATE VIEW [dbo].[OpenClosedMattersLedger]
AS
SELECT     ISNULL(dbo.MatterLedger.MATTER, dbo.ClosedMatterLedger.MATTER) AS Matter, ISNULL(dbo.MatterLedger.BATCHNO, 
                      dbo.ClosedMatterLedger.BATCHNO) AS BatchNo, ISNULL(dbo.MatterLedger.PREF, dbo.ClosedMatterLedger.PREF) AS Pref, 
                      ISNULL(dbo.MatterLedger.[DATE], dbo.ClosedMatterLedger.[DATE]) AS TransDate, ISNULL(dbo.MatterLedger.REF, dbo.ClosedMatterLedger.REF) 
                      AS Ref, ISNULL(dbo.MatterLedger.NARR, dbo.ClosedMatterLedger.NARR) AS Narr, ISNULL(dbo.MatterLedger.VALUED, 
                      dbo.ClosedMatterLedger.VALUED) AS ValueD, ISNULL(dbo.MatterLedger.VALUEO, dbo.ClosedMatterLedger.VALUEO) AS ValueO, 
                      ISNULL(dbo.MatterLedger.VALUEC, dbo.ClosedMatterLedger.VALUEC) AS ValueC, ISNULL(dbo.MatterLedger.FEE, dbo.ClosedMatterLedger.FEE) AS Fee, 
                      ISNULL(dbo.MatterLedger.PER, dbo.ClosedMatterLedger.PER) AS Per, ISNULL(dbo.MatterLedger.[YEAR], dbo.ClosedMatterLedger.[YEAR]) AS TranYear,
                       ISNULL(dbo.MatterLedger.ENTRYDATE, dbo.ClosedMatterLedger.ENTRYDATE) AS EntryDate, ISNULL(dbo.MatterLedger.CLIENT, 
                      dbo.ClosedMatterLedger.CLIENT) AS Client, ISNULL(dbo.MatterLedger.VALUECC, dbo.ClosedMatterLedger.VALUECC) AS ValueCC, 
                      ISNULL(dbo.MatterLedger.VALUECD, dbo.ClosedMatterLedger.VALUECD) AS ValueCD, ISNULL(dbo.MatterLedger.DEPOSITTYPE, 
                      dbo.ClosedMatterLedger.DEPOSITTYPE) AS Deposittype, ISNULL(dbo.MatterLedger.LREF, dbo.ClosedMatterLedger.LREF) AS lref
FROM         dbo.MatterLedger FULL OUTER JOIN
                      dbo.ClosedMatterLedger ON dbo.MatterLedger.MATTER = dbo.ClosedMatterLedger.MATTER AND 
                      dbo.MatterLedger.LREF = dbo.ClosedMatterLedger.LREF'

    EXEC (@COMMAND)
  END
ELSE
  BEGIN
    SET @COMMAND = 'CREATE VIEW
       [dbo].[OpenClosedMatterLedger]
  WITH SCHEMABINDING
AS
 SELECT RTRIM(ISNULL(MAL.[MATTER], ''''))          AS [Matter],
        ISNULL(MAL.[Batchno], 0)                 AS [BatchNo],
        ISNULL(MAL.[PREF], 0)                    AS [Pref],
        MAL.[DATE]                               AS [TransDate],
        RTRIM(ISNULL(MAL.[REF], ''''))             AS [Ref],
        RTRIM(ISNULL(MAL.[NARR], ''''))            AS [Narr],
        ISNULL(MAL.[ValueD], 0)                  AS [ValueD],
        ISNULL(MAL.[ValueO], 0)                  AS [ValueO],
        ISNULL(MAL.[ValueC], 0)                  AS [ValueC],
        RTRIM(ISNULL(MAL.[FEE], ''''))             AS [Fee],
        ISNULL(MAL.[PER], 0)                     AS [Per],
        ISNULL(MAL.[YEAR], 0)                    AS [TranYear],
        MAL.[ENTRYDATE]                          AS [EntryDate],
        RTRIM(ISNULL(MAL.[CLIENT], ''''))          AS [Client],
        ISNULL(MAL.[VALUECC], 0)                 AS [ValueCC],
        ISNULL(MAL.[VALUECD], 0)                 AS [ValueCD],
        RTRIM(ISNULL(MAL.[deposittype], ''''))     AS [Deposittype],
        ISNULL(MAL.[LREF], 0)                    AS [lref]
   FROM [dbo].[MatterLedger] MAL'
    
    EXEC (@COMMAND)

    SET @COMMAND = 'CREATE UNIQUE CLUSTERED INDEX
       [IX_LREF]
    ON [dbo].[OpenClosedMatterLedger]
      ([Matter],
       [lref])'
    
    EXEC (@COMMAND)

    SET @COMMAND = 'CREATE VIEW
       [dbo].[OpenClosedMattersLedger]
  WITH SCHEMABINDING
AS
 SELECT RTRIM(ISNULL(MAL.[MATTER], ''''))          AS [Matter],
        ISNULL(MAL.[Batchno], 0)                 AS [BatchNo],
        ISNULL(MAL.[PREF], 0)                    AS [Pref],
        MAL.[DATE]                               AS [TransDate],
        RTRIM(ISNULL(MAL.[REF], ''''))             AS [Ref],
        RTRIM(ISNULL(MAL.[NARR], ''''))            AS [Narr],
        ISNULL(MAL.[ValueD], 0)                  AS [ValueD],
        ISNULL(MAL.[ValueO], 0)                  AS [ValueO],
        ISNULL(MAL.[ValueC], 0)                  AS [ValueC],
        RTRIM(ISNULL(MAL.[FEE], ''''))             AS [Fee],
        ISNULL(MAL.[PER], 0)                     AS [Per],
        ISNULL(MAL.[YEAR], 0)                    AS [TranYear],
        MAL.[ENTRYDATE]                          AS [EntryDate],
        RTRIM(ISNULL(MAL.[CLIENT], ''''))          AS [Client],
        ISNULL(MAL.[VALUECC], 0)                 AS [ValueCC],
        ISNULL(MAL.[VALUECD], 0)                 AS [ValueCD],
        RTRIM(ISNULL(MAL.[deposittype], ''''))     AS [Deposittype],
        ISNULL(MAL.[LREF], 0)                    AS [lref]
   FROM [dbo].[MatterLedger] MAL'
    
    EXEC (@COMMAND)

    SET @COMMAND = 'CREATE UNIQUE CLUSTERED INDEX
       [IX_LREF]
    ON [dbo].[OpenClosedMattersLedger]
      ([Matter],
       [lref])'
    
    EXEC (@COMMAND)
  END

GO

SELECT * FROM [Keyhouse_MoveClosed].[dbo].[ExecutionLog]
GO