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'
	) 

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] = @DATE,
		[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] = @ENTRYDATE,
		[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,
		@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 
		)

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 null,
			@BATCHNO int,
	        @TYPE char(1)='R',
			@CODE char(10)='CLCONTROL',
			@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,
			@OUTLAY char(1)='C',
			@YEAR smallint,
			@PERNO smallint,
			@ENTRYCURRENCY char(1)='E',
			@CURRENCYVALUE decimal(17,2)=393.78,
			@CURRENCYVAT decimal(17,2) = 0.00,
			@OUTLAYCODE char(4)  null,
			@THIRDPARTY char(1) null,
			@PAYEE char(60) null,
			@HEADPREF int null,
			@HEADBATCH int null,
			@ALLOCBATCH int null,
			@ALLOCPREF int null,
			@ALLOCREF char(10)='',
			@ALLOCVALUE decimal(17,2) =0.00,
			@ALLOCWRITEOFF char(1) ='',
			@PSUPP char(10) null,
			@UNDETAKING smallint null,
			@CASEASSCODE varchar(6) ='',
			@ONCEOFFPAYMENT char(1) ='N',
			@PAYCLIENT char(1)='N',
			@CLIENTCODE varchar(10)='',
			@EFTEMAILYORN char(1)='N'
		) 
AS

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

BEGIN
 SET NOCOUNT ON 

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] =@DATE,			
			[NARR] = @NARR,
			[MATTER] = @MATTER,
			[VALUE] = @VALUE,
			[VATCODE] = @VATCODE,
			[VATVAL]= @VATVAL,
			[OUTLAY]= @OUTLAY,
			[YEAR] = @YEAR,
			[PERNO] = @PERNO,
			[ENTRYCURRENCY]= @ENTRYCURRENCY,
			[CURRENCYVALUE]=@CURRENCYVALUE,
			[CURRENCYVAT] =@CURRENCYVAT,
			[OUTLAYCODE]= @OUTLAYCODE,
			[THIRDPARTY] = @THIRDPARTY,
			[PAYEE]= @PAYEE,
			[HEADPREF] =@HEADPREF,
			[HEADBATCH] =@HEADBATCH,
			[ALLOCBATCH] =@ALLOCBATCH,
			[ALLOCPREF]=@ALLOCPREF,
			[ALLOCREF]=@ALLOCREF,
			[ALLOCVALUE]= @ALLOCVALUE,
			[ALLOCWRITEOFF]=@ALLOCWRITEOFF,
			[PSUPP]=@PSUPP,
			[UNDETAKING]=@UNDETAKING,
			[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],
			[OUTLAYCODE],
			[THIRDPARTY],
			[PAYEE],
			[HEADPREF],
			[HEADBATCH],
			[ALLOCBATCH],
			[ALLOCPREF],
			[ALLOCREF],
			[ALLOCVALUE],
			[ALLOCWRITEOFF],
			[PSUPP],
			[UNDETAKING],
			[CASEASSCODE],
			[ONCEOFFPAYMENT],
			[PAYCLIENT],
			[CLIENTCODE],
			[EFTEMAILYORN]
		) 
VALUES
		(   
			@BATCHNO,
	        @TYPE,
			@CODE,
			@PREF,
			@REF,
			@DATE,
			@NARR,
			@MATTER,
			@VALUE,
			@VATCODE,
			@VATVAL,
			@OUTLAY,
			@YEAR,
			@PERNO,
			@ENTRYCURRENCY,
			@CURRENCYVALUE,
			@CURRENCYVAT,
			@OUTLAYCODE,
			@THIRDPARTY,
			@PAYEE,
			@HEADPREF,
			@HEADBATCH,
			@ALLOCBATCH,
			@ALLOCPREF,
			@ALLOCREF,
			@ALLOCVALUE,
			@ALLOCWRITEOFF,
			@PSUPP,
			@UNDETAKING,
			@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  
  A.[Code], 
  A.[Desc], 
  A.[Type], 
  A.[Pb], 
  A.[Seq], 
  A.[Clientac], 
  A.[Deposityn], 
  A.[Deposittype], 
  A.[BankBranch] 
FROM 
  dbo.[Nominal] A 
WHERE A.[TYPE] = 'BANK' 
  AND A.[RETIRED] <> 'Y' 
ORDER BY  
  A.[CODE] ASC


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] 

AS

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

BEGIN

SELECT 
  A.[BatchNo], 
  A.[Type], 
  A.[Code], 
  A.[Ref], 
  A.[Date], 
  A.[Matter], 
  A.[Supp], 
  A.[Narr], 
  A.[Value], 
  A.[Posted], 
  A.[PerNo], 
  A.[Year], 
  A.[EntryDate], 
  A.[RecNo], 
  A.[ChequeReqNo],
  A.[UserCode],
  A.[Pref]
FROM 
  dbo.[BatchH] A 
WHERE A.[TYPE] = 'R' 
    AND A.[POSTED] = 'N'
ORDER BY 
  A.[POSTED] ASC, 
  A.[ENTRYDATE] ASC


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_GetNextPrefNBatchNo',N'P')IS NOT NULL
	DROP PROCEDURE [dbo].[KAAS_GetNextPrefNBatchNo]
GO
CREATE PROCEDURE [dbo].[KAAS_GetNextPrefNBatchNo]
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   

SELECT 
	MAX(PREF)+1 Pref, 
	MAX(BATCHNO)+1 BatchNo 
FROM 
(
  (
	SELECT 
		PREF, 
		BATCHNO 
	FROM DBO.BATCHH 
  )
 UNION ALL
  (
	SELECT 
		PREF, 
		BATCHNO 
	FROM DBO.BATCHDETAILS
  )
) BAT

 SET NOCOUNT OFF


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]
      (@PageNumber			INT           = NULL,
       @PageSize			INT           = NULL,
	   @SearchText			VARCHAR(4000) = NULL,
	   @SortColumn			VARCHAR(50)   = NULL,
	   @SortDirection		VARCHAR(10)   = NULL)
AS
/***********************************************************************
 
 * Fetching Open Matter Details
 *
 * 2021-03-10   Prabhu.V	  Created to Fetch the Open Matter details for SAM
 ***********************************************************************/
BEGIN
IF(@SortColumn = '' OR @SortColumn = NULL)
BEGIN
	SET @SortColumn = NULL
END

IF(@SortDirection = '' OR @SortDirection = NULL)
BEGIN
	SET @SortDirection = 'ASC'
END
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 OUTER JOIN dbo.[contacts] B ON  A.[CLIENTCODE]= B.[CODE]   
WHERE [Closed] <> 'Y'  
AND
@SearchText = ''
			OR
			(
			A.[Code] LIKE '%' + @SearchText + '%'
			OR
			A.[Code] LIKE '%' + @SearchText + '%'
			OR
			A.[FECode] LIKE '%' + @SearchText + '%'
			OR
			B.[Name] LIKE '%' + @SearchText + '%'
			OR
			A.[Description] LIKE '%' + @SearchText + '%'
			OR
			A.[OldRef] LIKE '%' + @SearchText + '%'
			OR
			A.[FileNum] LIKE '%' + @SearchText + '%'
			OR
			A.[Dept] LIKE '%' + @SearchText + '%'
			OR
			A.[WType] LIKE '%' + @SearchText + '%'
			OR
			A.[User1] LIKE '%' + @SearchText + '%'
			OR
			A.[User2] LIKE '%' + @SearchText + '%'
			OR
			A.[User3] LIKE '%' + @SearchText + '%'
			OR
			A.[Branch] LIKE '%' + @SearchText + '%'
			OR
			B.[Address] LIKE '%' + @SearchText + '%'
			OR
			A.[Comment] LIKE '%' + @SearchText + '%'
			)

ORDER BY
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'Code'   THEN A.[Code]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'Code'   THEN A.[Code]  
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'Closed'   THEN A.[Closed]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'Closed'   THEN A.[Closed]  
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'FeeCode'   THEN A.[FECODE]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'FeeCode'   THEN A.[FECODE]  
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'Name'   THEN B.[Name]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'Name'   THEN B.[Name]  
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'Description'   THEN A.[Description]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'Description'   THEN A.[Description] 
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'OldRef'   THEN A.[OldRef]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'OldRef'   THEN A.[OldRef] 
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'FileNum'   THEN A.[FileNum]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'FileNum'   THEN A.[FileNum]
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'Dept'   THEN A.[Dept]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'Dept'   THEN A.[Dept]
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'WorkType'   THEN A.[WType]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'WorkType'   THEN A.[WType]
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'User1'   THEN A.[User1]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'User1'   THEN A.[User1]
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'User2'   THEN A.[User2]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'User2'   THEN A.[User2]
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'User3'   THEN A.[User3]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'User3'   THEN A.[User3]
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'Branch'   THEN A.[Branch]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'Branch'   THEN A.[Branch]
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'Address'   THEN B.[Address]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'Address'   THEN B.[Address]
	     	END
END DESC,
CASE WHEN @SortDirection = 'ASC'  AND @SortColumn IS NOT NULL  THEN
	     	CASE @SortColumn
	     			WHEN 'Comment'   THEN A.[Comment]
	     		END
	     	END,
	     	CASE WHEN @SortDirection = 'DESC'  AND @SortColumn IS NOT NULL  THEN
	     		CASE @SortColumn
	     			WHEN 'Comment'   THEN A.[Comment]
	     	END
END DESC,

A.CODE ASC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
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)

AS

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

BEGIN

 SELECT  
	A.[BatchNo], 
	A.[Type], 
	A.[Code], 
	A.[Pref], 
	A.[Ref], 
	A.[Date], 
	A.[Fee], 
	A.[Narr], 
	A.[Matter], 
	A.[Supp], 
	A.[Value], 
	A.[VatCode], 
	A.[Outlay], 
	A.[Year], 
	A.[PerNo], 
	A.[OutlayCode], 
	A.[HeadBatch], 
	A.[AllocBatch], 
	A.[AllocPref], 
	A.[KeyId] 
 FROM 
	dbo.[BatchDetails] A 
 WHERE A.[BATCHNO] = @BatchNo
 ORDER BY  
	A.[BATCHNO] ASC, 
	A.[PREF] ASC, 
	A.[KEYID] ASC

 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  
				[CC].[CASECODE] [CaseCode], 
				[CC].[CONTYPE]  [ConType],
				[CC].[CONNUM] [ConNum], 
				[CC].[NAMECODE] [CaseContactsNameCode], 
				[CC].[SOLCODE] [CaseContactsSolCode], 
				[CC].[INSCODE] [CaseContactsInsCode], 
				[CC].[ASSIGNEDCONTACT] [AssignedContact], 
				[CA].[CODE] [CaseAssociateCode], 
				[CA].[TYPE] [CaseAssociateType], 
				[CA].[NAME] [CaseAssociateName], 
				[CA].[COMPANY] [CaseAssociateCompany], 
				[CA].[ADDRESS] [CaseAssociateAddress], 
				[CA].[SEARCH] [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 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*      
   *******************************************************************************************************/
  BEGIN 
   SET NOCOUNT ON   
     SELECT	 [MTL].[Matter], 
             [MTL].[BatchNo], 
             [MTL].[PRef], 
             [MTL].[Date], 
             [MTL].[Ref], 
             [MTL].[Narr], 
             [ValueD], 
             [ValueO], 
             [MTL].[Fee], 
             [MTL].[EntryDate], 
             [Client], 
             [ValueCC], 
             [ValueCD], 
             [LRef], 
             [Billed],
			 [Undertaking],
			 BH.Type,
	 CASE 
		WHEN [MTL].[BATCHNO]=0  AND [MTL].[Narr] like '%FNL%' THEN '#a7e7f2'
		WHEN [MTL].[BATCHNO]=0  AND [MTL].[Narr] like '%RESERVE%' THEN '#a7e7f2' 
		WHEN [MTL].[BATCHNO]=0  AND   [MTL].[Narr] like '%Undertaking%' THEN '#f0f09f' 
		WHEN [MTL].[BATCHNO]=0 THEN '#a7e7f2' 
		ELSE 'NONE' END AS [NarrColour]
     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_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)
	   ) 
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],
			[DATE] AS [DATE],
			[UNDDATE] 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)) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to Get Undertaking details																		 *
  * Stored Procedure Name: [dbo].KAAS_GetSAMUndertakingDetails											 * 
  * Modification History:                                                                                *
  * 2021-03-30 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 

EXEC [dbo].KAAS_GetSAMHeaderDetails	@MatterCode  

	SELECT 
		[UND].[RecordID], 
		[UND].[Matter], 
		[UND].[Seq], 
		[UND].[DischargeDate], 
	CASE[UND].[Type]
			WHEN 'F' THEN 'Financial' 
			ELSE 'Documents' END AS [Type],
         [UND].[Description], 
         [UND].[Who], 
         [UND].[ActionID], 
         [UND].[Status], 
         [UND].[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) 
AS 
  /*******************************************************************************************************
  *                                                                                                      *
  *Used to Get Undertaking details	for edit															 *
  * Stored Procedure Name: [dbo].KAAS_GetSAMUndertakingDetailsForEdit  94					  * 
  * Modification History:                                                                                *
  * 2021-03-30 Natarajan S        Created																 *      
   *******************************************************************************************************/
  BEGIN 

SELECT 
		[UDK].[RecordId],
		[UDK].[MATTER],
		[UDK].[SEQ],
		[UDK].[DischargeDate],
		[UDK].[TYPE],
		[UDK].[DESCRIPTION],
		[UDK].[WHO],
		[UDK].[ACTIONID],
		[UDK].[GivenOrReceived],
		[UDK].[DischargeDescription],
		[UDK].[DischargedBy],
		[UDK].[DealingNumber],
		[UDK].[Notes],
		[UDK].[CommercialOrNon],
		[UDK].[STATUS],
		[UDK].[DATE],
		[UDK].[VALUE],
		[UDK].[AUTHORISEDBYFE],
		[UDK].[Handler],
		[UDK].[UndertakingTo],
		[UDK].[CaseContact],
		[UDK].[EntryDate],
		[UDK].[Category],
		[UDK].[IsConditional],
		[UDK].[Condition],
		[UDK].[NeedsAttention],
		[UDK].[AddCommentLedger],
		[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 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),
			@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 @Date DATETIME
  SET @Date = GETDATE()
  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,
            @Date,
			@Status,
			@ActionCode,
			@ActionStatus,
			@ActionType,
			@ProcessType,
			@FNCode,
			@TEAMCODE,
			@TEXT1,
			NULL,
			NULL,
			NULL,
			NULL,
			NULL,
			@Date,
			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,
				GETDATE(),
				'2967649',
				NULL,
				'',
				GETDATE(),
				'29676',
				' ',
				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),
			@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],
	[ENTRYDATE],
	[CATEGORY],
	[ISCONDITIONAL],
	[CONDITION],
	[NEEDSATTENTION]) 
VALUES (
	@MatterCode,
	@Seq,
	NULL,
	@Type,
	@Details,
	@Who,
	@ActionID,
	@GivenOrReceived,
	' ',
	'',
	'',
	@DealingNumber,
	@Notes,
	@CommercialOrNonCommerical,
	3,
	@CurrentDate,
	@CurrentDate,
	@Value,
	@Value,
	@AuthorizedByFE,
	@AuthorizedByFE,
	@UndertakingTo,
	@UndertakingTo,
	@CurrentDate,
	'',
	0,
	'',
	0)
	SET @Result = 1

	
  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,
			@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]=@Date,
		[UNDDATE]=@Date,
		[VALUE]=@Value,
		[UNDVALUE]=@Value,
		[AuthorisedByFE]=@AuthorizedByFE,
		[Handler]=@AuthorizedByFE,
		[UndertakingTo]=@UndertakingTo,
		[CaseContact]=@UndertakingTo
	Where
		[ActionID]=@ActionID

	UPDATE 
		[dbo].[diary] 
	SET [DATE]=@Date,
		[TEXT1]=@Text1 
	WHERE
		[ACTIONID]=@ActionID


	SET @Result = 1

	
  END 

GO