IF OBJECT_ID(N'KY_NETRevenueInsertintoDiary',N'P')IS NOT NULL
	DROP PROCEDURE KY_NETRevenueInsertintoDiary
GO
CREATE PROCEDURE KY_NETRevenueInsertintoDiary

	(@CASECODE          VARCHAR(20),
    --@FNCODE             VARCHAR(10),
    --@TEAMCODE           VARCHAR(10),
    @value				VARCHAR(100),
    @principal			VARCHAR(100),
    @intrest			VARCHAR(100),
    @From				VARCHAR(100)='',
    @DebtDescription	VARCHAR(MAX))
 AS
 
	/*
		KY_NETRevenueInsertintoDiary
		
		This Procedure is used to insert dairy action when ever changes made in debt details or while allocate payment in debt ledger.
		
		02FEB2016	Magesh	Added 'NA' to the action status column in diary
		10FEB2016   Magesh	Added Debtledger post payment description to Text2 column in diary table 
		16FEB2016	Magesh	Added values for Priority,Publish ,ActionType ,ProcessType ,DueDate ,OrginalActionID  
	*/
BEGIN

	DECLARE @ACTIONID	AS BIGINT
	DECLARE @Subject	AS VARCHAR(500)
	DECLARE @ACTIONCODE AS VARCHAR(15)
	DECLARE @FNCODE     AS VARCHAR(10)
	DECLARE @TEAMCODE   AS VARCHAR(10)
	
	EXEC @ACTIONID = [dbo].[ky_NETGetNextActionID]
	
	DECLARE @TIMECONVERT INT
    SET @TIMECONVERT = dbo.ky_ConvertTimeToClarion(GETDATE())
    SET @principal =(SELECT CAST(@principal AS NUMERIC(18,2)))
    SET @intrest =(SELECT CAST(@intrest AS NUMERIC(18,2)))
    
    SELECT 
		@TEAMCODE= ISNULL(H.TEAM,'') ,
		@FNCODE =ISNULL (H.CODE,'')
	FROM 
		matters M
			inner join
		Handlers H
	on
		 M.FECode =H.CODE
	WHERE M.Code =@CASECODE

        
    DECLARE @TEXT1 AS VARCHAR(5000)
    
    IF @From ='AddDebtDetails'
		BEGIN

			SET @TEXT1 = 'Manual Amendment.'+CHAR(13)+'Sequence No: '+@value + CHAR(13)+'New Tax Amount: ' +CONVERT(VARCHAR(100),@principal) +CHAR(13)+'New Interest Amount: '+CONVERT(VARCHAR(100),@intrest )
			SET @ACTIONCODE ='R004'
			SET @Subject ='Manual Debt Breakdown Amendment'
		END
	ELSE
		BEGIN
			SELECT @TEXT1 = CASE WHEN @From='Direct Payment'  THEN 'Direct Payment'+CHAR(13)
								 WHEN @From ='Credit' THEN 'Credit'+CHAR(13)
								 when @From ='Set off' THEN 'Set off'+CHAR(13)
								 ELSE 'Payment Received From Tax Payer'+CHAR(13)
								 END
									
			SET @TEXT1 = @TEXT1 +'Amount:'+@value + CHAR(13)+'Tax:' +CONVERT(VARCHAR(100),@principal) +CHAR(13)+'Interest:'+CONVERT(VARCHAR(100),@intrest )
			SET @ACTIONCODE ='O01'
			SET @Subject ='Payment received'
		END
    
    DECLARE @DisplaytextValue VARCHAR(220)

	SET @DisplaytextValue = [dbo].[ky_RemoveSpuriousWhitespace2](ISNULL(CONVERT(VARCHAR(MAX), @TEXT1), ''), 200)
    
	INSERT
      INTO [dbo].[diary]
          ([CASECODE],
           [DATE],
           [STATUS],
           [ACTIONCODE],
           [ACTIONSTATUS],
           [ACTIONTYPE],
           [PROCESSTYPE],
           [FNCODE],
           [TEAMCODE],
           [TEXT1],
           [TEXT2],
           [DELEGATEDFNR],
           [DELEGATEDDATE],
           [DELEGATEDBACKDATE],
           [DEFERRED],
           [DUEDATE],
           [PUBLISH],
           [DYSTARTTIME],
           [DYENDTIME],
           [DURATION],
           [ACTIONID],
           [ORGINALACTIONID],
           [PRIORITY],
           [HIGHLIGHTED],
           [MILESTEONETYPE],
           [ATTACHMENTS],
           [PROCESSSTATUS],
           [WORKPROCESS],
           [BILLABLE],
           [BILLDESCRIPTION],
           [EMAILADDRESS],
           [ADDRESSTO],
           [CCTo],
           [BCCTo],
           [EMAIL],
           [SUBJECT],
           [DELEGATIONSTATUS],
           [DRAFTBILLNO],
           [CHEQUEREQNO],
           [TxmSent],
           [Location],
           [HearingType],
           [ForCopy],
           [TxmDate],
           [TxmSeqNo],
           [DisplayText],
           [Flag])
    VALUES(@CASECODE,
           GETDATE(),
           1,
           @ACTIONCODE,
           'NA',
           'A',
           'I',
           @FNCODE,
           @TEAMCODE,
           @TEXT1 ,
           @DebtDescription,
           null,
           null,
           null,
           null,
           GETDATE(),
           'N',
           @TIMECONVERT,
           @TIMECONVERT,
           0,
           @ACTIONID,
           @ACTIONID,
           'N',
           null,
           null,
           null,
           0,
           0,
           0,
           null,
           null,
           null,
           null,
           null,
           null,
           @Subject,
           0,
           0,
           0,
           0,
           null,
           'Not Applicable',
           null,
           null,
           0,
          @DisplaytextValue,
           0)
    INSERT
              INTO [dbo].[DiaryDelegations]
                  ([ACTIONID],
                   [HANDLER],
                   [TEAM],
                   [DATE],
                   [TIME],
                   [DATER],
                   [TIMER],
                   [DUEDATE],
                   [DUETIME],
                   [REVIEW],
                   [STATUS],
                   [OWNER],
                   [DELEGATE],
                   [DELEGATESTATUS],
                   [ActionType],
                   [FromHandler],
                   [ReturnedBy],
                   [DelType])
            VALUES(@ACTIONID,
                   @FNCODE,
                   @TEAMCODE,
                   GETDATE(),
                   @TIMECONVERT,
                   null,
				   @TIMECONVERT,
                   GETDATE(),
                   @TIMECONVERT,
                   null,
                   1,
                   'Y',
                   @FNCODE,
                   0,
       null,
                   @FNCODE,
                   null,
                   'Created')
END
GO
IF OBJECT_ID(N'Ky_NETDebtLedgerUpdateDeletePayment', N'P') IS NOT NULL
DROP PROCEDURE Ky_NETDebtLedgerUpdateDeletePayment
GO
/*    
Created By : Magesh Kumar    
Date	   : 19-MAR-2014    
Description: Created to Update Payment Type details FROM debtcostingledger table    
     
Modification History    
-----------------------------------------------------    
Date  | Modified By   | Description    
-----------------------------------------------------    
28JAN2016	Magesh	Added revenue details 
              
*/
CREATE PROCEDURE [dbo].[Ky_NETDebtLedgerUpdateDeletePayment] @postref INT
    ,@Mattercode			VARCHAR(15)
    ,@date					DATETIME
    ,@reference				VARCHAR(20)
    ,@type					VARCHAR(20)
    ,@Method				VARCHAR(40)
    ,@value					DECIMAL(18, 2)
    ,@principalPaid			DECIMAL(18, 2)
    ,@costpaid				DECIMAL(18, 2)
    ,@interestpaid			DECIMAL(18, 2)
    ,@pstatus				CHAR(1)
    ,@Cstatus				CHAR(1)
    ,@Istatus				CHAR(1)
    ,@description			VARCHAR(200)
    ,@enteredby				VARCHAR(20)
    ,@feeearner				VARCHAR(10)
    ,@time					INT
    ,@rate					DECIMAL(18, 2)
    ,@chargevalue			DECIMAL(18, 2)
    ,@timeorcharge			CHAR(1)
    ,@bounce				VARCHAR(1)
    ,@remittanceno			INT
    ,@DATEREMITTED			DATETIME
    ,@HELDDATE				DATETIME
    ,@isRevenue				BIT
    ,@EffectiveDate			DATETIME
    ,@Outcome				INT	
AS
/*
Last Updated: 12 FEB 2015	Arun- Change NVARCHAR to VARCHAR
			  04JAN2016		Magesh - added query to add hold days and task to the revenue payment
			  10FEB2016		Magesh - Added Effecive date and xnld value to debtcosting table 
			  12FEB2016		Magesh - Remove the update to reset the collecting balance and debt value from the matter.
*/
BEGIN
    --Update into debtcosting table              
    DECLARE @amt				DECIMAL(18, 2)
    DECLARE @Bounceamt			DECIMAL(18, 2)
    DECLARE @mattercharge		DECIMAL(18, 2)
	DECLARE @CARRYFORWARD		DECIMAL(18,2)
    DECLARE @TASK				VARCHAR(100)
    DECLARE @HOLDDAYS			INT
    DECLARE @COMISSIONAMT		DECIMAL(18,2)
    DECLARE @Xnld				INT
    DECLARE @PrincipalInterest	DECIMAL(18,2)
    
    SET @COMISSIONAMT =(SELECT (@principalPaid  + @interestpaid) )
    
    SET @HOLDDAYS =(SELECT ISNULL(KEYVALUE,0) FROM Settings WHERE UPPER(KeyName) ='REV_HOLDDATE')
    
    SET @mattercharge = @chargevalue
        
    SET @amt =(SELECT ISNULL(VALUE,0) FROM DebtCostingLedger WHERE postingref = @postref)
    
    SET @PrincipalInterest =(SELECT ISNULL(premiumpaid,0)+ISNULL(INTERESTPAID,0) FROM DebtCostingLedger WHERE postingref = @postref)

	IF @isRevenue =1
		BEGIN
			IF @HOLDDAYS =0
			BEGIN
				SELECT @HELDDATE =DATEADD (DAY,21,GETDATE ())
			END
			ELSE
			BEGIN
				SELECT @HELDDATE=CASE WHEN DATEPART(DW,DATEADD (DAY,@HOLDDAYS,GETDATE ()))=1 THEN DATEADD (DAY,@HOLDDAYS+1,GETDATE ())
								 WHEN DATEPART(DW,DATEADD (DAY,@HOLDDAYS,GETDATE ()))=7 THEN DATEADD (DAY,@HOLDDAYS+2,GETDATE ())
								 ELSE DATEADD (DAY,@HOLDDAYS,GETDATE ()) END
			END
			SELECT @TASK =ISNULL(RTRIM(LTRIM(ChargeArrangement)),'')  FROM matters WHERE Code =@Mattercode 
			SET @Xnld =-1
		END
	ELSE
		BEGIN
			SET @TASK=''
			SET @Xnld =NULL
			SET @EffectiveDate =NULL
		END

   
	  IF @value >@amt 
		BEGIN
			SET @CARRYFORWARD =@value -@amt 
			SET @value =@amt +@CARRYFORWARD 
		END
	  ELSE
		BEGIN
			SET @CARRYFORWARD  =@amt -@value 
			SET @value =@amt -@CARRYFORWARD 
		END
		
	IF @bounce='Y'
		BEGIN
			SET @Bounceamt =0
		END
	ELSE
		BEGIN
			SET @Bounceamt =@value 
		END
			


    UPDATE 
        DebtCostingLedger
    SET    
        [DATE] = @date,
        [reference] = @reference,
        [description] = @description,
        [type] = @type,
        --value = @amt,
        Value=@Bounceamt,
        bounced = @value,
        premiumpaid = @principalpaid,
        premiumstatus = @pstatus,
        interestpaid = @interestpaid,
        intereststatus = @istatus,
        costspaid = @costpaid,
        coststatus = @Cstatus,
        paymentmethod = @method,
        HELDDATE = @HELDDATE,
        DATEREMITTED = @DATEREMITTED,
        REMITTANCENO = @remittanceno,
        EffectiveDate=@EffectiveDate,
        XnId =@Xnld,
        Outcome =@Outcome   
    WHERE 
        postingref = @postref

	--Update debtallocation
	
		UPDATE 
			DebtAllocations 
		SET
			[Type] = @type,
			ProcessDate =@HELDDATE ,
			[Sent] =0,
			Sequence =0
			--[SendDate] =GETDATE()  
		WHERE
			PaymentId =@postref 


    --update matter table              
    BEGIN
    DECLARE @collectvalue DECIMAL(18, 2)
    DECLARE @Debtvalue DECIMAL(18, 2)

    SET 
        @collectvalue = (SELECT
								ISNULL(CollectingBalance,0)-@PrincipalInterest
							FROM 
								matters
							WHERE 
								code = @mattercode)
        
   SET @collectvalue=@collectvalue +@principalPaid +@interestpaid  

	 SET 
        @Debtvalue = (SELECT
								ISNULL(DebtCollected,0)-@amt 
							FROM 
								matters
							WHERE 
								code = @mattercode)
        
   SET @Debtvalue=@Debtvalue +@value 

 

    IF (@bounce = 'Y') 
		BEGIN
      			UPDATE 
				matters
			SET    
				DebtCollected = (ISNULL(@Debtvalue, 0) - @value),  
				CollectingBalance = (ISNULL(@collectvalue, 0) - (@principalPaid +@interestpaid )),  
				ChargeBal = ISNULL(ChargeBal, 0) - @mattercharge 
			WHERE 
				code = @mattercode

			DELETE FROM 
				TimeEntry
			WHERE 
				TRACKREF = @postref
		END 
    ELSE 
		BEGIN
		IF @isRevenue =1
			BEGIN
				 IF @type='Set off'
					BEGIN
						UPDATE  M
						SET 
							--CollectingBalance=ISNULL(@collectvalue,0),
							DebtCollected =ISNULL(@Debtvalue ,0),
							ChargeBal =  ISNULL(ChargeBal, 0) + @mattercharge
						FROM 
						Matters M
						WHERE 
						M.Code =@Mattercode
					END
				ELSE
					BEGIN
						UPDATE  M
						SET CollectingBalance=ISNULL(@collectvalue,0),
							DebtCollected =ISNULL(@Debtvalue ,0),
							ChargeBal =  ISNULL(ChargeBal, 0) + @mattercharge
						FROM 
						Matters M
						WHERE 
						M.Code =@Mattercode
					END 
			END
		ELSE
			BEGIN
				UPDATE  M
						SET CollectingBalance=ISNULL(@collectvalue,0),
							DebtCollected =ISNULL(@Debtvalue ,0),
							ChargeBal =  ISNULL(ChargeBal, 0) + @mattercharge
						FROM 
						Matters M
						WHERE 
						M.Code =@Mattercode
			END
       
			 

	 BEGIN
		IF @isRevenue =1
		BEGIN
			SET @description= ('Commission on ' + CONVERT(VARCHAR(20),@COMISSIONAMT))
		END
        ---Update into timeentry       
        IF @mattercharge <> 0 
        BEGIN
            IF EXISTS (SELECT TOP 1 1  FROM TimeEntry WHERE TRACKREF = @postref) 
            BEGIN
                UPDATE 
                    TimeEntry
                SET    
                    [DATE] = @date,
                    COMMENT = @description,
                    CHARGE = @mattercharge
                WHERE 
                    TRACKREF = @postref
            END
            ELSE 
            BEGIN
                INSERT INTO 
                    TimeEntry (MATTER
                    , FEEEARN
                    , [DATE]
                    , COMMENT
                    , [TIME]
                    , RATE
                    , CHARGE
                    , TIMEORCHARGE
                    , TRACKREF
                    , DOCKET
                    , REC_IRR
                    ,Task)
                VALUES 
                    (@mattercode, @feeearner, @date, 'Commission on ' + CONVERT(VARCHAR(20),@COMISSIONAMT), @time, @rate, @chargevalue, 'C', @postref, @postref, 'C',@TASK)
            END
        END
    END
    END
    END
END
GO
IF OBJECT_ID(N'[Ky_NETDebtLedgerDeleteCostingLedger]',N'P')IS NOT NULL
	DROP PROCEDURE [Ky_NETDebtLedgerDeleteCostingLedger]
GO 
-- =============================================
-- Author:		Magesh Kumar
-- Create date: 19-MAR-2014
-- Description:	Created to Delete Costing Ledger table based on the type
-- Last Modification :
--				23JUN2015	Sridharen	KEYD - 2332 - Use of LTRIM(RTRIM(
--				28JAN2016	Magesh		Added debtallocation and debtbreakdown while deleting payment
--				01FEB2016	Magesh		Changed the Update query of the debtbreakDown to collect the OS amt correctly. 
--				08FEB2016	Magesh		Added posting ref condition to the update query of debtbreakdown
-- =============================================
CREATE PROCEDURE [Ky_NETDebtLedgerDeleteCostingLedger] 
	@postref	INT,
	@IsRevenue	BIT
AS
BEGIN
	DECLARE @type VARCHAR(20)
	SET @type=(SELECT Upper(RTRIM(ISNULL([type],''))) FROM DebtCostingLedger WHERE POSTINGREF=@postref)
	
	IF @type='INTEREST'
	BEGIN
		UPDATE 
			matters 
		SET 
			DebtInterest=ISNULL(DebtInterest,0)-(SELECT ISNULL(interest,0) FROM DebtCostingLedger WHERE POSTINGREF=@postref) 
		WHERE 
			Code=(SELECT RTRIM(MATTERCODE) FROM DebtCostingLedger WHERE POSTINGREF=@postref)
	END
	ELSE IF @type='COSTS'
		BEGIN
			UPDATE 
				matters 
			SET 
				RecoverableCosts=ISNULL(RecoverableCosts,0)-(SELECT ISNULL(COSTS,0) FROM DebtCostingLedger WHERE POSTINGREF=@postref) 
			WHERE 
				Code=(SELECT RTRIM(MATTERCODE) FROM DebtCostingLedger WHERE POSTINGREF=@postref)
		END
	ELSE
		BEGIN
		--update Matter Table
			DECLARE @RESULT AS INT
			IF @IsRevenue =1
				BEGIN
					IF @type ='Set off'
						BEGIN
							SET @RESULT =0
						END
					ELSE
						BEGIN
							SET @RESULT =1
						END
				END
			ELSE
				BEGIN
					SET @RESULT =1
				END
			IF @RESULT =0
				BEGIN
					UPDATE 
						matters 
					SET 
						DebtCollected=ISNULL(DebtCollected,0)-(SELECT ISNULL(Value,0) FROM DebtCostingLedger WHERE POSTINGREF=@postref)
						--CollectingBalance=ISNULL(CollectingBalance,0)-(SELECT ISNULL(PREMIUMPAID,0)+ISNULL(INTERESTPAID,0)  FROM DebtCostingLedger WHERE POSTINGREF=@postref) 
					WHERE 
						Code=(SELECT RTRIM(MATTERCODE) FROM DebtCostingLedger WHERE POSTINGREF=@postref);
				END
			ELSE
				BEGIN
					UPDATE 
						matters 
					SET 
						DebtCollected=ISNULL(DebtCollected,0)-(SELECT ISNULL(Value,0) FROM DebtCostingLedger WHERE POSTINGREF=@postref),
						CollectingBalance=ISNULL(CollectingBalance,0)-(SELECT ISNULL(PREMIUMPAID,0)+ISNULL(INTERESTPAID,0)  FROM DebtCostingLedger WHERE POSTINGREF=@postref) 
					WHERE 
						Code=(SELECT RTRIM(MATTERCODE) FROM DebtCostingLedger WHERE POSTINGREF=@postref);
				END
			
		--Delete Data FROM Time Entry
			DELETE FROM 
				TimeEntry 
			WHERE 
				DOCKET=@postref;	
				
		--Revenue update 			
			UPDATE 
				debtbreakdown 
			SET 
				debtbreakdown.ostaxamount = debtbreakdown.ostaxamount + ISNULL((select SUM(TaxAmount) from DebtAllocations where BreakdownId = debtbreakdown.RecordId AND PaymentId=@postref ),0), 
				debtbreakdown.osinterestamt = debtbreakdown.osinterestamt + ISNULL((select SUM(InterestAmount) from DebtAllocations where BreakdownId = debtbreakdown.RecordId AND PaymentId=@postref),0) 
			WHERE 
				MatterCode = (SELECT RTRIM(MATTERCODE) FROM DebtCostingLedger WHERE POSTINGREF=@postref)  				  
				
			DELETE 
				DebtAllocations 
			WHERE 
				PaymentId = @POSTREF
			
		END

	DELETE FROM 
		debtcostingledger 
	WHERE 
		postingref=@postref
END
GO
IF OBJECT_ID(N'[Ky_NetDebtSpGetCostingLedger]',N'P')IS NOT NULL
	DROP PROCEDURE [Ky_NetDebtSpGetCostingLedger]
GO 
-- =============================================      
-- [Ky_NetDebtSpGetCostingLedger]
-- Author:  Magesh Kumar      
-- Create date: 17-MAR-2014      
-- Description: Get Details FROM Costing Ledger   

--This Procedure has been checked for SQL statement general standards/optimization on 24/11/2014 by Praveen Yadav.P     
-- 23JUN2015	Sridharen	KEYD - 2332 - Use of LTRIM(RTRIM(        
-- =============================================      
CREATE PROCEDURE [Ky_NetDebtSpGetCostingLedger]   
 @MatterCode VARCHAR(20)      
AS      
BEGIN      
	SELECT       
		Mattercode,      
		"Date",      
		ISNULL(Description,'') 'Description',          
		PAYMENTMETHOD 'Method',      
		ISNULL(Type,'') 'Type',      
		VALUE 'Payment',      
		PREMIUMPAID 'Principal Paid',      
		INTERESTPAID 'Interest Paid',      
		COSTSPAID 'Cost Paid',      
		INTEREST 'Interest Due',      
		COSTS 'Cost Due',      
		REMITTANCENO 'Remitted',      
		HELDDATE 'Held Date',      
		ISNULL(POSTINGREF,0) 'Posting Ref',      
		Reference,    
		BOUNCED 'Bounced',  
		CASE  
			WHEN ISNULL(Value,0)=0 AND ISNULL(BOUNCED,0)<>0 
			THEN 'Y'  
		ELSE 
			'N'  
		END AS 'BouncedStatus'                  
	FROM       
		DebtCostingLedger        
	WHERE       
		UPPER(RTRIM(MATTERCODE)) = UPPER(@MatterCode)      
	ORDER BY       
	   CONVERT(DATE,"DATE", 112) DESC
       ,POSTINGREF ASC      
END
GO
IF OBJECT_ID(N'[Ky_NETDebtGetDebtDetails]',N'P')IS NOT NULL
	DROP PROCEDURE [Ky_NETDebtGetDebtDetails]
GO 
-- =============================================
-- Author:		Magesh Kumar
-- Create date: 17-MAR-2014
-- Description:	Get details about Debt for a particular Matter 
-- Last Modification :
--				23JUN2015	Sridharen	KEYD - 2332 - Use of LTRIM(RTRIM(
--				04JAN2016   Magesh		Getting orignal debt from UDF
--				10FEB2016	magesh		Added youref,user3,recordno,status,commnent from matter and casemaster table	
--				16FEB2016	Magesh		Changed inner join to left join to get value more precisely.							
-- =============================================
CREATE PROCEDURE [Ky_NETDebtGetDebtDetails] 
	@mattercode VARCHAR(20)
AS
BEGIN
	SELECT 
		ISNULL(OriginalDebt,0.00)							AS 'OriginalDebt',
		ISNULL(DebtInterest,0.00)							AS 'DebtInterest',
		ISNULL(RecoverableCosts,0.00)						AS 'RecoverableCosts',
		ISNULL(DebtCollected,0.00)							AS 'DebtCollected',
		ISNULL(CA.[DESCRIPTION] ,'')						AS 'ChargeArrangement',
		ISNULL(CONVERT(VARCHAR(11),[Started],112),'')		AS 'Started',
		ISNULL(LTRIM(RTRIM([YourRef])),'')					AS 'YourRef',
		ISNULL(LTRIM(RTRIM([User3])),'')					AS 'User3',
		ISNULL(LTRIM(RTRIM(CM.CSPLAINTNO)),'')				AS 'RecordNo',
		ISNULL(LTRIM(RTRIM(SC.[DESCRIPTION])),'')			AS 'Status',
		ISNULL(LTRIM(RTRIM([Comment])),'')					AS 'Comment'
	FROM 
		matters M
			LEFT JOIN
		CaseMaster CM
	ON	
		M.Code =CM.CSCODE
			LEFT JOIN
		ChargeArrangements CA
	ON
		CA.CODE =M.ChargeArrangement  
			LEFT JOIN
		StatusCodes SC
	ON
		SC.CODE =M.[Status] 
	WHERE 
		RTRIM(M.Code)  =  @mattercode
		
	SELECT 
		ISNULL(RTRIM(LTRIM(Text1)),'0.00') 
	FROM 
		CaseUDFAnswers 
	WHERE 
		CaseCode = @mattercode 
		AND 
		UDFName = 'OriginalDebt' 
		
	SELECT
		ISNULL(RTRIM(LTRIM(USERPROMPT3)),'') AS USERPROMPT3
	FROM
		[Control] 
END
GO
IF OBJECT_ID(N'Ky_NETDebtLedgerGetCostandPayment',N'P') IS NOT NULL
	DROP PROCEDURE Ky_NETDebtLedgerGetCostandPayment
GO

CREATE PROCEDURE [Ky_NETDebtLedgerGetCostandPayment]             
@postref INT,            
@type VARCHAR(20)            
AS   
/*    
Created By :  Praveen Yadav   
Date	   : 25MAR2015 2:37 PM    
Description:     
     
Modification History    
-----------------------------------------------------    
Date  | Modified By   | Description    
----------------------------------------------------- 
12FEB2016	Magesh		Added effective date to the query
                 
*/         
BEGIN            
	IF @type='Costs'            
	BEGIN            
		SELECT         
			[Date],        
			ISNULL(COSTS,0) AS 'COST',        
			[DESCRIPTION]        
		FROM 
			DebtCostingLedger 
		WHERE 
			POSTINGREF = @postref            
	END            
	ELSE IF @type='Payment'            
	BEGIN            
		SELECT         
			[DATE],        
			ISNULL(REFERENCE,'') AS REFERENCE,        
			[TYPE],        
			PAYMENTMETHOD,        
			ISNULL(Value,0) AS 'Value',        
			ISNULL(PREMIUMPAID,0) AS 'PREMIUMPAID',        
			ISNULL(COSTSPAID,0) AS 'COSTSPAID',        
			ISNULL(INTERESTPAID,0) AS 'INTERESTPAID',        
			ISNULL(PREMIUMSTATUS,'') AS 'PREMIUMSTATUS',        
			ISNULL(COSTSTATUS,'') AS 'COSTSTATUS',        
			ISNULL(INTERESTSTATUS,'') AS 'INTERESTSTATUS',        
			ISNULL([DESCRIPTION],'') AS 'DESCRIPTION',        
			ISNULL(BOUNCED,0) AS 'BOUNCED',        
			ISNULL(Convert(VARCHAR(20),Helddate),'') AS 'Helddate',              
			ISNULL(Convert(VARCHAR(20),DATEREMITTED),'') AS 'DATEREMITTED',      
			ISNULL(ENTRYDATE,'') AS 'ENTRYDATE',        
			ISNULL(ENTEREDBY,'') AS 'ENTEREDBY',
			ISNULL(REMITTANCENO,'') AS 'Remittanceno',
			EffectiveDate AS 'EffectiveDate'        
		FROM 
			DebtCostingLedger 
		WHERE 
			POSTINGREF=@postref            
	END             
END
GO