IF OBJECT_ID(N'KAAS_CheckHandlerCodeExist', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_CheckHandlerCodeExist] END GO CREATE PROCEDURE [dbo].[KAAS_CheckHandlerCodeExist] (@HandlerCode VARCHAR(10), @Result TINYINT OUTPUT) AS BEGIN /************************************************************************************************************* * * * [dbo].[KAAS_CheckHandlerCodeExist] * * * * Description: This procedure is used to check handler code exists or not * * * * ***************************************************************************************************** * * * * Modification history: * * 2021-06-25 Natarajan S Created * *************************************************************************************************************/ IF EXISTS (SELECT TOP 1 1 FROM [dbo].[Handlers] HAN WHERE HAN.[CODE] = @HandlerCode) BEGIN SET @Result = 1 END ELSE IF EXISTS (SELECT TOP 1 1 FROM [dbo].[HandlerChargeGroups] HCG WHERE HCG.[Code] = @HandlerCode) BEGIN SET @Result = 2 END ELSE BEGIN SET @Result = 0 END END GO 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) = '', @FEE varchar(10) = '' ) 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, [FEE] = @FEE 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], [FEE] ) 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, @FEE ) 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 EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_DeleteDebtorsLedgerAllocation' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_DeleteDebtorsLedgerAllocation] END GO CREATE PROCEDURE [dbo].[KAAS_DeleteDebtorsLedgerAllocation] (@KeyId INT, @Result TINYINT OUTPUT) AS /******************************************************************************************************* * * * Used to Insert supplier ledger allocation * * Stored Procedure Name: [dbo].[KAAS_DeleteDebtorsLedgerAllocation] * * Modification History: * * 2021-03-26 Natarajan S Created * * 2021-03-28 Natarajan S Modified - Added @result parameter after update * ********************************************************************************************************/ BEGIN TRY BEGIN TRANSACTION DECLARE @BillBatchNo INT, @PayBatchNo INT, @Value INT, @Fees INT, @Outlay INT, @Vat INT, @AllNo INT SELECT @BillBatchNo = [BatchNo], @PayBatchNo = [ABatchNo], @Value = [Value], @Fees = [Fees], @Outlay = [Outlay], @Vat = [Vat], @AllNo = [AllNo] FROM [dbo].[Allocations] WHERE [KeyId]=@KeyId UPDATE [DBL] SET [DBL].[OSValue] = CASE [DBL].[Type] WHEN 'R' THEN [DBL].[OSValue] + @Value ELSE [DBL].[OSValue] - @Value END, [DBL].[OSFees] = CASE [DBL].[Type] WHEN 'R' THEN [DBL].[OSFees] + @Fees ELSE [DBL].[OSFees] - @Fees END, [DBL].[OSOutlay] = CASE [DBL].[Type] WHEN 'R' THEN [DBL].[OSOutlay] + @Outlay ELSE [DBL].[OSOutlay] - @Outlay END, [DBL].[OSVat] = CASE [DBL].[Type] WHEN 'R' THEN [DBL].[OSVat] + @Vat ELSE [DBL].[OSVat] - @Vat END FROM [dbo].[DebtorsLedger] [DBL] WHERE [DBL].[BatchNo] IN (@BillBatchNo,@PayBatchNo) DELETE FROM [dbo].[Allocations] WHERE [AllNo] = @AllNo SET @Result =1 COMMIT TRANSACTION END TRY BEGIN CATCH SET @Result =0 ROLLBACK TRANSACTION END CATCH 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 OBJECT_ID(N'KAAS_DeleteSAMAccruals', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_DeleteSAMAccruals] END GO CREATE PROCEDURE [dbo].[KAAS_DeleteSAMAccruals] ( @RecordId INT ) AS /******************************************************************************************************* * [dbo].[KAAS_DeleteSAMAccruals] * * Description: Used to delete accrual detail * * Modification History: * * 2021-08-04 Revathy D Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON BEGIN DELETE FROM [dbo].[Accruals] WHERE [RECORDID] = @RecordId END SET NOCOUNT OFF END GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_DeleteSupplierLedger' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_DeleteSupplierLedger] END go CREATE PROCEDURE [dbo].[KAAS_DeleteSupplierLedger] (@RecordId INT, @Result TINYINT OUTPUT) AS /******************************************************************************************************* * * * Used to delete the supplier ledger * * Stored Procedure Name: [dbo].[KAAS_DeleteSupplierLedger] * * Modification History: * * 2021-07-07 Natarajan S Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON DELETE FROM [dbo].[SupplierLedger] WHERE [RecordId]=@RecordId SET @Result = 1 SET NOCOUNT OFF END GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_DeleteSupplierLedgerAllocation' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_DeleteSupplierLedgerAllocation] END go CREATE PROCEDURE [dbo].[KAAS_DeleteSupplierLedgerAllocation] (@KeyId INT, @Result TINYINT OUTPUT) AS /******************************************************************************************************* * * *Used to Insert supplier ledger allocation * * Stored Procedure Name: [dbo].[KAAS_DeleteSupplierLedgerAllocation] * * Modification History: * * 2021-03-26 Natarajan S Created * ********************************************************************************************************/ BEGIN TRY BEGIN TRANSACTION DECLARE @BillBatchNo INT, @PayBatchNo INT, @Value INT, @AllNo INT SELECT @BillBatchNo = BatchNo, @PayBatchNo = ABatchNo, @Value = Value, @AllNo = AllNo FROM [dbo].[Allocations] WHERE [KeyId]=@KeyId UPDATE [SLD] SET [SLD].[OSValue] = CASE [SLD].[TYPE] WHEN 'P' THEN [SLD].[OSValue]+ @Value ELSE [SLD].[OSValue]- @Value END FROM [dbo].[SupplierLedger] [SLD] WHERE [SLD].[BatchNo] IN (@BillBatchNo,@PayBatchNo) DELETE FROM [dbo].[Allocations] WHERE [AllNo] = @AllNo COMMIT TRANSACTION END TRY BEGIN CATCH SET @Result =0 ROLLBACK TRANSACTION END CATCH 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_FetchContacts]',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_FetchContacts] GO CREATE PROCEDURE [dbo].[KAAS_FetchContacts] AS /******************************************************************************************************* * This procedure is used To Fetch Contacts for Browse Contacts page * * CaseContacts AND CaseAssociateTypes . * * * * Stored Procedure Name : [dbo].[KAAS_FetchContacts] * * * * Modification History : * * 2021-07-14 Prabhu V Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT [Con].[Code], [Con].[Name], [Con].[Address], [Con].[Principal], [Con].[FE], [Con].[Client], [Con].[Nation], [Con].[ClientGroup], [Con].[OpenMatters], [Con].[StartDate], [Con].[BillBal], [Con].[OutlayBal], [Con].[ClientCur], [Con].[ClientDep], [Con].[RSINo], [Con].[OtherRef], [Con].[OpsisRef], [Con].[ContactNo], [Con].[CompBillOnOff], [Con].[TaxType] FROM [dbo].[contacts] [Con] WHERE [Con].[Client] = 'Y' ORDER BY [Con].[Client] ASC, [Con].[Code] ASC SET NOCOUNT OFF 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 TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_GetBatchByNo' AND SO.[type] = 'P' ) BEGIN DROP PROCEDURE [dbo].[KAAS_GetBatchByNo] END GO 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: * * 03 May 2021 Natarajan S Created * * 13 July 2021 Natarajan S Modified - Formatted as per coding standard * *******************************************************************************************************/ 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_GetCreditorsAllocateDetails', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_GetCreditorsAllocateDetails] END GO CREATE PROCEDURE [dbo].[KAAS_GetCreditorsAllocateDetails] (@BatchNo int) AS /******************************************************************************************************* * Fetches the Creditors Allocate details * * * * Stored Procedure Name : [dbo].[KAAS_GetCreditorsAllocateDetails] * * SAMPLE DATA [KAAS_GetCreditorsAllocateDetails] 5588 * * * Modification History: * * 2021-07-05 Natarajan S Created * * 2021-07-07 Natarajan S Modified Added record id * * 2021-07-09 Natarajan S Modified changed column record id to KeyId,added PRef * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT [SUP].[CODE] AS [Code], [SUP].[Name] AS [Name], [SLD].[Date] AS [Date], [SLD].[Ref] AS [Ref], [SLD].[Original] AS [Original], [SLD].[OSValue] AS [ToAllocate], [SLD].[Matter] AS [Matter], [SLD].[PREF] AS [PRef] FROM [dbo].[SupplierLedger] [SLD] INNER JOIN [dbo].[Suppliers] [SUP] ON [SUP].[Code] = [SLD].[Supp] WHERE [SLD].[BatchNo] = @BatchNo SELECT [ALC].[Date], [ALC].[ORef], [SLD].[Date] AS [AgainstDate], [ALC].[Value] AS [Allocated], [ALC].[UserCode], [ALC].[KeyId] FROM [dbo].[SupplierLedger] [SLD] INNER JOIN [dbo].[Allocations] [ALC] ON [SLD].[BatchNo] = [ALC].[BatchNo] WHERE [SLD].[BatchNo] = @BatchNo 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 * * 2021-08-10 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, YearEndDate AS YearEndDate, [dbo].[KAAS_FN_GetDateFromClarion](PeriodEnd) AS PeriodEndDate FROM [dbo].[CONTROL] CNL LEFT JOIN [dbo].[VATcodes] VAC ON CNL.[Vatexclude] = VAC.[Code] SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_GetDebtorsLedgerDetailsByClient', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_GetDebtorsLedgerDetailsByClient] END GO CREATE PROCEDURE [dbo].[KAAS_GetDebtorsLedgerDetailsByClient] ( @Code VARCHAR(10) = '' ) AS /******************************************************************************************************* * Fetches the Debtors Ledger details by client * * * * Stored Procedure Name : [dbo].[KAAS_GetDebtorsLedgerDetailsByClient] * * * * Modification History: * * 2021-07-06 Balamurugan C Created * *******************************************************************************************************/ BEGIN SELECT [RecordId], [Type], [Date], [Matter], [Ref], [Narr], [Original], [OsValue] FROM [dbo].[DebtorsLedger] WHERE [Client]=@Code ORDER BY [Client] ASC 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 SET NOCOUNT ON SELECT [Code], [Name], [Nominal], [Retired] FROM [dbo].[FeeEarnerCodes] WHERE [CODE] = @Code ORDER BY [CODE] SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_GetHandlerDropdownList', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_GetHandlerDropdownList] END GO CREATE PROCEDURE [dbo].[KAAS_GetHandlerDropdownList] AS BEGIN /************************************************************************************************************* * * * [dbo].[KAAS_GetHandlerDropdownList] * * * * Description: This procedure is used to check Handler Code exists or not * * * * ***************************************************************************************************** * * * * Modification history: * * 2021-06-25 Natarajan S Created * * 2021-06-29 Natarajan S Modified - Added charges group * * 2021-07-09 Balamurugan C Modified - Fee Earner Details * *************************************************************************************************************/ SELECT [HAN].[Code] AS [Code], [HAN].[Team] AS [Description] FROM [dbo].[Handlers] [HAN] WHERE [HAN].[TeamCode] = 'Y' AND [HAN].[Retired]='N' ORDER BY [HAN].[Code] ASC SELECT [FEE].[Code], [FEE].[Name] AS [Description], [FEE].[Retired] FROM [dbo].[FeeEarnerCodes] FEE ORDER BY [FEE].[Code] ASC SELECT RTRIM([TAC].[Code]) AS [Code], [TAC].[Desc] AS [Description] FROM [dbo].[TasksCodes] [TAC] WHERE [TAC].[AxleTask] = 'N' OR [TAC].[AxleTask] = '''' ORDER BY [TAC].[Code] ASC SELECT RTRIM([HAN].[Code]) AS [Code], [HAN].[Name] AS [Description] FROM [dbo].[Handlers] [HAN] LEFT OUTER JOIN [dbo].[NoOverDueTasks] [NOT] ON [HAN].[Code]= [NOT].[Handler] WHERE [HAN].[Retired] <> 'Y' AND [HAN].[Code] <> 'JP ' ORDER BY [HAN].Code ASC SELECT [DEP].[Code] AS [Code], [DEP].[Description] AS [Description] FROM [dbo].Departments [DEP] SELECT RTRIM([BCH].[BranchCode]) AS [Code], [BCH].[Description] AS [Description] FROM [dbo].[Branch] [BCH] WHERE [BCH].[Retired]=0 SELECT RTRIM([HCG].[Code]) AS [Code], [HCG].[Name] AS [Description] FROM [dbo].[HandlerChargeGroups] [HCG] 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 EXISTS(SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_GetInvoiceList' AND SO.[type] = 'P' ) BEGIN DROP PROCEDURE [dbo].[KAAS_GetInvoiceList] END GO CREATE PROCEDURE [dbo].[KAAS_GetInvoiceList] ( @Matter VARCHAR(50) = NULL ) AS /******************************************************************************************************* * Fetches the Invoice list * * * * Stored Procedure Name : [dbo].[KAAS_GetInvoiceList] '000001/0003' * * * * Modification History: * * 14 Jul 2021 Natarajan S Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT [DBL].[RecordId] AS [RecordId], [DBL].[BATCHNO] AS [BatchNo], [DBL].[Type] AS [Type], [DBL].[Ref] AS [Ref], [CON].[Name] AS [Narr], [DBL].[Date] AS [Date], [DBL].[Original] AS [Original], [DBL].[OSValue] AS [OsValue], [DBL].[OSFees] AS [OsFees], [DBL].[OsOutlay] AS [OsOutlay], [DBL].[OsVat] AS [OsVat] FROM [dbo].[DebtorsLedger] [DBL] LEFT OUTER JOIN [dbo].[Matters] [MAT] ON [DBL].[MATTER] = [MAT].[Code] LEFT OUTER JOIN [dbo].[Contacts] [CON] ON [MAT].[ClientCode] = [CON].[Code] WHERE [DBL].[Matter] = @Matter AND [DBL].[OSValue] > 0 ORDER BY [DBL].[Matter] ASC, [DBL].[Date] ASC, [DBL].[RecordId] ASC SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_GetMatterValues',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_GetMatterValues] GO CREATE PROC dbo.[KAAS_GetMatterValues] ( @MatterCode VARCHAR(20) ) AS /******************************************************************************************************* * To fetch the matter billing details * * * * Stored Procedure Name : [dbo].[KAAS_GetMatterValues] * * * * Modification History: * * 2021-05-18 Revathy D Created * *******************************************************************************************************/ BEGIN 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 SET NOCOUNT ON /*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]) AS 'Code', RTRIM([NTYDESC]) AS 'Description' FROM [dbo].[NominalTypes] ORDER BY [NTYCODE] ASC /*STATIC DETAILS FOR NOMINAL REQPORT SEQ*/ SELECT RTRIM([CODE]) AS 'Code', RTRIM([DESC]) AS 'Desc', [PB] AS 'Pb' FROM [dbo].[NominalReportSeq] ORDER BY [Code] ASC /*STATIC DETAILS FOR DEPOSIT TYPE*/ SELECT RTRIM([CODE]) AS 'Code', RTRIM([DESCRIPTION]) AS 'Description' FROM [dbo].[DepositType] ORDER BY [Code] ASC SET NOCOUNT OFF 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 SET NOCOUNT ON /*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 SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_GetNominalLedgerDetails', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_GetNominalLedgerDetails] END GO 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 SET NOCOUNT ON /*NOMINAL TYPE LIST*/ IF(@Flag='NOMINALDETAILS') BEGIN SELECT [Code], [Desc], [Type] FROM [dbo].[Nominal] ORDER BY [Code] ASC END /*GRID DETAILS FROM NOMINAL LEDGER*/ IF(@Flag='NOMINALLEDGER') BEGIN SELECT [NOL].[Pref], [NOL].[Date], [NOL].[RecordId], [NOL].[Code], [NOL].[Ref], [NOL].[Narr], [NOL].[Matter], [NOL].[Supp], [NOL].[Value], [NOL].[BatchNo], [NOL].[Branch], [NOL].[Year], [NOL].[ClearedDate], [BH].[Type] FROM [dbo].[NominalLedger] [NOL] LEFT JOIN [BatchH] [BH] ON [BH].[BatchNo] = [NOL].[BatchNo] WHERE [NOL].[Code] = @Code AND [NOL].[Year] = @Year ORDER BY [Code] ASC END SET NOCOUNT OFF 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 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 SET NOCOUNT ON SELECT RTRIM([Code]) AS '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 SET NOCOUNT OFF 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_GetSAMAccruals', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_GetSAMAccruals] END GO CREATE PROCEDURE [dbo].[KAAS_GetSAMAccruals] AS /******************************************************************************************************* * [dbo].[KAAS_GetSAMAccruals] * * Description: Used to fetch accruals list * * Modification History: * * 2021-08-03 Revathy D Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT [ACC].[NOMINALFROM] AS [NominalAc], [ACC].[NOMINALTO] AS [AccrualAc], [ACC].[DESCRIPTION] AS [Description], [ACC].[VALUE] AS [Value], [ACC].[VALUEYTD] AS [ValueYTD], [ACC].[RECORDID] AS [RecordId] FROM dbo.[Accruals] ACC ORDER BY [ACC].[NOMINALFROM] DESC, [ACC].[RECORDID] DESC 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 * * 2021-07-28 Vinodhkumar M Added null check for FNL Date * *******************************************************************************************************/ 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], ISNULL(OM.[FNL_DATE],GETUTCDATE()) 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 * * 2021-07-08 Natarajan S Modified - Formatting Added. * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT [MTL].[Matter] AS [Matter], [MTL].[BatchNo] AS [BatchNo], [MTL].[PRef] AS [PRef], [MTL].[Date] AS [Date], [MTL].[Ref] AS [Ref], [MTL].[Narr] AS [Narr], [MTL].[ValueD] AS [ValueD], [MTL].[ValueO] AS [Outlay], [MTL].[Fee] AS [Fee], [MTL].[EntryDate] AS [EntryDate], [MTL].[Client] AS [Client], [MTL].[ValueCC] AS [ValueCC], [MTL].[ValueCD] AS [ValueCD], [MTL].[LRef] AS [LRef], [MTL].[Billed] AS [Billed], [MAT].[Undertaking] AS [Undertaking], [BTH].[Type] AS [Type] FROM [dbo].[MatterLedger] [MTL] INNER JOIN [dbo].[Matters] [MAT] ON [MTL].[Matter] = [MAT].[Code] LEFT JOIN [dbo].[BatchH] [BTH] ON [MTL].[BatchNo] = [BTH].[BatchNo] WHERE [MTL].[Matter] = @MatterCode ORDER BY [MTL].[Matter], [MTL].[Date], [MTL].[LRef] ASC SELECT [CLIENTMIN] FROM [dbo].[OpenMattersOnly] WHERE [Code] = @MatterCode SET NOCOUNT OFF 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_GetSAMPurchaseList',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_GetSAMPurchaseList] GO CREATE PROCEDURE [dbo].[KAAS_GetSAMPurchaseList] (@Type varchar(4) = '') AS /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_GetSAMPurchaseList] * * Description: To Get list based Type * * * * Modification History: * * 2021-07-28 Vinodhkumar M Created * * 2021-08-09 Vinodhkumar M Type parameter is added * * 2021-08-11 Vinodhkumar M new column is added * *******************************************************************************************************/ BEGIN SELECT [BAT].[BATCHNO] AS [BatchNo], [BAT].[TYPE] AS [Type], [BAT].[CODE] AS [Code], [BAT].[REF] AS [Ref], [BAT].[DATE] AS [Date], [BAT].[MATTER] AS [Matter], [BAT].[SUBTOT] AS [SubTotal], [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].[USERCODE] AS [UserCode], [BAT].[CLIENTBANK] AS [ClientBank], [BAT].[CHEQUEREQNO] As [ChequeReqNo] FROM [dbo].[BatchH] [BAT] WHERE ([BAT].[TYPE] = @Type AND [BAT].[POSTED] = 'N') ORDER BY [BAT].[POSTED] ASC, [BAT].[ENTRYDATE] ASC END GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_GetSAMSystemOptionsDetails' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_GetSAMSystemOptionsDetails] END GO CREATE PROCEDURE [dbo].[KAAS_GetSAMSystemOptionsDetails] AS /******************************************************************************************************* * Fetches the System Options Dropdown List * * * * Stored Procedure Name : [dbo].[KAAS_GetSAMSystemOptionsDetails] * * * * Modification History: * * 2021-08-02 Balamurugan.C Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT RTRIM([Name]) AS [Name], RTRIM([Debtors]) AS [DebtorsControlAc], RTRIM([Creditor]) AS [CreditorControlAc], RTRIM([VatNo]) AS [VatNo], RTRIM([Outlay]) AS [OutlayControlAc], RTRIM([Vat]) AS [VatAc], RTRIM([Pl]) AS [ProfitLossAc], RTRIM([Client]) AS [ClientLedgerControl], RTRIM([FeesWriteOff]) AS [FeesWriteOffAc], RTRIM([OutlayWriteOff]) AS [OutlayWriteOffAc], RTRIM([BillsWriteOff]) AS [BillsWriteOffAc], RTRIM([DefaultOfficeAc]) AS [DefaultOfficeAc], RTRIM([DefaultClientAc]) AS [DefaultClientAc], RTRIM([DefaultClientDep]) AS [DefaultClientDepositAc], RTRIM([MiscOutlay]) AS [MiscOutlay], RTRIM([PettyCash]) AS [DefaultPettyCash], RTRIM([withTax]) AS [WithHoldingTax], [Year] AS [Year], [CurPer] AS [CurrentPeriod], [YearEndDate] AS [YearEndDate], [OutlayBudget] AS [StdOutlayBudget], [LastBill] AS [LastBillNo], RTRIM([VatExclude]) AS [VatExclusionCode], RTRIM([DefaultVat]) AS [DefaultVat], RTRIM([FeeExclude]) AS [FeeExclusionCode], RTRIM([MiscFE]) AS [DefaultMiscOutlayFeeEarner], RTRIM([EnableFNL]) AS [EnableFNL], [EnableCDB] AS [EnableClientDB], [UK] AS [UK], [EnableDW] AS [EnableDeedsWills], [YearEndCompleted] AS [YearEndCompleted], [EnableFM] AS [EnableFM], [UseOutlayType] AS [UseOutlayType], [PendingOutlay] AS [PendingOutlay], [RecInstalled] AS [EnableBankRec], [Indicator18] AS [BillPrinting], [Indicator19] AS [UniqueMatterNumbering], [Indicator20] AS [IntrestCalculator], [Indicator22] AS [UseBranches], [Indicator23] AS [TaggedBatches], [Indicator24] AS [Indicator24], [Indicator25] AS [Indicator25], [Indicator26] AS [Indicator26], [Indicator27] AS [Indicator27], [Indicator28] AS [Indicator28], [Indicator29] AS [Indicator29], [Indicator30] AS [Indicator30], [WordPerfect] AS [WordPerfect], [DemoExpire] AS [DemoExpire], [MaxNumberofMatters] AS [MaxMatter], [OpSisLink] AS [CaseMgrLink], [TimeCosting] AS [TimeCostingInstalled], [ForceFE] AS [ForceFeeEarnerCode], [LabelPrinter] AS [LabelPrinter], [FeesRepSeq] AS [FeesReportingSeq], [NoBar] AS [OverdrawnClientAc], RTRIM([Solicitor]) AS [SolicitorCategory], RTRIM([OthParty]) AS [OtherPartyCategory], RTRIM([SupplierCategory]) AS [SupplierCategory], RTRIM([DefaultCategory]) AS [SystemCategoryType], [AutoNumber] AS [AutoNumberClients], [AutoAlphaClientNumbering] AS [AutoAlphaClientNumbering], [ThirdPartyLimit] AS [ThirdPartyLimit], [EiConvert] AS [ConversionRate], [DateFutValidation] AS [DateFutureValidation], [DatePastValidation] AS [DatePastValidation], RTRIM([USerPrompt1]) AS [PromptRef1], RTRIM([USerPrompt2]) AS [PromptRef2], RTRIM([USerPrompt3]) AS [PromptRef3], [NextFile] AS [NextFile], [ClosedFileNo] AS [ClosedFileNumber], RTRIM([ChequePrinting]) AS [ChequeType], [EnableCP] AS [EnableChequePrinting], [dbo].[KAAS_FN_GetDateFromClarion]([PeriodEnd]) AS [NextPeriodEnd] FROM [dbo].[Control] SELECT MAX(CASE WHEN [STI].[KeyName] = 'TimeBarred' THEN OutputValue END) AS [TimeBarred], MAX(CASE WHEN [STI].[KeyName] = 'SupplierRemittance_On' THEN OutputValue END) AS [SupplierRemittanceOn], MAX(CASE WHEN [STI].[KeyName] = 'EFTDefaultON' THEN OutputValue END) AS [EFTDefaultON], MAX(CASE WHEN [STI].[KeyName] = 'MatterOutlayBudgetCheckON' THEN OutputValue END) AS [MatterOutlayBudgetCheckON], MAX(CASE WHEN [STI].[KeyName] = 'CASE_LabelNo' THEN RTRIM(OutputValue) END) AS [CaseLabelNo], MAX(CASE WHEN [STI].[KeyName] = 'InvoiceApprover' THEN RTRIM(OutputValue) END) AS [InvoiceApprover], MAX(CASE WHEN [STI].[KeyName] = 'ChequeApprover' THEN RTRIM(OutputValue) END) AS [ChequeApprover], MAX(CASE WHEN [STI].[KeyName] = 'NotifyReleasedInv' THEN OutputValue END) AS [NotifyReleasedInv], MAX(CASE WHEN [STI].[KeyName] = 'NotifyApprovedInv' THEN OutputValue END) AS [NotifyApprovedInv], MAX(CASE WHEN [STI].[KeyName] = 'NotifyReleasedChq' THEN OutputValue END) AS [NotifyReleasedChq], MAX(CASE WHEN [STI].[KeyName] = 'NotifyApprovedChq' THEN OutputValue END) AS [NotifyApprovedChq], MAX(CASE WHEN [STI].[KeyName] = 'EMailServer' THEN OutputValue END) AS [SMTPMailServer], MAX(CASE WHEN [STI].[KeyName] = 'Money Laundering_MoneyLaunderingCheck' THEN OutputValue END) AS [MoneyLaunderingCheck], MAX(CASE WHEN [STI].[KeyName] = 'Money Laundering_Dept' THEN RTRIM(OutputValue) END) AS [MoneyLaunderingDept], MAX(CASE WHEN [STI].[KeyName] = 'Money Laundering_FeeEarner' THEN RTRIM(OutputValue) END) AS [MoneyLaunderingFeeEarner], MAX(CASE WHEN [STI].[KeyName] = 'Money Laundering_WorkType' THEN RTRIM(OutputValue) END) AS [MoneyLaunderingWorkType], MAX(CASE WHEN [STI].[KeyName] = 'Options_Conversion' THEN RTRIM(OutputValue) END) AS [ShowLedgerButton], MAX(CASE WHEN [STI].[KeyName] = 'CompositeBilling' THEN RTRIM(OutputValue) END) AS [CompositeBilling] FROM [dbo].[Settings] STI CROSS APPLY ( VALUES ([KeyValue]) ) CPV(OutputValue) WHERE [STI].[KeyName] IN ( 'TimeBarred', 'SupplierRemittance_On', 'EFTDefaultON', 'MatterOutlayBudgetCheckON', 'CASE_LabelNo', 'InvoiceApprover', 'ChequeApprover', 'NotifyReleasedInv', 'NotifyApprovedInv', 'NotifyReleasedChq', 'NotifyApprovedChq', 'Money Laundering_MoneyLaunderingCheck', 'Money Laundering_Dept', 'Money Laundering_FeeEarner', 'Money Laundering_WorkType', 'EMailServer', 'Options_Conversion', 'CompositeBilling' ) SET NOCOUNT OFF END GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_GetSAMSystemOptionsDropDownList' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_GetSAMSystemOptionsDropDownList] END GO CREATE PROCEDURE [dbo].[KAAS_GetSAMSystemOptionsDropDownList] AS /******************************************************************************************************* * Fetches the System Options Dropdown List * * * * Stored Procedure Name : [dbo].[KAAS_GetSAMSystemOptionsDropDownList] * * * * Modification History: * * 2021-08-01 Balamurugan.C Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT [Code], [Desc], [Type], [Retired] FROM [dbo].[Nominal] ORDER BY [Code] ASC SELECT [Code], [Name], [Nominal], [Retired] FROM [dbo].[FeeEarnerCodes] ORDER BY [Code] ASC SELECT [Code], [Rate] FROM [dbo].[VATCodes] ORDER BY [Code] ASC SELECT [Code], [Desc], [Pb] FROM [dbo].[NominalReportSeq] WHERE [Pb] = ' ' ORDER BY [Pb] DESC, [Code] DESC SELECT RTRIM([CatType]) AS [CatType], RTRIM([Code]) AS [Code], RTRIM([Desc$]) AS [Description], RTRIM([CategoryType]) AS [CategoryType] FROM [dbo].[Category] LEFT OUTER JOIN [dbo].[CategoryType] ON [CatType]=[CategoryType] WHERE [CatType]= (SELECT [DefaultCategory] FROM [dbo].[control]) ORDER BY [CatType] DESC, [Code] DESC SELECT RTRIM([CategoryType]) AS [CategoryType], RTRIM([Description]) AS [Description] FROM [dbo].[CategoryType] ORDER BY [CategoryType] ASC SELECT RTRIM([Code]) AS [Code], RTRIM([Description]) AS [Description] FROM [dbo].[Departments] WHERE ISNULL([Retired],'') <> 'Y' ORDER BY [Code] ASC SELECT RTRIM([Code]) AS [Code], RTRIM([Description]) AS [Description] FROM [dbo].[WorkTypes] WHERE ISNULL([Retired],'') <> 'Y' ORDER BY [Code] ASC SET NOCOUNT OFF END GO IF EXISTS(SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_GetSAMUndertakingBaseDetails' AND SO.[type] = 'P' ) BEGIN DROP PROCEDURE [dbo].[KAAS_GetSAMUndertakingBaseDetails] END GO CREATE PROCEDURE [dbo].[KAAS_GetSAMUndertakingBaseDetails]( @MatterCode VARCHAR(20), @FECode VARCHAR(10), @Handler VARCHAR(10) ) AS /******************************************************************************************************* * [dbo].[KAAS_GetSAMUndertakingBaseDetails] '000001/0000','ADM','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) AS [Date], [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,'ADM' * * Modification History: * * 30 MAR 2021 Natarajan S Created * * 14 JUL 2021 Natarajan S Modified - SP Formatted * *******************************************************************************************************/ BEGIN SELECT [UDK].[RecordId] AS [RecordId], [UDK].[Matter] AS [Matter], [UDK].[Seq] AS [Seq], [dbo].KAAS_FNConvertUTCDateToTargetTimeZone([UDK].[DISCHARGEDATE],@Handler) AS [DischargeDate], [UDK].[Type] AS [Type], [UDK].[Description] AS [Description], [UDK].[Who] AS [Who], [UDK].[ActionId] AS [ActionId], [UDK].[GivenOrReceived] AS [GivenOrReceived], [UDK].[DischargeDescription] AS [DisDescription], [UDK].[DischargedBy] AS [DischargedBy], [UDK].[DealingNumber] AS [DealingNumber], [UDK].[Notes] AS [Notes], [UDK].[CommercialOrNon] AS [CommercialOrNon], [UDK].[Status] AS [Status], [dbo].KAAS_FNConvertUTCDateToTargetTimeZone([UDK].[DATE],@Handler) AS [DATE], [UDK].[Value] AS [Value], [UDK].[AuthorisedByFe] AS [AuthorisedByFe], [UDK].[Handler] AS [Handler], [UDK].[UndertakingTo] AS [UndertakingTo], [UDK].[CaseContact] AS [CaseContact], [UDK].[EntryDate] AS [EntryDate], [UDK].[Category] AS [Category], [UDK].[IsConditional] AS [IsConditional], [UDK].[Condition] AS [Condition], [UDK].[NeedsAttention] AS [NeedsAttention], [UDK].[AddCommentLedger] AS [AddCommentLedger], [dbo].KAAS_FNConvertUTCDateToTargetTimeZone([DIA].[DATE],@Handler) AS [DueDate], [DIA].[TeamCode] AS [TeamCode], [DIA].[FNCode] AS [FNCode], [DIA].[Text1] AS [Text1] FROM [dbo].[Undertakings] [UDK] INNER JOIN [dbo].[diary] [DIA] ON [UDK].[ActionId]=[DIA].[ActionId] WHERE [RecordId]=@RecordId END GO IF EXISTS(SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_GetSAMUndertakingDiaryDetails' AND SO.[type] = 'P' ) BEGIN DROP PROCEDURE [dbo].[KAAS_GetSAMUndertakingDiaryDetails] END 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: * * 10 APR 2021 Natarajan S Created * * 14 JUL 2021 Natarajan S Modified - SP Formatted * *******************************************************************************************************/ 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 OBJECT_ID(N'KAAS_GetSupplierLedgerDetails', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_GetSupplierLedgerDetails] END GO CREATE PROCEDURE [dbo].[KAAS_GetSupplierLedgerDetails] (@Code VARCHAR(10)='', @Flag VARCHAR(15)='') AS /******************************************************************************************************* * Fetches the Supplier Ledger details * * * * Stored Procedure Name : [dbo].[KAAS_GetSupplierLedgerDetails] 'brady', 'SUPPLIERLEDGER' * * * * Modification History: * * 2021-06-30 Natarajan S Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON /*Supplier LIST*/ IF(@Flag='SUPPLIERLIST') BEGIN SELECT [Code], [Name] FROM [dbo].[Suppliers] ORDER BY [Code] END /*GRID DETAILS FROM SUPPLIER LEDGER*/ IF(@Flag='SUPPLIERLEDGER') BEGIN SELECT [Type] AS [Type], [Pref] AS [Pref], [Date] AS [Date], [RecordId] AS [RecordId], [Ref] AS [Ref], [Narr] AS [Narrative], [Matter] AS [Matter], [Supp] AS [Supp], [Original] AS [Original], [OsValue] AS [OsValue], [BatchNo] AS [BatchNo] FROM [dbo].[SupplierLedger] [SLD] WHERE [Supp]=@Code ORDER BY [Supp] END END SET NOCOUNT OFF GO IF OBJECT_ID(N'KAAS_GetThirdPartyLedgerDetails', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_GetThirdPartyLedgerDetails] END GO CREATE PROCEDURE [dbo].[KAAS_GetThirdPartyLedgerDetails] ( @SuppCode VARCHAR(10)='', @Year INT ) AS /******************************************************************************************************* * Fetches the Third party ledger details * * * * Stored Procedure Name : [dbo].[KAAS_GetThirdPartyLedgerDetails] * * SAMPLE DATA [KAAS_GetThirdPartyLedgerDetails] 'BRADY','2018' * * * Modification History: * * 2021-07-02 Natarajan S Created * *******************************************************************************************************/ BEGIN SELECT [BAT].[BatchNo], [BAT].[Type], [BAT].[Code], [BAT].[Pref], [BAT].[Ref], [BAT].[Date], [BAT].[Matter], [BAT].[Supp], [BAT].[Narr], [BAT].[Value], [BAT].[Posted], [BAT].[PerNo], [BAT].[Year], [BAT].[EntryDate], [BAT].[RecNo], [BAT].[ChequeReqNo], [SUP].[TaxNo], [SUP].[ServiceCode] FROM [dbo].[BatchH] [BAT] INNER JOIN [dbo].[Suppliers] [SUP] ON [BAT].[Supp] = [SUP].[Code] WHERE [BAT].[Supp] = @SuppCode AND [BAT].[Year] = @Year AND [BAT].[ThirdParty] = 'Y' AND [BAT].[Type] = 'P' AND [BAT].[Posted] = 'Y' ORDER BY [BAT].[Supp] ASC END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetVATCodes]') AND type in (N'P', N'PC')) BEGIN DROP PROCEDURE [dbo].[KAAS_GetVATCodes] END GO CREATE PROCEDURE [dbo].[KAAS_GetVATCodes] AS /******************************************************************************************************* * Fetches the list of VAT codes * * * * Stored Procedure Name : [dbo].[KAAS_GetVATCodes] * * * * Modification History: * * 2021-03-23 Revathy D Created * *******************************************************************************************************/ BEGIN SELECT VAC.[CODE] AS Vat, VAC.[RATE] AS Rate FROM [dbo].[VATCodes] VAC ORDER BY VAC.[CODE] ASC END GO IF EXISTS(SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_GetWorkTypeGroupDropdownList' AND SO.[type] = 'P' ) BEGIN DROP PROCEDURE [dbo].[KAAS_GetWorkTypeGroupDropdownList] END GO CREATE PROCEDURE [dbo].[KAAS_GetWorkTypeGroupDropdownList] AS /******************************************************************************************************* * Fetches dropdown list values for worktype group * * * * Stored Procedure Name : [dbo].[KAAS_GetWorkTypeGroupDropdownList] * * * * Modification History: * * 09 Aug 2021 Natarajan S Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT [Code], [Description] FROM [dbo].[WorkTypes] WHERE [Retired]='N' SELECT RIGHT(100+[WorkGroup],2) AS [WorkGroup], [GroupOrder], [Description] FROM [WorkGroup1] SELECT [WorkGroup2] AS [WorkGroup2], [description2] AS [Description] FROM [dbo].[WorkGroup2] SET NOCOUNT OFF END GO IF EXISTS(SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_GetWorkTypeGroupList' AND SO.[type] = 'P' ) BEGIN DROP PROCEDURE [dbo].[KAAS_GetWorkTypeGroupList] END GO CREATE PROCEDURE [dbo].[KAAS_GetWorkTypeGroupList] ( @GroupType VARCHAR(30)) AS /******************************************************************************************************* * Fetches the work group type * * * * Stored Procedure Name : [dbo].[KAAS_GetWorkTypeGroupList] 'typeOfWork1' * * * * Modification History: * * 05 Aug 2021 Natarajan S Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON IF(@GroupType = 'typeOfWorkGroup') BEGIN SELECT [WTG].[RecordID] AS [RecordID], [WTG].[WorkType] AS [WorkType], [WTS].[Desc] AS [WorkTypeDescription], RIGHT(100 + [WTG].[Group1],2) AS [Group1], [WG1].[Description] AS [G1Description], [WTG].[Group2] AS [Group2], [WG2].[Description2] AS [G2Description] FROM [dbo].[WorkTypeGroupings] [WTG] LEFT JOIN [dbo].[WorkGroup2] [WG2] ON [WTG].[Group2] = [WG2].[WorkGroup2] LEFT JOIN [dbo].[WorkGroup1] [WG1] ON [WTG].[Group1] = [WG1].[WorkGroup] LEFT JOIN [dbo].[WorkTypes] [WTS] ON [WTG].[WorkType] = [WTS].[Code] END IF(@GroupType = 'typeOfWork1') BEGIN SELECT [WorkGroup] [Group], [GroupOrder] [GroupOrder], [Description] [Description] FROM [dbo].[WorkGroup1] END IF(@GroupType = 'typeOfWork2') BEGIN SELECT [WorkGroup2] [Group], [Description2] [Description] FROM [dbo].[WorkGroup2] END SET NOCOUNT OFF 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_Get_ReportId', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_Get_ReportId] END GO CREATE PROCEDURE [dbo].[KAAS_Get_ReportId] ( @FileName VARCHAR(500) ) AS /******************************************************************************************************* * This SP used to Fetch report Id * * * * Stored Procedure Name : [dbo].[KAAS_Get_ReportId] * * * * Modification History: * * 16 July 2021 Revathy D Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT [CRR].[Reportid] As [ReportId], [CRR].[filename] As [FileName] FROM [dbo].[CRReport] CRR WHERE [CRR].[filename] = @FileName SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_InsertContactCategory', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_InsertContactCategory] END 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 SET NOCOUNT ON IF( @Mode = 'INSERTEDIT' ) BEGIN IF EXISTS ( SELECT TOP 1 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 SET NOCOUNT OFF END GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_InsertDebtorsLedgerAllocation' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_InsertDebtorsLedgerAllocation] END GO CREATE PROCEDURE [dbo].[KAAS_InsertDebtorsLedgerAllocation] (@DebtorsBatchNo INT, @InvoiceBatchNo INT, @Value FLOAT, @Fees FLOAT, @Outlay FLOAT, @Vat FLOAT, @Date DATETIME, @UserCode VARCHAR(30), @Result TINYINT OUTPUT) AS /******************************************************************************************************* * * *Used to Insert supplier ledger allocation * * Stored Procedure Name: [dbo].[KAAS_InsertDebtorsLedgerAllocation] * * Modification History: * * 2021-07-09 Natarajan S Created * * 2021-07-12 Natarajan S Modified Allocation ID from control table. ********************************************************************************************************/ BEGIN TRY BEGIN TRANSACTION DECLARE @AllNo INT SET @AllNo = (SELECT [LastAlloc]+1 FROM [dbo].[control]) UPDATE [CRL] SET [CRL].[LastAlloc] = @AllNo FROM [dbo].[control] [CRL] INSERT INTO [dbo].[Allocations] ([BatchNo], [PRef], [Date], [ABatchNo], [OPRef], [ORef], [Value], [Fees], [Outlay], [Vat], [UserCode], [AllNo], [VatCode]) SELECT [INV].[BatchNo], [INV].[PRef], @Date, [DEB].[BatchNo], [DEB].[PRef], [DEB].[Ref], @Value, @Fees, @Outlay, @Vat, @UserCode, @AllNo, [DEB].[VATCODE] FROM [dbo].[DebtorsLedger] [INV], [dbo].[DebtorsLedger] [DEB] WHERE [INV].[BatchNo]=@InvoiceBatchNo AND [DEB].[BatchNo]=@DebtorsBatchNo INSERT INTO [dbo].[Allocations] ([BatchNo], [PRef], [Date], [ABatchNo], [OPRef], [ORef], [Value], [Fees], [Outlay], [Vat], [UserCode], [AllNo], [VatCode]) SELECT [INV].[BatchNo], [INV].[PRef], @Date, [DEB].[BatchNo], [DEB].[PRef], [DEB].[Ref], @Value, @Fees, @Outlay, @Vat, @UserCode, @AllNo, [DEB].[VatCode] FROM [dbo].[DebtorsLedger] [INV], [dbo].[DebtorsLedger] [DEB] WHERE [INV].[BatchNo]=@DebtorsBatchNo AND [DEB].[BatchNo]=@InvoiceBatchNo UPDATE [INV] SET [INV].[OSValue] = [INV].[OSValue] + @Value, [INV].[OSFees] = [INV].[OSFees] + @Fees, [INV].[OSOUTLAY] = [INV].[OSOutlay] + @Outlay, [INV].[OSVAT] = [INV].[OSVat] + @Vat FROM [dbo].[DebtorsLedger] [INV] WHERE [INV].[BatchNo] = @InvoiceBatchNo UPDATE [INV] SET [INV].[OSValue] = [INV].[OSValue] - @Value, [INV].[OSFees] = [INV].[OSFees] - @Fees, [INV].[OSOUTLAY] = [INV].[OSOutlay] - @Outlay, [INV].[OSVAT] = [INV].[OSVat] - @Vat FROM [dbo].[DebtorsLedger] [INV] WHERE [INV].[BatchNo] = @DebtorsBatchNo SET @Result =1 COMMIT TRANSACTION END TRY BEGIN CATCH SET @Result =0 ROLLBACK TRANSACTION END CATCH GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_InsertDeleteNominalLedgerComments' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_InsertDeleteNominalLedgerComments] END GO CREATE PROCEDURE [dbo].[KAAS_InsertDeleteNominalLedgerComments] ( @Date DATETIME = NULL, @Narr VARCHAR(100) = '', @Code VARCHAR(10) = '', @Ref VARCHAR(10) = '', @Year INT = 0, @RecordId INT = 0, @Mode VARCHAR(20) = 'INSERT' ) /******************************************************************************************************* * * * Stored Procedure Name : [dbo].[KAAS_InsertDeleteNominalLedgerComments] * * Copied from : NA * * * * Modification History : * * 2021-07-28 Balamurugan C Created * *******************************************************************************************************/ AS BEGIN SET NOCOUNT ON IF( @Mode = 'INSERT' ) BEGIN INSERT INTO [dbo].[NominalLedger] ( [Code], [BatchNo], [Pref], [Date], [Narr], [Ref], [Value], [CurrencyValue], [Year], [EntryDate] ) VALUES ( @Code, 0, 0, @Date, @Narr, @Ref, 0.00, 0.00, @Year, CAST(GETDATE() AS DATE) ) END ELSE BEGIN DELETE FROM [dbo].[NominalLedger] WHERE [RecordID] = @RecordId END SET NOCOUNT OFF 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 OBJECT_ID(N'KAAS_InsertSAMAccruals', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_InsertSAMAccruals] END GO CREATE PROCEDURE [dbo].[KAAS_InsertSAMAccruals] ( @NominalAc VARCHAR(10), @AccrualAc VARCHAR(10), @Description VARCHAR(40), @Value DECIMAL, @ValueYTD DECIMAL, @RecordId INT ) AS /******************************************************************************************************* * [dbo].[KAAS_InsertSAMAccruals] * * Description: Used to post accrual details * * Modification History: * * 2021-08-04 Revathy D Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON IF ISNULL(@RecordId, 0) = 0 BEGIN INSERT INTO [dbo].[Accruals] ( [NOMINALFROM], [NOMINALTO], [DESCRIPTION], [VALUE], [VALUEYTD]) SELECT @NominalAc, @AccrualAc, @Description, @Value, @ValueYTD END ELSE BEGIN UPDATE [dbo].[Accruals] SET [NOMINALFROM] = @NominalAc, [NOMINALTO] = @AccrualAc, [DESCRIPTION] = @Description, [VALUE] = @Value, [VALUEYTD] = @ValueYTD WHERE [RECORDID] = @RecordId END SET NOCOUNT OFF END GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_INSERTSAMDiaryDetails' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_INSERTSAMDiaryDetails] END go CREATE PROCEDURE [dbo].[KAAS_INSERTSAMDiaryDetails] ( @MatterCode VARCHAR(20), @Priority varchar(5), @Highlighted varchar(20), @Publisher varchar(20), @Status int, @ActionCode varchar(20), @ActionStatus varchar(20), @ActionType varchar(20), @ProcessType varchar(20), @FNCode varchar(20), @TeamCode varchar(20), @Text1 varchar(max), @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 * * 2021-07-08 Natarajan S Modified - Formatting Added. * ********************************************************************************************************/ 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 [OMO] SET [OMO].[ClientMin]=@ClientMin FROM [dbo].[OpenMattersOnly] [OMO] WHERE [OMO].[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: * * 26 MAR 2021 Natarajan S Created * * 13 JUL 2021 Natarajan S Created * ********************************************************************************************************************************************************/ BEGIN TRY BEGIN TRANSACTION 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 COMMIT TRANSACTION END TRY BEGIN CATCH SET @Result = 0 ROLLBACK TRANSACTION END CATCH GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_InsertSupplierLedgerAllocation' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_InsertSupplierLedgerAllocation] END go CREATE PROCEDURE [dbo].[KAAS_InsertSupplierLedgerAllocation] (@BillBatchNo INT, @PayBatchNo INT, @Value FLOAT, @Date DATETIME, @UserCode VARCHAR(30), @Result TINYINT OUTPUT) AS /******************************************************************************************************* * * *Used to Insert supplier ledger allocation * * Stored Procedure Name: [dbo].[KAAS_InsertSupplierLedgerAllocation] * * Modification History: * * 2021-07-09 Natarajan S Created * * 2021-07-12 Natarajan S Modified Allocation ID from control table. ********************************************************************************************************/ BEGIN TRY BEGIN TRANSACTION DECLARE @AllNo INT SET @AllNo = (SELECT [LastAlloc]+1 FROM [dbo].[control]) UPDATE [CRL] SET [CRL].[LastAlloc] = @AllNo FROM [dbo].[control] [CRL] INSERT INTO [dbo].[Allocations] ([BatchNo], [PRef], [Date], [ABatchNo], [OPRef], [ORef], [Value], [Fees], [Vat], [UserCode], [AllNo], [VatCode]) SELECT [BIL].[BatchNo], [BIL].[PRef], @Date, [PAY].[BatchNo], [PAY].[PRef], [PAY].[Ref], @Value, 0.00, 0.00, @UserCode, @AllNo, '' FROM [dbo].[SupplierLedger] [BIL], [dbo].[SupplierLedger] [PAY] WHERE [BIL].[BatchNo]=@BillBatchNo AND [PAY].[BatchNo]=@PayBatchNo INSERT INTO [dbo].[Allocations] ([BatchNo], [PRef], [Date], [ABatchNo], [OPRef], [ORef], [Value], [Fees], [Vat], [UserCode], [AllNo], [VatCode]) SELECT [BIL].[BatchNo], [BIL].[PRef], @Date, [PAY].[BatchNo], [PAY].[PRef], [PAY].[Ref], @Value, 0.00, 0.00, @UserCode, @AllNo, '' FROM [dbo].[SupplierLedger] [BIL], [dbo].[SupplierLedger] [PAY] WHERE [BIL].[BatchNo]=@PayBatchNo AND [PAY].[BatchNo]=@BillBatchNo UPDATE [BIL] SET [BIL].[OSValue] = [BIL].[OSValue] + @Value FROM [dbo].[SupplierLedger] [BIL] WHERE [BIL].[BatchNo] = @BillBatchNo UPDATE [BIL] SET [BIL].[OSValue] = [BIL].[OSValue] - @Value FROM [dbo].[SupplierLedger] [BIL] WHERE [BIL].[BatchNo] = @PayBatchNo SET @Result =1 COMMIT TRANSACTION END TRY BEGIN CATCH SET @Result =0 ROLLBACK TRANSACTION END CATCH GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_InsertSupplierLedgerComment' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_InsertSupplierLedgerComment] END go CREATE PROCEDURE [dbo].[KAAS_InsertSupplierLedgerComment] (@Supp VARCHAR(20), @User VARCHAR(50), @Comments VARCHAR(MAX), @Date DATETIME, @Result TINYINT OUTPUT) AS /******************************************************************************************************* * Used to insert supplier ledger comment * * Stored Procedure Name: [dbo].[KAAS_InsertSupplierLedgerComment] * * Modification History: * * 2021-07-07 Natarajan S Created * * 2021-07-08 Natarajan S Modified - Formatting Added. * *******************************************************************************************************/ BEGIN SET NOCOUNT ON INSERT INTO [dbo].[SupplierLedger] ([Supp], [Type], [Date], [Ref], [BatchNo], [Pref], [Narr], [Original], [OSValue], [Year], [Per], [EntryDate], [ThirdParty], [ThirdPartyValue], [Matter], [PendingOutlay]) VALUES (@Supp, ' ', @Date, @User, 0, 0, @Comments, 0.00, 0.00, 0, 0, @Date, 'N', 0.00, '', 0.00) SET @Result=1 SET NOCOUNT OFF 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 SET NOCOUNT ON 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 SET NOCOUNT OFF 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 SET NOCOUNT ON 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 SET NOCOUNT OFF 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 SET NOCOUNT ON 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 SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_InsertUpdateWorkGroup', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_InsertUpdateWorkGroup] END GO CREATE PROCEDURE [dbo].[KAAS_InsertUpdateWorkGroup] ( @RecordID INT, @WorkType VARCHAR(50), @Group1 VARCHAR(10), @Group2 VARCHAR(10), @Result INT OUTPUT ) /******************************************************************************************************* * Stored Procedure Name : [dbo].[KAAS_InsertUpdateWorkGroup] * * * * * Modification History : * * 2021-08-10 Natarajan S Created * *******************************************************************************************************/ AS BEGIN SET NOCOUNT ON IF(@RecordID = 0) BEGIN INSERT INTO [dbo].[WorkTypeGroupings] ( [WorkType], [Group1], [Group2]) VALUES (@WorkType, @Group1, @Group2 ) SET @Result = 1; END ELSE BEGIN UPDATE [dbo].[WorkTypeGroupings] SET [WorkType] = @WorkType, [Group1] = @Group1, [Group2] = @Group2 WHERE RecordID = @RecordID SET @Result = 1; END SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_InsertUpdateWorkType', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_InsertUpdateWorkType] END GO CREATE PROCEDURE [dbo].[KAAS_InsertUpdateWorkType] ( @Mode VARCHAR(10), @Type VARCHAR(5), @OldWorkGroup varchar(50), @WorkGroup VARCHAR(50), @GroupOrder VARCHAR(10) NULL, @Description VARCHAR(10), @Result INT OUTPUT ) /******************************************************************************************************* * Stored Procedure Name : [dbo].[KAAS_InsertUpdateWorkType] * * * * * Modification History : * * 2021-08-10 Natarajan S Created * *******************************************************************************************************/ AS BEGIN SET NOCOUNT ON IF(@Type = 'ONE') BEGIN IF(@Mode = 'INSERT') BEGIN IF EXISTS(SELECT WorkGroup FROM WorkGroup1 WHERE WorkGroup=@WorkGroup) BEGIN SET @Result = 0; END ELSE BEGIN INSERT INTO [dbo].[WorkGroup1] ( [WorkGroup], [GroupOrder], [Description]) VALUES (@WorkGroup, @GroupOrder, @Description) SET @Result = 1; END END ELSE BEGIN IF EXISTS(SELECT WorkGroup FROM WorkGroup1 WHERE WorkGroup = @WorkGroup AND WorkGroup <> @OldWorkGroup) BEGIN SET @Result = 0; END ELSE BEGIN UPDATE [dbo].[WorkGroup1] SET [WorkGroup] = @WorkGroup, [GroupOrder] = @GroupOrder, [Description] = @Description WHERE [WorkGroup] = @OldWorkGroup SET @Result = 1; END END END ELSE BEGIN IF(@Mode = 'INSERT') BEGIN IF EXISTS(SELECT WorkGroup2 FROM WorkGroup2) BEGIN SET @Result = 0; END ELSE BEGIN INSERT INTO [dbo].[WorkGroup2] ( [WorkGroup2], [Description2]) VALUES (@WorkGroup, @Description) SET @Result = 1; END END ELSE BEGIN IF EXISTS(SELECT WorkGroup2 FROM WorkGroup2 WHERE WorkGroup2 = @WorkGroup AND WorkGroup2 <> @OldWorkGroup) BEGIN SET @Result = 0; END ELSE BEGIN UPDATE [dbo].[WorkGroup2] SET [WorkGroup2] = @WorkGroup, [Description2] = @Description WHERE [WorkGroup2] = @OldWorkGroup SET @Result = 1; END END END SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_MaintainInsertMaster', N'P') IS NOT NULL 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) = '', @BackGroundColour VARCHAR(30) = '#FF008000', @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 * * 2021-07-01 Prabhu V Changed Master Name for File Colour as per * Master Name we provided in front end and Added Retired for Update * * 2021-07-02 Prabhu V SP structure changed as per coding standrard in Keyhouse * *******************************************************************************************************/ BEGIN DECLARE @Command VARCHAR(MAX) DECLARE @HandleRate AS FLOAT DECLARE @EventDesc AS VARCHAR(300) SET @HandleRate = 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 [dbo].[WorkTypes] ([Code], [Description], [Desc], [Retired]) VALUES (@Code, @Description, @Description, @Retired) ELSE UPDATE [dbo].[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 [dbo].[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 [dbo].[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 [dbo].[NationCodes] SET [Nation] = @NewCodeVal, [Desc] = @Description WHERE Rtrim([Nation]) = @Code END ELSE IF (@MasterName='DepositType') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [dbo].[DepositType] ([Code], [Description] ) VALUES (@Code, @Description) ELSE UPDATE [dbo].[DepositType] SET [Code] = @NewCodeVal, [Description] = @Description WHERE [Code] = @Code END ELSE IF (@MasterName='OutlayCode') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [dbo].[OutlayCode] ([Code], [Description]) VALUES (@Code, @Description) ELSE UPDATE [dbo].[OutlayCode] SET [Code] = @NewCodeVal, [Description] = @Description WHERE [Code] = @Code END ELSE IF (@MasterName='Narratives') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [dbo].[Narratives] ([Code], [Narrative]) VALUES (@Code, @Description) ELSE UPDATE [dbo].[Narratives] SET [Code] = @NewCodeVal, [Narrative] = @Description WHERE [Code] = @Code END ELSE IF (@MasterName='ServiceCodes') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [dbo].[ServiceCodes] ([Code], [Description]) VALUES (@Code, @Description) ELSE UPDATE [dbo].[ServiceCodes] SET [Code] = @NewCodeVal, [Description] = @Description WHERE [Code] = @Code END ELSE IF (@MasterName='FileColours') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [dbo].[FileColours] ([ColourCode], [ColourDesc], [RGBColour], [BackgroundColour], [ForegroundColour]) VALUES (@Code, @Description, ISNULL(@RGBColour, -1), UPPER(@BackGroundColour), UPPER(@BackGroundColour)) ELSE UPDATE [dbo].[FileColours] SET [ColourCode] = @NewCodeVal, [ColourDesc] = @Description, [RGBColour] = ISNULL(@RGBColour, -1), [BackgroundColour] = UPPER(@BackGroundColour), [ForegroundColour] =UPPER(@BackGroundColour), [Retired] = @Retired WHERE [ColourCode] = @Code END ELSE IF (@MasterName='Branch') BEGIN IF ( @Mode = 'INSERT' ) INSERT INTO [dbo].[Branch] ([BranchCode], [Description], [Retired]) VALUES (@Code, @Description, CASE @Retired WHEN 'Y' THEN 1 ELSE 0 END) ELSE UPDATE [dbo].[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 [dbo].[UndertakingStatus] ([StatusDesc], [Retired]) VALUES (@NewCodeVal, @Retired) ELSE UPDATE [dbo].[UndertakingStatus] SET [StatusDesc] = @NewCodeVal, [Retired] = @Retired WHERE [RecordID] = @Code END ELSE IF (@MasterName='FeeEarners') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[FeeEarnerCodes] WHERE RTRIM([Code])=@Code ) BEGIN UPDATE [dbo].[FeeEarnerCodes] SET [Code] = @Code, [Name] = @Description, [Retired] = @Retired WHERE RTRIM([Code]) = @Code END ELSE BEGIN INSERT INTO [dbo].[FeeEarnerCodes] ([Code], [Name], [Retired] ) VALUES (@Code, @Description, @Retired) END END ELSE IF (@MasterName='FileColours') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[FileColours] WHERE RTRIM(ISNULL([ColourCode],''))=@Code ) BEGIN UPDATE [dbo].[FileColours] SET [ColourCode] = @Code, [ColourDesc] = @Description, [RGBColour] = ISNULL(@RGBColour, -1), [BackGroundColour] = UPPER(@BackGroundColour), [ForegroundColour] = UPPER(@BackGroundColour), [Retired] = @Retired WHERE RTRIM(ISNULL([ColourCode],'')) = @Code END ELSE BEGIN INSERT INTO [dbo].[FileColours] ([ColourCode], [ColourDesc], [RGBColour], [Retired], [BackgroundColour], [ForegroundColour]) VALUES (@Code, @Description, ISNULL(@RGBColour, -1), @Retired, UPPER(@BackGroundColour), UPPER(@BackGroundColour)) 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 [dbo].[TaxType] WHERE RTRIM([TaxType])=@Code) BEGIN UPDATE [dbo].[TaxType] SET [TaxType] = @Code, [TaxDesc] = @Description WHERE RTRIM([TaxType]) = @Code END ELSE BEGIN INSERT INTO [dbo].[TaxType] ([TaxType], [TaxDesc]) VALUES (@Code, @Description) END END ELSE IF (@MasterName='Standard Narratives') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[DesktopNarr] WHERE RTRIM([Code])=@Code) BEGIN UPDATE [dbo].[DesktopNarr] SET [Code] = @Code, [Narrative] = @Description, [Retired] = @Retired WHERE RTRIM([Code]) = @Code END ELSE BEGIN INSERT INTO [dbo].[DesktopNarr] ([Code], [Narrative], [Retired]) VALUES (@Code, @Description, @Retired) END END ELSE IF (@MasterName='Admin Codes') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[TimeAdminCodes] WHERE RTRIM([NCCode]) = @Code) BEGIN UPDATE [dbo].[TimeAdminCodes] SET [NCCode] = @Code, [Description] = @Description, [Retired] = @Retired WHERE RTRIM([NCCode]) = @Code END ELSE BEGIN INSERT INTO [dbo].[TimeAdminCodes] ([NCCode], [Description], [Retired]) VALUES (@Code, @Description, @Retired) END END ELSE IF (@MasterName='Billing') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[BillDescriptions] WHERE RTRIM(ISNULL([SearchField],''))=@Code) BEGIN UPDATE [dbo].[BillDescriptions] SET [SearchField] = @Code, [BillDescriptions] = @Description, [Retired] = @Retired WHERE RTRIM([Number]) = @RGBColour END ELSE BEGIN INSERT INTO [dbo].[BillDescriptions] ([SearchField], [BillDescriptions], [Retired]) VALUES (@Code, @Description, @Retired) END END ELSE IF (@MasterName='Undertaking') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[UndertakingStatus] WHERE [RecordID] = @Code) BEGIN UPDATE [dbo].[UndertakingStatus] SET [StatusDesc] = @Description, [Retired] = @Retired WHERE [RecordID] = @Code END ELSE BEGIN INSERT INTO [dbo].[UndertakingStatus] ([StatusDesc], [Retired]) VALUES (@Description, @Retired) END END ELSE IF (@MasterName='Branch Code') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[Branch] WHERE RTRIM([BranchCode])=@Code) BEGIN UPDATE [dbo].[Branch] SET [BranchCode] = @Code, [Description] = @Description WHERE RTRIM([BranchCode]) = @Code END ELSE BEGIN INSERT INTO [dbo].[Branch] ([BranchCode], [Description]) VALUES (@Code, @Description) END END ELSE IF (@MasterName='Tax Head') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[TaxHead] WHERE RTRIM([TaxHead])=@Code) BEGIN UPDATE [dbo].TaxHead SET [TaxHead] = @Code, [Description] = @Description WHERE RTRIM([TaxHead])=@Code END ELSE BEGIN INSERT INTO [dbo].[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 [dbo].[SalutationCodes] SET [Code] = @Code, [Description] = @Description WHERE RTRIM([Code])=@Code END ELSE BEGIN INSERT INTO [dbo].[SalutationCodes] ([Code], [Description]) VALUES (@Code, @Description) END END ELSE IF (@MasterName='Charge Type') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[ChargeType] WHERE RTRIM([ChargeType])=@Code AND RTRIM([TaxHead])=@Retired) BEGIN UPDATE [dbo].[ChargeType] SET [ChargeType] = @Code, [Description] = @Description WHERE RTRIM([ChargeType]) = @Code AND RTRIM([TaxHead]) = @Retired END ELSE BEGIN INSERT INTO [dbo].[ChargeType] ([ChargeType], [Description], [TaxHead]) VALUES (@Code, @Description, @Retired) END END ELSE IF (@MasterName='Item Types') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[ItemTypes] WHERE RTRIM([TypeCode])=@Code) BEGIN UPDATE [dbo].[ItemTypes] SET [TypeDescription] = @Description WHERE RTRIM([TypeCode]) = @Code END ELSE BEGIN INSERT INTO [dbo].[ItemTypes] ([TypeCode], [TypeDescription]) VALUES (@Code, @Description) END END ELSE IF (@MasterName='Location') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[ItemLocations] WHERE RTRIM([Code])=@Code) BEGIN UPDATE [dbo].[ItemLocations] SET [Description] = @Description, [Fullyn] = @childcare, [Retired] = @Retired WHERE RTRIM([Code]) = @Code END ELSE BEGIN INSERT INTO [dbo].[ItemLocations] ([Code], [Description], [Fullyn], [Retired]) VALUES (@Code, @Description, @childcare, @Retired ) END END ELSE IF (@MasterName='GroupModule') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[ModuleGroup] WHERE RTRIM([GroupName])=@Code) BEGIN UPDATE [dbo].[ModuleGroup] SET [Description] = @Description, [IsActive] = @Retired WHERE RTRIM([GroupName]) = @Code END ELSE BEGIN INSERT INTO [dbo].[ModuleGroup] ([GroupName], [Description], [IsActive]) VALUES (@Code, @Description, @Retired) END END ELSE IF (@MasterName='Occupation') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[MiscCodes] WHERE RTRIM([CdeCode]) = RTRIM(@Code) AND RTRIM([CdeType]) = 'OCC') BEGIN UPDATE [dbo].[MiscCodes] SET [CdeDesc] = @Description WHERE RTRIM([CdeCode]) = @Code END ELSE BEGIN INSERT INTO [dbo].[MiscCodes] ([CdeType], [CdeCode], [CdeDesc], [CdeTeam] ) VALUES ('OCC', @Code, @Description, '') END END ELSE IF (@MasterName='Contact Type') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[MiscCodes] WHERE RTRIM([CdeCode]) = RTRIM(@Code) AND RTRIM([CdeType]) = 'CCL') BEGIN UPDATE [dbo].[MiscCodes] SET [CdeDesc] = @Description WHERE RTRIM([CdeCode]) = @Code END ELSE BEGIN INSERT INTO [dbo].[MiscCodes] ([CdeType], [CdeCode], [CdeDesc], [CdeTeam] ) VALUES ('CCL', @Code, @Description, '') END END ELSE IF (@MasterName='Client Group') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[ClientGroup] WHERE RTRIM([GroupCode]) = RTRIM(@Code)) BEGIN UPDATE [dbo].[ClientGroup] SET [GroupName] = @Description WHERE RTRIM([GroupCode]) = @Code END ELSE BEGIN INSERT INTO [dbo].[ClientGroup] ([GroupCode], [GroupName] ) VALUES (@Code, @Description) END END ELSE IF (@MasterName='HandlerChargeGroup') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[HandlerChargeGroups] WHERE RTRIM(ISNULL([Code],'')) = RTRIM(@Code)) BEGIN SET @HandleRate=(Select [Defaultrate] from [dbo].[HandlerChargeGroups] where RTRIM(ISNULL([Code],'')) = @Code) IF @HandleRate <> @DefaultRate BEGIN SET @EventDesc='Updated Default Rate from ( ' + CONVERT(varchar(10),@HandleRate)+ ' ) 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 [dbo].[HandlerChargeGroups] SET [Name] = @Description, [DefaultRate] = @DefaultRate WHERE RTRIM(ISNULL([Code],'')) = @Code END ELSE BEGIN INSERT INTO [dbo].[HandlerChargeGroups] ([Code], [Name], [DefaultRate]) VALUES (@Code, @Description, @DefaultRate) END END ELSE IF (@MasterName='StatusCodes') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[StatusCodes] WHERE RTRIM([Code]) = RTRIM(@Code)) BEGIN UPDATE [dbo].[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 [dbo].[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 [dbo].[UDFCategory] SET [UDFCategory] = @Description WHERE [ID] = @Code END ELSE BEGIN INSERT INTO [dbo].[UDFCategory] ([UDFCategory] ) VALUES (@Description) END END ELSE IF (@MasterName='SDLTForm') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[SDLTForm] WHERE [FormID] = @RGBColour) BEGIN UPDATE [dbo].[SDLTForm] SET [FormName] = @Code , [Description] = @Description WHERE [FormID] = @RGBColour END ELSE BEGIN INSERT INTO [dbo].[SDLTForm] ([FormName], [Description]) VALUES (@Code, @Description) END END ELSE IF (@MasterName='Undertaking Category') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[UndertakingsCategory] WHERE RTRIM([Code])= RTRIM(@Code)) BEGIN UPDATE [dbo].[UndertakingsCategory] SET [Description] = @Description WHERE RTRIM([Code]) = @Code END ELSE BEGIN INSERT INTO [dbo].[UndertakingsCategory] ([Code], [Description]) VALUES (@Code, @Description) END END ELSE IF (@MasterName='KYCType') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[KYCType] WHERE RTRIM([PK_Type])= RTRIM(@Code)) BEGIN UPDATE [dbo].[KYCType] SET [Description] = @Description WHERE RTRIM([PK_Type]) = @Code END ELSE BEGIN INSERT INTO [dbo].[KYCType] ([Description]) VALUES (@Description) END END ELSE IF (@MasterName='IntroBy') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[IntroductionBy] WHERE RTRIM([Pk_Intro])= RTRIM(@Code)) BEGIN UPDATE [dbo].[IntroductionBy] SET [Description] = @Description WHERE RTRIM([Pk_Intro]) = @Code END ELSE BEGIN INSERT INTO [dbo].[IntroductionBy] ([Description]) VALUES (@Description) END END ELSE IF (@MasterName='ClientType') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[ClientType] WHERE RTRIM([PK_ClientType])= RTRIM(@Code)) BEGIN UPDATE [dbo].[ClientType] SET [Description] = @Description WHERE RTRIM([PK_ClientType]) = @Code END ELSE BEGIN INSERT INTO [dbo].[ClientType] ([Description]) VALUES (@Description) END END ELSE IF (@MasterName='ID Code') BEGIN IF EXISTS(SELECT TOP 1 1 FROM [dbo].[IDCodes] WHERE RTRIM(IdCode)= RTRIM(@Code)) BEGIN UPDATE [dbo].[IDCodes] SET [Description] = @Description, [Category] = @Retired WHERE RTRIM([IdCode]) = @Code END ELSE BEGIN INSERT INTO [dbo].[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 [dbo].[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 [dbo].[CourtFees] SET [Code] = @Code, [Description] = @Description, [ChargeClient] = @clientCharge, [Value] = @value, [ClientValue] = @ClientValue WHERE [Code] = @Code END ELSE BEGIN INSERT INTO [dbo].[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_PostSAMPurchaseBill',N'P')IS NOT NULL DROP PROCEDURE [dbo].[KAAS_PostSAMPurchaseBill] GO CREATE PROCEDURE [dbo].[KAAS_PostSAMPurchaseBill] ( @BatchNo int ) AS /******************************************************************************************************* * Update Purchase Bill * * * * Stored Procedure Name : [dbo].[KAAS_PostSAMPurchaseBill] * * * * Modification History: * * 2021-08-06 Vinodhkumar M Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON select Narr= 'Post Bill successfull' SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_SamGetCategory', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_SamGetCategory] END GO CREATE PROCEDURE [dbo].[KAAS_SamGetCategory] AS /******************************************************************************************************* * This SP used to Fetch category * * * * Stored Procedure Name : [dbo].[KAAS_SamGetCategory] * * * * Modification History: * * 29 July 2021 Revathy D Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT [CGR].[CATTYPE] AS [CatType], [CGR].[CODE] AS [Code], [CGR].[DESC$] AS [CodeDescription], [CGT].[CATEGORYTYPE] AS [CategoryType], [CGT].[DESCRIPTION] AS [CategoryTypeDescription] FROM [dbo].[Category] CGR LEFT OUTER JOIN [dbo].[CategoryType] CGT ON [CGR].[CATTYPE] = [CGT].[CATEGORYTYPE] ORDER BY [CGR].[CATTYPE] ASC, [CGR].[CODE] ASC SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_SamInsertKPIParamerter', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_SamInsertKPIParamerter] END GO CREATE PROCEDURE [dbo].[KAAS_SamInsertKPIParamerter] ( @ID INT, @StartOfYear DATETIME, @FromDate DATETIME, @ToDate DATETIME, @Period INT, @Year INT) AS /******************************************************************************************************* * This procedure is used to update the KPI Parameters * * * * Stored Procedure Name : [dbo].[KAAS_SamInsertKPIParamerter] * * * * Modification History : * * 2021-08-03 Revathy D Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON IF ISNULL(@ID, 0) = 0 AND NOT EXISTS(SELECT TOP 1 1 FROM [dbo].[KPIParameters]) BEGIN INSERT INTO [dbo].[KPIParameters] ( [StartOfYear], [FromDate], [ToDate], [Period], [Year]) SELECT @StartOfYear, @FromDate, @ToDate, @Period, @Year END ELSE BEGIN UPDATE [dbo].[KPIParameters] SET [StartOfYear] = @StartOfYear, [FromDate] = @FromDate, [ToDate] = @ToDate, [Period] = @Period, [Year] = @Year WHERE [ID] = @ID END SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_SaveColumnChooserList', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_SaveColumnChooserList] END 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 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 SET NOCOUNT OFF END GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_SetSAMClientToApprovedForMoneyLaundering' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_SetSAMClientToApprovedForMoneyLaundering] END GO CREATE PROCEDURE [dbo].[KAAS_SetSAMClientToApprovedForMoneyLaundering] ( @Result INT OUTPUT ) AS /******************************************************************************************************* * This stored procedure is used to SET MoneyLaudering is Approved for all client * * * * Stored Procedure Name : [dbo].[KAAS_SetSAMClientToApprovedForMoneyLaundering] * * Copied From : [dbo].[ky_NETMATSetClientToApprovedForMoneyLaundering] * * * * Modification History: * * 2021-08-11 Balamurugan.C Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON UPDATE [dbo].[Contacts] SET [Approved]=1 WHERE [Client] = 'Y' SET @Result = 1 SET NOCOUNT OFF END GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_SetSAMMatterOutlayBudget' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_SetSAMMatterOutlayBudget] END GO CREATE PROCEDURE [dbo].[KAAS_SetSAMMatterOutlayBudget] ( @OutlayAmount DECIMAL(18,9), @Target VARCHAR(30) ) AS /*********************************************************************************** * * * This stored procedure is used to set all matter outlaybudget is set value * * * * Stored Procedure Name : [dbo].[KAAS_SetSAMMatterOutlayBudget] * * Copied From : [dbo].[ky_netSetMatterOutlayBudget] * * * * Modification History: * * 2021-08-11 Balamurugan.C Created * * * ***********************************************************************************/ BEGIN SET NOCOUNT ON IF LOWER(ISNULL(@Target, '')) = 'all' BEGIN UPDATE [dbo].[Matters] SET [OutlayBud] = ISNULL(@OutlayAmount, 0) END IF LOWER(ISNULL(@Target, '')) = 'nobudget' BEGIN UPDATE MAT SET MAT.[OutlayBud] = ISNULL(@OutlayAmount, 0) FROM [dbo].[Matters] AS MAT WHERE ISNULL(MAT.[OutlayBud], 0) = 0 END SET NOCOUNT OFF END GO IF OBJECT_ID(N'KAAS_SPMaintainUpdateHandler', N'P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[KAAS_SPMaintainUpdateHandler] END GO CREATE PROCEDURE [dbo].[KAAS_SPMaintainUpdateHandler] (@XML NVARCHAR(MAX), @SkipWelcomeScreen BIT) AS BEGIN /************************************************************************************************************* * * * [dbo].[KAAS_SPMaintainUpdateHandler] * * * * Description: This procedure is used to update handler information * * * * ***************************************************************************************************** * * * * Modification history: * * 2021-06-25 Natarajan S Created * * 2021-06-29 Natarajan S Modified - Changed variables as per coding standard. * *************************************************************************************************************/ DECLARE @iSL INT EXEC sp_xml_preparedocument @iSL OUTPUT, @XML DECLARE @COMMAND NVARCHAR(MAX) DECLARE @NEWHANDLERPHONES TABLE ([Code] VARCHAR(10) PRIMARY KEY, [PhoneNo] VARCHAR(50), [PhoneNumber] VARCHAR(50)) INSERT INTO @NEWHANDLERPHONES ([Code], [PhoneNo], [PhoneNumber]) SELECT [Code], [PhoneNo], [PhoneNumber] FROM OPENXML(@iSL, N'HAN') WITH ([Code] VARCHAR(10) '@Code', [Phoneno] VARCHAR(50) '@Phoneno', [PhoneNumber] VARCHAR(50) '@PhoneNumber') [DTA] UPDATE @NEWHANDLERPHONES SET [Phoneno] = ISNULL([PhoneNumber], '') WHERE ISNULL([PhoneNo], '') = '' UPDATE @NEWHANDLERPHONES SET [PhoneNumber] = ISNULL([Phoneno], '') WHERE ISNULL([PhoneNumber], '') = '' UPDATE @NEWHANDLERPHONES SET [PhoneNumber] = ISNULL([Phoneno], '') WHERE [PhoneNumber] <> [Phoneno] SELECT @COMMAND = N' DECLARE @Exists BIT INSERT INTO [dbo].[Handlers] ([Code]) SELECT [NEW].[Code] FROM (SELECT ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Code]), '''', '''''')) + N''' [Code]) [NEW] LEFT OUTER JOIN [dbo].[Handlers] [HAN] ON [HAN].[Code] = [NEW].[Code] WHERE [HAN].[Code] IS NULL UPDATE [HAN] SET ' + CASE WHEN [DTA].[Team] IS NULL THEN N'' ELSE N', [Team] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Team]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[TeamCode] IS NULL THEN N'' ELSE N', [TeamCode] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[TeamCode]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[Dept] IS NULL THEN N'' ELSE N', [Dept] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Dept]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[Type] IS NULL THEN N'' ELSE N', [Type] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Type]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[Name] IS NULL THEN N'' WHEN RTRIM([DTA].[Name]) = RTRIM(ISNULL([HAN].[Name], '')) THEN N'' ELSE N', [Name] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Name]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[Rate] IS NULL THEN N'' ELSE N', [Rate] = ' + CONVERT(NVARCHAR(200), [DTA].[Rate]) END + CASE WHEN [DTA].[Budget1] IS NULL THEN N'' ELSE N', [Budget1] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget1]) END + CASE WHEN [DTA].[Budget2] IS NULL THEN N'' ELSE N', [Budget2] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget2]) END + CASE WHEN [DTA].[Budget3] IS NULL THEN N'' ELSE N', [Budget3] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget3]) END + CASE WHEN [DTA].[Budget4] IS NULL THEN N'' ELSE N', [Budget4] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget4]) END + CASE WHEN [DTA].[Budget5] IS NULL THEN N'' ELSE N', [Budget5] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget5]) END + CASE WHEN [DTA].[Budget6] IS NULL THEN N'' ELSE N', [Budget6] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget6]) END + CASE WHEN [DTA].[Budget7] IS NULL THEN N'' ELSE N', [Budget7] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget7]) END + CASE WHEN [DTA].[Budget8] IS NULL THEN N'' ELSE N', [Budget8] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget8]) END + CASE WHEN [DTA].[Budget9] IS NULL THEN N'' ELSE N', [Budget9] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget9]) END + CASE WHEN [DTA].[Budget10] IS NULL THEN N'' ELSE N', [Budget10] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget10]) END + CASE WHEN [DTA].[Budget11] IS NULL THEN N'' ELSE N', [Budget11] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget11]) END + CASE WHEN [DTA].[Budget12] IS NULL THEN N'' ELSE N', [Budget12] = ' + CONVERT(NVARCHAR(200), [DTA].[Budget12]) END + CASE WHEN [DTA].[Actual1] IS NULL THEN N'' ELSE N', [Actual1] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual1])) END + CASE WHEN [DTA].[Actual2] IS NULL THEN N'' ELSE N', [Actual2] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual2])) END + CASE WHEN [DTA].[Actual3] IS NULL THEN N'' ELSE N', [Actual3] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual3])) END + CASE WHEN [DTA].[Actual4] IS NULL THEN N'' ELSE N', [Actual4] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual4])) END + CASE WHEN [DTA].[Actual5] IS NULL THEN N'' ELSE N', [Actual5] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual5])) END + CASE WHEN [DTA].[Actual6] IS NULL THEN N'' ELSE N', [Actual6] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual6])) END + CASE WHEN [DTA].[Actual7] IS NULL THEN N'' ELSE N', [Actual7] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual7])) END + CASE WHEN [DTA].[Actual8] IS NULL THEN N'' ELSE N', [Actual8] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual8])) END + CASE WHEN [DTA].[Actual9] IS NULL THEN N'' ELSE N', [Actual9] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual9])) END + CASE WHEN [DTA].[Actual10] IS NULL THEN N'' ELSE N', [Actual10] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual10])) END + CASE WHEN [DTA].[Actual11] IS NULL THEN N'' ELSE N', [Actual11] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual11])) END + CASE WHEN [DTA].[Actual12] IS NULL THEN N'' ELSE N', [Actual12] = ' + CONVERT(NVARCHAR(200), DATEDIFF(DAY, CONVERT(DATETIME, '18001228'), [DTA].[Actual12])) END + CASE WHEN [DTA].[TempCalc] IS NULL THEN N'' ELSE N', [TempCalc] = ' + CONVERT(NVARCHAR(200), [DTA].[TempCalc]) END + CASE WHEN [DTA].[Logon] IS NULL THEN N'' ELSE N', [Logon] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Logon]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[MailAddress] IS NULL THEN N'' ELSE N', [MailAddress] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[MailAddress]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[TimeCoster] IS NULL THEN N'' ELSE N', [TimeCoster] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[TimeCoster]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[TEAMLEADER] IS NULL THEN N'' ELSE N', [TEAMLEADER] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[TEAMLEADER]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[NoRecentMatters] IS NULL THEN N'' ELSE N', [NoRecentMatters] = ' + CONVERT(NVARCHAR(200), [DTA].[NoRecentMatters]) END + CASE WHEN [DTA].[DefaultTask] IS NULL THEN N', [DefaultTask] = ''''' ELSE N', [DefaultTask] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[DefaultTask]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[ExchangeAlias] IS NULL THEN N'' ELSE N', [ExchangeAlias] = ''' + CONVERT(NVARCHAR(300), REPLACE(RTRIM([DTA].[ExchangeAlias]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[Rate1] IS NULL THEN N'' ELSE N', [Rate1] = ' + CONVERT(NVARCHAR(200), [DTA].[Rate1]) END + CASE WHEN [DTA].[Rate2] IS NULL THEN N'' ELSE N', [Rate2] = ' + CONVERT(NVARCHAR(200), [DTA].[Rate2]) END + CASE WHEN [DTA].[Rate3] IS NULL THEN N'' ELSE N', [Rate3] = ' + CONVERT(NVARCHAR(200), [DTA].[Rate3]) END + CASE WHEN [DTA].[Rate4] IS NULL THEN N'' ELSE N', [Rate4] = ' + CONVERT(NVARCHAR(200), [DTA].[Rate4]) END + CASE WHEN [DTA].[Rate5] IS NULL THEN N'' ELSE N', [Rate5] = ' + CONVERT(NVARCHAR(200), [DTA].[Rate5]) END + CASE WHEN [DTA].[Signature] IS NULL THEN N'' ELSE N', [Signature] = ''' + CONVERT(NVARCHAR(MAX), REPLACE(RTRIM([DTA].[Signature]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[Email] IS NULL THEN N'' ELSE N', [Email] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Email]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[Retired] IS NULL THEN N'' ELSE N', [Retired] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Retired]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[Delegate] IS NULL THEN N'' ELSE N', [Delegate] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Delegate]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[OutlookView] IS NULL THEN N'' ELSE N', [OutlookView] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[OutlookView]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[AutoLaunchTimer] IS NULL THEN N'' ELSE N', [AutoLaunchTimer] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[AutoLaunchTimer]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[Phoneno] IS NULL THEN N'' ELSE N', [Phoneno] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([HPN].[Phoneno]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[PhoneNumber] IS NULL THEN N'' ELSE N', [PhoneNumber] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([HPN].[Phoneno]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[AutoLaunchKPI] IS NULL THEN N'' ELSE N', [AutoLaunchKPI] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[AutoLaunchKPI]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[AxleNumber] IS NULL THEN N'' ELSE N', [AxleNumber] = ' + CONVERT(NVARCHAR(200), [DTA].[AxleNumber]) END + CASE WHEN [DTA].[UnitCost] IS NULL THEN N'' ELSE N', [UnitCost] = ' + CONVERT(NVARCHAR(200), [DTA].[UnitCost]) END + CASE WHEN [DTA].[TargetRecov] IS NULL THEN N'' ELSE N', [TargetRecov] = ' + CONVERT(NVARCHAR(200), [DTA].[TargetRecov]) END + CASE WHEN [DTA].[Branch] IS NULL THEN N'' ELSE N', [Branch] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Branch]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[FaxNumber] IS NULL THEN N'' ELSE N', [FaxNumber] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[FaxNumber]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[ChargeGroup] IS NULL THEN N'' ELSE N', [ChargeGroup] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[ChargeGroup]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[EmployeeRefNo] IS NULL THEN N'' ELSE N', [EmployeeRefNo] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[EmployeeRefNo]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[ISChargeable] IS NULL THEN N'' ELSE N', [ISChargeable] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[ISChargeable]), '''', '''''')) + N'''' END + CASE WHEN RTRIM(ISNULL([DTA].[TradingName], '')) = '' THEN N', [TradingName] = RTRIM(ISNULL([CON].[Name], ''''))' ELSE N', [TradingName] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[TradingName]), '''', '''''')) + N'''' END + CASE WHEN [DTA].[JobTitle] IS NULL THEN N'' ELSE N', [JobTitle] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[JobTitle]), '''', '''''')) + N'''' END + N' FROM [dbo].[Handlers] [HAN] CROSS APPLY (SELECT TOP 1 [Name] FROM [dbo].[Control]) [CON] WHERE [HAN].[Code] = ''' + CONVERT(NVARCHAR(200), REPLACE(RTRIM([DTA].[Code]), '''', '''''')) + N'''' FROM OPENXML(@iSL, N'HAN') WITH ([Code] VARCHAR(10) '@Code', [Team] VARCHAR(10) '@Team', [TeamCode] CHAR(1) '@TeamCode', [Dept] VARCHAR(5) '@Dept', [Type] CHAR(1) '@Type', [Name] VARCHAR(30) '@Name', [Rate] DECIMAL(10,2) '@Rate', [Budget1] DECIMAL(13,2) '@Budget1', [Budget2] DECIMAL(13,2) '@Budget2', [Budget3] DECIMAL(13,2) '@Budget3', [Budget4] DECIMAL(13,2) '@Budget4', [Budget5] DECIMAL(13,2) '@Budget5', [Budget6] DECIMAL(13,2) '@Budget6', [Budget7] DECIMAL(13,2) '@Budget7', [Budget8] DECIMAL(13,2) '@Budget8', [Budget9] DECIMAL(13,2) '@Budget9', [Budget10] DECIMAL(13,2) '@Budget10', [Budget11] DECIMAL(13,2) '@Budget11', [Budget12] DECIMAL(13,2) '@Budget12', [Actual1] DATETIME '@Actual1', [Actual2] DATETIME '@Actual2', [Actual3] DATETIME '@Actual3', [Actual4] DATETIME '@Actual4', [Actual5] DATETIME '@Actual5', [Actual6] DATETIME '@Actual6', [Actual7] DATETIME '@Actual7', [Actual8] DATETIME '@Actual8', [Actual9] DATETIME '@Actual9', [Actual10] DATETIME '@Actual10', [Actual11] DATETIME '@Actual11', [Actual12] DATETIME '@Actual12', [TempCalc] DECIMAL(13,2) '@TempCalc', [Logon] VARCHAR(10) '@Logon', [MailAddress] VARCHAR(20) '@MailAddress', [TimeCoster] CHAR(1) '@TimeCoster', [TeamLeader] VARCHAR(10) '@TeamLeader', [NoRecentMatters] INT '@NoRecentMatters', [DefaultTask] CHAR(6) '@DefaultTask', [ExchangeAlias] VARCHAR(256) '@ExchangeAlias', [Rate1] DECIMAL(7,2) '@Rate1', [Rate2] DECIMAL(7,2) '@Rate2', [Rate3] DECIMAL(7,2) '@Rate3', [Rate4] DECIMAL(7,2) '@Rate4', [Rate5] DECIMAL(7,2) '@Rate5', [Signature] VARCHAR(MAX) '@Signature', [Email] VARCHAR(100) '@Email', [Retired] CHAR(1) '@Retired', [Delegate] VARCHAR(50) '@Delegate', [OutlookView] CHAR(1) '@OutlookView', [AutoLaunchTimer] CHAR(1) '@AutoLaunchTimer', [Phoneno] VARCHAR(50) '@Phoneno', [PhoneNumber] VARCHAR(50) '@PhoneNumber', [AutoLaunchKPI] CHAR(1) '@AutoLaunchKPI', [AxleNumber] INT '@AxleNumber', [UnitCost] DECIMAL(14,2) '@UnitCost', [TargetRecov] DECIMAL(7,2) '@TargetRecov', [Branch] CHAR(3) '@Branch', [FaxNumber] VARCHAR(50) '@FaxNumber', [ChargeGroup] CHAR(5) '@ChargeGroup', [EmployeeRefNo] VARCHAR(10) '@EmployeeRefNo', [ISChargeable] VARCHAR(1) '@ISChargeable', [TradingName] VARCHAR(200) '@TradingName', [JobTitle] VARCHAR(100) '@JobTitle', [SyncRetire] bit '@SyncRetire') [DTA] INNER JOIN @NEWHANDLERPHONES [HPN] ON [HPN].[Code] = [DTA].[Code] LEFT OUTER JOIN [dbo].[Handlers] [HAN] ON [HAN].[Code] = [DTA].[Code] SET @COMMAND = REPLACE(@COMMAND, N'SET ,' + NCHAR(13) + NCHAR(10) + N' ', N'SET ') EXEC (@COMMAND) UPDATE [FEC] SET [FEC].[Retired] = [HAN].[Retired] FROM OPENXML(@iSL, N'HAN') WITH ([Code] VARCHAR(10) '@Code', [SyncRetire] bit '@SyncRetire') [DTA] INNER JOIN [dbo].[Handlers] [HAN] ON [HAN].[Code] = [DTA].[Code] INNER JOIN [dbo].[FeeEarnerCodes] [FEC] ON [FEC].[Code] = [DTA].[Code] WHERE ISNULL([DTA].[SyncRetire], CONVERT(BIT, 0)) = 1 INSERT INTO [dbo].[UserSettings] ([Handler], [Key], [Value]) SELECT [DTA].[Code], 'NewMatterWizardDontShowWelcomeScreen', CONVERT(NVARCHAR(MAX), ISNULL(@SkipWelcomeScreen, CONVERT(BIT, 0))) FROM OPENXML(@iSL, N'HAN') WITH ([Code] VARCHAR(10) '@Code') [DTA] LEFT OUTER JOIN [dbo].[UserSettings] [SET] ON [SET].[Handler] = [DTA].[Code] AND [SET].[Key] = 'NewMatterWizardDontShowWelcomeScreen' WHERE [SET].[SettingID] IS NULL UPDATE [SET] SET [SET].[Value] = CONVERT(NVARCHAR(MAX), ISNULL(@SkipWelcomeScreen, CONVERT(BIT, 0))) FROM OPENXML(@iSL, N'HAN') WITH ([Code] VARCHAR(10) '@Code') [DTA] INNER JOIN [dbo].[UserSettings] [SET] ON [SET].[Handler] = [DTA].[Code] AND [SET].[Key] = 'NewMatterWizardDontShowWelcomeScreen' -- KEYD-6912 - Insert new name into HandlerNames table, but only update now if the setting is turned off (default) IF NOT EXISTS (SELECT TOP 1 1 FROM [dbo].[Settings] AS [SET] WHERE [SET].[KeyName] = 'DelayHandlerNameUpdate' AND [SET].[KeyValue] = 'True') BEGIN UPDATE [HN] SET [HN].[Name] = ISNULL([DTA].[Name], '') FROM OPENXML(@iSL, N'HAN') WITH ([Code] VARCHAR(10) '@Code', [TeamCode] CHAR(1) '@TeamCode', [Name] VARCHAR(30) '@Name') [DTA] INNER JOIN [dbo].[HandlerNames] AS [HN] ON [HN].[Code] = [DTA].[Code] --WHERE ISNULL([DTA].[TeamCode], '') <> 'Y' END INSERT INTO [dbo].[HandlerNames] ( [Code], [Name]) SELECT [DTA].[Code], ISNULL([DTA].[Name], '') FROM OPENXML(@iSL, N'HAN') WITH ([Code] VARCHAR(10) '@Code', [TeamCode] CHAR(1) '@TeamCode', [Name] VARCHAR(30) '@Name') [DTA] LEFT OUTER JOIN [dbo].[HandlerNames] AS [HN] ON [HN].[Code] = [DTA].[Code] WHERE [HN].[Code] IS NULL AND ISNULL([DTA].[TeamCode], '') <> 'Y' EXEC sp_xml_removedocument @iSL 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 * * 2021-07-13 Natarajan S Modified - Added Formatting * *************************************************************************************************************************************************/ BEGIN TRY BEGIN TRANSACTION DECLARE @Narrative VARCHAR(80) DECLARE @SpaceVal INT DECLARE @NameLen INT DECLARE @CurrDate DATETIME SET @CurrDate = GETDATE() UPDATE [OMO] SET [OMO].[FNL_Date] = @FNLDate, [OMO].[FNL_Name] = @FNLName, [OMO].[FNL_Address] = @FNLAddress, [OMO].[FNL_Fees] = @FNLFees, [OMO].[FNL_VAT] = @FNLVat, [OMO].[FNL_Outlay] = @FNLOutlay FROM [dbo].[OpenMattersOnly] [OMO] WHERE [OMO].[Code] = @MatterCode UPDATE [MAT] SET [MAT].[FNL_Date] = @FNLDate, [MAT].[FNL_Name] = @FNLName, [MAT].[FNL_Address] = @FNLAddress, [MAT].[FNL_Fees] = @FNLFees, [MAT].[FNL_VAT] = @FNLVat, [MAT].[FNL_Outlay] = @FNLOutlay, [MAT].[FNL_Total] = @FNLTotal FROM [dbo].[Matters] [MAT] WHERE [MAT].[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 COMMIT TRANSACTION END TRY BEGIN CATCH SET @Result = 0 ROLLBACK TRANSACTION END CATCH GO IF EXISTS (SELECT TOP 1 1 FROM sys.[objects] SO WHERE SO.[name] = N'KAAS_UpdateSAMSystemOptionsDetails' AND SO.[type] = 'P') BEGIN DROP PROCEDURE [dbo].[KAAS_UpdateSAMSystemOptionsDetails] END GO CREATE PROCEDURE [dbo].[KAAS_UpdateSAMSystemOptionsDetails] ( @TabName VARCHAR(50) = '', @Licencee VARCHAR(40) = '', @VatNo VARCHAR(20) = '', @Year INT = 0, @DebtorsControlAc VARCHAR(10) = '', @CreditorsControlAc VARCHAR(10) = '', @CurrentPeriod INT = 0, @NextPeriodEnd DATETIME = NULL, @OutlayControlAc VARCHAR(10) = '', @VatAc VARCHAR(10) = '', @YearEndDate DATETIME = NULL, @ProfitLossAc VARCHAR(10) = '', @ClientLedgerControl VARCHAR(10) = '', @StdOutlayBudget DECIMAL = 0, @FeeWriteOffAc VARCHAR(10) = '', @OutlayWriteOffAc VARCHAR(10) = '', @LastBillNo INT = 0, @VatExclusionCode VARCHAR(1) = '', @PurchBillWriteOffAc VARCHAR(10) = '', @DefaultOfficeAc VARCHAR(10) = '', @DefaultVat VARCHAR(1) = '', @FeeExclusionCode VARCHAR(10) = '', @DefaultClientDepositAc VARCHAR(10) = '', @MiscOutlay VARCHAR(10) = '', @DefaultMiscOutlayFee VARCHAR(10) = '', @DefaultPettyCash VARCHAR(10) = '', @WithHoldingTax VARCHAR(10) = '', @EnableFNL VARCHAR(10) = '', @ShowConvertedLedgerBtn VARCHAR(1) = '', @EnableClientDB VARCHAR(1) = '', @UK VARCHAR(1) = '', @EnableDeedWills VARCHAR(1) = '', @YearEndCompleted VARCHAR(1) = '', @EnableFileManager VARCHAR(1) = '', @UseOutlayType VARCHAR(1) = '', @PendingOutlay VARCHAR(1) = '', @UseBranches VARCHAR(1) = '', @EnableBankRec VARCHAR(1) = '', @BillPrinting VARCHAR(1) = '', @UniqueMatterNumber VARCHAR(1) = '', @TaggedBatches VARCHAR(1) = '', @OverdrawnClientAc VARCHAR(1) = '', @IntrestCalculator VARCHAR(1) = '', @WordPerfect VARCHAR(1) = '', @Indicator24 VARCHAR(1) = '', @Indicator25 VARCHAR(1) = '', @Indicator26 VARCHAR(1) = '', @Indicator27 VARCHAR(1) = '', @Indicator28 VARCHAR(1) = '', @Indicator29 VARCHAR(1) = '', @Indicator30 VARCHAR(1) = '', @DemoExpire INT = 0, @MaxMatter DECIMAL = 0, @CaseMgrLink VARCHAR(1) = '', @TimeCostingInstalled VARCHAR(1) = '', @ForceFeeEarner VARCHAR(1) = '', @LabelPrinter VARCHAR(1) = '', @FeeReporting VARCHAR(10) = '', @CompositeBilling VARCHAR(1) = '', @SolicitorCategory VARCHAR(6) = '', @OtherPartyCategory VARCHAR(6) = '', @SupplierCategory VARCHAR(6) = '', @SystemCategoryType VARCHAR(6) = '', @AutoNumberClient VARCHAR(1) = '', @AutoAlphaNumbering VARCHAR(1) = '', @CloseMatterTimeBarred VARCHAR(1) = '', @SupplierRemittance VARCHAR(1) = '', @EFTDefaultON VARCHAR(1) = '', @MatterOutlayBudgetChkON VARCHAR(1) = '', @ThirdPartyLimit DECIMAL = 0, @DatePastValidation INT = 0, @DateFutureValidation INT = 0, @ConversionRate DECIMAL = 0, @PromptRef1 VARCHAR(20) = '', @PromptRef2 VARCHAR(20) = '', @PromptRef3 VARCHAR(20) = '', @NextFile INT = 0, @ClosedFileNo INT = 0, @ChequeType VARCHAR(2) = '', @EnableChequePrinting VARCHAR(1) = '', @LabelNo VARCHAR(2) = '', @InvoiceApproverEmail VARCHAR(100) = '', @ChequeApproverEmail VARCHAR(100) = '', @NotifyReturnInvoice VARCHAR(1) = '', @NotifyApprovedInvoice VARCHAR(1) = '', @NotifyReturnCheque VARCHAR(1) = '', @NotifyApprovedCheque VARCHAR(1) = '', @SMTPMailServer VARCHAR(100) = '', @MoneyLaunderingCheck VARCHAR(1) = '', @MoneyLaunderingDept VARCHAR(20) = '', @MoneyLaunderingFE VARCHAR(20) = '', @MoneyLaunderingWorkType VARCHAR(20) = '' ) AS /******************************************************************************************************* * This SP used to update the System Options details * * * * Stored Procedure Name : [dbo].[KAAS_UpdateSAMSystemOptionsDetails] * * Copied From : ky_NETSYSCONUpdateConfigurationSettings10 * * * * Modification History : * * 2021-08-09 Balamurugan.C Created * *******************************************************************************************************/ BEGIN SET NOCOUNT ON IF(@TabName = 'SystemOptions') BEGIN -- Log changes to LastBill DECLARE @CURLASTBILL VARCHAR(20) SELECT TOP 1 @CURLASTBILL = CONVERT(VARCHAR(20), ISNULL(CON.[LASTBILL], 0)) FROM [dbo].[control] AS CON IF ISNULL(@CURLASTBILL, '') <> ISNULL(@LastBillNo, '') BEGIN INSERT INTO [dbo].[Log] ( [DATE], [USER], [EVENT] ) SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0), [dbo].[ky_NETFNGetLoggedOnHandler](), 'System Options Change (Last Bill No) from: ' + ISNULL(@CURLASTBILL, '') + ' to: ' + ISNULL(@LastBillNo, '') END UPDATE [dbo].[Control] SET [Name] = @Licencee, [VatNo] = @VatNo, [Year] = @Year, [Debtors] = @DebtorsControlAc, [Creditor] = @CreditorsControlAc, [CurPer] = @CurrentPeriod, [Outlay] = @OutlayControlAc, [Vat] = @VatAc, [YearEndDate] = @YearEndDate, [Pl] = @ProfitLossAc, [Client] = @ClientLedgerControl, [OutlayBudget] = @StdOutlayBudget, [FeesWriteOff] = @FeeWriteOffAc, [OutlayWriteOff] = @OutlayWriteOffAc, [LastBill] = @LastBillNo, [VatExclude] = @VatExclusionCode, [BillsWriteOff] = @PurchBillWriteOffAc, [DefaultOfficeAc] = @DefaultOfficeAc, [DefaultVat] = @DefaultVat, [FeeExclude] = @FeeExclusionCode, [DefaultClientDep] = @DefaultClientDepositAc, [MiscOutlay] = @MiscOutlay, [MiscFE] = @DefaultMiscOutlayFee, [PettyCash] = @DefaultPettyCash, [WithTax] = @WithHoldingTax, [EnableFNL] = @EnableFNL, [PeriodEnd] = (SELECT [dbo].[KAAS_FN_ConvertDateToClarion](@NextPeriodEnd)) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'Options_Conversion' ) UPDATE [dbo].[Settings] SET [KeyValue] = @ShowConvertedLedgerBtn WHERE [KeyName] = 'Options_Conversion' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'Options_Conversion', @ShowConvertedLedgerBtn ) END IF(@TabName = 'Keyhouse') BEGIN UPDATE [dbo].[Control] SET [EnableCDB] = @EnableClientDB, [UK] = @UK, [EnableDW] = @EnableDeedWills, [YearEndCompleted] = @YearEndCompleted, [EnableFM] = @EnableFileManager, [UseOutlayType] = @UseOutlayType, [PendingOutlay] = @PendingOutlay, [Indicator22] = @UseBranches, [RecInstalled] = @EnableBankRec, [Indicator18] = @BillPrinting, [Indicator19] = @UniqueMatterNumber, [Indicator23] = @TaggedBatches, [NoBar] = @OverdrawnClientAc, [Indicator20] = @IntrestCalculator, [WordPerfect] = @WordPerfect, [Indicator24] = @Indicator24, [Indicator25] = @Indicator25, [Indicator26] = @Indicator26, [Indicator27] = @Indicator27, [Indicator28] = @Indicator28, [Indicator29] = @Indicator29, [Indicator30] = @Indicator30, [DemoExpire] = @DemoExpire, [MaxNumberofMatters] = @MaxMatter, [OpSisLink] = @CaseMgrLink, [TimeCosting] = @TimeCostingInstalled, [ForceFE] = @ForceFeeEarner, [LabelPrinter] = @LabelPrinter, [FeesRepSeq] = @FeeReporting IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'CompositeBilling' ) UPDATE [dbo].[Settings] SET [KeyValue] = @CompositeBilling WHERE [KeyName] = 'CompositeBilling' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'CompositeBilling', @CompositeBilling ) END IF(@TabName = 'More') BEGIN UPDATE [dbo].[Control] SET [Solicitor] = @SolicitorCategory, [OthParty] = @OtherPartyCategory, [SupplierCategory] = @SupplierCategory, [DefaultCategory] = @SystemCategoryType, [AutoNumber] = @AutoNumberClient, [AutoAlphaClientNumbering] = @AutoAlphaNumbering, [ThirdPartyLimit] = @ThirdPartyLimit, [DatePastValidation] = @DatePastValidation, [DateFutValidation] = @DateFutureValidation, [EiConvert] = @ConversionRate, [UserPrompt1] = @PromptRef1, [UserPrompt2] = @PromptRef2, [UserPrompt3] = @PromptRef3, [NextFile] = @NextFile, [ClosedFileNo] = @ClosedFileNo, [ChequePrinting] = @ChequeType, [EnableCP] = @EnableChequePrinting IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'TimeBarred' ) UPDATE [dbo].[Settings] SET [KeyValue] = @CloseMatterTimeBarred WHERE [KeyName] = 'TimeBarred' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'TimeBarred', @CloseMatterTimeBarred ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'SupplierRemittance_On' ) UPDATE [dbo].[Settings] SET [KeyValue] = @SupplierRemittance WHERE [KeyName] = 'SupplierRemittance_On' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'SupplierRemittance_On', @SupplierRemittance ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'EFTDefaultON' ) UPDATE [dbo].[Settings] SET [KeyValue] = @EFTDefaultON WHERE [KeyName] = 'EFTDefaultON' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'EFTDefaultON', @EFTDefaultON ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'MatterOutlayBudgetCheckON' ) UPDATE [dbo].[Settings] SET [KeyValue] = @MatterOutlayBudgetChkON WHERE [KeyName] = 'MatterOutlayBudgetCheckON' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'MatterOutlayBudgetCheckON', @MatterOutlayBudgetChkON ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'CASE_LabelNo' ) UPDATE [dbo].[Settings] SET [KeyValue] = @LabelNo WHERE [KeyName] = 'CASE_LabelNo' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'CASE_LabelNo', @LabelNo ) END IF(@TabName = 'NotifyByEmail') BEGIN IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'InvoiceApprover' ) UPDATE [dbo].[Settings] SET [KeyValue] = @InvoiceApproverEmail WHERE [KeyName] = 'InvoiceApprover' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'InvoiceApprover', @InvoiceApproverEmail ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'ChequeApprover' ) UPDATE [dbo].[Settings] SET [KeyValue] = @ChequeApproverEmail WHERE [KeyName] = 'ChequeApprover' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'ChequeApprover', @ChequeApproverEmail ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'NotifyReleasedInv' ) UPDATE [dbo].[Settings] SET [KeyValue] = @NotifyReturnInvoice WHERE [KeyName] = 'NotifyReleasedInv' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'NotifyReleasedInv', @NotifyReturnInvoice ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'NotifyApprovedInv' ) UPDATE [dbo].[Settings] SET [KeyValue] = @NotifyApprovedInvoice WHERE [KeyName] = 'NotifyApprovedInv' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'NotifyApprovedInv', @NotifyApprovedInvoice ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'NotifyReleasedChq' ) UPDATE [dbo].[Settings] SET [KeyValue] = @NotifyReturnCheque WHERE [KeyName] = 'NotifyReleasedChq' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'NotifyReleasedChq', @NotifyReturnCheque ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'NotifyApprovedChq' ) UPDATE [dbo].[Settings] SET [KeyValue] = @NotifyApprovedCheque WHERE [KeyName] = 'NotifyApprovedChq' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'NotifyApprovedChq', @NotifyApprovedCheque ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'EMailServer' ) UPDATE [dbo].[Settings] SET [KeyValue] = @SMTPMailServer WHERE [KeyName] = 'EMailServer' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'EMailServer', @SMTPMailServer ) END IF(@TabName = 'MoneyLaundering') BEGIN IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'Money Laundering_MoneyLaunderingCheck' ) UPDATE [dbo].[Settings] SET [KeyValue] = @MoneyLaunderingCheck WHERE [KeyName] = 'Money Laundering_MoneyLaunderingCheck' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'Money Laundering_MoneyLaunderingCheck', @MoneyLaunderingCheck ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'Money Laundering_Dept' ) UPDATE [dbo].[Settings] SET [KeyValue] = @MoneyLaunderingDept WHERE [KeyName] = 'Money Laundering_Dept' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'Money Laundering_Dept', @MoneyLaunderingDept ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'Money Laundering_FeeEarner' ) UPDATE [dbo].[Settings] SET [KeyValue] = @MoneyLaunderingFE WHERE [KeyName] = 'Money Laundering_FeeEarner' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'Money Laundering_FeeEarner', @MoneyLaunderingFE ) IF EXISTS ( SELECT TOP 1 1 FROM [dbo].[Settings] WHERE [KeyName] = 'Money Laundering_WorkType' ) UPDATE [dbo].[Settings] SET [KeyValue] = @MoneyLaunderingWorkType WHERE [KeyName] = 'Money Laundering_WorkType' ELSE INSERT INTO [dbo].[Settings] ( [KeyName], [KeyValue] ) VALUES ( 'Money Laundering_WorkType', @MoneyLaunderingWorkType ) END SET NOCOUNT OFF 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 TRY BEGIN TRANSACTION SET @Type = SUBSTRING(@Type, 1, 1) SET @GivenOrReceived = SUBSTRING(@GivenOrReceived, 1, 1) SET @CommercialOrNonCommerical = SUBSTRING(@CommercialOrNonCommerical, 1, 1) UPDATE [UTG] SET [UTG].[Type] = @Type, [UTG].[Description] = @Details, [UTG].[Who] = @Who, [UTG].[GivenOrReceived] = @GivenOrReceived, [UTG].[DealingNumber] = @DealingNumber, [UTG].[Notes] = @Notes, [UTG].[CommercialOrNon] = @CommercialOrNonCommerical, [UTG].[Status] = @Status, [UTG].[Date] = [dbo].KAAS_FNConvertDateToUTC(@Date,@Handler), [UTG].[UNDDate] = [dbo].KAAS_FNConvertDateToUTC(@Date,@Handler), [UTG].[Value] = @Value, [UTG].[UNDValue] = @Value, [UTG].[AuthorisedByFE] = @AuthorizedByFE, [UTG].[Handler] = @Handler, [UTG].[UndertakingTo] = @UndertakingTo, [UTG].[CaseContact] = @UndertakingTo, [UTG].[DischargeDate] = CASE @IsDischarge WHEN 1 THEN [dbo].KAAS_FNConvertDateToUTC(@DischargeDate,@Handler) ELSE NULL END FROM [dbo].[Undertakings] [UTG] WHERE [UTG].[ActionID] = @ActionID UPDATE [DIA] SET [DIA].[Date] = [dbo].KAAS_FNConvertDateToUTC(@diaryWarningDate,@Handler), [DIA].[DueDate] = [dbo].KAAS_FNConvertDateToUTC(@Date,@Handler), [DIA].[Text1] = @Text1 FROM [dbo].[diary] [DIA] WHERE [DIA].[ActionId] = @ActionID UPDATE [DEL] SET [DEL].[Date] = [dbo].KAAS_FNConvertDateToUTC(@diaryWarningDate,@Handler), [DEL].[DueDate] = [dbo].KAAS_FNConvertDateToUTC(@Date,@Handler) FROM [dbo].[DiaryDelegations] [DEL] WHERE [ActionId] = @ActionID SET @Result = 1 COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH GO