USP_RECURRINGGIFT_SETNEXTTRANSACTIONDATE

CREATE procedure dbo.USP_RECURRINGGIFT_SETNEXTTRANSACTIONDATE 
(
    @REVENUEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
begin
    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @CHANGEDATE is null
        set @CHANGEDATE = getdate();

    declare @NEXTTRANSACTIONDATE date;

    select 
        @NEXTTRANSACTIONDATE = RECURRINGGIFTINSTALLMENT.DATE
    from 
        dbo.RECURRINGGIFTINSTALLMENT
    where 
        RECURRINGGIFTINSTALLMENT.ID = dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENT(@REVENUEID,null);

    if @NEXTTRANSACTIONDATE is not null
        update dbo.REVENUESCHEDULE
        set 
            REVENUESCHEDULE.NEXTTRANSACTIONDATE = @NEXTTRANSACTIONDATE,
            REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
            REVENUESCHEDULE.DATECHANGED = @CHANGEDATE,
            REVENUESCHEDULE.STATUSCODE = case STATUSCODE when 3 then 0 else STATUSCODE end --if completed then reset to active, otherwise keep at current status

        where 
            REVENUESCHEDULE.ID = @REVENUEID
            and (REVENUESCHEDULE.NEXTTRANSACTIONDATE <> @NEXTTRANSACTIONDATE or REVENUESCHEDULE.NEXTTRANSACTIONDATE is null)
            and REVENUESCHEDULE.STATUSCODE in (0,3,5);
end