USP_DATAFORMTEMPLATE_ADD_RECURRINGGIFTINSTALLMENTSKIP

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RECURRINGGIFTINSTALLMENTSKIP
(
     @ID uniqueidentifier = null output,
     @INSTALLMENTCONTEXT nvarchar(36),
     @CHANGEAGENTID uniqueidentifier = null,
     @INSTALLMENTDATE datetime = null,   
     @INSTALLMENTS xml = null,
     @SKIPPEDINSTALLMENTS tinyint = 1,  
     @SKIPREASONID uniqueidentifier = null
)    
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

-- parse
declare @REVENUEID uniqueidentifier = cast(substring(@INSTALLMENTCONTEXT,1,36) as uniqueidentifier);    
set @INSTALLMENTDATE = cast(substring(@INSTALLMENTCONTEXT,38,10) as date);  
declare @INSTALLMENTID uniqueidentifier = null
if len(@INSTALLMENTCONTEXT) > 48
  set @INSTALLMENTID = cast(substring(@INSTALLMENTCONTEXT,49,36) as uniqueidentifier);  

-- use the date of the last (newest) future installment to be skipped to create all other future expected installments that will be skipped
declare @NEWESTINSTALLMENTDATE date;

select @NEWESTINSTALLMENTDATE = max(T.c.value('(DATE)[1]', 'date'))
from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T(c)

begin try
  -- add the installments to be skipped to the write-off table
  exec dbo.USP_RECURRINGGIFT_ADDSKIPANDWRITEOFF
        @ID output,
        @REVENUEID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        null,
        @SKIPREASONID,
        0,
        @INSTALLMENTS,
        @NEWESTINSTALLMENTDATE,
        1

end try
begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0