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

CREATE PROCEDURE 
	[dbo].[KAAS_CheckIfBatchHExists]  
	( 
		@BatchNo varchar(50)
	)

AS

/*******************************************************************************************************
* Check if Batch header exists                                                                         * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CheckIfBatchHExists]        								       *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-08    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON  

 IF NOT EXISTS
	(
		SELECT TOP 1 * 
		FROM dbo.[BatchH] 
		WHERE [BatchNo] = @BatchNo
	)
    BEGIN
		DELETE FROM 
			dbo.[BatchDetails] 
		WHERE 
			[BatchNo] =  @BatchNo  
		 
	END 
 

 SET NOCOUNT OFF


 END

 GO


 


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

CREATE PROCEDURE 
	[dbo].[KAAS_CopyBatchDetails]
	(
		@KeyId decimal
	)

AS

/*******************************************************************************************************
* Copy existing batch details                                                                          * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CopyBatchDetails]          								       *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-04-10    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN
  SET NOCOUNT ON 

DECLARE @PREF int 
SET @PREF = (SELECT 
	MAX(PREF)+1 PREF
FROM 
(
  (
	SELECT 
		PREF, 
		BATCHNO 
	FROM DBO.BATCHH 
  )
 UNION ALL
  (
	SELECT 
		PREF, 
		BATCHNO 
	FROM DBO.BATCHDETAILS
  )
) BAT)


INSERT INTO 
	[dbo].[BatchDetails] 
		(
			[BATCHNO], 
			[TYPE], 
			[CODE], 
			[PREF], 
			[REF], 
			[DATE], 
			[FEE], 
			[BRANCH], 
			[NARR], 
			[MATTER], 
			[SUPP], 
			[VALUE], 
			[VATCODE], 
			[VATVAL], 
			[CLIENTYN], 
			[OUTLAY], 
			[PENDING], 
			[YEAR], 
			[PERNO], 
			[ENTRYCURRENCY], 
			[CURRENCYVALUE], 
			[CURRENCYVAT], 
			[OUTLAYCODE], 
			[THIRDPARTY], 
			[PAYEE], 
			[HEADPREF], 
			[HEADBATCH], 
			[ALLOCBATCH], 
			[ALLOCPREF], 
			[ALLOCREF], 
			[ALLOCVALUE], 
			[ALLOCWRITEOFF], 
			[PSUPP], 
			[UNDETAKING], 
			[CaseAssCode], 
			[OnceOffPayment], 
			[PayClient], 
			[ClientCode], 
			[EFTEmailYorN], 
			[RegisteredPost]
		)
		SELECT 
			[BATCHNO], 
			[TYPE], 
			[CODE], 
			@PREF, 
			[REF], 
			[DATE], 
			[FEE], 
			[BRANCH], 
			[NARR], 
			[MATTER], 
			[SUPP], 
			[VALUE], 
			[VATCODE], 
			[VATVAL], 
			[CLIENTYN], 
			[OUTLAY], 
			[PENDING], 
			[YEAR], 
			[PERNO], 
			[ENTRYCURRENCY], 
			[CURRENCYVALUE], 
			[CURRENCYVAT], 
			[OUTLAYCODE], 
			[THIRDPARTY], 
			[PAYEE], 
			[HEADPREF], 
			[HEADBATCH], 
			[ALLOCBATCH], 
			[ALLOCPREF], 
			[ALLOCREF], 
			[ALLOCVALUE], 
			[ALLOCWRITEOFF], 
			[PSUPP], 
			[UNDETAKING], 
			[CaseAssCode], 
			[OnceOffPayment], 
			[PayClient], 
			[ClientCode], 
			[EFTEmailYorN], 
			[ALLOCBATCH]
		FROM 
			[dbo].[BatchDetails] 
			WHERE [KEYID] = @KeyId


 
 SET NOCOUNT OFF


 END


 GO



IF OBJECT_ID(N'KAAS_CreateBatch',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CreateBatch]
GO
CREATE PROCEDURE [dbo].[KAAS_CreateBatch]
	(
		@BATCHNO int,
		@POSTFWD char(1) = '',
		@TYPE char(1),
		@CODE char(10),
		@BRANCH varchar(3)='',
		@PREF int,
		@REF char(10),
		@DATE datetime,
		@FEE varchar(10) = '',
		@MATTER varchar(20) = '',
		@SUPP char(10) = '',
		@NARR char(150),
		@VALUE decimal(17,2),
		@SUBTOT decimal(17,2) = 0.00,
		@POSTED char(1),
		@CLIENTAC char(1),
		@PERNO smallint,
		@YEAR smallint,
		@PAYEE char(60) ='',
		@OUTLAYTOT decimal(17,2) = 0.00,
		@VATTOT decimal(17,2) = 0.00,
		@FEETOT decimal(17,2) = 0.00,
		@INVCR char(1),
		@THIRDPARTY char(1) = '',
		@USERCODE char(8),
		@ENTRYDATE datetime,
		@CLIENTBANK char(10) = '',
		@CURRENCY char(1)='E',
		@CURRENCYVALUE decimal(17,2) = 236.27,
		@CUROUTLAYTOT decimal(17,2) = 0.00,
		@CURVATTOT decimal(17,2) = 0.00,
		@CURFEETOT decimal(17,2) = 0.00,
		@RECNO int = 0,
		@SINGLELINE char(1) = '',
		@TEMPLATE char(10) = '',
		@CHEQUEREQNO int = 0,
		@WRITEDOWN datetime = null,
		@WRITEBACKHOURS decimal(7,2) = 0.00,
		@WRITEBACKVALUE decimal(13,2) = 0.00,
		@CORRECTCOPY char(1) = '',
		@CREDITINVOICENO int = 0,
		@DRAFTBILLNO int = 0,
		@EFTSENT char(1) = 'N',
		@EFTDATE datetime = null,
		@EFTFILENUMBER int = null,
		@EFTFILENAME varchar(500) = null,
		@EFTYORN char(1) = 'N',
		@CLIENTCODE varchar(10) = null,
		@PAYCLIENT char(1)= 'N',
		@ONCEOFFPAYMENT char(1)= 'N',
		@CASEASSCODE varchar(6) = null,
		@EFTEMAILYORN char(1)= 'N',
		@FNCODE VARCHAR(10) = NULL 
	) 

AS

/*******************************************************************************************************
* Save the Batch Header                                                                                * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CreateBatch]   			            					       *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-04-09    Revathy D        Created                                                               *
* 2021-04-12    Revathy D        Updated                                                               *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON 

IF EXISTS
	(
		SELECT TOP 1 * 
		FROM dbo.[BatchH] 
		WHERE [BatchNo] = @BATCHNO
	)

   UPDATE dbo.[Batchh] 
	 SET 
		[BATCHNO] = @BATCHNO,
		[POSTFWD] = @POSTFWD,
		[TYPE] = @TYPE,
		[CODE] = @CODE,
		[BRANCH] = @BRANCH,
		[PREF] = @PREF,
		[REF] = @REF,
		[DATE] = dbo.KEYHM_FNConvertDateToUTC(@DATE, @FNCODE),
		[FEE] = @FEE,
		[MATTER] = @MATTER,
		[SUPP] = @SUPP,
		[NARR] = @NARR,
		[VALUE] = @VALUE,
		[SUBTOT] = @SUBTOT,
		[POSTED] = @POSTED,
		[CLIENTAC] = @CLIENTAC,
		[PERNO] = @PERNO,
		[YEAR] = @YEAR,
		[PAYEE] = @PAYEE,
		[OUTLAYTOT] = @OUTLAYTOT,
		[VATTOT] = @VATTOT,
		[FEETOT] = @FEETOT,
		[INVCR] = @INVCR,
		[THIRDPARTY] = @THIRDPARTY,
		[USERCODE]= @USERCODE,
		[ENTRYDATE] = dbo.KEYHM_FNConvertDateToUTC(@ENTRYDATE, @FNCODE),
		[CLIENTBANK] = @CLIENTBANK,
		[CURRENCY] = @CURRENCY,
		[CURRENCYVALUE] = @CURRENCYVALUE,
		[CUROUTLAYTOT] = @CUROUTLAYTOT,
		[CURVATTOT]= @CURVATTOT,
		[CURFEETOT] = @CURFEETOT,
		[RECNO] = @RECNO,
		[SINGLELINE] =@SINGLELINE,
		[TEMPLATE] = @TEMPLATE,
		[CHEQUEREQNO] = @CHEQUEREQNO,
		[WRITEDOWN] = @WRITEDOWN,
		[WRITEBACKHOURS] = @WRITEBACKHOURS,
		[WRITEBACKVALUE] = @WRITEBACKVALUE,
		[CORRECTCOPY] = @CORRECTCOPY,
		[CREDITINVOICENO]= @CREDITINVOICENO,
		[DRAFTBILLNO] = @DRAFTBILLNO,
		[EFTSENT] = @EFTSENT,
		[EFTDATE] = @EFTDATE,
		[EFTFILENUMBER] = @EFTFILENUMBER,
		[EFTFILENAME]= @EFTFILENAME,
		[EFTYORN]=@EFTYORN,
		[CLIENTCODE]=@CLIENTCODE,
		[PAYCLIENT]=@PAYCLIENT,
		[ONCEOFFPAYMENT]=@ONCEOFFPAYMENT,
		[CASEASSCODE]=@CASEASSCODE,
		[EFTEMAILYORN] = @EFTEMAILYORN 
	 WHERE [BatchNo] = @BATCHNO 

ELSE 

	INSERT INTO dbo.[BatchH] 
		(
		[BATCHNO],
		[POSTFWD],
		[TYPE],
		[CODE],
		[BRANCH],
		[PREF],
		[REF],
		[DATE],
		[FEE],
		[MATTER],
		[SUPP],
		[NARR],
		[VALUE],
		[SUBTOT],
		[POSTED],
		[CLIENTAC],
		[PERNO],
		[YEAR],
		[PAYEE],
		[OUTLAYTOT],
		[VATTOT],
		[FEETOT],
		[INVCR],
		[THIRDPARTY],
		[USERCODE],
		[ENTRYDATE],
		[CLIENTBANK],
		[CURRENCY],
		[CURRENCYVALUE],
		[CUROUTLAYTOT],
		[CURVATTOT],
		[CURFEETOT],
		[RECNO],
		[SINGLELINE],
		[TEMPLATE],
		[CHEQUEREQNO],
		[WRITEDOWN],
		[WRITEBACKHOURS],
		[WRITEBACKVALUE],
		[CORRECTCOPY],
		[CREDITINVOICENO],
		[DRAFTBILLNO],
		[EFTSENT],
		[EFTDATE],
		[EFTFILENUMBER],
		[EFTFILENAME],
		[EFTYORN],
		[CLIENTCODE],
		[PAYCLIENT],
		[ONCEOFFPAYMENT],
		[CASEASSCODE],
		[EFTEMAILYORN]) 
	VALUES 
		(
		@BATCHNO,
		@POSTFWD,
		@TYPE,
		@CODE,
		@BRANCH,
		@PREF,
		@REF,
		dbo.KEYHM_FNConvertDateToUTC(@DATE, @FNCODE),
		@FEE,
		@MATTER,
		@SUPP,
		@NARR,
		@VALUE,
		@SUBTOT,
		@POSTED,
		@CLIENTAC,
		@PERNO,
		@YEAR,
		@PAYEE,
		@OUTLAYTOT,
		@VATTOT,
		@FEETOT,
		@INVCR,
		@THIRDPARTY,
		@USERCODE,
		dbo.KEYHM_FNConvertDateToUTC(@ENTRYDATE, @FNCODE),
		@CLIENTBANK,
		@CURRENCY,
		@CURRENCYVALUE,
		@CUROUTLAYTOT,
		@CURVATTOT,
		@CURFEETOT,
		@RECNO,
		@SINGLELINE,
		@TEMPLATE,
		@CHEQUEREQNO,
		@WRITEDOWN,
		@WRITEBACKHOURS,
		@WRITEBACKVALUE,
		@CORRECTCOPY,
		@CREDITINVOICENO,
		@DRAFTBILLNO,
		@EFTSENT,
		@EFTDATE,
		@EFTFILENUMBER,
		@EFTFILENAME,
		@EFTYORN,
		@CLIENTCODE,
		@PAYCLIENT,
		@ONCEOFFPAYMENT,
		@CASEASSCODE,
		@EFTEMAILYORN 
		)

SET NOCOUNT OFF

END 



GO



IF OBJECT_ID(N'KAAS_CreateBatchDetails',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_CreateBatchDetails]
GO
CREATE PROCEDURE 
	[dbo].[KAAS_CreateBatchDetails]
		(
		    @KEYID int,
			@BATCHNO int,	       
			@PREF int,
			@REF char(10),
			@DATE datetime, 
			@NARR char(100),
			@MATTER varchar(20),
			@VALUE decimal(17,2),
			@VATCODE char(1),
			@VATVAL decimal(17,2) = 0.00,			
			@YEAR smallint,
			@PERNO smallint,
			@CODE char(10)='',
            @ALLOCREF char(10)='',
            @ALLOCVALUE decimal(17,2) =0.00,
			@ALLOCBATCH int,
            @ALLOCPREF int,
            @OUTLAY char(1),
            @ALLOCWRITEOFF char(1) ='',
			@FNCODE VARCHAR(10) = NULL ,
			@TYPE char(1)='R'			
		) 
		 
AS

/*******************************************************************************************************
* Save the Batch Details                                                                               * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_CreateBatchDetails]   			            				   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-04-10    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON 
 
 --DECLARE @TYPE char(1)='R'  
 DECLARE @ENTRYCURRENCY char(1)='E'
 DECLARE @CURRENCYVALUE decimal(17,2)=393.78
 DECLARE @CURRENCYVAT decimal(17,2) = 0.00 
 DECLARE @CASEASSCODE varchar(6) =''
 DECLARE @ONCEOFFPAYMENT char(1) ='N'
 DECLARE @PAYCLIENT char(1)='N'
 DECLARE @CLIENTCODE varchar(10)=''
 DECLARE @EFTEMAILYORN char(1)='N'

SET @CODE = ( 
		SELECT 
			CASE WHEN @OUTLAY = 'C' 
			     THEN ISNULL(CLIENT,'')
                 WHEN @OUTLAY = 'O' 
				 THEN ISNULL(OUTLAY,'')
				 ELSE @CODE 
           END   
		FROM DBO.[CONTROL]
		   )

IF EXISTS
	(
		SELECT TOP 1 * 
		FROM dbo.[BatchDetails] 
		WHERE [KeyId] = @KEYID
	)
BEGIN
   UPDATE dbo.[BatchDetails] 
	 SET 
		    [BATCHNO] = @BATCHNO,
			[TYPE] = @TYPE,
			[CODE] = @CODE,
			[PREF] =@PREF,
			[REF] =@REF,
			[DATE] = dbo.KEYHM_FNConvertDateToUTC(@DATE, @FNCODE),			
			[NARR] = @NARR,
			[MATTER] = @MATTER,
			[VALUE] = @VALUE,
			[VATCODE] = @VATCODE,
			[VATVAL]= @VATVAL,
			[OUTLAY]= @OUTLAY,
			[YEAR] = @YEAR,
			[PERNO] = @PERNO,
			[ENTRYCURRENCY]= @ENTRYCURRENCY,
			[CURRENCYVALUE]=@CURRENCYVALUE,
			[CURRENCYVAT] =@CURRENCYVAT,  
			[ALLOCREF]=@ALLOCREF,
			[ALLOCVALUE]= @ALLOCVALUE,
			[ALLOCBATCH] = @ALLOCBATCH,
            [ALLOCPREF] = @ALLOCPREF,
			[ALLOCWRITEOFF]=@ALLOCWRITEOFF, 
			[CASEASSCODE]=@CASEASSCODE,
			[ONCEOFFPAYMENT]=@ONCEOFFPAYMENT,
			[PAYCLIENT]=@PAYCLIENT,
			[CLIENTCODE]=@CLIENTCODE,
			[EFTEMAILYORN]=@EFTEMAILYORN
	 WHERE KEYID = @KEYID
END
ELSE
BEGIN

INSERT INTO 
	dbo.BATCHDETAILS 
		(
			[BATCHNO],
			[TYPE],
			[CODE],
			[PREF],
			[REF],
			[DATE],			
			[NARR],
			[MATTER],
			[VALUE],
			[VATCODE],
			[VATVAL],
			[OUTLAY],
			[YEAR],
			[PERNO],
			[ENTRYCURRENCY],
			[CURRENCYVALUE],
			[CURRENCYVAT], 
			[ALLOCREF],
			[ALLOCVALUE],
			[ALLOCBATCH],
            [ALLOCPREF],
			[ALLOCWRITEOFF],			 
			[CASEASSCODE],
			[ONCEOFFPAYMENT],
			[PAYCLIENT],
			[CLIENTCODE],
			[EFTEMAILYORN]
		) 
VALUES
		(   
			@BATCHNO,
	        @TYPE,
			@CODE,
			@PREF,
			@REF,
			@DATE,
			@NARR,
			@MATTER,
			@VALUE,
			@VATCODE,
			@VATVAL,
			@OUTLAY,
			@YEAR,
			@PERNO,
			@ENTRYCURRENCY,
			@CURRENCYVALUE,
			@CURRENCYVAT, 
			@ALLOCREF,
			@ALLOCVALUE,
			@ALLOCBATCH,
            @ALLOCPREF,
			@ALLOCWRITEOFF, 
			@CASEASSCODE,
			@ONCEOFFPAYMENT,
			@PAYCLIENT,
			@CLIENTCODE,
			@EFTEMAILYORN
		)
 END
 SET NOCOUNT OFF

 END


 GO



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

CREATE PROCEDURE 
	[dbo].[KAAS_DeleteBatchDetails]
	(
		@KeyId decimal
	)

AS

/*******************************************************************************************************
* Delete Batch Details                                                                                 * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_DeleteBatchDetails]        								       *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-04-10    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON  

 DELETE FROM 
	dbo.[BatchDetails] 
 WHERE 
	[KEYID] =  @KeyId 


 SET NOCOUNT OFF


 END


 GO



IF OBJECT_ID(N'KAAS_DeleteBatchHeader',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_DeleteBatchHeader]
GO
CREATE PROCEDURE 
	[dbo].[KAAS_DeleteBatchHeader]
	(
		@BatchNo varchar(50)
	)

AS

/*******************************************************************************************************
* Delete Batch Header                                                                                  * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_DeleteBatchHeader]   											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-04-10    Revathy D        Created                                                               *
 *******************************************************************************************************/


BEGIN
 SET NOCOUNT ON   


 DELETE FROM 
	dbo.BatchH 
 WHERE 
	BATCHNO =  @BatchNo 
 DELETE FROM 
	dbo.BatchDetails 
 WHERE 
	BATCHNO =  @BatchNo 

 SET NOCOUNT OFF


 END


 GO



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

go 

CREATE PROCEDURE [dbo].[Kaas_DeleteMatterledger] (@LRef INT,@Result	TINYINT OUTPUT) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  * Used to delete the matter ledger                                                                     *
  * Stored Procedure Name: [dbo].[Kaas_DeleteMatterledger]												 * 
  * Modification History:                                                                                *
  * 2021-04-12 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 
  DELETE FROM 
		[dbo].[MatterLedger]
	WHERE
	    [LREF]=@LRef
	 SET @Result = 1
  END 

go 




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

go 

CREATE PROCEDURE [dbo].[Kaas_DeleteUndertaking] 
(@RecordId INT,
 @MatterCode varchar(20),
 @Narrative varchar(max),
 @ClientMin varchar(30),
 @ActionId int,
 @DiaryMessage varchar(max),
 @LogMessage varchar(max),
 @Result	TINYINT OUTPUT) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  * Used to delete the matter ledger                                                                     *
  * Stored Procedure Name: [dbo].[Kaas_DeleteUndertaking]	115,'000001/0000','test','20.00',7489,'test','test',0											 * 
  * Modification History:                                                                                *
  * 2021-04-12 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 
  DECLARE @CurrDate datetime
  SET @CurrDate=GETDATE()
  DECLARE @TIMECONVERT INT
  SET @TIMECONVERT = [dbo].ky_ConvertTimeToClarion(GETDATE())

  DELETE FROM 
		[dbo].[Undertakings]
	WHERE
	    [RECORDID]=@RecordId
	 SET @Result = 1  
	end
	BEGIN
  EXEC KAAS_INSERTSAMMatterLedgerComment @MatterCode,@Narrative,@ClientMin,@CurrDate,@Result
  END
  BEGIN


  UPDATE 
		[dbo].[diary] 
	SET
		[DATE]=@CurrDate,
		[STATUS]=1,
		[TEXT1]=@DiaryMessage
	WHERE
		[ACTIONID]=@ActionId

	INSERT INTO 
		[dbo].[Log]
		([DATE],
		[USER],
		[EVENT])
	VALUES
		(@CurrDate,
		'admin',
		@LogMessage)

	INSERT INTO 
		[dbo].[DesktopMessages]
		([MESSAGE],
		[HEADING],
		[DATE],
		[TIME],
		[BUTTON],
		[DURATION],
		[NAME],
		[FLAG],
		[EXTRAS],
		[EXTRAL])
	VALUES
		(@LogMessage,
		'Undertaking Deleted',
		@TIMECONVERT,
		@TIMECONVERT,
		0,
		0,
		'admin',
		0,
		' ',
		0)
		
END
go
IF OBJECT_ID(N'KAAS_GetAssociatesTypes',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_GetAssociatesTypes]
GO
CREATE PROCEDURE 
       [dbo].[KAAS_GetAssociatesTypes] 

AS
/*******************************************************************************************************
* Fetches the case associate types                                                                     * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetAssociatesTypes]   							     		   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-04-09    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON 
	SELECT  
		RTRIM(ISNULL([ACT].[CODE],''))     AS [Code], 
		[ACT].[DESCRIPTION]                AS [Description]
	FROM 
		[dbo].[AssociateTypes] [ACT] 
	ORDER BY  
		[ACT].[CODE] ASC

 SET NOCOUNT OFF
END

GO





IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetBankDetails]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetBankDetails]
END
GO
/****** Object:  StoredProcedure [dbo].[KAAS_GetBankDetails]    Script Date: 23-03-2021 16:19:44 ******/
CREATE PROCEDURE [dbo].[KAAS_GetBankDetails]

AS

/*******************************************************************************************************
* Fetches the Bank details                                                                             * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetBankDetails]   											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-03-23    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN

SELECT  
  NOM.[Code], 
  NOM.[Desc], 
  NOM.[Type], 
  NOM.[Pb], 
  NOM.[Seq], 
  NOM.[Clientac]    AS [Client], 
  NOM.[Deposityn]   AS [Deposit], 
  NOM.[Deposittype] AS [DepType], 
  NOM.[BankBranch] 
FROM 
  dbo.[Nominal] NOM 
WHERE NOM.[TYPE] = 'BANK' 
  AND NOM.[RETIRED] <> 'Y' 
ORDER BY  
  NOM.[CODE] ASC


END

GO



IF  EXISTS 
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetBatchByNo]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetBatchByNo]
END	
GO
/****** Object:  StoredProcedure [dbo].[KAAS_GetBatchByNo]    Script Date: 19-03-2021 14:46:11 ******/
 
CREATE PROCEDURE [dbo].[KAAS_GetBatchByNo] 
(
@BatchNo int,
@handlerCode VARCHAR(10) = NULL
)

AS

/*******************************************************************************************************
* Fetches the batch details of unposted payments                                                       * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetBatchByNo]   	'4392','adm'								   *
*                                                                                                      *
* Modification History:                                                                                *
* 3 May 2021    Natarajan S        Created															   *                                          
 *******************************************************************************************************/

BEGIN

SET NOCOUNT ON

		   SELECT
				[BAT].[BATCHNO]			AS [BatchNo],
				[BAT].[TYPE]			AS [Type],
				[BAT].[CODE]			AS [Code],
				[BAT].[REF]				AS [Ref],
				[dbo].[KEYHM_FNConvertUTCDateToTargetTimeZone]([BAT].[Date], @handlerCode)  AS [Date],
				[BAT].[MATTER]			AS [Matter],
				[BAT].[SUPP]			AS [Supp],
				[BAT].[NARR]			AS [Narr],
				[BAT].[VALUE]			AS [Value],
				[BAT].[POSTED]			AS [Posted],
				[BAT].[PERNO]			AS [PerNo],
				[BAT].[YEAR]			AS [Year],
				[BAT].[ENTRYDATE]		AS [EntryDate],
				[BAT].[RECNO]			AS [RecNo],
				[BAT].[CHEQUEREQNO]		AS [ChequeReqNo],
				[BAT].[PREF]			AS [Pref],
				[BAT].[PAYEE]			AS [Payee],
				[dbo].[KEYHM_FNConvertUTCDateToTargetTimeZone]([BAT].[ENTRYDATE], @handlerCode)  AS [ENTRYDATE],
				[BAT].[USERCODE]		AS [UserCode],
				[BAT].[EFTYorN]			AS [EFTYorN],
				[BAT].[EFTSent]			AS [EFTSent],
				[BAT].[CLIENTAC]		AS [ClientAc],
				[BAT].[ThirdParty]		AS [Thirdparty],
				[BAT].[PayClient]		AS [PayClient],
				[BAT].[OnceOffPayment]		AS [OnceOffPayment],
				[BAT].[ClientCode]		AS [ClientCode]

		    FROM	
				[dbo].[BatchH] BAT 
		   WHERE 
				[BAT].[BATCHNO] = @BatchNo

SET NOCOUNT OFF

END

GO

IF  EXISTS 
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetBatchDetails]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetBatchDetails]
END	
GO
/****** Object:  StoredProcedure [dbo].[KAAS_GetBatchDetails]    Script Date: 19-03-2021 14:46:11 ******/
 
 CREATE PROCEDURE [dbo].[KAAS_GetBatchDetails]
(
@handlerCode VARCHAR(10) = NULL
)

AS

/*******************************************************************************************************
* Fetches the batch details of unposted receipts                                                       * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetBatchDetails]   											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-03-19    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN

SET NOCOUNT ON

SELECT 
  BAH.[BatchNo], 
  RTRIM(ISNULL(BAH.[Type],'')) AS [Type], 
  BAH.[Code], 
  RTRIM(ISNULL(BAH.[Ref],''))  AS [Ref], 
  dbo.KEYHM_FNConvertUTCDateToTargetTimeZone(BAH.[Date], @handlerCode)  AS [Date],  
  BAH.[Matter], 
  BAH.[Supp], 
  BAH.[Narr], 
  BAH.[Value], 
  BAH.[Posted], 
  BAH.[PerNo], 
  BAH.[Year],
  dbo.KEYHM_FNConvertUTCDateToTargetTimeZone(BAH.[EntryDate], @handlerCode) AS [EntryDate],
  BAH.[RecNo], 
  BAH.[ChequeReqNo],
  BAH.[UserCode],
  BAH.[Pref],
  BAH.[ClientAc]   AS ClientAc,
  BAH.[EFTYorN]    AS EFT,
  BAH.[CLIENTBANK] AS ClientBank,
  BAH.[ThirdParty] AS ThirdParty
FROM 
  dbo.[BatchH] BAH
WHERE BAH.[TYPE] = 'R' 
    AND BAH.[POSTED] = 'N'
ORDER BY 
  BAH.[POSTED] ASC, 
  BAH.[ENTRYDATE] ASC

SET NOCOUNT OFF

END
GO




IF OBJECT_ID(N'KAAS_GetCaseAssociatesNames',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_GetCaseAssociatesNames]
GO
CREATE PROCEDURE 
	   [dbo].[KAAS_GetCaseAssociatesNames] 
		(
			@TypeCode varchar(1000)
	    )

AS
/*******************************************************************************************************
* Fetches the case associate names                                                                     * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetCaseAssociatesNames]   								       *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-04-09    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON   

	SELECT  
		[ACV].[NAMECODE] AS Code,  
		CASE WHEN 
	 		RTRIM(ISNULL([CAN].[COMPANY],'')) <> ''    
				THEN RTRIM([CAN].[NAME]) + ' ('+ RTRIM([CAN].[COMPANY]) +')'    
	    		ELSE RTRIM([CAN].[NAME]) 
	 	END       	                        AS [NameNCompany],			 
		[CAN].[ADDRESS]                     AS [Address], 
		[CAN].[PHONENO]						AS [Phone], 
		[ACV].[CATEGORY]					AS [Category], 
		[ACV].[TYPECODE]					AS [Type], 
		[CAN].[NAME]						AS [Name],
		[CAN].[COMPANY]						AS [Company], 
		RTRIM(ISNULL([CAT].[NAMECODE],''))  AS [NAMECODE], 
		RTRIM(ISNULL([CAT].[TYPECODE],''))  AS [TYPECODE], 
		[CAN].[NOTES]						AS [Notes]  
	FROM  
		 [dbo].[AssociateCategoryView] [ACV] 
	  LEFT OUTER JOIN [dbo].[CaseAssociatesNames] [CAN] 
		   	   ON  [ACV].[NAMECODE]= [CAN].[CODE] 
	  LEFT OUTER JOIN [dbo].[CaseAssoicatesTypes] [CAT] 
		   	   ON  [ACV].[NAMECODE]= [CAT].[NAMECODE] AND  [ACV].[TYPECODE]= [CAT].[TYPECODE]
	WHERE 
	    UPPER([ACV].[TYPECODE]) = UPPER(@TypeCode)  
	ORDER BY  
		[ACV].[TYPECODE] ASC, 
		[ACV].[NAMECODE] ASC

 SET NOCOUNT OFF

END

GO



IF OBJECT_ID(N'KAAS_GetDateValidationControls',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_GetDateValidationControls]
GO
CREATE PROCEDURE [dbo].[KAAS_GetDateValidationControls] 
AS
/*******************************************************************************************************
* This sp is used to GET THE CONTROL setting    													                             * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetDateValidationControls]									                     *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-19    Revathy           Created                                                              *
* 2021-05-31    Revathy           Modified                                                             *
 *******************************************************************************************************/ 
BEGIN
	SET NOCOUNT ON;

SELECT 
  CASE WHEN [DateFutValidation] = ''  THEN 0 
  ELSE ISNULL([DateFutValidation],0) END  AS DateFutValidation, 
  CASE WHEN [DatePastValidation] = ''  THEN 0 
  ELSE ISNULL([DatePastValidation],0) END AS DatePastValidation,
  ISNULL(VAC.[Code],'')                   AS DefaultVATCode, 
  ISNULL(VAC.[Rate],0.00)                 AS DefaultVATRate,
  RTRIM(ISNULL(CNL.[Creditor],''))		  AS CreditorControlAc,
  RTRIM(ISNULL(CNL.[Client],''))  		  AS ClientControlAc						
FROM [dbo].[CONTROL] CNL
LEFT JOIN [dbo].[VATcodes] VAC ON CNL.[Vatexclude] = VAC.[Code]

	SET NOCOUNT OFF
END

GO


IF OBJECT_ID(N'KAAS_GetMatterValues',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_GetMatterValues]
GO
CREATE PROC dbo.[KAAS_GetMatterValues]
(
@MatterCode VARCHAR(20)
)
AS

/*******************************************************************************************************
* To fetch the matter billing details                                                                  * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetMatterValues]      	            					       *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-18    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN

DECLARE @RECORDCOUNT INT
SET @RECORDCOUNT = (SELECT count(*) FROM dbo.[MatterLedger] WHERE [MATTER] = @MatterCode) 
IF( @RECORDCOUNT > 0) 
BEGIN
	SELECT 
		SUM(MLR.[VALUEO]) OutlayAc,
		SUM(MLR.[VALUED]) BillingAc, 
		SUM(MLR.[VALUECC]) CurrentAc,		 
		SUM(MLR.[VALUECD]) DepositAc
	FROM 
		dbo.[MatterLedger] MLR 
	WHERE 
		MLR.[MATTER] = @MatterCode
	GROUP BY 
		MLR.[MATTER] 
END
ELSE
BEGIN
	SELECT 
		0.00 OutlayAc,
		0.00 BillingAc, 
		0.00 CurrentAc,
		0.00 DepositAc
END

END
GO

IF OBJECT_ID(N'KAAS_GetNextPrefNBatchNo',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_GetNextPrefNBatchNo]
GO
CREATE PROCEDURE [dbo].[KAAS_GetNextPrefNBatchNo]
(
@IsBatchHeader VARCHAR(1) = 'Y'
)
AS

/*******************************************************************************************************
* Fetches the next Posting ref and batch number                                                        * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetNextPrefNBatchNo]      								       *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-04-10    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON   

DECLARE @NEWBATCH INT
SET @NEWBATCH =   (SELECT LASTBATCH FROM control) +1
DECLARE @NEWPREF INT
SET @NEWPREF = ( SELECT LASTPREF FROM control) +1
IF(@IsBatchHeader = 'Y')
  BEGIN
	UPDATE 
		[dbo].[control] 
	SET
		LASTBATCH = @NEWBATCH,
		LASTPREF = @NEWPREF
  END
ELSE
  BEGIN 
	UPDATE 
		[dbo].[control] 
	SET 
		LASTPREF = @NEWPREF
  END

SELECT
	 [LASTBATCH]	AS [BatchNo],
	 [LASTPREF]		AS [Pref],
	 [CURPER]		AS [CurPer],
	 [YEAR]			AS [Year]
FROM 
	 [dbo].[control]

 SET NOCOUNT OFF


END


GO






IF OBJECT_ID(N'KAAS_GetNominalAccountsDetails', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[KAAS_GetNominalAccountsDetails]
END

GO
/****** Object:  StoredProcedure [dbo].[KAAS_GetNominalAccountsDetails]    Script Date: 4/30/2021 1:56:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[KAAS_GetNominalAccountsDetails]

AS

/*******************************************************************************************************
* Fetches the Nomonal details																		   *
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetNominalAccountsDetails]   									   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-04-28    Balamurugan C        Created														   *
* 2021-05-04    Balamurugan C		 Dropdown Values Added                                             *
 *******************************************************************************************************/

BEGIN

/*MASTER DETAILS FROM NOMINAL TABLE*/
	SELECT NOM.[CODE],
		   NOM.[DESC],
		   NOM.[TYPE],
		   NOM.[PB]
	FROM   dbo.Nominal NOM
	WHERE  ( NOM.[Retired] <> 'Y' )
	ORDER  BY NOM.[CODE] ASC 

/*STATIC DETAILS FOR NOMINAL TYPE DROPDOWN*/
	SELECT NOMTYPE.[NTYCODE],
		   NOMTYPE.[NTYDESC]
	FROM   dbo.NominalTypes NOMTYPE
	ORDER  BY NOMTYPE.[NTYCODE] ASC 

/*STATIC DETAILS FOR NOMINAL REQPORT SEQ*/
	SELECT NOMRPT.[CODE],
		   NOMRPT.[DESC],
		   NOMRPT.[PB]
	FROM   dbo.NominalReportSeq NOMRPT
	ORDER  BY NOMRPT.[CODE] ASC 

/*STATIC DETAILS FOR DEPOSIT TYPE*/
	SELECT DEPTYPE.[CODE],
		   DEPTYPE.[DESCRIPTION]
	FROM   dbo.DepositType DEPTYPE
	ORDER  BY DEPTYPE.[CODE] ASC 

END

GO

IF OBJECT_ID(N'KAAS_GetNominalBudgetDetails', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[KAAS_GetNominalBudgetDetails]
END

GO
/****** Object:  StoredProcedure [dbo].[KAAS_GetNominalBudgetDetails]    Script Date: 5/6/2021 10:17:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[KAAS_GetNominalBudgetDetails]
(
@year INT
)
AS

/*******************************************************************************************************
* Fetches the Nomonal Budget details																   *
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetNominalBudgetDetails]   									   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-06    Balamurugan C        Created														   *                                             
 *******************************************************************************************************/

BEGIN

/*GRID DETAILS FROM NOMINAL BUDGET*/
	SELECT NOM.[RecordID],
		   NOM.[Code],
		   NOM.[Branch],
		   NOM.[Year],
		   NOM.[Period],
		   NOM.[Budget]
	FROM   dbo.NominalBudgets NOM
	WHERE  NOM.[Year]=@year
	ORDER  BY NOM.[Code] ASC 

END

GO

IF OBJECT_ID(N'KAAS_GetNominalLedgerDetails', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[KAAS_GetNominalLedgerDetails]
END

GO
/****** Object:  StoredProcedure [dbo].[KAAS_GetNominalLedgerDetails]    Script Date: 5/17/2021 1:10:42 PM ******/

CREATE PROCEDURE [dbo].[KAAS_GetNominalLedgerDetails]
(
@year INT=0,
@code VARCHAR(10)='',
@flag VARCHAR(15)=''
)
AS

/*******************************************************************************************************
* Fetches the Nominal Ledger details																   *
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetNominalLedgerDetails]   									   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-14    Balamurugan C        Created														   *  
* 2021-05-14    Prabhu V             Added Type column to identity Bank type Account				   *                                             
 *******************************************************************************************************/
BEGIN

/*NOMINAL TYPE LIST*/
IF(@flag='NOMINALDETAILS')
	BEGIN
		SELECT 
			[Code],
			[Desc],
			[Type]
		FROM dbo.[Nominal]
		ORDER BY [Code]
	END

/*GRID DETAILS FROM NOMINAL LEDGER*/
IF(@flag='NOMINALLEDGER')
	BEGIN
		SELECT 
				NL.[Pref],
				NL.[Date],
				NL.[RecordId],
				NL.[Code],
				NL.[Ref],
				NL.[Narr],
				NL.[Matter],
				NL.[Supp],
				NL.[Value],
				NL.[BatchNo],
				NL.[Branch],
				NL.[Year],
				NL.[ClearedDate],
				BH.[Type]
		FROM dbo.[NominalLedger] NL
			LEFT JOIN [BatchH] BH ON
			BH.BATCHNO=NL.BATCHNO
		WHERE NL.[Code]=@code AND 
			  NL.[Year]=@year
		ORDER BY [Code]
	END
END

GO

 
IF  EXISTS 
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetNominalList]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetNominalList]
END	
GO
CREATE PROCEDURE [dbo].[KAAS_GetNominalList]

AS

/*******************************************************************************************************
* Fetches the Nominal list                                                                             * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetNominalList]      											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-03-23    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN

SELECT 
	NOM.[CODE] AS Code,
    NOM.[DESC] AS [Description]
FROM
	[dbo].[Nominal] NOM
ORDER BY
	NOM.[CODE] ASC


END

GO








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


CREATE PROCEDURE
        [dbo].[KAAS_GetOpenMatterDetails]
AS
/***********************************************************************
 
 * Fetching Open Matter Details
 *
 * 2021-03-10   Prabhu.V	  Created to Fetch the Open Matter details for SAM
 ***********************************************************************/
BEGIN
SELECT  A.[Code], 
		A.[ClientCode], 
		A.[Description], 
		A.[FECODE] As FeeCode, 
		A.[Dept], 
		A.[WTYPE] As WorkType, 
		A.[Branch], 
		A.[Comment], 
		A.[FileNum], 
		A.[ThirdPart], 
		A.[THPARTSOL], 
		A.[OldRef], 
		A.[PCode], 
		A.[PFECode], 
		A.[User1], 
		A.[User2], 
		A.[User3], 
		A.[FileColour], 
		A.[OUTFILENO], 
		A.[Closed], 
		B.[CODE], 
		B.[Name], 
		B.[Address], 
		B.[FE], 
		B.[CLIENT], 
		B.[CLIENTGROUP], 
		B.[OTHERREF], 
		B.[OPSISREF], 
		B.[CONTACTNO], 
		B.[COMPBILLONOFF], 
		B.[TAXTYPE] 
FROM   dbo.[Matters] A 
left join dbo.[RecentMatterList] R ON R.[MATTER] =  A.[Code] AND R.[FEE]= 'ADM'
LEFT OUTER JOIN dbo.[contacts] B ON  A.[CLIENTCODE]= B.[CODE]   
WHERE [Closed] <> 'Y'  
--GROUP BY [A].[Code]
ORDER BY R.[DateField] DESC
END
GO


IF  EXISTS 
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetPaymentBatches]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetPaymentBatches]
END	
GO
/****** Object:  StoredProcedure [dbo].[KAAS_GetPaymentBatches]    Script Date: 19-03-2021 14:46:11 ******/
 
CREATE PROCEDURE [dbo].[KAAS_GetPaymentBatches] 
(
@handlerCode VARCHAR(10) = NULL
)

AS

/*******************************************************************************************************
* Fetches the batch details of unposted payments                                                       * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetPaymentBatches]   											   *
*                                                                                                      *
* Modification History:                                                                                *
* 11 Mar 2021    Natarajan S        Created															   *
* 27-May-2021	 Balamurugan C		Modified-Add few columns                                           *
 *******************************************************************************************************/

BEGIN

SET NOCOUNT ON

		   SELECT
				[BAT].[BATCHNO]			AS [BatchNo],
				[BAT].[TYPE]			AS [Type],
				[BAT].[CODE]			AS [Code],
				[BAT].[REF]				AS [Ref],
				[dbo].[KEYHM_FNConvertUTCDateToTargetTimeZone]([BAT].[Date], @handlerCode)  AS [Date],
				[BAT].[MATTER]			AS [Matter],
				[BAT].[SUPP]			AS [Supp],
				[BAT].[NARR]			AS [Narr],
				[BAT].[VALUE]			AS [Value],
				[BAT].[POSTED]			AS [Posted],
				[BAT].[PERNO]			AS [PerNo],
				[BAT].[YEAR]			AS [Year],
				[BAT].[ENTRYDATE]		AS [EntryDate],
				[BAT].[RECNO]			AS [RecNo],
				[BAT].[CHEQUEREQNO]		AS [ChequeReqNo],
				[BAT].[PREF]			AS [Pref],
				[BAT].[PAYEE]			AS [Payee],
				[dbo].[KEYHM_FNConvertUTCDateToTargetTimeZone]([BAT].[ENTRYDATE], @handlerCode)  AS [ENTRYDATE],
				[BAT].[USERCODE]		AS [UserCode],
				[BAT].[EFTYorN]			AS [EFTYorN],
				[BAT].[EFTSent]			AS [EFTSent],
				[BAT].[ThirdParty]		AS [Thirdparty],
				[BAT].[PayClient]		AS [PayClient],
				[BAT].[OnceOffPayment]		AS [OnceOffPayment],
				[BAT].[ClientCode]		AS [ClientCode]

		    FROM	
				[dbo].[BatchH] BAT 
		   WHERE 
				[BAT].[TYPE] = 'P' AND  [BAT].[POSTED] = 'N'
		   ORDER BY
				[BAT].[POSTED] ASC,
				[BAT].[ENTRYDATE] ASC

SET NOCOUNT OFF

END

GO

IF OBJECT_ID(N'KAAS_GetPaymentClientList', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[KAAS_GetPaymentClientList]
END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[KAAS_GetPaymentClientList]


AS

/*******************************************************************************************************
* Fetches the Payment Screen -Client Details                                                                             * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetPaymentClientList]      									   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-20    Balamurugan.C        Created                                                               *
 *******************************************************************************************************/

BEGIN

SELECT 
		[Code],
		[OtherRef],
		[Name],
		[Address],
		[BankName],
		[BankSortCode],
		[BankAccNo],
		[Iban],
		[Bic]
FROM
	[dbo].[Contacts]
ORDER BY
		[Code] ASC


END

GO

IF OBJECT_ID(N'KAAS_GetPaymentSupplierList', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[KAAS_GetPaymentSupplierList]
END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[KAAS_GetPaymentSupplierList]
(
@thirdparty varchar(1)=''
)

AS

/*******************************************************************************************************
* Fetches the Payment Screen -Supplier Details                                                                             * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetPaymentSupplierList]      									   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-19    Balamurugan.C        Created                                                               *
 *******************************************************************************************************/

BEGIN

SELECT 
		[Code],
		[Name],
		[Address],
		[Nominal],
		[DeftpStatus],
		[BankName],
		[BankSortCode],
		[BankAccNo],
		[Iban],
		[Bic],
		[BankType],
		[Retired]
FROM
	[dbo].[Suppliers]
	WHERE [DeftpStatus] = CASE WHEN @thirdparty='Y' THEN 'Y' ELSE [DeftpStatus] END
ORDER BY
		[Code] ASC


END

GO

 
IF  EXISTS 
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetReceiptsDetails]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetReceiptsDetails]
END
GO
/****** Object:  StoredProcedure [dbo].[KAAS_GetReceiptsDetails]    Script Date: 24-03-2021 10:38:53 ******/
 CREATE PROCEDURE [dbo].[KAAS_GetReceiptsDetails]  
(@BatchNo INT,
 @handlerCode VARCHAR(10) = NULL)

AS

/*******************************************************************************************************
* Fetches the receipts list of specific batch                                                          * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetReceiptsDetails]  											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-03-24    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN

 SET NOCOUNT ON 

 SELECT  
	 BTD.[BatchNo], 
	 BTD.[Type], 
	 BTD.[Code], 
	 BTD.[Pref], 
	 BTD.[Ref], 
	 dbo.KEYHM_FNConvertUTCDateToTargetTimeZone(BTD.[Date], @handlerCode)  AS [Date], 
	 BTD.[Fee], 
	 BTD.[Narr], 
	 BTD.[Matter], 
	 BTD.[Supp], 
	 BTD.[Value], 
	 BTD.[VatCode],
	 BTD.[VatVal],
	 BTD.[Outlay], 
	 BTD.[Year], 
	 BTD.[PerNo], 
	 BTD.[OutlayCode], 
	 BTD.[HeadBatch], 
	 BTD.[AllocBatch], 
	 BTD.[AllocPref], 
	 BTD.[KeyId] ,
	 BTD.[ClientYN],
	 BTD.[AllocRef],
	 BTD.[AllocValue],
	 BTD.[AllocWriteOff]
 FROM 
	dbo.[BatchDetails] BTD 
 WHERE 
	BTD.[BATCHNO] = @BatchNo
 ORDER BY  
	BTD.[BATCHNO] ASC, 
	BTD.[PREF] ASC, 
	BTD.[KEYID] ASC
 
 SET NOCOUNT OFF 


 END

 GO





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

CREATE PROCEDURE 
	   [dbo].[KAAS_GetSAMCaseContactDetails] 
	   (
			@MatterCode VARCHAR(20)
	   ) 
AS 
/*******************************************************************************************************
* [dbo].[KAAS_GetSAMCaseContactDetails] '000001/0000'                                                              *
* Description: Used to Case contact details for matter code                                            *					  
* Modification History:                                                                                *
* 2021-04-09   Natarajan S        Created															   *      
* 2021-04-09   Arun V             Implemented coding standards                                         *      
*******************************************************************************************************/
BEGIN 
 SET NOCOUNT ON   
			SELECT  
				ROW_NUMBER() OVER(ORDER BY [CC].assignedcontact) AS ConNum,
				[CC].[CASECODE] [CaseCode], 
				[CC].[CONTYPE]  [ConType],
				[CC].[NAMECODE] [CaseContactsNameCode], 
				[CC].[SOLCODE] [CaseContactsSolCode], 
				[CC].[INSCODE] [CaseContactsInsCode], 
				[CC].[ASSIGNEDCONTACT] [AssignedContact], 
				[CA].[CODE] [CaseAssociateCode], 
				[CA].[TYPE] [CaseAssociateType], 
				[CA].[NAME]  + '(' + [CA].[COMPANY] + ')' AS [CaseAssociateName], 
				[CA].[COMPANY] [CaseAssociateCompany], 
				[CA].[ADDRESS] [CaseAssociateAddress], 
				[CA].[NAME] [CaseAssociateSearch], 
				[CA].[CONTACTNO] [CaseAssociateContactNo],
				[CA].[BANKNAME] [CaseAssociateBankName], 
				[CA].[BANKSORTCODE] [CaseAssociateBankSortCode], 
				[CA].[BANKACCNO] [CaseAssociateBankAccNo], 
				[CA].[IBAN] [CaseAssociateIBAN], 
				[CA].[BIC] [CaseAssociateBIC], 
				[AT].[CODE] [AssociateTypeCode], 
				[AT].[DESCRIPTION] [AssociateTypeDescription]
			FROM
				[dbo].[CaseContacts] CC
				LEFT OUTER JOIN [dbo].[CaseAssociatesNames] CA 
							 ON [CC].[NAMECODE]= [CA].[CODE] 
				LEFT OUTER JOIN [dbo].[AssociateTypes] AT 
							 ON  [CC].[CONTYPE]= AT.CODE  
			WHERE 
				[CC].[CASECODE] =@MatterCode
			ORDER BY
			    [CC].[CASECODE] ASC,
				[CC].[CONTYPE] ASC, 
				[CC].[CONNUM] ASC
			
SET NOCOUNT OFF
END 

GO



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

go 

CREATE PROCEDURE [dbo].KAAS_GetSAMClientAccountExpansion (@MatterCode VARCHAR(20)) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to list the matter ledger                                                                        *
  * Stored Procedure Name: [dbo].KAAS_GetSAMClientAccountExpansion	'000001/0000'											 * 
  * Modification History:                                                                                *
  * 2021-03-30 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 

	EXEC [dbo].KAAS_GetSAMHeaderDetails	@MatterCode

	SELECT 
	[Date],
	[REF] AS Ref,
	[NARR] AS Narr,  
	[ValueC], 
	[ValueCC],
	[ValueCD],
	[DepositType],
	[PRef]
	from dbo.MatterLedger 
	where MATTER= @MatterCode AND 
	FORMAT(- valuec, '0; (0)') > 0

  END 

go 


 
IF  EXISTS 
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetSAMDebtorsLedgerAllocation]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetSAMDebtorsLedgerAllocation]
END
GO
/****** Object:  StoredProcedure [dbo].[KAAS_GetSAMDebtorsLedgerAllocation]    Script Date: 25-03-2021 10:38:53 ******/
 CREATE PROCEDURE [dbo].[KAAS_GetSAMDebtorsLedgerAllocation]  
(@BatchNo int,
 @Pref int)

AS

/*******************************************************************************************************
* Fetches the Debtors Ledger Allocation by Batch No and Pref                                           * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetSAMDebtorsLedgerAllocation]     							   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-03-31    Prabhu V        Created                                                                *
 *******************************************************************************************************/

BEGIN

SELECT  A.[BatchNo], 
		A.[Pref], 
		A.[Date], 
		A.[ABatchNo], 
		A.[Oref], 
		A.[Value], 
		A.[Fees], 
		A.[Outlay], 
		A.[Vat], 
		A.[UserCode], 
		A.[AllNo], 
		A.[KeyID] 
FROM dbo.[Allocations] A 
WHERE A.[BatchNo] = @BatchNo 
AND  A.[Pref] = @Pref 
ORDER BY  A.[BatchNo] ASC, 
		  A.[Pref] ASC, 
		  A.[KeyID] ASC
END
GO
 
IF  EXISTS 
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetSAMDebtorsLedgerbyMatterCode]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetSAMDebtorsLedgerbyMatterCode]
END
GO
/****** Object:  StoredProcedure [dbo].[KAAS_GetSAMDebtorsLedgerbyMatterCode]    Script Date: 25-03-2021 10:38:53 ******/
 CREATE PROCEDURE [dbo].[KAAS_GetSAMDebtorsLedgerbyMatterCode]  
(@MatterCode Varchar(20))

AS

/*******************************************************************************************************
* Fetches the Debtors Ledger by Matter Code		                                                       * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetSAMDebtorsLedgerbyMatterCode]  											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-03-25    Prabhu V        Created                                                               *
 *******************************************************************************************************/

BEGIN

SELECT  A.[Matter], 
		A.[Client], 
		A.[Date], 
		A.[Ref], 
		A.[BatchNo], 
		A.[PRef], 
		A.[Narr], 
		A.[Original], 
		A.[OSValue], 
		A.[RecordID] ,
		S.[Name],
		S.[Description]
FROM dbo.[DebtorsLedger] A 
LEFT JOIN dbo.[SearchMatters] S On S.[Code] = A.[MATTER]
WHERE A.[Matter] = @MatterCode 
ORDER BY  A.[Matter] ASC, A.[Date] ASC, A.[RecordID] ASC

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

CREATE PROCEDURE 
	   [dbo].[KAAS_GetSAMFeeEarnerCodes]  
AS 
/*******************************************************************************************************
* [dbo].[KAAS_GetSAMFeeEarnerCodes]								                                      *
* Description: Get basic details to insert into diary information.                                    *					  
* Modification History:                                                                               *
* 2021-04-10   Natarajan S        Created															  *      
*******************************************************************************************************/
BEGIN 
 SET NOCOUNT ON   

			SELECT  
				[Code],
				
				[Name]
			
			 FROM 
				[dbo].FeeEarnerCodes A 
			 WHERE  
				[A].[Retired] <> 'Y' 
			 ORDER BY
				[Code] ASC

			 SELECT  
				[RECORDID] AS [RecordId],
				[STATUSDESC] AS [Status] 
			 FROM 
				[dbo].[UndertakingStatus]
			 ORDER BY  
				[STATUSDESC] ASC


 SET NOCOUNT OFF
END 

GO

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

go 

CREATE PROCEDURE [dbo].KAAS_GetSAMFNLDetails (@MatterCode VARCHAR(20)) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to Get FNL Details                                                                        *
  * Stored Procedure Name: [dbo].KAAS_GetSAMFNLDetails	'000001/0000'											 * 
  * Modification History:                                                                                *
  * 2021-03-30 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 
    
SELECT 
  OM.[Code] as [Code], 
   C.[Code] as [ClientCode], 
   C.[Name] as [ClientName], 
  OM.[Matter] AS [MatterCode], 
  OM.[Description] as [MatterName], 
   C.[Address] AS [ClientAddress], 
  OM.[FECode] AS [FECode], 
  OM.[FNL_DATE] AS [FNLDate], 
  OM.[FNL_NAME] AS [FNLName], 
  OM.[FNL_ADDRESS] AS [FNLAddress], 
  OM.[FNL_FEES] AS [FNLFees], 
  OM.[FNL_OUTLAY] AS [FNLOutlay], 
  OM.[FNL_VAT] AS [FNLVat], 
  OM.[FNL_TOTAL] AS [FNLTotal] 
FROM 
  dbo.OpenMattersONLY OM 
  INNER JOIN Contacts C ON OM.ClientCode = C.Code 
WHERE 
  OM.CODE = @MatterCode

  END 

GO 


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

go 

CREATE PROCEDURE [dbo].KAAS_GetSAMHeaderDetails (@MatterCode VARCHAR(20)) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to list the matter ledger                                                                        *
  * Stored Procedure Name: [dbo].KAAS_GetSAMHeaderDetails	'000001/0000'											 * 
  * Modification History:                                                                                *
  * 2021-03-30 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 
  select   
  OM.[Code] as [Code], 
   C.[Code] as [ClientCode], 
   C.[Name] as [ClientName], 
  OM.[Matter] AS [MatterCode], 
  OM.[Description] as [MatterName]

FROM 
  dbo.OpenMattersONLY OM 
  INNER JOIN Contacts C ON OM.ClientCode = C.Code 
WHERE 
  OM.CODE = @MatterCode



  END 

go 


IF  EXISTS 
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetSAMJournalBatches]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetSAMJournalBatches]
END	
GO
  
 CREATE PROCEDURE [dbo].[KAAS_GetSAMJournalBatches]
AS
/*******************************************************************************************************
* Fetches the journal batch details                                                                    * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetSAMJournalBatches] 										   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-05-10    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN

SET NOCOUNT ON


SELECT  
	BAH.[BATCHNO]     AS [BatchNo], 
	BAH.[TYPE]        AS [Type],
	BAH.[CODE]        AS [Code],
	BAH.[REF]         AS [Ref],
	BAH.[DATE]        AS [Date],
	BAH.[MATTER]      AS [Matter], 
	BAH.[SUPP]        AS [Supp],
	BAH.[NARR]        AS [Narr],
	BAH.[POSTED]      AS [Posted],
	BAH.[CLIENTAC]    AS [ClientAc],
	BAH.[PERNO]       AS [PerNo],
	BAH.[YEAR]        AS [Year],
	BAH.[ENTRYDATE]   AS [EntryDate],
	BAH.[RECNO]       AS [RecNo],
	BAH.[CHEQUEREQNO] AS [ChequeReqNo]
FROM dbo.BatchH BAH 
WHERE (  
	BAH.[TYPE] = 'J' 
AND BAH.[POSTED] = 'N' 
) 
 
ORDER BY  
	BAH.[POSTED] ASC, 
	BAH.[ENTRYDATE] ASC
SET NOCOUNT OFF

END

GO



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

go 

CREATE PROCEDURE [dbo].[Kaas_getsammatterledger] (@MatterCode VARCHAR(20)) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to list the matter ledger                                                                        *
  * Stored Procedure Name: [dbo].[KAAS_GetSAMMatterLedger]	'KEY001/0001'								 * 
  * Modification History:                                                                                *
  * 2021-03-11 Natarajan S        Created																 *      
  * 2021-03-11 Prabhu V           Modified -- Added BatchH table in left join to verify the type of Batch*      
  * 2021-03-11 Prabhu V           Modified -- Handling Row color from UI instead of fetching value		 *
											  using like search in BD script							 *  
  * 2021-05-19 Prabhu V           Modified -- Fetching ValueO as Outlay for matter Ledger page           * 
   *******************************************************************************************************/
  BEGIN 
   SET NOCOUNT ON   
     SELECT	 [MTL].[Matter], 
             [MTL].[BatchNo], 
             [MTL].[PRef], 
             [MTL].[Date], 
             [MTL].[Ref], 
             [MTL].[Narr], 
             [ValueD], 
             [ValueO] as Outlay, 
             [MTL].[Fee], 
             [MTL].[EntryDate], 
             [Client], 
             [ValueCC], 
             [ValueCD], 
             [LRef], 
             [Billed],
			 [Undertaking],
			 BH.Type

     FROM   [dbo].[matterledger] AS [MTL] 
	  INNER JOIN [dbo].[matters] [MT] 
		ON [MTL].[MATTER]=[MT].[Code] 
	  LEFT JOIN BatchH BH 
	    ON [MTL].[BATCHNO] = BH.[BATCHNO]
     WHERE  [MTL].[matter] = @MatterCode 
     ORDER  BY [MTL].[matter],[MTL].[DATE],[MTL].LREF ASC 

      SELECT [CLIENTMIN]
      FROM   dbo.openmattersonly 
      WHERE  [code] = @MatterCode 

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

go 

CREATE PROCEDURE [dbo].[KAAS_GetSAMMatterLedgerComment] (@MatterCode VARCHAR(20)) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to list the matter ledger                                                                        *
  * Stored Procedure Name: [dbo].[KAAS_GetSAMMatterLedgerComment]	'KEY001/0001'							 * 
  * Modification History:                                                                                *
  * 2021-03-11 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 
     IF( (SELECT top 1 1  FROM MatterLedger WHERE (MATTER = @MatterCode) AND ([BATCHNO] = 0) AND (NARR LIKE 'RESERVE%') AND ([PREF] = 0)) > 0)

	 BEGIN
		 SELECT 
		 [NARR] FROM MatterLedger WHERE 
		 (MATTER = @MatterCode) AND 
		 ([BATCHNO] = 0) AND 
		 ([NARR] LIKE 'RESERVE%') AND 
		 ([PREF] = 0)
		 ORDER BY [DATE] DESC
	 END
	  ELSE
	 BEGIN
		 SELECT [NARR] FROM MatterLedger WHERE 
		 ([BATCHNO] = 0) AND 
		 ([NARR] LIKE 'RESERVE%') AND 
		 ([PREF] = 0)
		 ORDER BY [LREF] DESC
	 END

  END 

GO


 IF OBJECT_ID(N'KAAS_GetSAMOutstandingInvoices',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_GetSAMOutstandingInvoices]
GO
CREATE PROCEDURE
       [dbo].[KAAS_GetSAMOutstandingInvoices]
      (@matter           VARCHAR(20))
AS
BEGIN
/*******************************************************************
 *
 * Get Outstanding Invoices for a matter
 * 2020-05-18   Revathy     Copied from KAAS_GetOutstandingInvoices
 *******************************************************************/
SELECT RTRIM(ISNULL(DTL.[MATTER], '')) AS [MatterCode],
           RTRIM(ISNULL(DTL.[TYPE], '')) AS [Type],
           RTRIM(ISNULL(DTL.[CLIENT], '')) AS [ClientCode],
           DTL.[DATE] As [Date],
           RTRIM(ISNULL(DTL.[REF], '')) AS [Ref],
           ISNULL(DTL.[BATCHNO], 0) AS [BatchNo],
           ISNULL(DTL.[PREF], 0) AS [Pref],
           ISNULL(DTL.[NARR], 0) AS [Narrative],
           ISNULL(DTL.[ORIGINAL], 0) AS [Original],
           ISNULL(DTL.[ORFEES], 0) AS [Original Fees],
           ISNULL(DTL.[OROUTLAY], 0) AS [Original Outlay],
           ISNULL(DTL.[ORVAT], 0) AS [Original VAT],
           ISNULL(DTL.[OSVALUE], 0) AS [OSValue],
           ISNULL(DTL.[OSFEES], 0) AS [Outstanding Fees],
           ISNULL(DTL.[OSOUTLAY], 0) AS [Outstanding Outlay],
           ISNULL(DTL.[OSVAT], 0) AS [Outstanding VAT],
           RTRIM(ISNULL(DTL.[VATCODE], '')) AS [VAT Code],
           ISNULL(DTL.[PER], 0) AS [Period],
           ISNULL(DTL.[YEAR], 0) AS [Year],
           RTRIM(ISNULL(DTL.[FEECODE], '')) AS [Ledger FeeEarnerCode],
           RTRIM(ISNULL(LFE.[NAME], '')) AS [Ledger FeeEarner],
           CONVERT(INT, ISNULL(DTL.[RECORDID], 0)) AS [RecordID],
           RTRIM(ISNULL(MAT.[FECODE], '')) AS [FeeEarnerCode],
           RTRIM(ISNULL(FE.[NAME], '')) AS [FeeEarner],
           RTRIM(ISNULL(MAT.[DEPT], '')) AS [Dept],
           RTRIM(ISNULL(MAT.[WTYPE], '')) AS [WType],
           RTRIM(ISNULL(MAT.[BRANCH], '')) AS [Branch],
           ISNULL(MAT.[FILENUM], 0) AS [FileNum],
           RTRIM(ISNULL(MAT.[THIRDPART], '')) AS [ThirdPart],
           RTRIM(ISNULL(MAT.[ThPartSol], '')) AS [ThPartSol],
           RTRIM(ISNULL(MAT.[OldRef], '')) AS [OldRef],
           RTRIM(ISNULL(MAT.[PCODE], '')) AS [PCODE],
           RTRIM(ISNULL(MAT.[PFECODE], '')) AS [PFeeEarnerCode],
           RTRIM(ISNULL(PFE.[NAME], '')) AS [PFeeEarner],
           RTRIM(ISNULL(MAT.[USER1], '')) AS [User1],
           RTRIM(ISNULL(MAT.[USER2], '')) AS [User2],
           RTRIM(ISNULL(MAT.[USER3], '')) AS [User3],
           RTRIM(ISNULL(MAT.[FileColour], '')) AS [FileColour],
           ISNULL(MAT.[OUTFILENO], 0) AS [OutFileNo],
           RTRIM(ISNULL(CTC.[NAME], '')) AS [ClientName],
           RTRIM(ISNULL(CTC.[FE], '')) AS [ClientFeeEarnerCode],
           RTRIM(ISNULL(CFE.[NAME], '')) AS [ClientFeeEarner],
           CASE WHEN RTRIM(ISNULL(CTC.[CLIENT], 'N')) = 'Y'
                     THEN 'Y'
                ELSE 'N' END AS [Client],
           RTRIM(ISNULL(CTC.[OTHERREF], '')) AS [OtherRef],
           RTRIM(ISNULL(CTC.[OPSISREF], '')) AS [OpsisRef],
           ISNULL(CTC.[CONTACTNO], 0) AS [ContactNo],
           ISNULL(CTC.[COMPBILLONOFF], 0) AS [CompBillOnOff],
           RTRIM(ISNULL(CTC.[CLIENTGROUP], '')) AS [ClientGroup],
           RTRIM(ISNULL(CTC.[TAXTYPE], '')) AS [TaxType],
           RTRIM(ISNULL(FCL.[COLOURDESC], '')) AS [FileColourDescription],
           ISNULL(FCL.[RGBColour], -1) AS [FileColourRGB],
		    CASE WHEN ISNULL(DTL.[BATCHNO], 0) IN (SELECT DISTINCT ISNULL(ALLOCBATCH,0) FROM DBO.BATCHDETAILS)
		        THEN 'Y'
			ELSE 'N' END AS [Isallocated],
			ISNULL((SELECT  TOP 1 ISNULL(BAT.POSTED , 'N') POSTED 
		   FROM DBO.BATCHDETAILS BAD
           LEFT JOIN DBO.BATCHH BAT ON BAD.BATCHNO = BAT.BATCHNO
           WHERE ISNULL(BAT.POSTED , 'N') = 'N' AND ISNULL(BAD.ALLOCBATCH,0) <> 0 AND ISNULL(DTL.BATCHNO, 0) = ISNULL(BAD.ALLOCBATCH,0)),'N')
		    AS [Posted]
      FROM [dbo].[DebtorsLedger] DTL
           LEFT OUTER JOIN [dbo].[matters] MAT
                           LEFT OUTER JOIN [dbo].[Handlers] FE
                                        ON FE.[CODE] = MAT.[FECode]
                           LEFT OUTER JOIN [dbo].[Handlers] PFE
                                        ON PFE.[CODE] = MAT.[PFECODE]
                           LEFT OUTER JOIN [dbo].[contacts] CTC
                                           LEFT OUTER JOIN [dbo].[Handlers] CFE
                                                        ON CFE.[CODE] = CTC.[FE]
                                        ON  MAT.[ClientCode]= CTC.[Code]
                   ON  DTL.[MATTER] = MAT.[Code]
           LEFT OUTER JOIN [dbo].[Handlers] LFE
                       ON LFE.[CODE] = DTL.[FEECODE]
           LEFT OUTER JOIN [dbo].[FileColours] FCL
                        ON FCL.[COLOURCODE] = MAT.[FileColour]
     WHERE DTL.[MATTER] = @matter
       AND DTL.[OSVALUE] > 0
       AND DTL.[DATE] IS NOT NULL
  ORDER BY DTL.[RECORDID] ASC

END



GO


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

CREATE PROCEDURE 
	   [dbo].[KAAS_GetSAMPendingClientACDetails] 
	   (
			@MatterCode VARCHAR(20)
	   ) 
AS 
/*******************************************************************************************************
* [dbo].[KAAS_GetSAMPendingClientACDetails]   '000001/0000'                                                         *
* Description: Used to list the matter ledger                                                          *					  
* Modification History:                                                                                *
* 2021-04-09   Natarajan S        Created															   *      
* 2021-04-09   Arun V             Implemented coding standards                                         *      
*******************************************************************************************************/
BEGIN 
 SET NOCOUNT ON   
	SELECT 
		[BAD].[KEYID]	AS [PRef],
		[BAD].[REF]     AS [Ref],
		[BAD].[DATE]    AS [Date],
		[BAD].[NARR]    AS [Narr],
		[BAD].[VALUE]   AS [ValueC],
		[BAD].[BATCHNO] AS [BatchNo]
	FROM		
		[dbo].[BatchDetails] [BAD] 
	    LEFT OUTER JOIN [dbo].[BatchH] [BAH] 
	                 ON [BAD].[BATCHNO] = [BAH].[BATCHNO] 
	WHERE	
		([BAD].[MATTER] = @MatterCode  AND 
		 [BAH].[POSTED] = 'N'          AND 
			( [BAD].[OUTLAY]     = 'C'  OR  
			  [BAD].[TYPE]       = 'R'  AND  
			  [BAH].[THIRDPARTY] = 'Y'  OR  
			  [BAD].[TYPE]       = 'T'  AND  
			  [BAH].[CLIENTAC]   = 'Y') 
            )  
	ORDER BY 
		[BAD].[MATTER] ASC, 
		[BAD].[KEYID] ASC

	SELECT 
		SUM([VALUECC]) AS [ClientAccountTotal] 
	FROM 
		[dbo].[MatterLedger] 
	WHERE 
		[MATTER] = @MatterCode

 SET NOCOUNT OFF
END 

GO


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

CREATE PROCEDURE 
	   [dbo].[KAAS_GetSAMUndertakingBaseDetails] 
	   (
			@MatterCode VARCHAR(20),
			@FECode VARCHAR(10),
			@Handler VARCHAR(10)
	   ) 
AS 
/*******************************************************************************************************
* [dbo].[KAAS_GetSAMUndertakingBaseDetails] '000001/0000','ADM'                                        *
* Description: Get basic undertaking details to insert undertakings                                    *					  
* Modification History:                                                                                *
* 2021-04-10   Natarajan S        Created															   *      
*******************************************************************************************************/
BEGIN 
 SET NOCOUNT ON   

		SELECT 
			[RECORDID] AS [RecordId],
			[MATTER] AS [MATTER],
			[SEQ] AS [SEQ],
			[DISCHARGEDATE] AS [DischargeDate],
			[TYPE] AS [TYPE],
			[DESCRIPTION] AS [DESCRIPTION],
			[WHO] AS [WHO],
			[ACTIONID] AS [ACTIONID],
			[GIVENORRECEIVED] AS [GivenOrReceived],
			[NOTIFIED] AS [Notified],
			[DISCHARGEDESCRIPTION] AS [DischargeDescription],
			[DISCHARGEDBY] AS [DischargedBy],
			[DEALINGNUMBER] AS [DealingNumber],
			[NOTES] AS [Notes],
			[COMMERCIALORNON] AS [CommercialOrNon],
			[STATUS] AS [STATUS],
			[dbo].KAAS_FNConvertUTCDateToTargetTimeZone([DATE],@Handler),
			[dbo].KAAS_FNConvertUTCDateToTargetTimeZone([UNDDATE],@Handler) AS [UndDate],
			[VALUE] AS [VALUE],
			[UNDVALUE] AS [UndValue],
			[AUTHORISEDBYFE] AS [AUTHORISEDBYFE],
			[HANDLER] AS [Handler],
			[UNDERTAKINGTO] AS [UndertakingTo],
			[CASECONTACT] AS [CaseContact],
			[ENTRYDATE] AS [EntryDate],
			[CATEGORY] AS [Category],
			[ISCONDITIONAL] AS [IsConditional],
			[CONDITION] AS [Condition],
			[NEEDSATTENTION] AS [NeedsAttention]
		FROM
			[dbo].[Undertakings] 
		WHERE
			([MATTER] = @MatterCode  AND (MATTER < @MatterCode
		  OR
			([SEQ] <= 99 AND ([SEQ] < 99 OR ([DATE] <= null AND ([DATE] < NULL OR ([RECORDID] <= 0 )))))))
		ORDER BY 
			[MATTER] DESC,
			[SEQ] DESC,
			[DATE] DESC,
			[RECORDID] DESC

		SELECT 
			[CODE] AS [Code],
			[BRANCH] AS [Branch],
			[NAME] AS [Name],
			[NOMINAL] AS [Nominal],
			[RETIRED] AS [Retired]
		FROM
			[dbo].[FeeEarnerCodes] WHERE [CODE] = @FECode
 SET NOCOUNT OFF
END 

GO

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

go 

CREATE PROCEDURE [dbo].[KAAS_GetSAMUndertakingDetails] (@MatterCode VARCHAR(20),@Handler VARCHAR(10)) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to Get Undertaking details																		 *
  * Stored Procedure Name: [dbo].KAAS_GetSAMUndertakingDetails '000003/0008','ADM'					     * 
  * Modification History:                                                                                *
  * 2021-03-30 Natarajan S        Created																 *
  * 2021-06-01 Balamurugan        Modified--Commented the type column case when							 *      
   *******************************************************************************************************/
  BEGIN 

EXEC [dbo].KAAS_GetSAMHeaderDetails	@MatterCode  

	SELECT 
	 [UND].[RecordID], 
	 [UND].[Matter], 
	 [UND].[Seq], 
	 [dbo].KAAS_FNConvertUTCDateToTargetTimeZone([UND].[DischargeDate],@Handler) AS [DischargeDate],
 	 [UND].[Type],
         [UND].[Description], 
         [UND].[Who], 
         [UND].[ActionID], 
         [UND].[Status], 
         [dbo].KAAS_FNConvertUTCDateToTargetTimeZone([UND].[Date],@Handler) AS [Date],
         [UND].[Value], 
         [UND].[AuthorisedByFE] 
	FROM 
		 [dbo].[Undertakings] [UND] 
	WHERE 
		 [UND].[MATTER] = @MatterCode 
	ORDER BY 
		[UND].[MATTER] ASC, 
		[UND].[SEQ] ASC, 
		[UND].[DATE] ASC, 
		[UND].[RECORDID]

  END 

GO

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

go 

CREATE PROCEDURE [dbo].KAAS_GetSAMUndertakingDetailsForEdit (@RecordId INT,@Handler VARCHAR(10)) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to Get Undertaking details	for edit															 *
  * Stored Procedure Name: [dbo].KAAS_GetSAMUndertakingDetailsForEdit  166					  * 
  * Modification History:                                                                                *
  * 2021-03-30 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 
 

SELECT 
		[UDK].[RecordId],
		[UDK].[MATTER],
		[UDK].[SEQ],
		[dbo].KAAS_FNConvertUTCDateToTargetTimeZone([UDK].[DISCHARGEDATE],@Handler),
		[UDK].[TYPE],
		[UDK].[DESCRIPTION],
		[UDK].[WHO],
		[UDK].[ACTIONID],
		[UDK].[GivenOrReceived],
		[UDK].[DischargeDescription],
		[UDK].[DischargedBy],
		[UDK].[DealingNumber],
		[UDK].[Notes],
		[UDK].[CommercialOrNon],
		[UDK].[STATUS],
		[dbo].KAAS_FNConvertUTCDateToTargetTimeZone([UDK].[DATE],@Handler) AS [DATE],
		[UDK].[VALUE],
		[UDK].[AUTHORISEDBYFE],
		[UDK].[Handler],
		[UDK].[UndertakingTo],
		[UDK].[CaseContact],
		[UDK].[EntryDate],
		[UDK].[Category],
		[UDK].[IsConditional],
		[UDK].[Condition],
		[UDK].[NeedsAttention],
		[UDK].[AddCommentLedger],
		[dbo].KAAS_FNConvertUTCDateToTargetTimeZone([DIA].[DATE],@Handler) AS [DueDate],
		[DIA].[TeamCode],
		[DIA].[FNCode],
		[DIA].[Text1]
	FROM 
		[dbo].[Undertakings] [UDK]
	INNER JOIN [dbo].[diary] [DIA]
			ON  [UDK].[ActionID]=[DIA].[ACTIONID]
	WHERE 
		[RecordId]=@RecordId

  END 

GO 



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

CREATE PROCEDURE 
	   [dbo].[KAAS_GetSAMUndertakingDiaryDetails] 
	   (
			@ActionCode VARCHAR(20),
			@FECode VARCHAR(10)
	   ) 
AS 
/*******************************************************************************************************
* [dbo].[KAAS_GetSAMUndertakingDiaryDetails]															*
* Description: Get basic details to insert into diary information.                                    *					  
* Modification History:                                                                                *
* 2021-04-10   Natarajan S        Created															   *      
*******************************************************************************************************/
BEGIN 
 SET NOCOUNT ON   

		SELECT 
		CASE[Priority] WHEN 'High' 
							THEN 'H'
						ELSE 'N' 
						 END				
							AS [Priority],
			[Highlighted]	AS [Highlighted],
			[Publisher]		AS [Publish],
			[ActionCode]	AS [ActionCode],
			[Status]		AS [ActionStatus],
			[ActionCategory]AS [ActionType],
			[ProcessType]	AS [ProcessType],
			[Desc]			AS [ActionCodeDescription],
			[Code]			AS [FNCode],
			[Team]			AS[TeamCode]
		FROM
		   [dbo].[TemplateActions],
		   [dbo].[Handlers]
		WHERE
		   [CODE] = @FECode  and  ACTIONCODE=@ActionCode

 SET NOCOUNT OFF
END 

GO



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

CREATE PROCEDURE 
	   [dbo].[KAAS_GetSettingsDetails] 
	   (
			@KeyName VARCHAR(20) ='',
			@mode    VARCHAR(6)  =''
	   ) 
AS 
/*******************************************************************************************************
* [dbo].[KAAS_GetSettingsDetails]						                                               *
* Description: Used to get EFT status from system options                                              *					  
* Modification History:                                                                                *
* 2021-05-19   Natarajan S        Created															   *
* 2021-05-26   Balamurugan C	  Modified-Added EFT Alert Details 									   *         
*******************************************************************************************************/
BEGIN 
 SET NOCOUNT ON 
   
IF(@mode='SELECT')
BEGIN 

/*EFT Default ON Setting*/

	SELECT 
		  KeyValue
	  FROM 
		  Settings 
	 Where 
		  KeyName = @KeyName

/*EFT Alert Setting*/

		SELECT  
				[EFTYN] 
		FROM [EFTDetails]

END

IF(@mode='UPDATE')
BEGIN

/*UPDATE EFT ALERT STATUS*/

		UPDATE [EFTDetails] SET [EFTYN]='Y'

END

 SET NOCOUNT OFF

END 

GO

IF  EXISTS 
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetVATCodes]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [dbo].[KAAS_GetVATCodes]
END	
GO

CREATE PROCEDURE [dbo].[KAAS_GetVATCodes]

AS

/*******************************************************************************************************
* Fetches the list of VAT codes                                                                        * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_GetVATCodes]       											   *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-03-23    Revathy D        Created                                                               *
 *******************************************************************************************************/

BEGIN

SELECT	
	   VAC.[CODE] AS Vat,
       VAC.[RATE] AS Rate     
  FROM [dbo].[VATCodes] VAC
 
ORDER BY  
  VAC.[CODE] ASC


END

GO



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

go 

CREATE PROCEDURE [dbo].[KAAS_INSERTSAMDiaryDetails] (
			@MatterCode VARCHAR(20),
			@Priority varchar(5),
			@Highlighted varchar(20),
			@Publisher varchar(20),
			@Status int,
			@ActionCode varchar(20),
			@ActionStatus varchar(20),
			@ActionType varchar(20),
			@ProcessType varchar(20),
			@FNCode varchar(20),
			@TeamCode varchar(20),
			@Text1 varchar(max),
			@Date datetime,
			@diaryWarningDate datetime,
			@Handler VARCHAR(10),
			@Result	INT OUTPUT
) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to list the matter ledger                                                                        *
  * Stored Procedure Name: [dbo].[KAAS_INSERTSAMDiaryDetails]		'000001/0000','H','','N',0,'ADMUD','NA','U','I','ADM','COM','UNDERTAKING EXAMPLE',0					 * 
  * Modification History:                                                                                *
  * 2021-03-26 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 
 

  DECLARE @TIMECONVERT INT
  SET @TIMECONVERT = [dbo].ky_ConvertTimeToClarion(GETDATE())
  DECLARE @IdentityValue int
   EXEC @IdentityValue = [dbo].[KAAS_GetNextActionID]
     INSERT INTO dbo.diary 
			([ActionID],
			[CASECODE],
			[PRIORITY],
			[HIGHLIGHTED],
			[PUBLISH],
			[DATE],
			[STATUS],
			[ACTIONCODE],
			[ACTIONSTATUS],
			[ACTIONTYPE],
			[PROCESSTYPE],
			[FNCODE],
			[TEAMCODE],
			[TEXT1],
			[TEXT2],
			[DELEGATEDFNR],
			[DELEGATEDDATE],
			[DELEGATEDBACKDATE],
			[DEFERRED],
			[DUEDATE],
			[IMAGENO],
			[DYSTARTTIME],
			[DYENDTIME],
			[DURATION],
			[ORGINALACTIONID],
			[MILESTEONETYPE],
			[ATTACHMENTS],
			[PROCESSSTATUS],
			[WORKPROCESS],
			[BILLABLE],
			[BILLDESCRIPTION],
			[EMAILADDRESS],
			[EMAIL],
			[SUBJECT],
			[DELEGATIONSTATUS],
			[DRAFTBILLNO],
			[CHEQUEREQNO],
			[TXMSENT],
			[LOCATION],
			[HEARINGTYPE],
			[FORCOPY],
			[TXMDATE],
			[TXMSEQNO],
			[DISPLAYTEXT],
			[FLAG],
			[ADDRESSTO],
			[CCTO],
			[BCCTO],
			[CLIENTCONTACTID],
			[CONTACTCATEGORY],
			[CAMPAIGN],
			[FILENUM],
			[CASEASSOCIATE]) 
     VALUES(
			@IdentityValue, 
			@MatterCode, 
			@Priority,
			@Highlighted,
			@Publisher,
			[dbo].KAAS_FNConvertDateToUTC(@diaryWarningDate,@Handler),
			@Status,
			@ActionCode,
			@ActionStatus,
			@ActionType,
			@ProcessType,
			@FNCode,
			@TEAMCODE,
			@TEXT1,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			[dbo].KAAS_FNConvertDateToUTC(@Date,@Handler),
			0,
			@TIMECONVERT,
			@TIMECONVERT,
			NULL,
			@IdentityValue,
			'JP',
			'N',
			0,
			NULL,
			0,
			'',
			NULL,
			NULL,
			'',
			0,
			0,
			NULL,
			0,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			@TEXT1,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			0,
			NULL)

	 INSERT INTO [dbo].[DiaryDelegations]
				([ACTIONID],
				[HANDLER],
				[TEAM],
				[DATE],
				[TIME],
				[DATER],
				[TIMER],
				[DUEDATE],
				[DUETIME],
				[REVIEW],
				[STATUS],
				[OWNER],
				[DELEGATE],
				[DELEGATESTATUS],
				[ACTIONTYPE],
				[FROMHANDLER],
				[RETURNEDBY],
				[DELTYPE])
			VALUES 
				(@IdentityValue,
				@FNCode,
				@TeamCode,
				CONVERT(DATETIME, @diaryWarningDate ,112),
				@TIMECONVERT,
				NULL,
				'',
				CONVERT(DATETIME, @Date ,112),
				@TIMECONVERT,
				' ',
				0,
				'Y',
				'JP',
				0,
				'U',
				'JP',
				'',
				'Created')
				SET @Result = @IdentityValue
				


	
  END 

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

go 

CREATE PROCEDURE [dbo].[KAAS_INSERTSAMMatterLedgerComment] (
@MatterCode VARCHAR(20),
@Narrative VARCHAR(MAX),
@ClientMin float,
@Date datetime,
@Result	TINYINT OUTPUT
) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to list the matter ledger                                                                        *
  * Stored Procedure Name: [dbo].[KAAS_INSERTSAMMatterLedgerComment]									 * 
  * Modification History:                                                                                *
  * 2021-03-26 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 
      INSERT INTO [dbo].[MatterLedger]
           ([MATTER]
           ,[BATCHNO]
           ,[PREF]
           ,[DATE]
           ,[REF]
           ,[NARR]
           ,[VALUED]
           ,[VALUEO]
           ,[VALUEC]
           ,[FEE]
           ,[PER]
           ,[YEAR]
           ,[ENTRYDATE]
           ,[CLIENT]
           ,[VALUECC]
           ,[VALUECD]
           ,[DEPOSITTYPE]
           ,[CURVALUED]
           ,[CURVALUEO]
           ,[CURVALUEC]
           ,[CURVALUECC]
           ,[CURVALUECD]
           ,[BILLED]
           ,[BILLINGYEAR]
           ,[BILLINGPERIOD]
           ,[BILLINGDATE]
           ,[BilledOutlayValue]
           ,[InvoiceNo]
           ,[IncludeInBill])
     VALUES(
			@MatterCode
           ,0
           ,0
           ,@Date
           ,'admin'
           ,@Narrative
           ,0.00
           ,0.00
           ,0.00
           ,''
           ,0
           ,0
           ,GETDATE()
           ,''
           ,0.00
           ,0.00
           ,''
           ,0.00
           ,0.00
           ,0.00
           ,0.00
           ,0.00
           ,''
           ,0
           ,0
           ,NULL
           ,0.00
           ,0
           ,0)

		   SET @Result = 1

	  UPDATE dbo.openmattersonly SET [ClientMin]=@ClientMin
      WHERE  [code] = @MatterCode 
  END 

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

go 

CREATE PROCEDURE [dbo].[KAAS_INSERTSAMUndertakingDetails] (
			@MatterCode VARCHAR(20),
			@Seq INT,
			@Type VARCHAR(10),
			@Details VARCHAR(MAX),
			@Who VARCHAR(MAX),
			@ActionID INT,
			@GivenOrReceived VARCHAR(10),
			@DealingNumber VARCHAR(10),
			@Notes VARCHAR(MAX),
			@CommercialOrNonCommerical VARCHAR(10),
			@Status VARCHAR(50),
			@Value decimal,
			@AuthorizedByFE VARCHAR(10),
			@UndertakingTo VARCHAR(10),
			@Date datetime,
			@Handler VARCHAR(10),
			@Result	TINYINT OUTPUT
) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to list the matter ledger                                                                        *
  * Stored Procedure Name: [dbo].[KAAS_INSERTSAMUndertakingDetails]		'000001/0000','H','','N',0,'ADMUD','NA','U','I','ADM','COM','UNDERTAKING EXAMPLE',0					 * 
  * Modification History:                                                                                *
  * 2021-03-26 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 
  DECLARE @CurrentDate DATETIME
  SET @CurrentDate = GETDATE()
  SET @Type = SUBSTRING(@Type, 1, 1)
  SET @GivenOrReceived = SUBSTRING(@GivenOrReceived, 1, 1)
  SET @CommercialOrNonCommerical = SUBSTRING(@CommercialOrNonCommerical, 1, 1)
    INSERT INTO 
	dbo.Undertakings 
	([MATTER],
	[SEQ],
	[DISCHARGEDATE],
	[TYPE],
	[DESCRIPTION],
	[WHO],
	[ACTIONID],
	[GIVENORRECEIVED],
	[NOTIFIED],
	[DISCHARGEDESCRIPTION],
	[DISCHARGEDBY],
	[DEALINGNUMBER],
	[NOTES],
	[COMMERCIALORNON],
	[STATUS],
	[DATE],
	[UNDDATE],
	[VALUE],
	[UNDVALUE],
	[AUTHORISEDBYFE],
	[HANDLER],
	[UNDERTAKINGTO],
	[CASECONTACT],
	[CATEGORY],
	[ISCONDITIONAL],
	[CONDITION],
	[NEEDSATTENTION]) 
VALUES (
	@MatterCode,
	@Seq,
	NULL,
	@Type,
	@Details,
	@Who,
	@ActionID,
	@GivenOrReceived,
	' ',
	'',
	'',
	@DealingNumber,
	@Notes,
	@CommercialOrNonCommerical,
	@Status,
	[dbo].KAAS_FNConvertDateToUTC(@Date,@Handler),
	[dbo].KAAS_FNConvertDateToUTC(@Date,@Handler),
	@Value,
	@Value,
	@AuthorizedByFE,
	@AuthorizedByFE,
	@UndertakingTo,
	@UndertakingTo,
	'',
	0,
	'',
	0)
	SET @Result = 1

	
  END 

GO

IF OBJECT_ID(N'KAAS_PostCheque',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_PostCheque]
GO
CREATE PROCEDURE [dbo].[KAAS_PostCheque]
(
@BatchNo int
)
AS

/*******************************************************************************************************
* Update Lodgement																					   * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_PostCheque]  3558			  						               *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-10    Natarajan S      Created                                                               *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON   

	select Narr= 'Post Cheque successfull'

 SET NOCOUNT OFF


END


GO
IF OBJECT_ID(N'KAAS_PostJournal',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_PostJournal]
GO
CREATE PROCEDURE [dbo].[KAAS_PostJournal]
(
@BatchNo int
)
AS

/*******************************************************************************************************
* Update Lodgement																					   * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_PostJournal]  3558			  						               *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-10    Natarajan S      Created                                                               *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON   

	select Narr= 'Post Journal successfull'

 SET NOCOUNT OFF


END


GO
IF OBJECT_ID(N'KAAS_PostReceipts',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_PostReceipts]
GO
CREATE PROCEDURE [dbo].[KAAS_PostReceipts]
(
@BatchNo int
)
AS

/*******************************************************************************************************
* Update Receipts																					   * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_PostReceipts]  3558    						               *
*                                                                                                      *
* Modification History:                                                                                *
* 2021-06-10    Natarajan S      Created                                                               *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON   

	select Narr= 'Post Receipts successfull'

 SET NOCOUNT OFF


END


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

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[KAAS_SaveColumnChooserList]
		(
			@mode			varchar(4)='',
		    @handlercode	varchar(50)='',
			@pagename		varchar(50)='',       
			@defaultcolumns nvarchar(MAX)=''
		)
AS
/*******************************************************************************************************
* Save the Default ColumnChooser Column List                                                           * 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_SaveColumnChooserList]   			            				   *
*                                                                                                      *
* Modification History:                                                                                *
* Balamurugan			21-May-2021					Created                                            *
 *******************************************************************************************************/

BEGIN
 SET NOCOUNT ON 
 

IF(@mode='SAVE')
BEGIN
/*CHECK THE DATA ALREADY PRESENT OR NOT*/

IF EXISTS
	(
			SELECT TOP 1 * 
			FROM dbo.[DefaultPageColumnsByHanlder] 
			WHERE [HandlerCode] = @handlercode 
				  AND  [PageName]=@pagename		
	)
BEGIN
			UPDATE dbo.[DefaultPageColumnsByHanlder]
			SET    [DefaultColumns] = @defaultcolumns
			WHERE  [HandlerCode] = @handlercode
				   AND [PageName] = @pagename 

END
ELSE
BEGIN
			INSERT INTO dbo.[DefaultPageColumnsByHanlder] 
					(
					[HandlerCode],
					[PageName],
					[DefaultColumns]
					)
			VALUES  (
					@handlercode,
					@pagename,
					@defaultcolumns
					)
END

END
IF(@mode='VIEW')
BEGIN
		SELECT 
				[HandlerCode],
				[PageName],
				[DefaultColumns]
		FROM dbo.[DefaultPageColumnsByHanlder] 
		WHERE  [HandlerCode] = @handlercode
				   AND [PageName] = @pagename 


END

END

GO

IF EXISTS (
  SELECT
    TOP 1 1
  FROM
    sys.[objects] SO
  WHERE
    SO.[name] = N'KAAS_UpdateSAMFNLDetails'
    AND SO.[type] = 'P'
) BEGIN DROP PROCEDURE [dbo].[KAAS_UpdateSAMFNLDetails] END
GO
  CREATE PROCEDURE [dbo].[KAAS_UpdateSAMFNLDetails] (
    @MatterCode VARCHAR(20),
    @FNLDate datetime,
    @FNLName varchar(50),
    @FNLAddress varchar(100),
    @FNLFees decimal,
    @FNLVat decimal,
    @FNLOutlay decimal,
    @FNLTotal decimal,
    @ClientMin decimal,
    @Result TINYINT OUTPUT
  ) AS
  /*******************************************************************************************************
    *                                                                                                      *
    *Used to list the matter ledger                                                                        *
    * Stored Procedure Name: [dbo].[KAAS_UpdateSAMFNLDetails]	'000001/0000','2021-04-12 17:54:13.453','RAJ','SET ADDRESS','40','20','10','70',50,0	 * 
    * Modification History:                                                                                *
    * 2021-03-11 Natarajan S        Created		
    
    *														 *      
     *******************************************************************************************************/
  BEGIN declare @Narrative varchar(80) declare @SpaceVal int declare @NameLen int
  DECLARE @CurrDate datetime
  set @CurrDate = GETDATE()
update
  OpenMattersONLY
set
  FNL_Date = @FNLDate,
  FNL_Name = @FNLName,
  FNL_Address = @FNLAddress,
  FNL_Fees = @FNLFees,
  FNL_VAT = @FNLVat,
  FNL_Outlay = @FNLOutlay
where
  CODE = @MatterCode
update
  matters
set
  FNL_Date = @FNLDate,
  FNL_Name = @FNLName,
  FNL_Address = @FNLAddress,
  FNL_Fees = @FNLFees,
  FNL_VAT = @FNLVat,
  FNL_Outlay = @FNLOutlay,
  FNL_Total = @FNLTotal
where
  CODE = @MatterCode
SET
  @NameLen = LEN(@FNLName);
SET
  @SpaceVal = 50 - @NameLen;
SET
  @Narrative = CONCAT(
    'FNL: €',
    @FNLTotal,
    SPACE(2),
    @FNLName,
    SPACE(@SpaceVal),
    SPACE(2),
    'Fees: €',
    @FNLFees,
    SPACE(2),
    'O/L: €',
    @FNLOutlay
  ) 
  EXEC KAAS_INSERTSAMMatterLedgerComment @MatterCode,@Narrative,@ClientMin,@CurrDate,@Result
SET	@Result = 1 

END

GO

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

go 

CREATE PROCEDURE [dbo].[KAAS_UpdateSAMUndertakingDetails] (
		
			@Type VARCHAR(10),
			@Details VARCHAR(MAX),
			@Who VARCHAR(MAX),
			@GivenOrReceived VARCHAR(10),
			@DealingNumber VARCHAR(10),
			@Notes VARCHAR(MAX),
			@CommercialOrNonCommerical VARCHAR(10),
			@Status VARCHAR(50),
			@Date datetime,
			@Value decimal,
			@AuthorizedByFE VARCHAR(10),
			@UndertakingTo VARCHAR(10),
			@Text1 varchar(max),
			@ActionID INT,
			@IsDischarge bit,
			@DiaryWarningDate datetime,
			@DischargeDate datetime = null,
			@Handler VARCHAR(10),
			@Result	TINYINT OUTPUT
) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to list the matter ledger                                                                        *
  * Stored Procedure Name: [dbo].[KAAS_UpdateSAMUndertakingDetails]		'000001/0000','H','','N',0,'ADMUD','NA','U','I','ADM','COM','UNDERTAKING EXAMPLE',0					 * 
  * Modification History:                                                                                *
  * 2021-03-26 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 
  
  SET @Type = SUBSTRING(@Type, 1, 1)
  SET @GivenOrReceived = SUBSTRING(@GivenOrReceived, 1, 1)
  SET @CommercialOrNonCommerical = SUBSTRING(@CommercialOrNonCommerical, 1, 1)
	
	UPDATE 
		[dbo].[Undertakings] 
	SET [TYPE]=@Type,
		[DESCRIPTION]=@Details,
		[WHO]=@Who,
		[GivenOrReceived]=@GivenOrReceived,
		[DealingNumber]=@DealingNumber,
		[Notes]=@Notes,
		[CommercialOrNon]=@CommercialOrNonCommerical,
		[Status]=@Status,
		[DATE]=[dbo].KAAS_FNConvertDateToUTC(@Date,@Handler),
		[UNDDATE]=[dbo].KAAS_FNConvertDateToUTC(@Date,@Handler),
		[VALUE]=@Value,
		[UNDVALUE]=@Value,
		[AuthorisedByFE]=@AuthorizedByFE,
		[Handler]=@Handler,
		[UndertakingTo]=@UndertakingTo,
		[CaseContact]=@UndertakingTo,
		[DISCHARGEDATE]= CASE WHEN @IsDischarge=1 THEN [dbo].KAAS_FNConvertDateToUTC(@DischargeDate,@Handler) ELSE NULL END
	Where
		[ActionID]=@ActionID

	UPDATE 
		[dbo].[diary] 
	SET [DATE]=[dbo].KAAS_FNConvertDateToUTC(@diaryWarningDate,@Handler),
		[DUEDATE] = [dbo].KAAS_FNConvertDateToUTC(@Date,@Handler),
		[TEXT1]=@Text1 
	WHERE
		[ACTIONID]=@ActionID

	UPDATE 
		[dbo].[DiaryDelegations] 
	SET [DATE]=[dbo].KAAS_FNConvertDateToUTC(@diaryWarningDate,@Handler),
		[DUEDATE] = [dbo].KAAS_FNConvertDateToUTC(@Date,@Handler)
		
	WHERE
		[ACTIONID]=@ActionID


	SET @Result = 1

	
  END 

GO