IF OBJECT_ID(N'TRANS_AddTransactProperty',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[TRANS_AddTransactProperty]
GO



CREATE PROCEDURE
        [dbo].[TRANS_AddTransactProperty]
		(
		  @MatterCode   VARCHAR(20),
		  @TransactId   UNIQUEIDENTIFIER,
		  @EIRCode      VARCHAR(10),
		  @TransactUrl  NVARCHAR(300),
		  @HandlerCode   VARCHAR(10)
		)
AS
/*******************************************************************************************************
* This stored procedure is used to Insert Transact property.											   *
*                                                                                                      *									   *
* Stored Procedure Name			:  [dbo].[TRANS_AddTransactProperty]	
* Copied From					:  [dbo].[ky_NETAddTransactProperty]									   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2020-08-28	Sadiq		Created                                                                *
*******************************************************************************************************/
BEGIN
  SET NOCOUNT ON
  DECLARE @COUNT				INT
  DECLARE @Url				    NVARCHAR(300)
    IF EXISTS(SELECT TOP 1 1
        FROM
          [dbo].[TransactViewProperty] As TransactProperty
        WHERE
	      TransactProperty.[MatterCode]= @MatterCode)
     BEGIN
		 SELECT @COUNT=COUNT(*),@Url=[TransactUrl]
		    FROM  [dbo].[TransactViewProperty]
				WHERE [MatterCode]= @MatterCode GROUP By [TransactUrl]
		 SELECT @COUNT As DataCount,@Url As TransactUrl
     END
   ELSE
     BEGIN
	  IF (@TransactId='00000000-0000-0000-0000-000000000000')
	   BEGIN
	     SET @COUNT=0
		 SELECT @COUNT As DataCount
	   END
	   ELSE
	    BEGIN
		 INSERT INTO [dbo].[TransactViewProperty]
						(TransactViewPropertyId,
						 MatterCode,
						 TransactionId,
						 TransactUrl,
						 EIRCode,
						 HandlerCode)
                VALUES
						(NewID(),
						 @MatterCode,
						 @TransactId,
						 @TransactUrl,
						 @EIRCode,
						 @HandlerCode)
		END
	 END
  SET NOCOUNT OFF  
END
GO


IF OBJECT_ID(N'TRANS_FetchUDFValuesWithCasePlan',N'P')IS NOT NULL
	DROP PROCEDURE TRANS_FetchUDFValuesWithCasePlan
GO	
CREATE PROCEDURE TRANS_FetchUDFValuesWithCasePlan      
(      
@CasePlan  VARCHAR(5)      
)      
AS      
/*******************************************************************************************************
* This procedure is used to FETCH udf actions for wizard.		 									   *
*                                                                                                      *
* Stored Procedure Name	: TRANS_FetchUDFValuesWithCasePlan												   *
* Copied from			: [dbo].[KAAS_WizUDFActions]												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2029-09-09	Sadiq      Copied from KAAS
*******************************************************************************************************/  
BEGIN      
SET NOCOUNT ON      
	DECLARE @Value as VARCHAR(255)  
	SET @Value = ''  
		SELECT       
			RTRIM(ISNULL(u.UDFCategory,'')) AS  UDFCategory, 
			RTRIM(B.PROMPT)					AS PROMPT, 
			RTRIM(B.FILEPREFIX)				AS FILEPREFIX, 
			RTRIM(B.FIELDNAME)				AS FIELDNAME, 
			RTRIM(B.[TYPE])					AS FIELDTYPE,
			B.[LENGTH]						AS [Length],
			B.[FORMAT]						AS [Format],
			B.UDFCategory					AS Category, 
			@Value							AS Value, 
			A.UDSEQ							AS SEQ, 
			A.UDSEQ							AS RunningSeq, 
			RTRIM(UDFILE)					AS UDFILE, 
			RTRIM(UDUVCODE)					AS UDUVCODE, 
			RTRIM(UDWKCODE)					AS UDWKCODE,
			B.[VALIDATION]					AS ValidationType,
			s.TransactUDFField						As SettingKeyName,
			s.KeyhouseUDFField						As SettingKeyValue
		FROM       
			Udftemplatesworktypes  A       
		LEFT JOIN       
			Systemuserdefinedfields B ON A.UDFILE = B.FILEPREFIX AND A.UDUVCODE = B.FIELDNAME      
		LEFT JOIN       
			UDFCategory u on u.ID  = B.UDFCategory
		LEFT JOIN 
			TransactUDFFieldMapping s on s.KeyhouseUDFField=b.FIELDNAME 
		WHERE       
		RTRIM(UDWKCODE)=@CasePlan    AND (ISNULL(s.IsRetired,0)=0) 
		ORDER BY       
		UDSEQ      
      
SET NOCOUNT OFF      
END 
GO
IF EXISTS
  (SELECT TOP 1 1
     FROM sys.[objects] SO
    WHERE SO.[name] = 'TRANS_FNUDFValueSplit'
      AND SO.[type] = 'TF')
  BEGIN
    DROP FUNCTION [dbo].[TRANS_FNUDFValueSplit]
  END
GO


Create FUNCTION [dbo].[TRANS_FNUDFValueSplit]
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      UDFFieldName NVARCHAR(1000),
	  UDFFieldValue NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT
	  Declare @firstvalue varchar(100)

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)
			SET @firstvalue=SUBSTRING(@Input, @StartIndex, @EndIndex - 1);
			SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
			SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT 
				INTO @Output
					(UDFFieldName,
					 UDFFieldValue)
            SELECT @firstvalue,
				   SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
			SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
			
			
      END

      RETURN
END
GO
IF OBJECT_ID(N'TRANS_GetTransactMatterWithProperty',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[TRANS_GetTransactMatterWithProperty]
GO



CREATE PROCEDURE
        [dbo].[TRANS_GetTransactMatterWithProperty]
		(
		  @transId   UniqueIdentifier,
		  @HandlerCode   VARCHAR(10)
		)
AS
/*******************************************************************************************************
* This stored procedure is used to check whether Matter is available for the specified property transaction  id.											   *
*                                                                                                      *									   *
* Stored Procedure Name			:   [dbo].[TRANS_GetTransactMatterWithProperty]												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2020-08-28	Sadiq		Created                                                                *
*******************************************************************************************************/
BEGIN
	SET NOCOUNT ON
		 IF EXISTS(SELECT TOP 1 1
				   FROM
				   [dbo].[TransactViewProperty] As TransactProperty
				   WHERE
				   TransactProperty.TransactionId= @transId)
			BEGIN
				SELECT TR.MatterCode, TR.EIRCode,M.[Description]
				FROM  [dbo].[TransactViewProperty] TR 
				join
				[dbo].matters M 
				on M.Code=TR.MatterCode
				WHERE TransactionId= @transId and TR.HandlerCode=@HandlerCode
			END
    SET NOCOUNT OFF
END
GO


IF OBJECT_ID(N'TRANS_InserUDFActions',N'P')IS NOT NULL
	DROP PROCEDURE TRANS_InserUDFActions
GO	
CREATE PROCEDURE TRANS_InserUDFActions      
(@MatterCode        VARCHAR(20),
       @CasePlan          VARCHAR(5),
	   @UDFString VARCHAR(MAX))    
AS      
/*******************************************************************************************************
* This procedure is used to FETCH udf actions for wizard.		 									   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[TRANS_InserUDFActions]												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2020-09-10	Sadiq		Created                                                                *

*******************************************************************************************************/  
BEGIN

SET NOCOUNT ON

  INSERT
    INTO [dbo].[CaseUDFAnswers]
        ([CASECODE],
         [UDFFILE],
         [UDFNAME],
         [SEQNO],
         [DateValue],
         [NumberValue],
         [ForCopy],
         [TEXT1])
  SELECT x.[MatterCode],
         x.[UDFILE],
         x.[UDFName],
         x.[UDSEQ],
         x.[UDFDate],
         x.[NumberValue],
         x.[ForCopy],
         x.[Text1]
    FROM ( 
	SELECT @MatterCode As [MatterCode],	
		   UTWT.[UDFILE] As [UDFILE],
		   UTWT.[UDUVCODE] AS [UDFName],
		   (UTWT.[UDSEQ]) AS [UDSEQ],
		   CASE
				WHEN SD.[TYPE]='Date' AND [UDFFieldValue]!='' THEN [UDFFieldValue]	
				WHEN [UDFFieldValue]='null' THEN Null						
				ELSE Null
		   END AS [UDFDate],
		   CASE
				WHEN SD.[TYPE]='Numeric' AND [UDFFieldValue]!='null' AND [UDFFieldValue]!='' THEN CAST([UDFFieldValue] AS INT)
				WHEN [UDFFieldValue]='null' THEN Null							
				ELSE Null
		   END AS [NumberValue],
		   Null AS [ForCopy],		
		   CASE
				WHEN SD.[TYPE]='Date' THEN Null	
				WHEN [UDFFieldValue]='null' THEN Null					
				ELSE [UDFFieldValue]
		   END AS [Text1]
	from 
		[dbo].[TRANS_FNUDFValueSplit]( @UDFString, '|')
		LEFT JOIN 
		[dbo].[UDFTemplatesWorkTypes] UTWT
		ON 
		UTWT.UDUVCODE=UDFFieldName
		LEFT JOIN
		[dbo].[SystemUserDefinedFields] SD 
		ON
		UTWT.UDUVCODE=SD.FIELDNAME AND SD.FILEPREFIX=UTWT.[UDFILE]
		WHERE 
		UTWT.UDWKCODE=@CasePlan				 
	) x   


SET NOCOUNT OFF
END
GO

IF OBJECT_ID(N'TransactViewProperty',N'U') IS NULL
BEGIN

CREATE
 TABLE [dbo].[TransactViewProperty]
      ([TransactViewPropertyId]                 UNIQUEIDENTIFIER,
       [MatterCode]                             VARCHAR(20)                       NOT NULL,
       [TransactionId]                          UNIQUEIDENTIFIER                  NOT NULL,
       [EIRCode]                                VARCHAR(10)                       NOT NULL,
       [TransactUrl]                            NVARCHAR(300)                     NOT NULL, 
       [CreatedDateTime]                        DATETIME
                                                CONSTRAINT [DF_TVP_CDT] 
                                                           DEFAULT GETUTCDATE()   NOT NULL,
       [IsActive]                               BIT
                                                CONSTRAINT [DF_TVP_IsActive]
                                                           DEFAULT 1              NOT NULL
       CONSTRAINT [pk_TransactViewProperty]
                  PRIMARY KEY CLUSTERED
                 ([TransactViewPropertyId]),
       CONSTRAINT [fk_TransactViewProperty_MattersCode__SearchMatters_Code]
                  FOREIGN KEY([MatterCode])
                  REFERENCES [dbo].[SearchMatters]([Code])
               ON DELETE CASCADE)
END

GO

IF OBJECT_ID(N'TRANS_WizFetchNewWizardData',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[TRANS_WizFetchNewWizardData]
GO

CREATE PROCEDURE [dbo].[TRANS_WizFetchNewWizardData]      
(      
 @LoginHandlerCode VARCHAR(20)      
)      
AS      
/*******************************************************************************************************
* This will load the following data's for transact new case wizard,       									   *
*    1. Client contacts,       																		   *
*    2. Fee Earners List,      																		   *
*    3. Handlers      																				   *
*    4. Departments,      																			   *
*    5. Work Types,      																			   *
*    6. Case Plan  									    											   *
*                                                                                                      *
* Stored Procedure Name : [dbo].[TRANS_WizFetchNewWizardData]										   *
* Copied from			: [dbo].[KAAS_WizFetchMatterWizardData]																			   *
*                                                                                                      *
* Modification History:                                                                                *
* 2020-09-07    Sadiq        Created                                                              *
 *******************************************************************************************************/    
BEGIN      
 SET NOCOUNT ON      
       
      
        
  EXEC  KAAS_GetContactsByHandlerPrivilege  @LoginHandlerCode      
      
  SELECT RTRIM([Code])			  AS Code,      
		 RTRIM(ISNULL([Name],'')) AS FeeEarners       
    FROM Feeearnercodes       
   WHERE Retired <> 'Y'      
      
      
  SELECT RTRIM([Code]) AS Code,      
		 RTRIM([Name]) AS Partners     
    FROM Handlers       
   WHERE [TYPE] ='P' AND [RETIRED]  <> 'Y'   
      
  SELECT RTRIM([Code]) AS Code,      
		 RTRIM([Desc]) AS Department       
    FROM Departments       
   WHERE Retired <> 'Y'      
      
  SELECT RTRIM([Code])			  AS Code,
		 RTRIM(ISNULL([Desc],'')) AS WorkTypes       
    FROM Worktypes       
   WHERE Retired <> 'Y'      
   
           
  SELECT RTRIM(WKCODE) AS Code, 
		 RTRIM(ISNULL(WKDESC,'')) AS CasePlan       
    FROM Templates       
   WHERE ((Dept = '') 
		 OR (Dept = ( SELECT Dept 
						FROM Handlers
					   WHERE (CODE = @LoginHandlerCode )))) 
							  AND Retired = 'N'  
           
  SELECT [UserPrompt1],[UserPrompt2],[UserPrompt3], YourRef, AUTONUMBER AS [AutoNumber]       
    FROM [CONTROL]        
        
  SELECT ISNULL(KeyValue,'False') AS EstFeeRequiredMatterSetup 
    FROM SETTINGS      
   WHERE KeyName = 'EstFeeRequiredatMatterSetup'       
      
  SELECT ISNULL(KeyValue,'False') AS ExpInvoiceDateRequiredAtMatterSetup 
    FROM SETTINGS      
   WHERE KeyName = 'ExpInvoiceDateRequiredAtMatterSetup' 
        
  SELECT [BRANCHCODE]  AS [Code],       
		 [DESCRIPTION] AS [Branch]        
    FROM Branch       
        
  SELECT RTRIM(ISNULL(Branch,'')) AS Branch    
    FROM Handlers       
   WHERE RTRIM(CODE) = @LoginHandlerCode 
		 AND [Retired] <> 'Y'  
     
  SELECT RTRIM(Code) AS Code,     
		 RTRIM(Name) AS Secretary     
    FROM Handlers    
   WHERE [Type] ='S'
		 AND [Retired] <> 'Y'  
  
  SELECT KeyValue AS [BranchRequiredAtMatterSetup]
    FROM Settings    
   WHERE [KeyName]  = 'NewCaseWizardBranch'  
  
  SELECT RTRIM(ISNULL([ColourCode],'')) AS Code,       
		 RTRIM(ISNULL([ColourDesc],'')) AS FileDescription,      
		 [RGBColour]      
    FROM FileColours      
   WHERE Retired <> 'Y'  
  
--Code Configuration   
Declare @AutoNumberClient BIT  
Declare @AutoAlphaNumberingClient BIT  
Declare @MaxNumberOfMatters INT  
Declare @UniqueMatterCodeNumbering BIT  
  
Declare @MaxClientCode INT  
Declare @MaxMatterCode INT  
Declare @AutoNumberIfBlank BIT  
Declare @AllowManualEntryOnMatterCode BIT  
Declare @ManualClientCode BIT  
  
  
  
SELECT @AutoNumberClient = (CASE WHEN AUTONUMBER = 'Y' 
									THEN 'True' 
								ELSE 'False' 
							END),  
	   @AutoAlphaNumberingClient = (CASE WHEN AUTOALPHACLIENTNUMBERING = 'Y' 
											THEN 'True' 
										ELSE 'False' 
									END),  
	   @MaxNumberOfMatters = MAXNUMBEROFMATTERS,  
	   @UniqueMatterCodeNumbering = (CASE WHEN INDICATOR19 = 'Y' 
											THEN 'True' 
										ELSE 'False' 
									END)  
  FROM [CONTROL]  
  
  
  
  
  SELECT @MaxClientCode = (CASE WHEN [SET].[KeyName] = 'MaxClCode'  
							  THEN [SET].[KeyValue]  
							 ELSE @MaxClientCode   
						   END),  
		 @MaxMatterCode = (CASE WHEN [SET].[KeyName] = 'MaxMatNum'  
								 THEN [SET].[KeyValue]  
							 ELSE @MaxMatterCode   
						   END),  
		 @AutoNumberIfBlank = (CASE WHEN [SET].[KeyName] = 'AutoNumberIfBlank'  
									 THEN [SET].[KeyValue]  
								 ELSE @AutoNumberIfBlank   
							   END),  
		 @AllowManualEntryOnMatterCode = (CASE WHEN [SET].[KeyName] = 'ShowMatterCode'  
											  THEN [SET].[KeyValue]  
											 ELSE @AllowManualEntryOnMatterCode   
										   END),  
		 @ManualClientCode = (CASE WHEN [SET].[KeyName] = 'ManualClientCode'  
									THEN [SET].[KeyValue]  
									ELSE ISNULL(@ManualClientCode,'False')   
								END)  
    FROM SETTINGS [SET]  
  
 SELECT   
 @MaxClientCode					AS [MaxClientCode],   
 @MaxMatterCode					AS [MaxMatterCode],   
 @AutoNumberIfBlank				AS [AutoNumberIfBlank],   
 @AllowManualEntryOnMatterCode	AS [AllowManualEntryOnMatterCode],  
 @AutoNumberClient				AS [AutoNumberClient],  
 @AutoAlphaNumberingClient		AS [AutoAlphaNumberingClient],  
 @MaxNumberOfMatters			AS [MaxNumberOfMatters],  
 @UniqueMatterCodeNumbering		AS [UniqueMatterCodeNumbering],  
 @ManualClientCode				AS [ManualClientCode]  

  SELECT KeyValue AS [TransactNewCaseDefaultWorktype]
    FROM Settings    
   WHERE [KeyName]  = 'TransactNewCaseDefaultWorktype' 
    SELECT KeyValue AS [TransactNewCaseDefaultBranch]
    FROM Settings    
   WHERE [KeyName]  = 'TransactNewCaseDefaultBranch' 
    SELECT KeyValue AS [TransactNewCaseDefaultFeeCode]
    FROM Settings    
   WHERE [KeyName]  = 'TransactNewCaseDefaultFeeCode' 
    SELECT KeyValue AS [TransactNewCaseDefaultDept]
    FROM Settings    
   WHERE [KeyName]  = 'TransactNewCaseDefaultDept'
   
    SELECT KeyValue AS [TransactNewCaseDefaultCasePlan]
    FROM Settings    
   WHERE [KeyName]  = 'TransactNewCaseDefaultCasePlan'  
            
 SET NOCOUNT OFF      
END
GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'TRANS_WizInsertTransactNewMatter'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[TRANS_WizInsertTransactNewMatter]
  END
GO

CREATE PROCEDURE
       [TRANS_WizInsertTransactNewMatter]
      (@ClientCode             VARCHAR(10),
       @MatterCode1            VARCHAR(20),
       @Description            VARCHAR(200),
       @FeeEarnerCode          VARCHAR(10),
       @PartnerCode            VARCHAR(10),
       @DepartmentCode         VARCHAR(10),
       @WorkType               VARCHAR(10),
       @CasePlan               VARCHAR(10),
       @OldReference           VARCHAR(12),
       @YourReference          VARCHAR(100),
       @FileReference          VARCHAR(100),
       @UserReference2         VARCHAR(100),
       @UserReference3         VARCHAR(100),
       @LoginCode              VARCHAR(10),
       @EstimateFee            DECIMAL(17,2),
       @ExpectedDate           DATETIME,
       @OutlayBudget           DECIMAL(18,9),
       @IsFixedFee             BIT,
       @MoneyLaunderingCheck   SMALLINT,
       @MLDept                 VARCHAR(20),
       @MLFeeEarner            VARCHAR(20),
       @MLWorkType             VARCHAR(20),
       @MLCasePlan             VARCHAR(20),
       @Branch                 CHAR(3),
       @Secretary              VARCHAR(10),
       @FileColour             VARCHAR(3),
       @BillingMatter          VARCHAR(10),
       @Result                 VARCHAR(20) OUTPUT,
       @ResultFECode           VARCHAR(500)OUTPUT,
	   @UDFString              VARCHAR(MAX))
AS
/*******************************************************************************************************
* This procedure is used to insert new matter. This procedure will return 1 IF                         *
* successful insert of new matter.																	   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[TRANS_WizInsertTransactNewMatter]											   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2020-09-10	Sadiq		Created                                                                *
*******************************************************************************************************/ 
BEGIN

SET NOCOUNT ON
    DECLARE @MatterCode      VARCHAR(20)
    DECLARE @MatterCount     INT
    DECLARE @Team            VARCHAR(20)
    DECLARE @ActionId        INT
    DECLARE @AssignNo        INT
    DECLARE @OpenCount       INT
    DECLARE @CaseCode        VARCHAR(20)
    DECLARE @MatCodeLen      INT
    DECLARE @MatCodeZeros    VARCHAR(10)
    DECLARE @CompositeMatter VARCHAR(20)
    DECLARE @ClientFeeEarner VARCHAR(10)
    DECLARE @ChargeLevel     TINYINT
    DECLARE @ClientAddress   VARCHAR(15) --=''
    DECLARE @OfficeAddress   VARCHAR(15) --= ''
    DECLARE @ClientStatement VARCHAR(5)

    SET @ChargeLevel = (SELECT ISNULL(ChargeLevel,3)
					    FROM Contacts 
						WHERE Code=@ClientCode)

    SET @ClientStatement = (SELECT ISNULL(Statements,'Y')
						    FROM Contacts 
							WHERE Code = @ClientCode)

    IF NOT(@Branch = '')
    BEGIN
        SET @ClientAddress   = (SELECT ISNULL(DEFCLIENT,'') 
								FROM Branch 
								WHERE BRANCHCODE = @Branch)

        SET @OfficeAddress   = (SELECT ISNULL(DEFOFFICE,'') 
								FROM Branch 
								WHERE BRANCHCODE = @Branch)
    END

    SET @ClientAddress = RTRIM(ISNULL(@ClientAddress, ''))
    SET @OfficeAddress = RTRIM(ISNULL(@OfficeAddress, ''))
    SELECT 
        @MatCodeLen = [SET].[KeyValue]
    FROM 
        [dbo].[Settings] [SET]
    WHERE 
        [SET].[KeyName] = 'MaxMatNum'
    SET @MatCodeLen = ISNULL(@MatCodeLen, 4)
    --PINO02 start
    SET @MatCodeZeros = SUBSTRING('0000000000', 1, @MatCodeLen)
    --PINO02 end
    --2015-05-07 START
    DECLARE @MAXNUM INT
    SELECT @MAXNUM = ISNULL(MAX([FILENUM]), 0)
    FROM [dbo].[matters]

    SELECT 
         @MAXNUM = CASE WHEN [NEXTFILE] > @MAXNUM
                        THEN [NEXTFILE]
                        ELSE @MAXNUM END
    FROM [dbo].[control]
    --2015-05-07 END
    IF(@MoneyLaunderingCheck=2)
    BEGIN
    --Matters
        IF NOT EXISTS(SELECT TOP 1 1  FROM Matters WHERE CODE = @ClientCode + '/' + @MatCodeZeros)    --PINO02
        BEGIN
            --2015-05-07 START
            SET @MAXNUM = @MAXNUM + 1
            UPDATE CTL
               SET [NEXTFILE] = @MAXNUM
            FROM 
               [dbo].[control] CTL
            --2015-05-07 END
            INSERT INTO 
            [dbo].[matters]
                   ([Code],
                   [ClientCode],
                   [Matter],
                   [Description],
                   [FECode],
                   [Partner],
                   [Dept],
                   [WType],
                   [Permissions],
                   [Publish],
                   [FileNum],                        --2015-05-07
                   [Started],                        --PINO01
                   [Branch],
                   [Secretary],
                   [ChargeLevel],
                   [ClientBankAc],
                   [OfficeBankAc],
                   [FileColour],
                   [Statements])
            SELECT INS.[Code],
                   INS.[ClientCode],
                   INS.[Matter],
                   INS.[Description],
                   INS.[FECode],
                   INS.[Partner],
                   INS.[Dept],
                   INS.[WType],
                   INS.[Permissions],
                   INS.[Publish],
                   @MAXNUM,                          --2015-05-07
                   MST.[Started],                                                                     --PINO01
                   INS.[Branch],
                   INS.[Secratery],
                   INS.[ChargeLevel],
                   INS.[ClientBankAc],
                   INS.[OfficeBankAc],
                   INS.[FileColour],
                   INS.[Statements]
              FROM (SELECT @ClientCode +'/'+@MatCodeZeros    AS [Code],                               --PINO02
                           @ClientCode                       AS [ClientCode],
                           @MatCodeZeros                     AS [Matter],                             --PINO02
                           'Document matter'                 AS [Description],
                           @MLFeeEarner                      AS [FECode],
                           @PartnerCode                      AS [Partner],
                           @MLDept                           AS [Dept],
                           @MLWorkType                       AS [WType],
                           0                                 AS [Permissions],
                           'N'                               AS [Publish],
                           @Branch                           AS [Branch],
                           @Secretary                        AS [Secratery],
                           @ChargeLevel                      AS [ChargeLevel],
                           @ClientAddress                    AS [ClientBankAc],
                           @OfficeAddress                    AS [OfficeBankAc],
                           @FileColour                       AS [FileColour],
                           @ClientStatement                  AS [Statements]) INS
                   CROSS APPLY (SELECT ISNULL(MIN(MAT.[Started]), GetDate()) AS [Started]             --PINO01
                                  FROM [dbo].[matters] MAT
                                 WHERE MAT.[ClientCode] = @ClientCode) MST

            --CaseMaster
            INSERT
              INTO [dbo].[CaseMaster]
                  ([CSCODE],
                   [CSCLIENTCDE],
                   [CSFNR],
                   [CSDESC],
                   [CSWKTCODE],
                   [CSDATEENTERED_DATE])
            SELECT MAT.[Code],
                   MAT.[ClientCode],
                   MAT.[FECode],
                   MAT.[Description],
                   @MLCasePlan,
                   MAT.[Started]
              FROM [dbo].[matters] MAT
             WHERE MAT.[Code] = @ClientCode + '/' +@MatCodeZeros                                      --PINO02
        END
    END
    -- 2015-10-23    John    @MatterCount is now the maximum matter found in [dbo].[matters].[Matter]
    SET @MatterCount = ISNULL((SELECT TOP 1 MAX(MAT.[Matter]) FROM [dbo].[matters] AS MAT WHERE ISNUMERIC(MAT.[Matter]) = 1 AND MAT.[ClientCode] = @ClientCode), 0) + 1

  

    IF(@MatterCode1 = '')
    BEGIN
        SET @MatterCode = @MatCodeZeros + CONVERT(VARCHAR(10), @MatterCount)                          --PINO02
        SET @MatterCode = REVERSE(SUBSTRING(REVERSE(@MatterCode), 1, @MatCodeLen))                    --PINO02
    END
    ELSE
    BEGIN
        SET @MatterCode = @MatterCode1
    END
    --2015-05-07 START
    SET @MAXNUM = @MAXNUM + 1

    UPDATE 
       CTL
    SET 
       [NEXTFILE] = @MAXNUM
    FROM 
       [dbo].[control] CTL
    --2015-05-07 END
    
      -- KEYD-3919: CompositeBilling Indiactor
    IF @BillingMatter='OldMatter' 
    BEGIN
        SELECT @CompositeMatter = RTRIM(ISNULL(CTC.[CompMatter], '')),
               @ClientFeeEarner = RTRIM(ISNULL(CTC.[FE], ''))
        FROM [dbo].[Contacts] CTC
        WHERE CTC.[Code] = @ClientCode
    END
    ELSE
    BEGIN
      Set @CompositeMatter=''
    END
    INSERT
      INTO [dbo].[matters]
          ([Code],
           [ClientCode],
           [Matter],
           [Description],
           [FECode],
           [Partner],
           [Dept],
           [FileNum],                         --2015-05-07
           [Started],
           [WType],
           [YourRef],
           [OldRef],
           [User1],
           [User2],
           [User3],
           [EstFee],
           [ExpBillD],
           [OutlayBud],
           [FixedFee],
           [Branch],
           [CompBillingMatter],
           [CompFrequency],  --2015-11-23
           [Secretary],
           [ClientBankAc],
           [OfficeBankAc],
           [FileColour],
           [PCode],
           [PFECode],
           [Statements])
    VALUES(@ClientCode + '/' + @MatterCode,
           @ClientCode,
           @MatterCode,
           @Description,
           @FeeEarnerCode,
           @PartnerCode,
           @DepartmentCode,
           @MAXNUM,                           --2015-05-07
           GETDATE(),
           @WorkType,
           @YourReference,
           @OldReference,
           @FileReference,
           @UserReference2,
           @UserReference3,
           @EstimateFee,
           @ExpectedDate,
           @OutlayBudget,
           CASE WHEN @IsFixedFee = 'True'
                     THEN 'Y'
                ELSE 'N' END,
            @Branch,
            @CompositeMatter,
            'N', --2015-11-23
            @Secretary,
            @ClientAddress,
            @OfficeAddress,
            @FileColour,
            @ClientCode + '/' + @MatterCode,
            @FeeEarnerCode,
            @ClientStatement)
    SET @CaseCode = @ClientCode + '/' + @MatterCode
    INSERT
      INTO [dbo].[CaseMaster]
          ([CSCODE],
           [CSCLIENTCDE],
           [CSFNR],
           [CSDESC],
           [CSWKTCODE],
           [CSDATEENTERED_DATE])
    VALUES(@ClientCode + '/' + @MatterCode,
           @ClientCode,
           @FeeEarnerCode,
           CONVERT(VARCHAR(50), @Description),
           @CasePlan,
           GETDATE())
    ----Case UDF
	IF (@UDFString!='')
	BEGIN
    EXEC TRANS_InserUDFActions  @CaseCode,
                                        @CasePlan,@UDFString
		END

    --Gettting Open Matter Count
    SELECT @OpenCount = count(MAT.[Matter])
    FROM [dbo].[matters] MAT
    WHERE MAT.[ClientCode] = @ClientCode
         AND   ISNULL(MAT.[Closed], 'N') <> 'Y'

    --Update Last Matter Count
    UPDATE CTC
       SET CTC.[LastMatter] = @MatterCount,
           CTC.[OpenMatters] = @OpenCount,
           CTC.[Client] = 'Y'
      FROM [dbo].[Contacts] CTC
     WHERE CTC.[Code] = @ClientCode

    --Update Next Doc number - Arun
    --UPDATE
    --    [control]
    --SET
    --    NEXT_DOC_NO = 0

    SELECT @Team = HAN.[Team]
      FROM [dbo].[Handlers] HAN
     WHERE HAN.[CODE] = @FeeEarnerCode

    --PINO 2015-09-30 START
    EXEC @ActionID = [dbo].[KAAS_GetNextActionID]
    --SELECT @ActionId = ISNULL(MAX(ActionId), 0) --PINO03
    --  FROM [dbo].[diary]
    SET @ActionID = @ActionID - 1 --addition of sequence numbers below
    --PINO 2015-09-30 END
    
     DECLARE @FEDel       VARCHAR(10)
   DECLARE @FESec       VARCHAR(10)
   DECLARE @FEDelEmpty       CHAR(1)
   DECLARE @FESecEmpty       CHAR(1)
   --DECLARE @Assign      VARCHAR(10)
   
   --SELECT @Assign = CASE [TMP].[AssignActionTo]
   --                   WHEN 'D' THEN 'Del'
   --                   WHEN 'S' THEN 'Sec'
   --                   ELSE '' END
   --                 FROM [dbo].[TemplateActions] [TMP]
   --                 Inner Join [dbo].[ActionWorkTypes] AWT
   --                 ON TMP.[ACTIONCODE]=awt.ACTIONCODE
   --                 AND TMP.[AutoPopulate]='Y'
   --                 WHERE AWT.WORKTYPE=@CasePlan
 
 SELECT @FEDel= HAN.[Delegate] 
 FROM Handlers HAN
 WHERE CODE= (SELECT FECODE from matters where CODE=@ClientCode + '/' + @MatterCode)
                
 SELECT @FESec = Secretary 
 FROM Matters
 WHERE Code=@ClientCode + '/' + @MatterCode
                 
 If @FESec ='' OR @FESec = null
   BEGIN
   SET @FESecEmpty = 'Y'
      SELECT @FESec=HAN.[Delegate] 
      FROM Handlers HAN
      WHERE CODE= (
					SELECT FECODE 
					from matters 
					where CODE=@ClientCode + '/' + @MatterCode
					)
   END
   ELSE
   SET @FESecEmpty = 'N'
   
 
 IF @FEDel = '' or @FEDel=null 
 BEGIN
	 SET @FEDelEmpty = 'Y'
     SET @FEDel=@LoginCode
 END
 ELSE
	SET @FEDelEmpty = 'N'
 
  IF @FESec = '' or @FEDel=null 
 BEGIN
     SET @FESec=@LoginCode
 END
    
    DECLARE @RETURNDEFAULTFAILURES VARCHAR(500)

    DECLARE @NewDiary TABLE
           ([CASECODE]          VARCHAR(20),
            [DATE]              DATETIME,
            [STATUS]            SMALLINT,
            [ACTIONCODE]        VARCHAR(15),
            [ACTIONSTATUS]      VARCHAR(3),
            [ACTIONTYPE]        VARCHAR(1),
            [PROCESSTYPE]       VARCHAR(1),
            [FNCODE]            VARCHAR(10),
            [ASSIGN]            VARCHAR(500),
            [TEAMCODE]          VARCHAR(10),
            [TEXT1]             VARCHAR(MAX),
            [IMAGENO]           INT,
            [DUEDATE]           DATETIME,
            [PUBLISH]           VARCHAR(1),
            [DYSTARTTIME]       VARCHAR(10),
            [DYENDTIME]         VARCHAR(10),
            [DURATION]          SMALLINT,
            [ACTIONID]          INT,
            [ORGINALACTIONID]   INT,
            [PRIORITY]          VARCHAR(1),
            [HIGHLIGHTED]       VARCHAR(1),
            [MILESTEONETYPE]    VARCHAR(12),
            [PROCESSSTATUS]     SMALLINT,
            [WORKPROCESS]       SMALLINT,
            [BILLABLE]          INT,
            [ACTIONCATEGORY]    CHAR(1))

    --Insert Diary - first insert into @NewDiary, then use @NewDiary to drive the inserts into
    --               Diary and DiaryDelegations
    INSERT
      INTO @NewDiary
          ([CASECODE],
           [DATE],
           [STATUS],
           [ACTIONCODE],
           [ACTIONSTATUS],
           [ACTIONTYPE],
           [PROCESSTYPE],
           [FNCODE],
           [ASSIGN],
           [TEAMCODE],
           [TEXT1],
           [IMAGENO],
           [DUEDATE],
           [PUBLISH],
           [DYSTARTTIME],
           [DYENDTIME],
           [DURATION],
           [ACTIONID],
           [ORGINALACTIONID],
           [PRIORITY],
           [HIGHLIGHTED],
           [MILESTEONETYPE],
           [PROCESSSTATUS],
           [WORKPROCESS],
           [BILLABLE],
           [ACTIONCATEGORY]) --ActionCategory for DiaryDelegations, below
    SELECT @ClientCode + '/' + @MatterCode,
           GETDATE(),
           '0',
           TAC.[ACTIONCODE],
           TAC.[STATUS],
           TAC.[ACTTYPE],
           TAC.[PROCESSTYPE],
           CASE WHEN RTRIM(ISNULL(TAC.[AssignActionTo], 'L')) ='L'
                     THEN @LoginCode
                WHEN RTRIM(ISNULL(TAC.[AssignActionTo], 'L'))='C'
                     THEN @FeeEarnerCode
                WHEN RTRIM(ISNULL(TAC.[AssignActionTo], 'L'))='P'
                     THEN @PartnerCode
                WHEN RTRIM(ISNULL(TAC.[AssignActionTo], 'L'))='F'
                     THEN @ClientFeeEarner
                WHEN RTRIM(ISNULL(TAC.[AssignActionTo], 'L'))='O'
                     THEN TAC.[ASSIGNHANDLER]
                WHEN RTRIM(ISNULL(TAC.[AssignActionTo], 'L'))='D'
                     THEN (@FEDel)
                WHEN RTRIM(ISNULL(TAC.[AssignActionTo], 'L'))='S'
                     THEN @FESec
                ELSE @LoginCode END,
             CASE WHEN (RTRIM(ISNULL(TAC.[AssignActionTo], 'L'))='D' AND @FEDelEmpty='Y')
                        THEN TAC.[ACTIONCODE] + '- Empty Delegate, '                         
                  WHEN (RTRIM(ISNULL(TAC.[AssignActionTo], 'L'))='S' AND @FESecEmpty='Y')
                        THEN TAC.[ACTIONCODE] + '- Empty Secretary, '
                        ELSE '' END,
           @Team,
           TAC.[DESC],
           '0',
           GETDATE(),
           TAC.[PUBLISHER],
           [dbo].[ky_ConvertTimeToClarion](CONVERT(VARCHAR(20), GETDATE(), 8)),
           [dbo].[ky_ConvertTimeToClarion](CONVERT(VARCHAR(20), GETDATE(), 8)),
           '0',
           @ActionId + (ROW_NUMBER() OVER (ORDER BY TAC.[desc])),
           @ActionId + (ROW_NUMBER() OVER (ORDER BY TAC.[desc])),
           CASE SUBSTRING(RTRIM(ISNULL(TAC.[Priority], 'N')), 1, 1)
                WHEN 'H' THEN 'H'
                WHEN 'L' THEN 'L'
                ELSE 'N' END,
           TAC.[HIGHLIGHTED],
           TAC.[MILESTONETYPE],
           '0',
           '0',
           CASE RTRIM(ISNULL(TAC.[BILLABLE], 'N'))
                WHEN 'Y' THEN 1
                ELSE 0 END,
           TAC.[ACTIONCATEGORY]  --ActionCategory for DiaryDelegations, below
      FROM [dbo].[ActionWorkTypes] AWT
           INNER JOIN [dbo].[TemplateActions] TAC
                   ON TAC.[ACTIONCODE] = AWT.[ACTIONCODE]
                  AND TAC.[AutoPopulate] = 'Y'
     WHERE AWT.[WORKTYPE] = @CasePlan
     
     SET @RETURNDEFAULTFAILURES=''
     
   SELECT @RETURNDEFAULTFAILURES  =	@RETURNDEFAULTFAILURES+Assign 
   from @NewDiary
   Set @ResultFECode=@RETURNDEFAULTFAILURES

    INSERT
      INTO [dbo].[diary]
          ([CASECODE],
           [DATE],
           [STATUS],
           [ACTIONCODE],
           [ACTIONSTATUS],
           [ACTIONTYPE],
           [PROCESSTYPE],
           [FNCODE],
           [TEAMCODE],
           [TEXT1],
           [IMAGENO],
           [DUEDATE],
           [PUBLISH],
           [DYSTARTTIME],
           [DYENDTIME],
           [DURATION],
           [ACTIONID],
           [ORGINALACTIONID],
           [PRIORITY],
           [HIGHLIGHTED],
           [MILESTEONETYPE],
           [PROCESSSTATUS],
           [WORKPROCESS],
           [BILLABLE])
    SELECT NDIA.[CASECODE],
           NDIA.[DATE],
           NDIA.[STATUS],
           NDIA.[ACTIONCODE],
           NDIA.[ACTIONSTATUS],
           NDIA.[ACTIONTYPE],
           NDIA.[PROCESSTYPE],
           NDIA.[FNCODE],
           NDIA.[TEAMCODE],
           NDIA.[TEXT1],
           NDIA.[IMAGENO],
           NDIA.[DUEDATE],
           NDIA.[PUBLISH],
           NDIA.[DYSTARTTIME],
           NDIA.[DYENDTIME],
           NDIA.[DURATION],
           NDIA.[ACTIONID],
           NDIA.[ORGINALACTIONID],
           NDIA.[PRIORITY],
           NDIA.[HIGHLIGHTED],
           NDIA.[MILESTEONETYPE],
           NDIA.[PROCESSSTATUS],
           NDIA.[WORKPROCESS],
           NDIA.[BILLABLE]
      FROM @NewDiary NDIA

    --PINO 2015-09-30 START - reseed
    EXEC [dbo].[KAAS_ReseedActionID]
    --EXEC @ActionID = [dbo].[KAAS_GetNextActionID]
    --PINO 2015-09-30 END

    SELECT @AssignNo = ISNULL(MAX(AssignNo), 0) --PINO03
    FROM DiaryDelegations

    --Diary Delegations
    INSERT
      INTO [dbo].[DiaryDelegations]
          ([ACTIONID],
           [HANDLER],
           [TEAM],
           [DATE],
           [TIME],
           [DUEDATE],
           [DUETIME],
           [STATUS],
           [OWNER],
           [DELEGATE],
           [DELEGATESTATUS],
           [ActionType],
           [FromHandler],
           [DelType])
    SELECT NDIA.[ActionId],
           NDIA.FNCODE,
           @Team,
           NDIA.[DATE],
           [dbo].[ky_ConvertTimeToClarion](CONVERT(VARCHAR(20), NDIA.[DATE], 8)),
           GETDATE(),
           [dbo].[ky_ConvertTimeToClarion](CONVERT(VARCHAR(20), NDIA.[DATE], 8)),
           '0',
           'Y',
           NDIA.FNCODE,
           '0',
           NDIA.[ACTIONCATEGORY],
           NDIA.FNCODE,
           'Created'
      FROM @NewDiary NDIA

    SET @Result = @ClientCode + '/' + @MatterCode

    DECLARE @BitWiseID BIGINT

    SELECT @BitWiseID = CNP.[Previleges]
    FROM [dbo].[ContactNETPrivileges] CNP
    WHERE CNP.[ContactCode] = @ClientCode
          AND CNP.[ApplyToNewMatters] = 1

    --@ClientCode
    IF(@BitWiseID IS NOT NULL)
      BEGIN
        IF NOT EXISTS (SELECT TOP 1 1
                         FROM [dbo].[MatterNETPrivileges] MNP
                        WHERE MNP.[MATTER] = @Result )
          BEGIN
            INSERT
              INTO [dbo].[MatterNETPrivileges]
                  ([MATTER],
                   [Privileges])
            VALUES(@Result,
                   @BitWiseID)
          END
        ELSE
          BEGIN
            UPDATE MNP
               SET MNP.[Privileges] = @BitWiseID
              FROM [dbo].[MatterNETPrivileges] MNP
             WHERE MNP.[MATTER] = @Result
          END
      END

      --Update Matter Charge Level
      DECLARE @ClientChargeLevel TINYINT
      SET @ClientChargeLevel = (SELECT ISNULL(chargelevel,0) FROM Contacts WHERE code = @ClientCode )

      IF (@ClientChargeLevel<>0)
      BEGIN
        UPDATE Matters set ChargeLevel = @ClientChargeLevel
        WHERE Code = @Result
      END

    --Estimate Fee Log..
    IF(@EstimateFee <> '0.00')
    BEGIN
        INSERT INTO MatterEstimateFeeLogs
        (
            [Log],
            [Mattercode],
            [Who],
            [EstimateFee]
        )
        VALUES
        (
            'Added',
            @Result,
            @LoginCode,
            @EstimateFee
        )
    END
	
	--UPDATE C
	--SET 
	--	C.ApprovedDate = CASE C.Approved WHEN 1 THEN GETDATE() ELSE null END,
	--	C.ApprovedByUSER = CASE C.Approved WHEN 1 THEN  @LoginCode ELSE null END
	--	FROM
	--	Contacts C
	--WHERE
	--	Code = @ClientCode 
	
	IF OBJECT_ID('[dbo].[InitialDebtCharges]', 'U') IS NOT NULL AND
	   ISNULL((SELECT TOP 1 [KeyValue] FROM [dbo].[RevenueSettings] WHERE [KeyName] = 'ClaimSystemEnabled'), 'False') = 'True'
	BEGIN
		-- Setting original debt via User 1 field:
		IF ISNUMERIC(@FileReference) = 1 AND PATINDEX('%[^0-9.]%', @FileReference) = 0
		BEGIN
			UPDATE MAT
			   SET MAT.[OriginalDebt] = CONVERT(DECIMAL(13, 2), @FileReference)
			  FROM [dbo].[matters] AS MAT
			 WHERE MAT.[Code] = @ClientCode + '/' + @MatterCode
			   AND MAT.[User1] = @FileReference
		END
		
		IF (SELECT COUNT(1) FROM [dbo].[InitialDebtCharges]) > 0
		BEGIN
			DECLARE @NEXTPREF        INT
			DECLARE @I               INT -- This will be the ID of the next initial charge
			DECLARE @MAX             INT -- This will be the last ID
			DECLARE @SQL             NVARCHAR(MAX)
			DECLARE @MATTERCODE_FULL VARCHAR(20)
			
			SELECT @I               = ISNULL(MIN(ISNULL(IDC.[id], 0)), 0),
				   @MAX             = ISNULL(MAX(ISNULL(IDC.[id], 0)), 0),
				   @MATTERCODE_FULL = @ClientCode + '/' + @MatterCode
			  FROM [dbo].[InitialDebtCharges] AS IDC
			
			WHILE (ISNULL(@MAX, 0) <> 0 AND ISNULL(@I, 0) <> 0 AND ISNULL(@I, 0) < ISNULL(@MAX, 0) + 1)
			BEGIN
				SELECT @NEXTPREF = ISNULL(MAX(DCL.[POSTINGREF]), 0) + 1
				  FROM [dbo].[DebtCostingLedger] AS DCL
				
				SELECT @SQL = '
				INSERT INTO [dbo].[DebtCostingLedger] (
					   [MATTERCODE],
					   [DATE],
					   [REFERENCE],
					   [DESCRIPTION],
					   [TYPE],
					   [Value],
					   [INTEREST],
					   [COSTS],
					   [REMITTANCENO],
					   [ENTRYDATE],
					   [ENTEREDBY],
					   [POSTINGREF],
					   [Bounced],
					   [PREMIUMPAID],
					   [INTERESTPAID],
					   [COSTSPAID],
					   [XnId],
					   [CourtFeeCode])
				SELECT ''' + ISNULL(@MATTERCODE_FULL, '') + ''',' +
					   '''' + CONVERT(VARCHAR(8), GETDATE(), 112) + ''',' +
					   '''' + ISNULL(IDC.[Reference], '') + ''',' +
					   '''' + ISNULL(IDC.[Description], '') + ''',' +
					   '''' + ISNULL(IDC.[Type], '') + ''',' +
					   ISNULL(IDC.[Value], '') + ',' +
					   ISNULL(IDC.[Interest], '') + ',' +
					   ISNULL(IDC.[Costs], '') + ',' +
					   '0,' +
					   '''' + CONVERT(VARCHAR(8), GETDATE(), 112) + ''',' +
					   '''AUTO'',' +
					   CONVERT(VARCHAR(10), @NEXTPREF) + ',' +
					   '0,' +
					   '0,' +
					   '0,' +
					   '0,' +
					   '-1,' +
					   '''' + ISNULL(IDC.[CourtFeeCode], '') + '''' +
				 'FROM [dbo].[matters] AS MAT
				 WHERE MAT.[Code] = ''' + @MATTERCODE_FULL + ''''
				  FROM [dbo].[matters] AS MAT
					   LEFT OUTER JOIN [dbo].[InitialDebtCharges] AS IDC
									ON IDC.[id] = @I
				 WHERE MAT.[Code] = @MATTERCODE_FULL
			    
				EXEC sp_executesql @SQL
				
				DECLARE @COST_VALUE     DECIMAL(19, 2)
				DECLARE @PAYMENT_DESC   VARCHAR(MAX)
				
				SELECT TOP 1 
				       @COST_VALUE     = DCL.[COSTS],
				       @PAYMENT_DESC   = DCL.[DESCRIPTION]
				  FROM [dbo].[DebtCostingLedger] AS DCL
				 WHERE DCL.[POSTINGREF] = @NEXTPREF
				
				-- Try to create action for this payment
				EXEC [dbo].[KAAS_RevenueInsertintoDiary] @MATTERCODE_FULL, @COST_VALUE, 0, 0, 'Cost', @PAYMENT_DESC, 0, @LoginCode
			    
				SELECT @I = ISNULL(MIN(ISNULL(IDC.[id], 0)), 0)
				FROM [dbo].[InitialDebtCharges] AS IDC
				WHERE IDC.[id] > @I			  
			END
			
			UPDATE MAT
			   SET MAT.[RecoverableCosts] = ISNULL(DCL.[COSTS], 0)
			  FROM [dbo].[matters] AS MAT
				   LEFT OUTER JOIN (SELECT DCL.[MATTERCODE],
										   SUM(ISNULL(DCL.[COSTS], 0)) AS [COSTS]
									  FROM [dbo].[DebtCostingLedger] AS DCL
								      GROUP BY DCL.[MATTERCODE]) AS DCL
									  ON MAT.[Code] = DCL.[MATTERCODE]
			 WHERE MAT.[Code] = @ClientCode + '/' + @MatterCode
		END
	END
		
  SET NOCOUNT OFF
END

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

CREATE PROCEDURE
       [dbo].[TRANS_WizTransactInsertNewContact]
      (@Code        VARCHAR(10),
       @Name        VARCHAR(256),
       @Address     VARCHAR(500),
       @Salut       VARCHAR(256),
       @Tel         VARCHAR(20),
       @Mobile      VARCHAR(20),
       @Fax         VARCHAR(20),
       @Email       VARCHAR(50),
       @FeeEarner   VARCHAR(10),
       @IsApproved  BIT,
       @SecondaryEmail  VARCHAR(40),
       @RSINo        VARCHAR(50),
       @LoginCode   VARCHAR(3),
       @Result      INT OUTPUT,
	   @PostalCode VARCHAR(10))
AS
/*******************************************************************************************************
* This procedure is used to insert new contact AND It will return									   *
*                  result 1 IF successful insert AND it will return 2 IF duplicate EXISTS.             *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[TRANS_WizTransactInsertNewContact]											   *
											   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2020-02-09	Sadiq 		Created                                                                *
*******************************************************************************************************/ 
BEGIN
  SET NOCOUNT ON
  
  DECLARE @CHARGELEVEL INT
  
  SELECT
	 @CHARGELEVEL = ISNULL(CONVERT(INT, [SET].[KeyValue]), 3)
  FROM
	 [dbo].[Settings] AS [SET]
  WHERE
	 [SET].[KeyName] = 'DefaultClientChargeLevel'
     AND [SET].[KeyValue] IN ('1', '2', '3', '4', '5')
  
  SELECT @CHARGELEVEL = ISNULL(@CHARGELEVEL, 3)
  
  IF NOT EXISTS
    (SELECT TOP 1 1
       FROM [dbo].[Contacts] CON
       WHERE CON.[Code] = @Code)

    BEGIN
      INSERT
        INTO [dbo].[Contacts]
            ([Code],
             [Name],
             [Address],
             [Salut],
             [Tel],
             [Fax],
             [email],
             [FE],
             [Approved],
             [CompFrequency],
             [ChargeLevel],
             [SecondaryEmail],
             [RSINo],
			 [PostalCode],
             [Statements],
             [ApprovedDate],			 
             [ApprovedByUSER]
			)
      VALUES(@Code,
             @Name,
             @Address,
             @Salut,
             @Tel,
             @Fax,
             @Email,
             @FeeEarner,
             @IsApproved,
             'N',
             @CHARGELEVEL,
             @SecondaryEmail,
             @RSINo,
			 @PostalCode,
             'Y',
             CASE @IsApproved WHEN 1 THEN GETDATE() ELSE null END,
             CASE @IsApproved WHEN 1 THEN @LoginCode ELSE null END)

      SET @Result = 1

      INSERT
        INTO [dbo].[client]
            ([CLCODE],
             [CLNAME],
             [CLFNR],
             [CLLEGALNAME],
             [CLTEL],
             [CLMOBNO],
             [CLNEXTCSENO],
             [CLCSECOUNT],
             [CLNUMCHILD],
             [CLADDR],
             [CLEMAIL])
      VALUES(@Code,
             @Name,
             @FeeEarner,
             @Name,
             @Tel,
             @Mobile,
             0,
             0,
             0,
             @Address,
             @Email)
    END
  ELSE
    BEGIN
      SET @Result = 2
    END

  UPDATE CTL
     SET CTL.[LASTCLIENT] = [dbo].[KAAS_FNGetMaxNumericClient]()
     FROM [dbo].[control] CTL

  DECLARE @NextClientNo INT
  DECLARE @NCOMMAND NVARCHAR(MAX)

  IF [dbo].[ISSAM4]() = 1
    BEGIN
      SET @NCOMMAND = N'SELECT @NextClientNo = CONVERT(INT, CTN.[NumValue])
					  FROM [dbo].[CtrlNum] CTN
					  WHERE CTN.[Code] = ''NextClientNo'''

      EXEC sp_executesql @NCOMMAND, N'@NextClientNo INT OUTPUT', @NextClientNo OUTPUT
    END

  IF @NextClientNo IS NOT NULL
    BEGIN
      WHILE EXISTS
            (SELECT TOP 1 1
              FROM [dbo].[Contacts] CNT
              WHERE CASE WHEN ISNUMERIC(CNT.[Code]) = 0
                              THEN 0
                        ELSE CONVERT(INT, CONVERT(FLOAT, CNT.[CODE])) END = @NextClientNo)
        BEGIN
          SET @NextClientNo = @NextClientNo + 1
        END

      SET @NCOMMAND = N'      UPDATE CTN
         SET CTN.[NumValue] = @NextClientNo
        FROM [dbo].[CtrlNum] CTN
       WHERE CTN.[Code] = ''NextClientNo'''

      EXEC sp_executesql @NCOMMAND, N'@NextClientNo INT', @NextClientNo

    END
  SET NOCOUNT OFF
END
GO