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 ) BEGIN 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 END ELSE BEGIN 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 ) END 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', @SUPP varchar(10) = '', @OUTLAYCODE varchar(4) = '' ) AS /******************************************************************************************************* * Save the Batch Details * * * * Stored Procedure Name : [dbo].[KAAS_CreateBatchDetails] * * * * Modification History: * * 2021-04-10 Revathy D Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON 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,'') WHEN @OUTLAY = 'D' THEN ISNULL(DEBTORS,'') 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, [SUPP] = @SUPP, [OUTLAYCODE] = @OUTLAYCODE 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], [SUPP], [OUTLAYCODE] ) 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, @SUPP, @OUTLAYCODE ) END SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_CreateNominalAccountDetails', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_CreateNominalAccountDetails] END GO CREATE PROCEDURE [dbo].[KAAS_CreateNominalAccountDetails] ( @Code VARCHAR(10)='', @Retired VARCHAR(1)='', @Description VARCHAR(30)='', @Type VARCHAR(6)='', @Pbtype CHAR(1)='', @Currency CHAR(1)='', @Repseq VARCHAR(4)='', @Clientbankacct CHAR(1)='', @Deposit CHAR(1)='', @DepositType VARCHAR(4)='', @Allowchqreq CHAR(1)='', @Chqprinter VARCHAR(40)='', @Lastchqno INT=0, @Bank VARCHAR(40)='', @BankBranch VARCHAR(40)='', @Address VARCHAR(120)='', @BankType CHAR(1)='', @BankPhone VARCHAR(50)='', @AccountNo VARCHAR(10)='', @SortCode VARCHAR(10)='', @AccountName VARCHAR(50)='', @Signatories VARCHAR(255)='', @DateOpened DATETIME=null, @DateClosed DATETIME=null, @Iban VARCHAR(34)='', @Bic VARCHAR(20)='', @OrgIdNum VARCHAR(34)='', @Mode VARCHAR(10)='INSERTEDIT' ) AS /******************************************************************************************************* * Save the Nominal Account Details * * * * Stored Procedure Name : [dbo].[KAAS_CreateNominalAccountDetails] * * * * Modification History: * * Balamurugan 24-May-2021 Created * *******************************************************************************************************/ BEGIN SET nocount ON /*CHECK THE DATA ALREADY PRESENT OR NOT*/ IF( @Mode = 'INSERTEDIT' ) BEGIN IF EXISTS (SELECT TOP 1 1 FROM dbo.[Nominal] WHERE [Code] = @CODE) BEGIN /*IF DATA ALREADY PRESENT UPDATE PROCESS*/ UPDATE [dbo].[Nominal] SET [Desc] = @Description, [Type] = @Type, [Pb] = @Pbtype, [Retired] = @Retired, [Currency] = @Currency, [Seq] = @Repseq, [Clientac] = @Clientbankacct, [Deposityn] = @Deposit, [Deposittype] = @DepositType, [ChequeReq] = @Allowchqreq, [ChequePrinter] = @Chqprinter, [LastNo] = @Lastchqno, [Bank] = @Bank, [Bankbranch] = @BankBranch, [Bankaddress] = @Address, [Banktype] = @BankType, [Bankphone] = @BankPhone, [Accountno] = @AccountNo, [Sortcode] = @SortCode, [Accountname] = @AccountName, [Withdrawnames] = @Signatories, [Acopened] = @DateOpened, [Acclosed] = @DateClosed, [iban] = @Iban, [Bic] = @Bic, [OrgIDNumber] = @OrgIdNum WHERE [code] = @Code END ELSE BEGIN /*INSERT PROCESS IF DATA NOT PRESENT*/ INSERT INTO [dbo].[nominal] ( [CODE], [Desc], [Type], [Pb], [Retired], [Currency], [Seq], [Clientac], [Deposityn], [DepositType], [ChequeReq], [ChequePrinter], [LastNo], [Bank], [Bankbranch], [Bankaddress], [Banktype], [Bankphone], [Accountno], [Sortcode], [Accountname], [Withdrawnames], [Acopened], [Acclosed], [iban], [Bic], [OrgIDNumber] ) VALUES ( @Code, @Description, @Type, @Pbtype, @Retired, @Currency, @Repseq, @Clientbankacct, @Deposit, @DepositType, @Allowchqreq, @Chqprinter, @Lastchqno, @Bank, @BankBranch, @Address, @BankType, @BankPhone, @AccountNo, @SortCode, @AccountName, @Signatories, @DateOpened, @DateClosed, @Iban, @Bic, @OrgIdNum ) END END ELSE BEGIN /*DELETE PART*/ DELETE FROM [dbo].[Nominal] WHERE [Code] = @Code END 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 OBJECT_ID(N'KAAS_DeleteMasters',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_DeleteMasters] GO CREATE PROCEDURE [dbo].[KAAS_DeleteMasters] ( @TableName varchar(100), @CodeVal varchar(20), @Result int =0 Output ) AS /******************************************************************************************************* * Delete Master details * * select * from departments * * Stored Procedure Name : [dbo].[KAAS_DeleteMasters] 'departments', '' * * * * Modification History: * * 2021-06-17 Natarajan S Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON DECLARE @Qry Nvarchar(max) DECLARE @ColCode varchar(20) SET @ColCode = ( SELECT C.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C ON C.CONSTRAINT_NAME=T.CONSTRAINT_NAME WHERE C.TABLE_NAME=@TableName AND T.CONSTRAINT_TYPE='PRIMARY KEY' ) SET @Qry = 'DELETE FROM '+@TableName+' WHERE ' +@ColCode+'=''' +@CodeVal +'''' SET @Result =1 exec sp_executesql @Qry 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 SET NOCOUNT ON 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 SET NOCOUNT OFF 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, RTRIM(ISNULL(CNL.[DefaultClientAc],'')) AS DefaultClientAc, RTRIM(ISNULL(CNL.[DefaultClientDep],'')) AS DefaultClientDepAc FROM [dbo].[CONTROL] CNL LEFT JOIN [dbo].[VATcodes] VAC ON CNL.[Vatexclude] = VAC.[Code] SET NOCOUNT OFF END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetDepartments]') AND type in (N'P', N'PC')) BEGIN DROP PROCEDURE [dbo].[KAAS_GetDepartments] END GO /****** Object: StoredProcedure [dbo].[KAAS_GetDepartments] Script Date: 11-06-2021 17:46:11 ******/ CREATE PROCEDURE [dbo].[KAAS_GetDepartments] ( @Code varchar(20) ) AS /******************************************************************************************************* * Fetches Department Masters * * * * Stored Procedure Name : [dbo].[KAAS_GetDepartments] '0' * * * * Modification History: * * 11 June 2021 Natarajan S Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON IF(@Code <> '0') BEGIN SELECT [Code] AS [Code], [Description] AS [Department], [Retired] AS [Retired] FROM [dbo].[Departments] WHERE CODE = @Code END ELSE BEGIN SELECT [Code] AS [Code], [Description] AS [Department], [Retired] AS [Retired] FROM [dbo].[Departments] END SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_GetFeeEarnerCodesMasterDetails', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_GetFeeEarnerCodesMasterDetails] END GO CREATE PROCEDURE [dbo].[KAAS_GetFeeEarnerCodesMasterDetails] ( @Code VARCHAR(10)='' ) AS /******************************************************************************************************* * Fetch the Fee Earner Codes Master details * * * * Stored Procedure Name : [dbo].[KAAS_GetFeeEarnerCodesMasterDetails] * * * * Modification History: * * 2021-06-23 Balamurugan C Created * *******************************************************************************************************/ BEGIN SELECT [Code], [Name], [Nominal], [Retired] FROM [dbo].[FeeEarnerCodes] WHERE [CODE] = @Code ORDER BY [CODE] END GO IF OBJECT_ID(N'KAAS_GetHandlersMaster', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_GetHandlersMaster] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[KAAS_GetHandlersMaster] AS /******************************************************************************************************* * Fetches Handlers master list * * * * Stored Procedure Name : [dbo].[KAAS_GetHandlersMaster] * * * * Modification History: * * 2021-05-21 Natarajan S Created * *******************************************************************************************************/ BEGIN SELECT [HAN].CODE AS [Code], [HAN].TEAM AS [Team], [HAN].NAME AS [Name], [HAN].RATE AS [Rate], [HAN].RETIRED AS [Retired] FROM [dbo].Handlers AS [HAN] ORDER BY [HAN].CODE ASC 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 SET NOCOUNT ON 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 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] ( @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 CREATE PROCEDURE [dbo].[KAAS_GetNominalAccountsDetails] ( @Code VARCHAR(10)='', @Mode VARCHAR(10)='GRID' ) 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 * * 2021-06-25 Balamurugan C Select Nominal Details By Code * *******************************************************************************************************/ BEGIN /*MASTER DETAILS FROM NOMINAL TABLE*/ IF(@Mode='GRID') BEGIN SELECT [Code], [Desc], [Type], [Pb] FROM [dbo].[Nominal] ORDER BY [Code] ASC END ELSE BEGIN SELECT [Code], [Desc], [Type], [Pb], [Seq], [Retired], [ClientAc], [DepositYN], [DepositType], [ChequeReq], [ChequePrinter], [LastNo], [Bank], [BankBranch], [BankType], [BankPhone], [BankAddress], [AccountNo], [AccountName], [SortCode], [WithdrawNames], [AcOpened], [AcClosed], [Iban], [Bic], [OrgIDNumber] FROM [dbo].[Nominal] WHERE [Code]=@Code ORDER BY [Code] ASC END /*STATIC DETAILS FOR NOMINAL TYPE DROPDOWN*/ SELECT Rtrim([NTYCODE]) 'Code', Rtrim([NTYDESC]) 'Description' FROM [dbo].[NominalTypes] ORDER BY [NTYCODE] ASC /*STATIC DETAILS FOR NOMINAL REQPORT SEQ*/ SELECT Rtrim([CODE]) 'Code', Rtrim([DESC]) 'Desc', [PB] 'Pb' FROM [dbo].[NominalReportSeq] ORDER BY [CODE] ASC /*STATIC DETAILS FOR DEPOSIT TYPE*/ SELECT Rtrim([CODE]) 'Code', Rtrim([DESCRIPTION]) 'Description' FROM [dbo].[DepositType] ORDER BY [CODE] ASC END GO IF OBJECT_ID(N'KAAS_GetNominalBudgetDetails', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_GetNominalBudgetDetails] END GO CREATE PROCEDURE [dbo].[KAAS_GetNominalBudgetDetails] ( @Year INT=0, @Code VARCHAR(10)='' ) AS /******************************************************************************************************* * Fetches the Nomonal Budget details * * * * Stored Procedure Name : [dbo].[KAAS_GetNominalBudgetDetails] * * * * Modification History: * * 2021-05-06 Balamurugan C Created * * 2021-06-23 Balamurugan C Added Code where clause * *******************************************************************************************************/ 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 AND NOM.[Code]=@Code 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 OBJECT_ID(N'KAAS_GetOutlayCodeList',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_GetOutlayCodeList] GO CREATE PROCEDURE [dbo].[KAAS_GetOutlayCodeList] AS /******************************************************************************************************* * Fetches the Nominal list * * * * Stored Procedure Name : [dbo].[GetOutlayCodeList] * * * * Modification History: * * 2021-03-23 Revathy D Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT OCD.[CODE] AS Code, OCD.[DESCRIPTION] AS [Description] FROM [dbo].[OutlayCode] OCD ORDER BY OCD.[CODE] ASC SET NOCOUNT OFF 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 GO /****** Object: StoredProcedure [dbo].[KAAS_GetPaymentSupplierList] Script Date: 6/17/2021 6:17:13 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[KAAS_GetPaymentSupplierList] ( @ThirdParty varchar(1)='', @Code varchar(10)='' ) AS /******************************************************************************************************* * Fetches the Payment Screen -Supplier Details,Master Supplier Details * * * * Stored Procedure Name : [dbo].[KAAS_GetPaymentSupplierList] * * * * Modification History: * * 2021-05-19 Balamurugan.C Created * * 2021-06-16 Balamurugan.C Modified-Added Columns * *******************************************************************************************************/ BEGIN SELECT RTRIM([Code]) 'Code', [Description] FROM dbo.[ServiceCodes] SELECT [Code], [Name], [Address], [Nominal], [Phone], [EmailAddress], [TaxNo], [ServiceCode], [VATNo], [DeftpStatus], [BankName], [BankAddress], [BankSortCode], [BankAccNo], [Iban], [Bic], [BankType], [EFTEmailAddress], [Retired] FROM [dbo].[Suppliers] WHERE [DeftpStatus] = CASE WHEN @ThirdParty='Y' THEN 'Y' ELSE [DeftpStatus] END AND [Code]= CASE WHEN ISNULL(@Code,'')<>'' THEN @Code ELSE [Code] 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 /******************************************************************* * * Get Outstanding Invoices for a matter * 2020-05-18 Revathy Copied from KAAS_GetOutstandingInvoices *******************************************************************/ BEGIN SET NOCOUNT ON 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 SET NOCOUNT OFF 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 * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetWorkTypes]') AND type in (N'P', N'PC')) BEGIN DROP PROCEDURE [dbo].[KAAS_GetWorkTypes] END GO /****** Object: StoredProcedure [dbo].[KAAS_GetWorkTypes] Script Date: 11-06-2021 17:46:11 ******/ CREATE PROCEDURE [dbo].[KAAS_GetWorkTypes] ( @Code varchar(20) ) AS /******************************************************************************************************* * Fetches Work Types Masters * * * * Stored Procedure Name : [dbo].[KAAS_GetWorkTypes] 'ACC' * * * * Modification History: * * 11 June 2021 Natarajan S Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON IF(@Code <> '0') BEGIN SELECT [Code] AS [Code], [Description] AS [Description], [Retired] AS [Retired] FROM [dbo].[WorkTypes] WHERE CODE = @Code END ELSE BEGIN SELECT [Code] AS [Code], [Description] AS [Description], [Retired] AS [Retired] FROM [dbo].[WorkTypes] END SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_InsertContactCategory', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_InsertContactCategory] END GO /****** Object: StoredProcedure [dbo].[KAAS_InsertContactCategory] Script Date: 6/15/2021 10:19:22 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[KAAS_InsertContactCategory] ( @CatType VARCHAR(6)='', @Code VARCHAR(10)='', @Description VARCHAR(30)='', @Prompt1 VARCHAR(20)='', @Prompt2 VARCHAR(20)='', @Prompt3 VARCHAR(20)='', @Prompt4 VARCHAR(20)='', @Prompt5 VARCHAR(20)='', @Prompt6 VARCHAR(20)='', @Prompt7 VARCHAR(20)='', @Prompt8 VARCHAR(20)='', @Prompt9 VARCHAR(20)='', @Prompt10 VARCHAR(20)='', @mode VARCHAR(20)='INSERTEDIT' ) /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_InsertContactCategory] * * Copied from : NA * * * * Modification History : * * 2021-06-14 Balamurugan C Created * *******************************************************************************************************/ AS BEGIN IF( @mode = 'INSERTEDIT' ) BEGIN IF EXISTS (SELECT TOP 1 * FROM dbo.[category] WHERE [code] = @Code AND [cattype] = @CatType) BEGIN UPDATE dbo.[category] SET [desc$] = @Description, [prompt1] = @Prompt1, [prompt2] = @Prompt2, [prompt3] = @Prompt3, [prompt4] = @Prompt4, [prompt5] = @Prompt5, [prompt6] = @Prompt6, [prompt7] = @Prompt7, [prompt8] = @Prompt8, [prompt9] = @Prompt9, [prompt10] = @Prompt10 WHERE [code] = @Code AND [cattype] = @CatType END ELSE BEGIN INSERT INTO dbo.[category] ([code], [cattype], [desc$], [prompt1], [prompt2], [prompt3], [prompt4], [prompt5], [prompt6], [prompt7], [prompt8], [prompt9], [prompt10]) VALUES ( @Code, @CatType, @Description, @Prompt1, @Prompt2, @Prompt3, @Prompt4, @Prompt5, @Prompt6, @Prompt7, @Prompt8, @Prompt9, @Prompt10 ) END END ELSE BEGIN DELETE FROM dbo.[category] WHERE [code] = @Code AND [cattype] = @CatType END END GO IF OBJECT_ID(N'KAAS_InsertDeleteSuppliersDetails', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_InsertDeleteSuppliersDetails] END GO /****** Object: StoredProcedure [dbo].[KAAS_InsertDeleteSuppliersDetails] Script Date: 6/17/2021 6:19:33 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[KAAS_InsertDeleteSuppliersDetails] ( @Code VARCHAR(10)='', @Name VARCHAR(40)='', @Address VARCHAR(500)='', @Phone VARCHAR(50)='', @EmailAddress VARCHAR(100)='', @TaxNo VARCHAR(15)='', @ServiceCode VARCHAR(6)='', @VATNo VARCHAR(20)='', @DeftpStatus VARCHAR(1)='', @BankName VARCHAR(60)='', @BankAddress VARCHAR(120)='', @BankSortCode VARCHAR(20)='', @BankAccNo VARCHAR(20)='', @Iban VARCHAR(34)='', @Bic VARCHAR(20)='', @BankType VARCHAR(1)='', @EFTEmailAddress VARCHAR(150)='', @Retired VARCHAR(1)='', @Mode VARCHAR(20)='INSERTEDIT' ) /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_InsertDeleteSuppliersDetails] * * Copied from : NA * * * * Modification History : * * 2021-06-16 Balamurugan C Created * *******************************************************************************************************/ AS BEGIN IF( @Mode = 'INSERTEDIT' ) BEGIN IF EXISTS (SELECT TOP 1 * FROM dbo.[Suppliers] WHERE [code] = @Code) BEGIN UPDATE dbo.[Suppliers] SET [Code] = @Code, [Name] = @Name, [Address] = @Address, [Phone] = @Phone, [EmailAddress] = @EmailAddress, [TaxNo] = @TaxNo, [ServiceCode] = @ServiceCode, [VATNo] = @VATNo, [DeftpStatus] = @DeftpStatus, [BankName] = @BankName, [BankAddress] = @BankAddress, [BankSortCode] = @BankSortCode, [BankAccNo] = @BankAccNo, [Iban] = @Iban, [Bic] = @Bic, [BankType] = @BankType, [EFTEmailAddress] = @EFTEmailAddress, [Retired] = @Retired WHERE [Code] = @Code END ELSE BEGIN INSERT INTO dbo.[Suppliers] ( [Code], [Name], [Address], [Phone], [EmailAddress], [TaxNo], [ServiceCode], [VATNo], [DeftpStatus], [BankName], [BankAddress], [BankSortCode], [BankAccNo], [Iban], [Bic], [BankType], [EFTEmailAddress], [Retired] ) VALUES ( @Code, @Name, @Address, @Phone, @EmailAddress, @TaxNo, @ServiceCode, @VATNo, @DeftpStatus, @BankName, @BankAddress, @BankSortCode, @BankAccNo, @Iban, @Bic, @BankType, @EFTEmailAddress, @Retired ) END END ELSE BEGIN DELETE FROM dbo.[Suppliers] WHERE [Code] = @Code END 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_InsertUpdateFeeEarnerCodesDetails', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_InsertUpdateFeeEarnerCodesDetails] END GO CREATE PROCEDURE [dbo].[KAAS_InsertUpdateFeeEarnerCodesDetails] ( @Code VARCHAR(10)='', @Name VARCHAR(40)='', @Nominal VARCHAR(10)='', @Retired VARCHAR(1)='', @Mode VARCHAR(20)='INSERTEDIT' ) /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_InsertUpdateFeeEarnerCodesDetails] * * Copied from : NA * * * * Modification History : * * 2021-06-23 Balamurugan C Created * *******************************************************************************************************/ AS BEGIN IF( @Mode = 'INSERTEDIT' ) BEGIN IF EXISTS (SELECT TOP 1 1 FROM [dbo].[FeeEarnerCodes] WHERE [Code] = @Code) BEGIN UPDATE [dbo].[FeeEarnerCodes] SET [Name] = @Name, [Nominal] = @Nominal, [Retired] = @Retired WHERE [CODE] = @Code END ELSE BEGIN INSERT INTO [dbo].[FeeEarnerCodes] ( [Code], [Name], [Nominal], [Retired] ) VALUES ( @Code, @Name, @Nominal, @Retired ) END END ELSE BEGIN DELETE FROM [dbo].[FeeEarnerCodes] WHERE [CODE] = @Code END END GO IF OBJECT_ID(N'KAAS_InsertUpdateNominalReportingSeq', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_InsertUpdateNominalReportingSeq] END GO CREATE PROCEDURE [dbo].[KAAS_InsertUpdateNominalReportingSeq] ( @Code VARCHAR(4)='', @Desc VARCHAR(40)='', @Pb VARCHAR(1)='', @Mode VARCHAR(20)='INSERTEDIT' ) /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_InsertUpdateNominalReportingSeq] * * Copied from : NA * * * * Modification History : * * 2021-06-18 Balamurugan C Created * *******************************************************************************************************/ AS BEGIN IF( @Mode = 'INSERTEDIT' ) BEGIN IF EXISTS (SELECT TOP 1 1 FROM [dbo].[NominalReportSeq] WHERE [Code] = @Code) BEGIN UPDATE [dbo].[NominalReportSeq] SET [DESC] = @Desc, [PB] = @Pb WHERE [CODE] = @Code END ELSE BEGIN INSERT INTO [dbo].[NominalReportSeq] ( [CODE], [DESC], [PB] ) VALUES ( @Code, @Desc, @Pb ) END END ELSE BEGIN DELETE FROM [dbo].[NominalReportSeq] WHERE [CODE] = @Code END END GO IF OBJECT_ID(N'KAAS_InsertUpdatePaymentMethods', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_InsertUpdatePaymentMethods] END GO CREATE PROCEDURE [dbo].[KAAS_InsertUpdatePaymentMethods] ( @Id INT=0, @Description VARCHAR(500)='', @ClearDays INT=0, @Retired TINYINT=0, @Mode VARCHAR(20)='INSERTEDIT' ) /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_InsertUpdatePaymentMethods] * * Copied from : NA * * * * Modification History : * * 2021-06-22 Balamurugan C Created * *******************************************************************************************************/ AS BEGIN IF( @Mode = 'INSERTEDIT' ) BEGIN IF EXISTS (SELECT TOP 1 1 FROM [dbo].[PaymentMethods] WHERE [id] = @Id) BEGIN UPDATE [dbo].[PaymentMethods] SET [Description] = @Description, [ClearDays] = @ClearDays, [Retired] = @Retired WHERE [id] = @Id END ELSE BEGIN INSERT INTO [dbo].[PaymentMethods] ( [Description], [ClearDays], [Retired] ) VALUES ( @Description, @ClearDays, @Retired ) END END ELSE BEGIN DELETE FROM [dbo].[PaymentMethods] WHERE [id] = @Id END END GO IF OBJECT_ID(N'KAAS_InsertUpdateVATCodes', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_InsertUpdateVATCodes] END GO CREATE PROCEDURE [dbo].[KAAS_InsertUpdateVATCodes] ( @Code VARCHAR(1)='', @Rate DECIMAL(5,2)=0, @Mode VARCHAR(20)='INSERTEDIT' ) /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_InsertUpdateVATCodes] * * Copied from : NA * * * * Modification History : * * 2021-06-21 Balamurugan C Created * *******************************************************************************************************/ AS BEGIN IF( @Mode = 'INSERTEDIT' ) BEGIN IF EXISTS (SELECT TOP 1 1 FROM [dbo].[VATCodes] WHERE [Code] = @Code) BEGIN UPDATE [dbo].[VATCodes] SET [RATE] = @Rate WHERE [CODE] = @Code END ELSE BEGIN INSERT INTO [dbo].[VATCodes] ( [CODE], [RATE] ) VALUES ( @Code, @Rate ) END END ELSE BEGIN DELETE FROM [dbo].[VATCodes] WHERE [CODE] = @Code END END GO IF EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO WHERE SO.[name] = N'KAAS_MaintainInsertMaster' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_MaintainInsertMaster] END GO CREATE PROCEDURE [dbo].[KAAS_MaintainInsertMaster] ( @MasterName VARCHAR(50), @Code VARCHAR(100), @Description VARCHAR(MAX), @Retired VARCHAR(2), @Childcare CHAR(1) ='', @RGBColour INT =0, @DefaultRate Float=0, @ClientCharge INT=0, @Value FLOAT=0, @ClientValue FLOAT=0, @User VARCHAR(8)='' , @Mode VARCHAR(10), @NewCodeVal varchar(20)='', @Result bit OUTPUT ) AS /******************************************************************************************************* *This procedure is used to Fetch details FROM the below listed Masters table. * * [dbo].[KAAS_MaintainInsertMaster] 'UndertakingStatus','as','yu','N','',0,0,0,0,0,'','INSERT','yu',0 select * from undertakingstatus * * Stored Procedure Name : [dbo].[KAAS_MaintainInsertMaster] * * Copied from : [dbo].[ky_NETMaintainInsertMaster] * * * * Modification History : * * 2021-06-16 NATARAJAN S Created * *******************************************************************************************************/ BEGIN DECLARE @COMMAND VARCHAR(MAX) DECLARE @HANDLERRATE as FLOAT DECLARE @EVENTDESC as VARCHAR(300) SET @HANDLERRATE = 0 SET @EVENTDESC='' DECLARE @TableVariable table (code varchar(50)) INSERT INTO @TableVariable EXEC [dbo].[Kaas_fn_checkcodeexists] @Mode, @MasterName, @Code, @NewCodeVal IF EXISTS (SELECT 1 FROM @TableVariable) BEGIN SET @Result =0 END ELSE BEGIN IF (@MasterName='Departments') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [dbo].[departments] ([code], [description], [desc], [retired]) VALUES (@Code, @Description, @Description, @Retired) ELSE UPDATE [dbo].[departments] SET [code] = @NewCodeVal, [description] = @Description, [desc] = @Description, [retired] = @Retired WHERE [code] = @Code END ELSE IF (@MasterName='Worktypes') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [worktypes] ([CODE], [Description], [DESC], [RETIRED]) VALUES (@Code, @Description, @Description, @Retired) ELSE UPDATE [worktypes] SET [code] = @NewCodeVal, [description] = @Description, [desc] = @Description, [retired] = @Retired WHERE code = @Code END ELSE IF (@MasterName='NominalTypes') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [dbo].[nominaltypes] ([ntycode], [ntydesc]) VALUES (@Code, @Description) ELSE UPDATE [nominaltypes] SET [ntycode] = @NewCodeVal, [ntydesc] = @Description WHERE [ntycode] = @Code END ELSE IF ( @MasterName = 'CategoryType' ) BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [dbo].categorytype ([categorytype], [description]) VALUES (@Code, @Description) ELSE UPDATE [CategoryType] SET [CategoryType] = @NewCodeVal, [Description] = @description WHERE Rtrim(CategoryType) = @Code END ELSE IF (@MasterName='NationCodes') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [dbo].[NationCodes] ([NATION], [DESC]) VALUES (@Code, @Description) ELSE UPDATE [NationCodes] SET [NATION] = @NewCodeVal, [DESC] = @Description WHERE Rtrim(NATION) = @Code END ELSE IF (@MasterName='DepositType') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [DepositType] ([CODE], [Description] ) VALUES (@Code, @Description) ELSE UPDATE [DepositType] SET [code] = @NewCodeVal, [description] = @Description WHERE code = @Code END ELSE IF (@MasterName='OutlayCode') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [OutlayCode] ([CODE], [Description] ) VALUES (@Code, @Description) ELSE UPDATE [OutlayCode] SET [CODE] = @NewCodeVal, [DESCRIPTION] = @Description WHERE [CODE] = @Code END ELSE IF (@MasterName='Narratives') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [Narratives] ([CODE], [NARRATIVE] ) VALUES (@Code, @Description) ELSE UPDATE [Narratives] SET [CODE] = @NewCodeVal, [NARRATIVE] = @Description WHERE [CODE] = @Code END ELSE IF (@MasterName='ServiceCodes') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [ServiceCodes] ([CODE], [Description] ) VALUES (@Code, @Description) ELSE UPDATE [ServiceCodes] SET [CODE] = @NewCodeVal, [DESCRIPTION] = @Description WHERE [CODE] = @Code END ELSE IF (@MasterName='FileColours') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [FileColours] ([COLOURCODE], [COLOURDESC], [RGBColour], [BackgroundColour], [ForegroundColour] ) VALUES (@Code, @Description, ISNULL(@RGBColour, -1), @Description, @Description) ELSE UPDATE [FileColours] SET [COLOURCODE] = @NewCodeVal, [COLOURDESC] = @Description, [RGBColour] = ISNULL(@RGBColour, -1), [BackgroundColour] = @Description, [ForegroundColour] =@Description WHERE [COLOURCODE] = @Code END ELSE IF (@MasterName='Branch') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [Branch] ([BRANCHCODE], [DESCRIPTION], [RETIRED]) VALUES (@Code, @Description, CASE @Retired WHEN 'Y' THEN 1 ELSE 0 END) ELSE UPDATE [Branch] SET [BRANCHCODE] = @NewCodeVal, [DESCRIPTION] = @Description, [Retired] = CASE @Retired WHEN 'Y' THEN 1 ELSE 0 END WHERE [BRANCHCODE] = @Code END ELSE IF (@MasterName='UndertakingStatus') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [UndertakingStatus] ([StatusDesc], [Retired]) VALUES (@NewCodeVal, @Retired) ELSE UPDATE [UndertakingStatus] SET [StatusDesc] = @NewCodeVal, [Retired] = @Retired WHERE [RecordID] = @Code END ELSE IF (@MasterName='FeeEarners') BEGIN IF EXISTS(SELECT TOP 1 1 FROM FeeEarnerCodes WHERE RTRIM(CODE)=@code ) BEGIN UPDATE FeeEarnerCodes SET CODE = @code ,NAME = @description ,RETIRED = @retired WHERE RTRIM(CODE) = @code END ELSE BEGIN INSERT INTO FeeEarnerCodes (CODE,NAME,RETIRED ) VALUES (@code,@description ,@retired) END END ELSE IF (@MasterName='FileColour') BEGIN IF EXISTS(SELECT TOP 1 1 FROM FileColours WHERE RTRIM(ISNULL(COLOURCODE,''))=@code ) BEGIN UPDATE FileColours SET COLOURCODE = @code ,COLOURDESC = @description ,RGBColour = ISNULL(@RGBColour, -1) ,Retired = @retired WHERE RTRIM(ISNULL(COLOURCODE,'')) = @code END ELSE BEGIN INSERT INTO FileColours (COLOURCODE,COLOURDESC, RGBColour,Retired) VALUES (@code,@description, ISNULL(@RGBColour, -1),@retired ) END END ELSE IF (@MasterName='Maritals') BEGIN IF EXISTS(SELECT TOP 1 1 FROM dbo.MiscCodes WHERE RTRIM(CDECODE)=@code AND CDETYPE='MRS') BEGIN UPDATE dbo.MiscCodes SET CDECODE = @code ,CDEDESC = @description ,Retired = @retired WHERE RTRIM(CDECODE)=@code AND CDETYPE='MRS' END ELSE BEGIN INSERT INTO dbo.MiscCodes (CDETYPE,CDECODE,CDEDESC,Retired) VALUES ('MRS',@code,@description,@retired) END END ELSE IF (@MasterName='TaxType') BEGIN IF EXISTS(SELECT TOP 1 1 FROM TaxType WHERE RTRIM(TaxType)=@code) BEGIN UPDATE TaxType SET TaxType = @code ,TaxDesc = @description WHERE RTRIM(TaxType) = @code END ELSE BEGIN INSERT INTO TaxType (TaxType,TaxDesc) VALUES (@code,@description) END END ELSE IF (@MasterName='Standard Narratives') BEGIN IF EXISTS(SELECT TOP 1 1 FROM DesktopNarr WHERE RTRIM(code)=@code) BEGIN UPDATE DesktopNarr SET code = @code ,NARRATIVE = @description ,Retired = @retired WHERE RTRIM(code) = @code END ELSE BEGIN INSERT INTO DesktopNarr (code,NARRATIVE,Retired) VALUES (@code,@description,@retired) END END ELSE IF (@MasterName='Admin Codes') BEGIN IF EXISTS(SELECT TOP 1 1 FROM TimeAdminCodes WHERE RTRIM(NCCODE)=@code) BEGIN UPDATE TimeAdminCodes SET NCCODE = @code ,[DESCRIPTION] = @description ,Retired = @retired WHERE RTRIM(NCCODE) = @code END ELSE BEGIN INSERT INTO TimeAdminCodes (NCCODE,[DESCRIPTION],Retired) VALUES (@code,@description,@retired) END END ELSE IF (@MasterName='Billing') BEGIN IF EXISTS(SELECT TOP 1 1 FROM dbo.BillDescriptions a WHERE RTRIM(ISNULL(SEARCHFIELD,''))=@code) BEGIN UPDATE BillDescriptions SET SEARCHFIELD = @code ,BILLDESCRIPTIONS = @description ,Retired = @retired WHERE RTRIM(NUMBER) = @RGBColour END ELSE BEGIN INSERT INTO BillDescriptions (SEARCHFIELD,BILLDESCRIPTIONS,Retired) VALUES (@code,@description,@retired) END END ELSE IF (@MasterName='Undertaking') BEGIN IF EXISTS(SELECT TOP 1 1 FROM UndertakingStatus WHERE RecordID = @code) BEGIN UPDATE UndertakingStatus SET StatusDesc = @description, Retired = @retired WHERE RecordID = @code END ELSE BEGIN INSERT INTO UndertakingStatus (StatusDesc,Retired) VALUES (@description,@retired) END END ELSE IF (@MasterName='Branch Code') BEGIN IF EXISTS(SELECT TOP 1 1 FROM Branch WHERE RTRIM(BRANCHCODE)=@code) BEGIN UPDATE Branch SET BRANCHCODE = @code ,DESCRIPTION = @description WHERE RTRIM(BRANCHCODE) = @code END ELSE BEGIN INSERT INTO Branch (BRANCHCODE,[Description]) VALUES (@code,@description) END END ELSE IF (@MasterName='Tax Head') BEGIN IF EXISTS(SELECT TOP 1 1 FROM taxhead WHERE RTRIM(TAXHEAD)=@code) BEGIN UPDATE taxhead SET [TAXHEAD] = @code ,[DESCRIPTION] = @description WHERE RTRIM(TAXHEAD)=@code END ELSE BEGIN INSERT INTO taxhead (taxhead,[Description]) VALUES (@code,@description) END END ELSE IF (@MasterName='Salutation') BEGIN IF EXISTS(SELECT TOP 1 1 FROM SalutationCodes WHERE RTRIM(Code)=@code) BEGIN UPDATE SalutationCodes SET Code=@code ,DESCRIPTION=@description WHERE RTRIM(Code)=@code END ELSE BEGIN INSERT INTO SalutationCodes (Code,[Description]) VALUES (@code,@description) END END ELSE IF (@MasterName='Charge Type') BEGIN IF EXISTS(SELECT TOP 1 1 FROM chargetype WHERE RTRIM(chargetype)=@code AND RTRIM(TAXHEAD)=@retired) BEGIN UPDATE chargetype SET CHARGETYPE = @code ,[DESCRIPTION] = @description WHERE RTRIM(chargetype) = @code AND RTRIM(TAXHEAD) = @retired END ELSE BEGIN INSERT INTO chargetype (CHARGETYPE,[Description],TAXHEAD) VALUES (@code,@description,@retired) END END ELSE IF (@MasterName='Item Types') BEGIN IF EXISTS(SELECT TOP 1 1 FROM ItemTypes WHERE RTRIM(TYPECODE)=@code) BEGIN UPDATE ItemTypes SET TYPEDESCRIPTION = @description WHERE RTRIM(TYPECODE) = @code END ELSE BEGIN INSERT INTO ItemTypes (TYPECODE,TYPEDESCRIPTION) VALUES (@code,@description) END END ELSE IF (@MasterName='Location') BEGIN IF EXISTS(SELECT TOP 1 1 FROM ItemLocations WHERE RTRIM(CODE)=@code) BEGIN UPDATE ItemLocations SET [DESCRIPTION] = @description ,FULLYN = @childcare ,Retired = @retired WHERE RTRIM(CODE) = @code END ELSE BEGIN INSERT INTO ItemLocations (CODE,[DESCRIPTION],FULLYN,Retired) VALUES (@code,@description,@childcare,@retired ) END END ELSE IF (@MasterName='GroupModule') BEGIN IF EXISTS(SELECT TOP 1 1 FROM ModuleGroup WHERE RTRIM(GroupName)=@code) BEGIN UPDATE ModuleGroup SET [DESCRIPTION] = @description ,IsActive = @retired WHERE RTRIM(GroupName) = @code END ELSE BEGIN INSERT INTO ModuleGroup (GroupName,[DESCRIPTION],IsActive) VALUES (@code,@description,@retired) END END ELSE IF (@MasterName='Occupation') BEGIN IF EXISTS(SELECT TOP 1 1 FROM MiscCodes WHERE RTRIM(CDECODE) = RTRIM(@code) AND RTRIM(CDETYPE) = 'OCC') BEGIN UPDATE MiscCodes SET CDEDESC = @description WHERE RTRIM(CDECODE) = @code END ELSE BEGIN INSERT INTO misccodes(CDETYPE, CDECODE, CDEDESC, CDETEAM ) VALUES ('OCC',@code,@description,'') END END ELSE IF (@MasterName='Contact Type') BEGIN IF EXISTS(SELECT TOP 1 1 FROM MiscCodes WHERE RTRIM(CDECODE) = RTRIM(@code) AND RTRIM(CDETYPE) = 'CCL') BEGIN UPDATE MiscCodes SET CDEDESC = @description WHERE RTRIM(CDECODE ) = @code END ELSE BEGIN INSERT INTO misccodes(CDETYPE, CDECODE, CDEDESC, CDETEAM ) VALUES ('CCL',@code,@description,'') END END ELSE IF (@MasterName='Client Group') BEGIN IF EXISTS(SELECT TOP 1 1 FROM ClientGroup WHERE RTRIM(GroupCode) = RTRIM(@code)) BEGIN UPDATE ClientGroup SET GroupName = @description WHERE RTRIM(GroupCode) = @code END ELSE BEGIN INSERT INTO ClientGroup(GroupCode, GroupName ) VALUES (@code,@description) END END ELSE IF (@MasterName='HandlerChargeGroup') BEGIN IF EXISTS(SELECT TOP 1 1 FROM HandlerChargeGroups WHERE RTRIM(ISNULL(Code,'')) = RTRIM(@code)) BEGIN SET @HANDLERRATE=(Select Defaultrate from HandlerChargeGroups where RTRIM(ISNULL(Code,'')) = @code) IF @HANDLERRATE <> @DefaultRate BEGIN SET @EVENTDESC='Updated Default Rate from ( ' + CONVERT(varchar(10),@HANDLERRATE)+ ' ) to ( ' + CONVERT(varchar(10),@DefaultRate) + ' )' INSERT INTO [dbo].RateChangesLog ([User], [Event], [FromWhere], [UpdatedDate], [AffectedTo]) VALUES (@User, @EVENTDESC, 'Maintain Handler Charge Group', GETDATE(), @code) END UPDATE HandlerChargeGroups SET Name = @description, DefaultRate= @DefaultRate WHERE RTRIM(ISNULL(Code,'')) = @code END ELSE BEGIN INSERT INTO HandlerChargeGroups (Code,Name,DefaultRate ) VALUES (@code,@description,@DefaultRate) END END ELSE IF (@MasterName='StatusCodes') BEGIN IF EXISTS(SELECT TOP 1 1 FROM StatusCodes WHERE RTRIM(Code) = RTRIM(@code)) BEGIN UPDATE StatusCodes SET [DESCRIPTION] = @description, Retired = @retired WHERE RTRIM(Code) = @code UPDATE [SM] SET [SM].[MatterStatusDescription] = RTRIM(ISNULL(@description, '')) FROM [dbo].[SearchMatters] [SM] WHERE [SM].[MatterStatus] = @code END ELSE BEGIN INSERT INTO StatusCodes (Code,[DESCRIPTION],Retired) VALUES (@code,@description,@retired) END END ELSE IF (@MasterName='UDFCategory') BEGIN IF EXISTS(SELECT TOP 1 1 FROM UDFCategory WHERE id=@code) BEGIN UPDATE UDFCategory SET UDFCategory = @description WHERE id = @code END ELSE BEGIN INSERT INTO UDFCategory(UDFCategory ) VALUES (@description) END END ELSE IF (@MasterName='SDLTForm') BEGIN IF EXISTS(SELECT TOP 1 1 FROM SDLTForm WHERE FormID=@RGBColour) BEGIN UPDATE SDLTForm SET FormName = @code , [Description] = @description WHERE FormID = @RGBColour END ELSE BEGIN INSERT INTO SDLTForm(FormName,[Description]) VALUES (@code,@description) END END ELSE IF (@MasterName='Undertaking Category') BEGIN IF EXISTS(SELECT TOP 1 1 FROM UndertakingsCategory WHERE RTRIM(CODE)= RTRIM(@code)) BEGIN UPDATE UndertakingsCategory SET [Description] = @description WHERE RTRIM(CODE) = @code END ELSE BEGIN INSERT INTO UndertakingsCategory(CODE,[DESCRIPTION]) VALUES (@code,@description) END END ELSE IF (@MasterName='KYCType') BEGIN IF EXISTS(SELECT TOP 1 1 FROM KYCType WHERE RTRIM(PK_Type)= RTRIM(@code)) BEGIN UPDATE KYCType SET [Description] = @description WHERE RTRIM(PK_Type) = @code END ELSE BEGIN INSERT INTO KYCType([DESCRIPTION]) VALUES (@description) END END ELSE IF (@MasterName='IntroBy') BEGIN IF EXISTS(SELECT TOP 1 1 FROM IntroductionBy WHERE RTRIM(Pk_Intro)= RTRIM(@code)) BEGIN UPDATE IntroductionBy SET [Description] = @description WHERE RTRIM(Pk_Intro) = @code END ELSE BEGIN INSERT INTO IntroductionBy([DESCRIPTION]) VALUES (@description) END END ELSE IF (@MasterName='ClientType') BEGIN IF EXISTS(SELECT TOP 1 1 FROM ClientType WHERE RTRIM(PK_ClientType)= RTRIM(@code)) BEGIN UPDATE ClientType SET [Description] = @description WHERE RTRIM(PK_ClientType) = @code END ELSE BEGIN INSERT INTO ClientType([DESCRIPTION]) VALUES (@description) END END ELSE IF (@MasterName='ID Code') BEGIN IF EXISTS(SELECT TOP 1 1 FROM IDCodes WHERE RTRIM(IdCode)= RTRIM(@code)) BEGIN UPDATE IDCodes SET [Description] = @description, Category = @retired WHERE RTRIM(IdCode) = @code END ELSE BEGIN INSERT INTO IdCodes(IdCode,[DESCRIPTION],Category) VALUES (@code,@description,@retired) END END ELSE IF (@MasterName='Outlay Code') BEGIN IF [dbo].[ISSAM4]() = 0 BEGIN SET @COMMAND = ' IF EXISTS(SELECT TOP 1 1 FROM OutlayCode WHERE RTRIM(CODE)= RTRIM(''' + Replace(@code, '''', '''''') + ''')) BEGIN UPDATE OutlayCode SET [Description] = ''' + Replace(@description, '''', '''''') + ''' WHERE RTRIM(CODE) = ''' + Replace(@code, '''', '''''') + ''' END ELSE BEGIN INSERT INTO OutlayCode(CODE,[DESCRIPTION]) VALUES (''' + Replace(@code, '''', '''''') + ''',''' + Replace(@description, '''', '''''') + ''') END' EXEC (@COMMAND) END ELSE BEGIN SET @COMMAND = ' IF EXISTS(SELECT TOP 1 1 FROM OutlayCode WHERE RTRIM([OutlayCode])= RTRIM(''' + Replace(@code, '''', '''''') + ''')) BEGIN UPDATE OutlayCode SET [Description] = ''' + Replace(@description, '''', '''''') + ''' WHERE RTRIM([OutlayCode]) = ''' + Replace(@code, '''', '''''') + ''' END ELSE BEGIN INSERT INTO OutlayCode([OutlayCode],[DESCRIPTION]) VALUES (''' + Replace(@code, '''', '''''') + ''',''' + Replace(@description, '''', '''''') + ''') END' EXEC (@COMMAND) END END ELSE IF (@MasterName='Interest') BEGIN IF EXISTS(SELECT TOP 1 1 FROM dbo.MiscCodes WHERE RTRIM(CDECODE)=@code AND CDETYPE='IRT') BEGIN UPDATE dbo.MiscCodes SET CDECODE = @code ,CDEDESC = @description ,Retired = @retired WHERE RTRIM(CDECODE)=@code AND CDETYPE='IRT' END ELSE BEGIN INSERT INTO dbo.MiscCodes (CDETYPE,CDECODE,CDEDESC,Retired) VALUES ('IRT',@code,@description,@retired) END END ELSE IF (@MasterName='CourtFees') BEGIN IF EXISTS(SELECT TOP 1 1 FROM dbo.CourtFees WHERE RTRIM(CODE)=@code) BEGIN UPDATE CourtFees SET CODE=@code, DESCRIPTION=@description, CHARGECLIENT=@clientCharge, VALUE=@value, CLIENTVALUE=@clientvalue WHERE CODE=@code END ELSE BEGIN INSERT INTO CourtFees (CODE,[DESCRIPTION],CHARGECLIENT,VALUE,CLIENTVALUE) VALUES (@code,@description,@clientCharge,@value,@clientvalue) END END SET @Result=1 END 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