CREATE PROCEDURE [dbo].[sp_InsertInsuranceFee] ( @userId varchar(50), @TransactionDate DATETIME, @loanApplicationId varchar(25), @amount money, @hierarchyId varchar(25), @customerId varchar(25), @transactionType varchar(25), @remarks varchar(200), @chequeNo varchar(25), @bankId varchar(25), @branch varchar(50), @storageReference varchar(200)) AS BEGIN DECLARE @TransactionID varchar(25) ,@uniqueId varchar(25),@JSONResponse VARCHAR(MAX),@LOSResponseId VARCHAR(25) IF(@transactionType ='Cheque') BEGIN IF NOT EXISTS (SELECT 1 FROM ChequeDetails where ChequeNo=@chequeNo) BEGIN EXEC sp_GetUniqueId 'ChequeDetails_Id', @uniqueId OUTPUT INSERT INTO ChequeDetails(CreatedBy,CreatedDateTime,CustomerId,LoanApplicationId,ChequeNo,ChequeDate,ReceivedDate,StorageReference,ChequeStatus,BankId,Branch,ChequeAmount,ChequeDetailsId,TransactionType) VALUES(@userId,GETDATE(),@customerId,@loanApplicationId,@chequeNo,@TransactionDate,@TransactionDate,@storageReference,'R',@bankId,@branch,@amount,@uniqueId,'I') UPDATE UserMaster SET RunningReceiptNo=CONVERT(INT,RunningReceiptNo)+1 where UserId=@userId SELECT 'S' END ELSE SELECT 'E' END ELSE BEGIN EXEC sp_GetTransactionIdSequence @TransactionID OUTPUT INSERT INTO LoanTransactions(CreatedBy, CreatedDateTime,HierarchyId, TransactionId, TransactionDate,ValueDate, CustomerId,LoanApplicationId, DebitOrCredit, TransactionType, Amount, TransactionMode,TransactionRemarks,InstallmentNumber) VALUES ('AUTO', GETDATE(),@HierarchyId, @TransactionID, @TransactionDate,@TransactionDate, @CustomerId, @loanApplicationId,'D' ,'I', @Amount,@transactionType,@remarks,0 ) EXEC sp_GetTransactionIdSequence @TransactionID OUTPUT INSERT INTO LoanTransactions(CreatedBy, CreatedDateTime,HierarchyId, TransactionId, TransactionDate,ValueDate, CustomerId,LoanApplicationId, DebitOrCredit, TransactionType, Amount, TransactionMode,TransactionRemarks,InstallmentNumber) VALUES ('AUTO', GETDATE(),@HierarchyId, @TransactionID, @TransactionDate,@TransactionDate, @CustomerId, @loanApplicationId,'C' ,'I', @Amount,@transactionType,@remarks ,0) UPdate UserMaster SET RunningReceiptNo=CONVERT(INT,RunningReceiptNo)+1 where UserId=@userId --SELECT @JSONResponse=(SELECT @loanApplicationId AS loanApplicationNo,'Insurance' AS feeType,@amount AS amount --FOR JSON PATH, INCLUDE_NULL_VALUES) SELECT @JSONResponse=(select ' [{"loanApplicationNo":"'+@loanApplicationId+'","feeType":"'+'Insurance'+'","amount":"'+@amount+'"}]') EXEC sp_GetUniqueId 'LOSJSONRESPONSE', @LOSResponseId OUTPUT INSERT INTO LOSJsonResponse(CreatedBy,CreatedDateTime,LOSResponseId,LOSServiceMethod,JSONResponse,Status,TableName,TableMainId,ResponseName) VALUES('auto',GETDATE(),@LOSResponseId,'',@JSONResponse,'N','CustomerLoanApplication',@loanApplicationId,'feeDetails') SELECT 'S' END END