(

GO

STORED PROCEDURE:

    
          
CREATE PROC [dbo].[TIX_PRC_GENERATE_PAYMENTSCHEDULE_DATA]               
(                   
  @XMLParams XML,   
  @ToDate datetime,
  @HasModified char(1)
               
)                 
AS               
BEGIN                     
   SET NOCOUNT ON                     
    --Exception Handling Variable Declaration                            
     DECLARE @ErrorMessage NVARCHAR(200),                     
      @ErrorNumber INT,                     
      @ErrorSeverity INT,                     
      @ErrorState  INT,                     
      @ErrorProcedure NVARCHAR(50),                     
      @ErrorLine  INT,               
      @ErrorDesc NVARCHAR(100)                        
               
   DECLARE @XMLPayment INT               
 BEGIN TRY                     
   IF @XMLParams IS NOT NULL                 
    BEGIN --BEGIN IF               
                 
               
          SET @ErrorDesc='Error Occured While Inserting into TIX_PAYMENT_SCHEDULE FROM XML'                 
               
     INSERT INTO TIX_PAYMENT_SCHEDULE               
         (               
   OwedAmountId,               
   ProposalId,               
   BrandId,                 
   DueDate,               
   OverdueDate ,   
   CreatedDateTime,   
   LastUpdatedDateTime,                
   ExpectedAmount,     
   ActualAmountReceived,                        
   ScheduleBatchJournalId,               
   RuleId,           
   TransactionStatusId,           
   ActionId,             
   IsLate,           
   IsPaymentReceived ,         
   IsValidSchedule,  
--Added by DC : 119 
   IsCatchupBalanced,
   CatchupBalanceIdentifier,
   HasModified  
--------------------------------------------------- 
)               
       SELECT       
Main.ELEMENT.value('(OwedAmountId)[1]','int') AS OwedAmountId,    
Main.ELEMENT.value('(ProposalId)[1]','int') AS ProposalId,   
Main.ELEMENT.value('(BrandId)[1]','int') AS BrandId,   
convert(datetime,Main.ELEMENT.value('(DueDate)[1]','varchar(100)')) AS DueDate,   
convert(datetime,Main.ELEMENT.value('(OverdueDate)[1]','varchar(100)')) AS OverdueDate,   
@ToDate AS CreatedDateTime,   
@ToDate AS LastUpdatedDateTime,   
convert(decimal(18,2),Main.ELEMENT.value('(ExpectedAmount)[1]','varchar(100)')) AS ExpectedAmount,   
convert(decimal(18,2),Main.ELEMENT.value('(ActualAmountReceived)[1]','varchar(100)')) AS ActualAmountReceived,   
Main.ELEMENT.value('(ScheduleBatchJournalId)[1]','bigint') AS ScheduleBatchJournalId,   
Main.ELEMENT.value('(RuleId)[1]','int') AS RuleId,   
Main.ELEMENT.value('(TransactionStatusId)[1]','int') AS TransactionStatusId,   
Main.ELEMENT.value('(ActionId)[1]','int') AS ActionId,   
Main.ELEMENT.value('(IsLate)[1]','char(1)') AS IsLate,   
Main.ELEMENT.value('(IsPaymentReceived)[1]','char(1)') AS IsPaymentReceived,   
Main.ELEMENT.value('(IsValidSchedule)[1]','char(1)') AS IsValidSchedule   
 
--Added by DC for 119 
 
,Main.ELEMENT.value('(IsCatchupBalanced)[1]','char(1)') AS IsCatchupBalanced   
,Main.ELEMENT.value('(CatchupBalanceIdentifier)[1]','nvarchar(1000)') AS CatchupBalanceIdentifier 
,@HasModified
---------------------------------------------------------------------
 
FROM @XMLParams.nodes ('(/ROOT/DATA)') AS Main(ELEMENT)               
          
END--END IF       
   
   
END TRY--Main END TRY                 
BEGIN CATCH  --Main BEGIN CATCH                   
                     
             
                            
        SELECT @ErrorMessage = @ErrorDesc+Char(13)+Error_Message(),                   
         @ErrorSeverity = Error_Severity(),                       
         @ErrorState  = Error_State(),                        
         @ErrorNumber = Error_Number(),                       
         @ErrorProcedure = Error_Procedure(),                       
        @ErrorLine  = Error_Line()                       
       RAISERROR(                       
           @ErrorMessage,                       
           @ErrorSeverity,                       
           @ErrorState,                       
           @ErrorNumber,                       
           @ErrorProcedure,                       
           @ErrorLine                       
         )                        
      END CATCH  --Main END CATCH                    
END --Main END  

STOREDPROCEDURE 2

CREATE PROCEDURE [dbo].[TIX_PRC_GET_PAYMENTSCHEDULE_SCHEDULE_FOR_DATE_RANGE]   
(   
   
  @ToDate datetime,
  @IsValidSchedule char(1) 
   
)   
AS 
BEGIN     
SET NOCOUNT ON     
     
 --Execption Handling Variable Declaration     
 DECLARE @ErrorMessage NVARCHAR(200),     
   @ErrorNumber INT,     
   @ErrorSeverity INT,     
   @ErrorState INT,     
   @ErrorProcedure NVARCHAR(50),     
   @ErrorLine INT, 
   @ErrorDesc NVARCHAR(100)   
          
      
 BEGIN TRY --Exception Handling     
  SET @ErrorDesc='Error Occured while fetching records from TIX_PAYMENT_SCHEDULE' 
  
  
 SELECT  
 PaymentScheduleId,
 OwedAmountId,
 ProposalId,
 DueDate,
 OverdueDate,
 ExpectedAmount,
 TransactionStatusId,
 IsPaymentReceived,
 IsLate,
 ActionId,
 ActualAmountReceived,
 IsValidSchedule,
 BrandId,
 CaseScheduleId,
 ReasonId,
 Comments,
 NoOfDays,
 ActionDate,
 IsCatchupBalanced,
 CatchupBalanceIdentifier,
 HasModified
 from TIX_PAYMENT_SCHEDULE with (nolock) 
   WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule
    
    
   SELECT DISTINCT OwedAmountId,proposalId,brandId from TIX_PAYMENT_SCHEDULE with (nolock) WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule Order By OwedAmountId,ProposalId,BrandId asc 
   SELECT DISTINCT ProposalId from TIX_PAYMENT_SCHEDULE with (nolock) WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule Order By ProposalId asc   
  
    
END TRY     
BEGIN CATCH     
 SELECT @ErrorMessage=@ErrorDesc+CHAR(13)+ Error_Message(),     
     @ErrorNumber=Error_Number(),     
     @ErrorState=Error_State(),     
     @ErrorProcedure=Error_Procedure(),     
     @ErrorLine=Error_Line(),     
     @ErrorSeverity=Error_Severity()     
        
 RAISERROR(     
   @ErrorMessage,     
   @ErrorSeverity,     
   @ErrorState,     
   @ErrorNumber,     
   @ErrorProcedure,     
   @ErrorLine    
     )       
      
END CATCH       
      
END 

-- Dropping index before insert is it feasible. during productional hour.

Thanks & Regards

Rajesh Varma