USP_RECURRINGGIFT_DELETEFUTUREINSTALLMENTS

CREATE procedure dbo.USP_RECURRINGGIFT_DELETEFUTUREINSTALLMENTS (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as
begin
    declare @CURRENTDATE date
    set @CURRENTDATE = getdate()

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

    --Cache and reset CONTEXT INFO

    declare @contextCache varbinary(128);
    set @contextCache = CONTEXT_INFO();
    set CONTEXT_INFO @CHANGEAGENTID;

    declare @LASTACTIVITYDATE date;

    select @LASTACTIVITYDATE = max(DATE)
    from dbo.RECURRINGGIFTINSTALLMENT
    left join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID
    left join dbo.RECURRINGGIFTINSTALLMENTWRITEOFF on RECURRINGGIFTINSTALLMENTWRITEOFF.RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID
    where RECURRINGGIFTINSTALLMENT.REVENUEID = @ID
    and (RECURRINGGIFTINSTALLMENTPAYMENT.ID is not null or RECURRINGGIFTINSTALLMENTWRITEOFF.ID is not null)

    if @LASTACTIVITYDATE is null or @LASTACTIVITYDATE < @CURRENTDATE
    begin
        -- no future activity, leave only one future installment

        delete from dbo.RECURRINGGIFTINSTALLMENT
        where REVENUEID = @ID
        and DATE > (select min(DATE)
                    from dbo.RECURRINGGIFTINSTALLMENT
                     where REVENUEID = @ID
                    and DATE >= @CURRENTDATE);
    end
    else
    begin
        declare @NEXTACTIVITYDATE date;

        select @NEXTACTIVITYDATE = min(DATE)
        from dbo.RECURRINGGIFTINSTALLMENT
        where REVENUEID = @ID
        and dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(ID) > 0
        and DATE > @CURRENTDATE;

        if @NEXTACTIVITYDATE < @LASTACTIVITYDATE
            -- delete all installments after the last activity

            delete from dbo.RECURRINGGIFTINSTALLMENT
            where REVENUEID = @ID
            and DATE > @LASTACTIVITYDATE;
        else
            -- delete all installments after the next one w/ a balance

            delete from dbo.RECURRINGGIFTINSTALLMENT
            where REVENUEID = @ID
            and DATE > @NEXTACTIVITYDATE;
    end

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;
end