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 EXISTS (SELECT TOP 1 1 
             FROM SYS.[objects] SO 
       
INNER JOIN [sys].[columns] SC  ON SC.[object_id] = SO.[object_id] 
              AND SC.[name] = 'TransactSide'
 
            WHERE SO.[name] = 'TransactUDFFieldMapping' 
              AND SO.[type] = 'U')

BEGIN
 Update [dbo].[TransactUDFFieldMapping] 
    SET TransactSide='P' 
  WHERE Id>6 and Id<15
	 

 Update [dbo].[TransactUDFFieldMapping] 
    SET TransactSide='S' 
  WHERE Id>14 
END 


GO


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

CREATE PROCEDURE [dbo].[TRANS_CreateActionForFECode]
(
    @CASECODE                 VARCHAR(20),
    @FNCODE                   VARCHAR(10),
    @TEAMCODE                 VARCHAR(10),
	@SUBJECT				  VARCHAR(MAX),
	@TEXT					  VARCHAR(MAX),
	@EditActionId			  INT = 0,
    @Result                   INT    OUTPUT
)

/*******************************************************************************************************
* This sp used to insert blank action.																   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[TRANS_CreateActionForFECode]											       *
* Copied from			: [dbo].[ky_NETInsertTasksAction]											   *
* Copied from			: [dbo].[KAAS_InsertTasksAction]											   *
* Copied from			: [dbo].[KAAS_CP_GetActionId]											       *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-12	Vinodhan K		Created                                                                *
* 2022-02-10	Vinodhkumar.M   Created for Client Portal - upload doument                             *
* 2022-02-10	Vinodhkumar.M   KAAS_CP_GetNextActionId is created instead of KAAS_GetNextActionID     *
							    KAAS_CP_ConvertTime is created Instead of ky_ConvertTimeToClarion      *
						        for client Portal                                                      *
* 2022-02-15   Vinodhkumar.M   In Inserting diary passing values for due date priority value,          *
							   Action type and Publish fields										   *
* 2022-04-18   Vinodhkumar.M   Created																   *
* 2022-04-26   Aakif			Created for Keyhouse and Transact Integration						   *
* 2022-05-05   Aakif			Use setting value for default action code							   *
*******************************************************************************************************/ 

AS
BEGIN
SET NOCOUNT ON
--Diary
    DECLARE @TIMECONVERT INT
    SET @TIMECONVERT = dbo.KAAS_CP_ConvertTime(GETDATE())

	--INSERT A NEW RECORD FOR NEW NOTE ELSE UPDATE
	IF(@EditActionId = 0)
	BEGIN
		DECLARE @ActionCode VARCHAR(10) = NULL;

		SELECT @ActionCode = ISNULL([KeyValue], 'ADMNT')
		  FROM [dbo].[Settings] 
		 WHERE [KeyName] = 'TransactActionCode';

	    DECLARE @ACTIONID    INT

    --Pino 2015-09-30 Start
   -- EXEC @ACTIONID = [dbo].[KAAS_GetNextActionID] 
    --SET @ACTIONID = (SELECT ISNULL(MAX(ACTIONID + 1), 1) FROM [dbo].[diary])
    --Pino 2015-09-30 End

	 -- This SP created for Client Portal Upload
	 EXEC @ACTIONID = [dbo].[KAAS_CP_GetNextActionId] 

    INSERT
      INTO [dbo].[diary]
          ([CASECODE],
           [DATE],
           [STATUS],
           [ACTIONCODE],
           [ACTIONSTATUS],
           [ACTIONTYPE],
           [PROCESSTYPE],
           [FNCODE],
           [TEAMCODE],
           [TEXT1],
           [TEXT2],
           [DELEGATEDFNR],
           [DELEGATEDDATE],
           [DELEGATEDBACKDATE],
           [DEFERRED],
           [DUEDATE],
           [PUBLISH],
           [DYSTARTTIME],
           [DYENDTIME],
           [DURATION],
           [ACTIONID],
           [ORGINALACTIONID],
           [PRIORITY],
           [HIGHLIGHTED],
           [MILESTEONETYPE],
           [ATTACHMENTS],
           [PROCESSSTATUS],
           [WORKPROCESS],
           [BILLABLE],
           [BILLDESCRIPTION],
           [EMAILADDRESS],
           [ADDRESSTO],
           [CCTo],
           [BCCTo],
           [EMAIL],
           [SUBJECT],
           [DELEGATIONSTATUS],
           [DRAFTBILLNO],
           [CHEQUEREQNO],
           [TxmSent],
           [Location],
           [HearingType],
           [ForCopy],
           [TxmDate],
           [TxmSeqNo],
           [DisplayText],
           [Flag])
    VALUES(@CASECODE,
           GETDATE(),
           0,
           @ActionCode,
           null,
           'N',
           null,
           @FNCODE,
           @TEAMCODE,
           @TEXT,
           null,
           null,
           null,
           null,
           null,
           GETDATE(),
           'P',
           @TIMECONVERT,
           @TIMECONVERT,
           0,
           @ACTIONID,
           0,
           'N',
           null,
           null,
           null,
           0,
           0,
           0,
           null,
           null,
           null,
           null,
           null,
           null,
           @SUBJECT,
           0,
           0,
           0,
           0,
           null,
           'Not Applicable',
           null,
           null,
           0,
          null,
           0)
    INSERT
              INTO [dbo].[DiaryDelegations]
                  ([ACTIONID],
                   [HANDLER],
                   [TEAM],
                   [DATE],
                   [TIME],
                   [DATER],
                   [TIMER],
                   [DUEDATE],
                   [DUETIME],
                   [REVIEW],
                   [STATUS],
                   [OWNER],
                   [DELEGATE],
                   [DELEGATESTATUS],
                   [ActionType],
                   [FromHandler],
                   [ReturnedBy],
                   [DelType])
            VALUES(@ACTIONID,
                   @FNCODE,
                   @TEAMCODE,
                   GETDATE(),
                   @TIMECONVERT,
                   null,
                   @TIMECONVERT,
                   GETDATE(),
                   @TIMECONVERT,
                   null,
                   0,
                   'Y',
                   @FNCODE,
                   0,
                   'N',
                   @FNCODE,
                   null,
                   'Created')
    SET @Result = @ACTIONID
	END
	ELSE
	BEGIN
		UPDATE [dbo].[diary]
		SET [SUBJECT] = @SUBJECT,
			[TEXT1] = @TEXT
		WHERE [ACTIONID] = @EditActionId

		SET @Result = 1;
	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)   ,
@TransactSide CHAR(1)
   
)      
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 AND (s.TransactSide ='C' OR s.TransactSide=@TransactSide)
		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_GetTransactActionList',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[TRANS_GetTransactActionList]
GO

CREATE PROCEDURE 
	[dbo].[TRANS_GetTransactActionList]

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[TRANS_GetTransactActionList]										   *
* Description: To fetch list of all actions that are published and mapped to transact property		   *
*                                                                                                      *
* Modification History:                                                                                *
* 2022-04-19    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

	SELECT
		[DY].[ACTIONID]		  AS [ActionId],
		[DY].[SUBJECT]		  AS [ActionSubject],
		[DY].[TEXT1]		  AS [ActionDesc],
		[DY].[CASECODE]		  AS [MatterCode],
		[TVP].[TransactionId] AS [TransactionId]
  	  FROM (
	  SELECT [TNU].[ActionId]
	    FROM [dbo].[TransactNoteUpdate] [TNU]
	   WHERE [TNU].[TransactUpdate] IS NULL
    GROUP BY [TNU].[ActionId]) [TNU]
	  JOIN [dbo].[diary] [DY]
	    ON [TNU].[ActionId] = [DY].[ACTIONID]
	  JOIN [dbo].[TransactViewProperty] [TVP]
	    ON [TVP].[MatterCode] = [DY].[CASECODE]

END

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

CREATE PROCEDURE 
	[dbo].[TRANS_GetTransactDetailForMatter]
    (@MatterCode       VARCHAR(20))

AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[TRANS_GetTransactDetailForMatter]									   *
* Description: To fetch Transact details for provided matter code									   *
*                                                                                                      *
* Modification History:                                                                                *
* 2022-04-08    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

	SELECT
		[Mattercode],
		[TransactionId],
		[EIRCode],
		[TransactUrl],
		[CreatedDateTime],
		[IsActive],
		[HandlerCode]
	FROM [dbo].[TransactViewProperty]
	WHERE [MatterCode] = @MatterCode


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 EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'TRANS_InsertDefaultTransactUDFQuestions'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[TRANS_InsertDefaultTransactUDFQuestions]
  END
GO

CREATE PROCEDURE
       [dbo].[TRANS_InsertDefaultTransactUDFQuestions]
	   
     
/********************************************************************************************************
 Stored Procedure Name: [TRANS_InsertDefaultTransactUDFQuestions]
 Description:     This stored procedure is used to insert Default values for transact question udf fields 

 Modifications: 2020-12-16 Sadiq - Created
               
 ********************************************************************************************************/
AS
BEGIN

	 IF EXISTS
      (SELECT TOP 1 [KeyValue] FROM [dbo].[Settings] SETT WHERE SETT.[KeyName] = 'TransactNewCaseDefaultCasePlan')
      BEGIN   
	  
	  DECLARE   @CasePlan VARCHAR(10);  
	   
      SELECT  TOP 1 @CasePlan = [KeyValue] FROM [dbo].[Settings] SETT WHERE SETT.[KeyName] = 'TransactNewCaseDefaultCasePlan'
	 --To make Proper orders for 2 new UDF field
			DELETE from [Udftemplatesworktypes] WHERE UDWKCODE = @CasePlan
	 --Common UDF 

			 --check for IncludedInSale UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND 
			  SUF.[FIELDNAME] = 'IncludedInSale')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]   
				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )           
				  VALUES('UDF:','IncludedInSale','Text','Enter the property included in sales',
						200,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for PropertyAddress UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'PropertyAddress')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]  
				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )            
				  VALUES('UDF:','PropertyAddress','Text','Enter Property Address',
						200,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for PropertyEIRCode UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'PropertyEIRCode')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]   
				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )           
				  VALUES('UDF:','PropertyEIRCode','Text','Enter EIR Code',
						20,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for PropertyGuidePrice UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'PropertyGuidePrice')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]  
				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )            
				  VALUES('UDF:','PropertyGuidePrice','Numeric','Enter property guide price',
						15,2,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for PropertySalePrice UDF 
			   
					   IF NOT EXISTS
					  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'PropertyPurchasePrice')
					  BEGIN        
						  INSERT
						  INTO [dbo].[SystemUserDefinedFields]  
						  (
							 [FILEPREFIX]
							,[FIELDNAME]
							,[TYPE]
							,[PROMPT]
							,[LENGTH]
							,[DECMIAL]
							,[FORMAT]
							,[INITIALVALUE]
							,[CALCINITIALVALUE]
							,[OVERRIDEINITIALVALUE]
							,[REQUIRED]
							,[VALIDATION]
							,[LOW]
							,[HIGH]
							,[SYSTEMFIELD]
							,[TOP]
							,[LEFT]
							,[HEIGHT]
							,[WIDTH]
							,[FONT]
							,[VISIBLE]
							,[ENABLED]
							,[READONLY]
							,[MULTILINE]
							,[ACTUALNAME]
							,[COMMON]
							,[UDFCategory]
							,[ISLOCK]
							,[SQLSTATEMENT]
						  )            
						  VALUES('UDF:','PropertyPurchasePrice','Numeric','Enter Property Purchase Price',
								15,2,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
					  END
				

			   --check for PropertyType UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 * FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'PropertyType')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]    
				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )          
				  VALUES('UDF:','PropertyType','Text','Enter Property Type',
						30,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			  --Check for DepositedAmount UDF
			   IF NOT EXISTS
			  (SELECT TOP 1 * FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'TransDepositAmount')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]   
				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )           
				  VALUES('UDF:','TransDepositAmount','Text','Enter deposited amount',
						20,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END
			  --Check for Estimated Closing Date
			  IF NOT EXISTS
			  (SELECT TOP 1 * FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'TransClosingDate')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]     
				  ( [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
	  )         
				  VALUES('UDF:','TransClosingDate','Date','Enter estimated closing date',
						20,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END


			--Starting with seller 

			--check for SellerBankruptQues UDF 
			IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerBankruptQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]     
				  ( [FILEPREFIX]
      ,[FIELDNAME]
      ,[TYPE]
      ,[PROMPT]
      ,[LENGTH]
      ,[DECMIAL]
      ,[FORMAT]
      ,[INITIALVALUE]
      ,[CALCINITIALVALUE]
      ,[OVERRIDEINITIALVALUE]
      ,[REQUIRED]
      ,[VALIDATION]
      ,[LOW]
      ,[HIGH]
      ,[SYSTEMFIELD]
      ,[TOP]
      ,[LEFT]
      ,[HEIGHT]
      ,[WIDTH]
      ,[FONT]
      ,[VISIBLE]
      ,[ENABLED]
      ,[READONLY]
      ,[MULTILINE]
      ,[ACTUALNAME]
      ,[COMMON]
      ,[UDFCategory]
      ,[ISLOCK]
      ,[SQLSTATEMENT])         
				  VALUES('UDF:','SellerBankruptQues','Text','Has anyone ever obtained a judgement against you or been adjudicated bankrupt?',
						100,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			  --check for SellerPurchasedQues UDF 
			IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerPurchasedQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]    
				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )          
				  VALUES('UDF:','SellerPurchasedQues','Text','Have there been any extensions or alterations since you purchased the property?',
						100,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			 --check for SellerThingsAvailQues UDF 
			IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerThingsAvailQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]    
				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )           
				  VALUES('UDF:','SellerThingsAvailQues','Text','Please tick if there is :',
						200,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerPropertyBuiltQues UDF 
			IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerPropertyBuiltQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]    
				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )           
				  VALUES('UDF:','SellerPropertyBuiltQues','Text','In what year was the property Built',
						50,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerMortageQues UDF 
			IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerMortageQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields] 
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )          
				  VALUES('UDF:','SellerMortageQues','Text','Other than the main mortgage, are there any other charges or mortgage.',
						200,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerClaimQues UDF 
			IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerClaimQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]    
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )           
				  VALUES('UDF:','SellerClaimQues','Text','Has any other person a claim to your property ? If yes please give detail below.',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerCourtQues UDF 
			IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerCourtQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]    
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )           
				  VALUES('UDF:','SellerCourtQues','Text','Has anybody taken court proceeding against you? If yes please give detail.',
						100,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerIrishEquivalentQues UDF 
			IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerIrishEquivalentQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]   
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )            
				  VALUES('UDF:','SellerIrishEquivalentQues','Text','Have you ever signed document in the Irish equivalent of your name ?',
						100,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerServicesQues UDF 
			IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerServicesQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]    
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )           
				  VALUES('UDF:','SellerServicesQues','Text','Please tick the service the property includes:',
						200,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerChargedQues UDF 
			IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerChargedQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]  
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )              
				  VALUES('UDF:','SellerChargedQues','Text','Is your property charged with any of the following?',
						200,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerMarriageQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerMarriageQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]  
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )             
				  VALUES('UDF:','SellerMarriageQues','Text','If married please furnish a copy of your marriage certificate',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerDivorcedQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerDivorcedQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]  
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )             
				  VALUES('UDF:','SellerDivorcedQues','Text','If separated or divorced furnish a copy of Separation Agreement / Court Order',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerEngagedQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerEngagedQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]  
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )             
				  VALUES('UDF:','SellerEngagedQues','Text','If single confirm you were not previously engaged with whom u purchased property',
						100,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerContributionQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerContributionQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields] 
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )              
				  VALUES('UDF:','SellerContributionQues','Text','Did any other person, contribute financially to the purchase of the your house?',
						100,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for SellerLPTTaxQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerLPTTaxQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]  
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )             
				  VALUES('UDF:','SellerLPTTaxQues','Text','Please Provide you Local Property Tax (LPT) ID',
						200,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

				--check for SellerLPTPinQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerLPTPinQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]   
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )            
				  VALUES('UDF:','SellerLPTPinQues','Text','Please provide Local Property Tax (LPT) Pin:',
						200,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

				--check for SellerRatingQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerRatingQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]      
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )         
				  VALUES('UDF:','SellerRatingQues','Text','Please furnish Energy Rating Certificate & enter the rating in the box below?',
						200,0,'','','','','N','N',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

				--check for SellerAlarmQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerAlarmQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields] 
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )              
				  VALUES('UDF:','SellerAlarmQues','Text','Is the property fitted with an Alarm. If yes please provide Alarm Code :',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

				--check for SellerRentQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerRentQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]   
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )            
				  VALUES('UDF:','SellerRentQues','Text','Has the property been rented ?',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

				--check for SellerNPPRchargesQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'SellerNPPRchargesQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]       
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )        
				  VALUES('UDF:','SellerNPPRchargesQues','Text','Proof of payment of the NPPR Charge.',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END


			  ---Starting for Purchaser --

			  --check for PrimaryFamilyHome UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'PrimaryFamilyHome')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]       
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )         
				  VALUES('UDF:','PrimaryFamilyHome','Text','Will this property be your primary residence?',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			  --check for FirstTimeBuyer UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'FirstTimeBuyer')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]   
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )             
				  VALUES('UDF:','FirstTimeBuyer','Text','Are you a first time buyer',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			  --check for BankruptQuestion UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'BankruptQuestion')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]   
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )             
				  VALUES('UDF:','BankruptQuestion','Text','Has anyone ever obtained a judgement against you or been adjudicated bankrupt?',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			  --check for MarrigeQuestion UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'MarrigeQuestion')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]    
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )            
				  VALUES('UDF:','MarrigeQuestion','Text','If married please furnish a copy of your marriage certificate',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			  --check for DocuSignQuestion UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'DocuSignQuestion')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]    
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )            
				  VALUES('UDF:','DocuSignQuestion','Text','Have you ever signed document in the Irish equivalent of your names ?',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END

			   --check for PropPurchaseQuestion UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'PropPurchaseQuestion')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]   
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )             
				  VALUES('UDF:','PropPurchaseQuestion','Text','Anything else you feel we need to know regarding the purchase of this property?',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END
		
				 --check for LifeInsuranceQues UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'LifeInsuranceQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]     
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )           
				  VALUES('UDF:','LifeInsuranceQues','Text','Do you have life insurance in place ? If no please contact your broker or bank.',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END
		
				 --check for DocuSignQuestion UDF 
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[SystemUserDefinedFields] SUF WHERE SUF.[FILEPREFIX] = 'UDF:' AND SUF.[FIELDNAME] = 'HomeInsuranceQues')
			  BEGIN        
				  INSERT
				  INTO [dbo].[SystemUserDefinedFields]    
				  				  (
				   [FILEPREFIX]
				  ,[FIELDNAME]
				  ,[TYPE]
				  ,[PROMPT]
				  ,[LENGTH]
				  ,[DECMIAL]
				  ,[FORMAT]
				  ,[INITIALVALUE]
				  ,[CALCINITIALVALUE]
				  ,[OVERRIDEINITIALVALUE]
				  ,[REQUIRED]
				  ,[VALIDATION]
				  ,[LOW]
				  ,[HIGH]
				  ,[SYSTEMFIELD]
				  ,[TOP]
				  ,[LEFT]
				  ,[HEIGHT]
				  ,[WIDTH]
				  ,[FONT]
				  ,[VISIBLE]
				  ,[ENABLED]
				  ,[READONLY]
				  ,[MULTILINE]
				  ,[ACTUALNAME]
				  ,[COMMON]
				  ,[UDFCategory]
				  ,[ISLOCK]
				  ,[SQLSTATEMENT]
				  )            
				  VALUES('UDF:','HomeInsuranceQues','Text','Do you have home insurance in place ? If no please contact your broker or bank.',
						200,0,'','','','','N','Y',0,0,'N',0,0,0, 0,'','Y','Y','N','N','',0,0,0,'')
			  END



			  --Inserting in Default case plan 

			   --SellerBankruptQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'IncludedInSale' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('IncludedInSale','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --PropertyAddress--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'PropertyAddress' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('PropertyAddress','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --PropertyEIRCode--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'PropertyEIRCode' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('PropertyEIRCode','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --PropertyGuidePrice--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'PropertyGuidePrice' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('PropertyGuidePrice','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --PropertySalePrice--
			    

					   IF NOT EXISTS
					  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'PropertyPurchasePrice' AND [UDWKCODE]=@CasePlan)
					  BEGIN
					   INSERT
							INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
						  VALUES('PropertyPurchasePrice','UDF:',@CasePlan,
						  ( 
						  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
							  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
							 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
						   )
	    
					  

				END

			   --PropertyType--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'PropertyType' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('PropertyType','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END
			  --TransDepositAmount
			  IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'TransDepositAmount' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('TransDepositAmount','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			  --TransClosingDate
			  IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'TransClosingDate' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('TransClosingDate','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

	  
			  --SellerBankruptQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerBankruptQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerBankruptQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			  --SellerPurchasedQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerPurchasedQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerPurchasedQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			  --SellerThingsAvailQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerThingsAvailQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerThingsAvailQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			  --SellerPropertyBuiltQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerPropertyBuiltQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerPropertyBuiltQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			  --SellerMortageQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerMortageQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerMortageQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerClaimQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerClaimQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerClaimQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			  --SellerCourtQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerCourtQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerCourtQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			  --SellerIrishEquivalentQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerIrishEquivalentQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerIrishEquivalentQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			  --SellerServicesQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerServicesQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerServicesQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			  --SellerChargedQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerChargedQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerChargedQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerMarriageQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerMarriageQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerMarriageQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerDivorcedQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerDivorcedQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerDivorcedQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerEngagedQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerEngagedQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerEngagedQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerContributionQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerContributionQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerContributionQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerLPTTaxQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerLPTTaxQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerLPTTaxQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerLPTPinQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerLPTPinQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerLPTPinQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerRatingQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerRatingQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerRatingQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerAlarmQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerAlarmQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerAlarmQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerRentQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerRentQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerRentQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --SellerNPPRchargesQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'SellerNPPRchargesQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('SellerNPPRchargesQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --PrimaryFamilyHome--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'PrimaryFamilyHome' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('PrimaryFamilyHome','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --FirstTimeBuyer--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'FirstTimeBuyer' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('FirstTimeBuyer','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --BankruptQuestion--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'BankruptQuestion' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('BankruptQuestion','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --MarrigeQuestion--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'MarrigeQuestion' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('MarrigeQuestion','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --DocuSignQuestion--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'DocuSignQuestion' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('DocuSignQuestion','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --PropPurchaseQuestion--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'PropPurchaseQuestion' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('PropPurchaseQuestion','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --LifeInsuranceQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'LifeInsuranceQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('LifeInsuranceQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END

			   --HomeInsuranceQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[UDFTemplatesWorkTypes] UDF WHERE UDF.[UDFILE] = 'UDF:' AND UDF.[UDUVCODE] = 'HomeInsuranceQues' AND [UDWKCODE]=@CasePlan)
			  BEGIN
			   INSERT
					INTO [dbo].[UDFTemplatesWorkTypes]([UDUVCODE],[UDFILE],[UDWKCODE],[UDSEQ],[IncludeInMatterCopy],[Required])
				  VALUES('HomeInsuranceQues','UDF:',@CasePlan,
				  ( 
				  SELECT CASE WHEN COUNT(1) > 0 THEN MAX( [UTW].[UDSEQ])+1 ELSE 1 END                   
					  FROM [dbo].[UDFTemplatesWorkTypes] [UTW]
					 WHERE [UTW].[UDWKCODE] = @CasePlan),NULL,NULL              
				   )
	    
			  END
	
			  --Inserting UDF field mapping 

			   --for UDFIncludedInSale--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'UDFIncludedInSale')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				 values ('UDFIncludedInSale','IncludedInSale',0,'C')
	    
			  END

			   --for UDFPropertyAddress--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'UDFPropertyAddress')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				   values ('UDFPropertyAddress','PropertyAddress',0,'C')
	    
			  END

			   --for UDFPropertyEIRCode--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'UDFPropertyEIRCode')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('UDFPropertyEIRCode','PropertyEIRCode',0,'C')
	    
			  END

			   --for UDFPropertyGuidePrice--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'UDFPropertyGuidePrice')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('UDFPropertyGuidePrice','PropertyGuidePrice',0,'C')
	    
			  END

			   --for UDFPropertySalePrice--
			   
				
			  

				   IF NOT EXISTS
				  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'UDFPropertyPurchasePrice')
				  BEGIN
					INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('UDFPropertyPurchasePrice','PropertyPurchasePrice',0,'C')
	    
				  END
			  

			   --for UDFPropertyType--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'UDFPropertyType')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('UDFPropertyType','PropertyType',0,'C')
	    
			  END

			  --for UDFTransDepositAmount--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'UDFTransDepositAmount')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('UDFTransDepositAmount','TransDepositAmount',0,'C')
	    
			  END

			  --for UDFTransClosingDate--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'UDFTransClosingDate')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('UDFTransClosingDate','TransClosingDate',0,'C')
	    
			  END


			  --for TransactQuestId01--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId01')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId01','SellerBankruptQues',0,'S')
	    
			  END

			   --for TransactQuestId02 SellerPurchasedQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId02')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId02','SellerPurchasedQues',0,'S')
	    
			  END

			   --for TransactQuestId03 SellerThingsAvailQues--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId03')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId03','SellerThingsAvailQues',0,'S')
	    
			  END

			   --for TransactQuestId04 --
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId04')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId04','SellerPropertyBuiltQues',0,'S')
	    
			  END

			   --for TransactQuestId05--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId05')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId05','SellerMortageQues',0,'S')
	    
			  END

			   --for TransactQuestId06--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId06')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId06','SellerClaimQues',0,'S')
	    
			  END

			   --for TransactQuestId07--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId07')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId07','SellerCourtQues',0,'S')
	    
			  END

			   --for TransactQuestId08--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId08')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId08','SellerIrishEquivalentQues',0,'S')
	    
			  END

			   --for TransactQuestId09--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId09')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId09','SellerServicesQues',0,'S')
	    
			  END

			   --for TransactQuestId10--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId10')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId10','SellerChargedQues',0,'S')
	    
			  END

			   --for TransactQuestId11--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId11')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId11','SellerMarriageQues',0,'S')
	    
			  END

			   --for TransactQuestId12--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId12')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId12','SellerDivorcedQues',0,'S')
	    
			  END

			   --for TransactQuestId13--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId13')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId13','SellerEngagedQues',0,'S')
	    
			  END

			   --for TransactQuestId14--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId14')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId14','SellerContributionQues',0,'S')
	    
			  END

			   --for TransactQuestId15--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId15')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId15','SellerLPTTaxQues',0,'S')
	    
			  END

			   --for TransactQuestId16--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId16')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId16','SellerLPTPinQues',0,'S')
	    
			  END

			   --for TransactQuestId17--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId17')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId17','SellerRatingQues',0,'S')
	    
			  END

			   --for TransactQuestId18--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId18')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId18','SellerAlarmQues',0,'S')
	    
			  END

			   --for TransactQuestId19--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId19')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId19','SellerRentQues',0,'S')
	    
			  END

			   --for TransactQuestId20--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId20')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId20','SellerNPPRchargesQues',0,'S')
	    
			  END

			   --for TransactQuestId21--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId21')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId21','FirstTimeBuyer',0,'P')
	    
			  END

				--for TransactQuestId22--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId22')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId22','PrimaryFamilyHome',0,'P')
	    
			  END

				--for TransactQuestId23--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId23')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId23','BankruptQuestion',0,'P')
	    
			  END

				--for TransactQuestId24--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId24')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId24','MarrigeQuestion',0,'P')
	    
			  END

				--for TransactQuestId25--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId25')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId25','DocuSignQuestion',0,'P')
	    
			  END

				----for TransactQuestId26--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId26')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId26','PropPurchaseQuestion',0,'P')
	    
			  END

				--for TransactQuestId27--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId27')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId27','LifeInsuranceQues',0,'P')
	    
			  END

				--for TransactQuestId28--
			   IF NOT EXISTS
			  (SELECT TOP 1 1 FROM [dbo].[TransactUDFFieldMapping] TD WHERE TD.TransactUDFField = 'TransactQuestId28')
			  BEGIN
				INSERT into   [dbo].[TransactUDFFieldMapping](
				[TransactUDFField]
				  ,[KeyhouseUDFField]
				  ,[IsRetired]
				  ,[TransactSide])
				    values ('TransactQuestId28','HomeInsuranceQues',0,'P')
	    
			  END

		
			


  END
	


END

  
GO
--To create defaut UDF and mapping for transact new case wizard 
exec TRANS_InsertDefaultTransactUDFQuestions

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_TR_LogTransactNote',N'TR')IS NOT NULL
	DROP TRIGGER [dbo].[TRANS_TR_LogTransactNote]
GO

IF EXISTS (SELECT TOP 1 1 FROM [dbo].[TransactViewProperty])
BEGIN EXEC('
CREATE TRIGGER 
       [dbo].[TRANS_TR_LogTransactNote] 
    ON [dbo].[diary] 
       AFTER INSERT, UPDATE
/*******************************************************************************************************
* This trigger is used to log in transact note map table if a diary item is added or edited 		   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[TRANS_TR_LogTransactNote]								   			   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2022-04-22	Aakif		Created																	   *
* 2022-04-27	Aakif		Updated trigger to handle bulk inserts & create trigger only for those	   *
*							Tenants who have transact property linked								   *
*******************************************************************************************************/

AS
BEGIN
SET NOCOUNT ON
IF EXISTS
  (SELECT TOP 1 1
     FROM [inserted] [INS]
          INNER JOIN [dbo].[TransactViewProperty] [TVP]
                     WITH (NOLOCK)
                  ON [TVP].[MatterCode] = [INS].[CASECODE]
    WHERE [INS].[PUBLISH] = ''P'')
BEGIN
  INSERT
    INTO [dbo].[TransactNoteUpdate]
        ([ActionId],
         [ChangeDate])
  SELECT [INS].[ACTIONID],
         GETUTCDATE()
    FROM [inserted] [INS]
         INNER JOIN [dbo].[TransactViewProperty] [TVP]
                    WITH (NOLOCK)
                 ON [TVP].[MatterCode] = [INS].[CASECODE]
   WHERE [INS].[PUBLISH] = ''P''
END

SET NOCOUNT OFF
END')
END
GO


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

CREATE PROCEDURE 
	[dbo].[TRANS_UpdateTransactActionList]
	(@ActionIdList VARCHAR(500))
AS

/*******************************************************************************************************
*                                                                                                      *
* Stored Procedure Name : [dbo].[TRANS_UpdateTransactActionList]										   *
* Description: To update all actions that are published and mapped to transact property				   *
*                                                                                                      *
* Modification History:                                                                                *
* 2022-04-20    Aakif        Created                                                                   *
 *******************************************************************************************************/

BEGIN

	UPDATE [dbo].[TransactNoteUpdate]
	   SET [TransactUpdate] = GETUTCDATE()
	 WHERE [TransactUpdate] IS NULL
	   AND [ActionId] IN (
		SELECT [id]
		  FROM [dbo].[KAAS_FN_CSVToTable](@ActionIdList)
	   )

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)			,
 @SearchText	VARCHAR(4000)		=	'',
 @PageSize      INT				=	NULL       
)      
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																   *
* 2022-06-10    Sadiq        Added COndition to get only Page size records when page size is Passed    *
 *******************************************************************************************************/    
BEGIN      
 SET NOCOUNT ON      
       
      
        
  EXEC  KAAS_GetContactsByHandlerPrivilege  @LoginHandlerCode ,@SearchText,@PageSize    
      
  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'  

    SELECT KeyValue AS [TransactNewCaseDefaultPartner]
    FROM Settings    
   WHERE [KeyName]  = 'TransactNewCaseDefaultPartner'  
            
 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