USP_RECURRINGGIFT_EDITSTATUS

            CREATE procedure dbo.USP_RECURRINGGIFT_EDITSTATUS
            (
                @ID uniqueidentifier,
                @STATUSCODE tinyint,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @REASONCODEID uniqueidentifier = null,
                @INSTALLMENTS xml = null,
                @SKIPPEDINSTALLMENTID uniqueidentifier = null,
                @STATUSCHANGETYPECODE tinyint = 0
            )
            as 
            set nocount on;

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

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

            begin try
              -- skip functionality when status is held

              if @STATUSCODE = 1
              begin
                -- If the old status was Canceled or Terminated, update the status to Held so that the skip USP will work properly.

                -- We need to pass through the original status in order to log the amendment.

                declare @ORIGINALSTATUSCODE tinyint;

                select @ORIGINALSTATUSCODE = STATUSCODE
                from dbo.REVENUESCHEDULE
                where ID = @ID;

                if @ORIGINALSTATUSCODE in(2,3)
                  update dbo.REVENUESCHEDULE
                  set STATUSCODE = @STATUSCODE,
                      ENDDATE = null,
                      NEXTTRANSACTIONDATE = null,   -- This will get reset if needed by USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE

                      CHANGEDBYID = @CHANGEAGENTID,
                      DATECHANGED = @CHANGEDATE
                  from dbo.REVENUESCHEDULE
                  where ID = @ID;
                else
                  set @ORIGINALSTATUSCODE = null;

                --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)    

                exec dbo.USP_RECURRINGGIFT_ADDSKIPANDWRITEOFF
                    @REVENUEID = @ID,
                    @CHANGEAGENTID = @CHANGEAGENTID,
                    @DATE = @CHANGEDATE,
                    @SKIPREASONID = @REASONCODEID,
                    @INSTALLMENTS = @INSTALLMENTS,
                    @ASOFDATE = @NEWESTINSTALLMENTDATE,
                    @TYPECODE = 1,
                    @ORIGINALSTATUSCODE = @ORIGINALSTATUSCODE;
              end

              else

              begin
                -- if setting the status to Active or Lapsed and skips exist, undo those skips

                if @STATUSCODE in(0,5) and @SKIPPEDINSTALLMENTID is not null
                begin
                  declare @INSTALLMENTDATE date;

                  select @INSTALLMENTDATE = DATE
                  from dbo.RECURRINGGIFTINSTALLMENT
                  where ID = @SKIPPEDINSTALLMENTID;

                  exec dbo.USP_RECURRINGGIFTINSTALLMENTUNDOSKIP @ID, @INSTALLMENTDATE, @SKIPPEDINSTALLMENTID, 0, @CHANGEAGENTID;
                end

                declare @AMOUNT money;
                declare @BASECURRENCYID uniqueidentifier;
                declare @ORGANIZATIONAMOUNT money;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @TRANSACTIONAMOUNT money;
                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASEEXCHANGERATEID uniqueidentifier;
                declare @PREVIOUSSTATUSCODE tinyint;

                --Set previous status to be used in amendments table

                select 
                  @PREVIOUSSTATUSCODE = STATUSCODE
                from dbo.REVENUESCHEDULE
                where ID = @ID; 

                if not exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @ID)
                begin
                    exec dbo.USP_RECURRINGGIFT_FIXMISSINGINSTALLMENTSCHEDULE @ID, @CHANGEAGENTID, @CHANGEDATE
                end

                -- on change to inactive, bring the installments up to date,

                -- but then get rid of any future installments so they won't

                -- be counted as expected/past due

                if @STATUSCODE not in (0,1,5)
                begin
                    select 
                        @AMOUNT = AMOUNT,
                        @BASECURRENCYID = BASECURRENCYID,
                        @ORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT,
                        @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID,
                        @TRANSACTIONAMOUNT = TRANSACTIONAMOUNT,
                        @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
                        @BASEEXCHANGERATEID = BASEEXCHANGERATEID
                    from dbo.REVENUE
                    where ID = @ID;

                    exec dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS 
                        @ID = @ID,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CREATIONDATE = @CHANGEDATE,
                        @AMOUNT = @AMOUNT,
                        @BASECURRENCYID = @BASECURRENCYID,
                        @ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
                        @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                        @TRANSACTIONAMOUNT = @TRANSACTIONAMOUNT,
                        @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                        @BASEEXCHANGERATEID = @BASEEXCHANGERATEID; 

                    -- Only delete future installments if the status is not changing to Held. In that case we want the a next expected installment to exist

                    if @STATUSCODE <> 1
                    begin              
                      --Cache CONTEXT INFO

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

                      -- delete future installments

                      delete from dbo.RECURRINGGIFTINSTALLMENT
                      where REVENUEID = @ID
                      and DATE > @CHANGEDATE
                      and not exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENTPAYMENT where RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID)
                      and not exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF where RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID);

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

                -- only update status if the recurring gift is not a sponsorship recurring additional gift (SRAG)

                -- and there are no other active SRAG on the linked sponsorship.


                if @STATUSCODE in (0,1,5)
                begin
                    if (select count(*) 
                        from dbo.SPONSORSHIPRECURRINGADDITIONALGIFT SRAG1
                        inner join dbo.SPONSORSHIPRECURRINGADDITIONALGIFT SRAG2 on SRAG2.SPONSORSHIPID = SRAG1.SPONSORSHIPID
                        where SRAG1.REVENUEID = @ID
                        and SRAG2.STATUSCODE in(0,1,5)
                        and SRAG2.ID <> SRAG1.ID
                 ) > 0
                    raiserror('BERR_LINKEDSPONSORSHIPHASACTIVE', 13, 1);
                end

                --UPDATE STATUS

                update dbo.REVENUESCHEDULE
                set STATUSCODE = @STATUSCODE,
                    ENDDATE = case when @STATUSCODE = 3 then case when ENDDATE is null or ENDDATE > cast(@CHANGEDATE as date) then case when cast(@CHANGEDATE as date) < STARTDATE then STARTDATE else cast(@CHANGEDATE as date) end else ENDDATE end  -- Ensure a current end date for Canceled

                                   when @STATUSCODE in(0,1,5) and @PREVIOUSSTATUSCODE in(2,3) then null  -- Clear out end date for Active/Lapsed/Held

                                   else ENDDATE end,
                    NEXTTRANSACTIONDATE = null,   -- This will get reset if needed by USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE

                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CHANGEDATE
                from dbo.REVENUESCHEDULE
                where ID = @ID;

                -- on change to active, refresh the schedule after the update has occurred

                -- Handle Lapsed like Active status

                if @STATUSCODE in (0,1,5)
                begin
                    select 
                        @AMOUNT = AMOUNT,
                        @BASECURRENCYID = BASECURRENCYID,
                        @ORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT,
                        @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID,
                        @TRANSACTIONAMOUNT = TRANSACTIONAMOUNT,
                        @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
                        @BASEEXCHANGERATEID = BASEEXCHANGERATEID
                    from dbo.REVENUE
                    where ID = @ID;

                    exec dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS 
                        @ID = @ID,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CREATIONDATE = @CHANGEDATE,
                        @AMOUNT = @AMOUNT,
                        @BASECURRENCYID = @BASECURRENCYID,
                        @ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
                        @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                        @TRANSACTIONAMOUNT = @TRANSACTIONAMOUNT,
                        @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                        @BASEEXCHANGERATEID = @BASEEXCHANGERATEID;
                end

                exec dbo.USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE
                  @ID,
                  @STATUSCHANGETYPECODE,
                  @REASONCODEID,
                  @PREVIOUSSTATUSCODE,
                  @STATUSCODE,
                  @AMOUNT,
                  @BASECURRENCYID,
                  @ORGANIZATIONAMOUNT,
                  @ORGANIZATIONEXCHANGERATEID,
                  @TRANSACTIONAMOUNT,
                  @TRANSACTIONCURRENCYID,
                  @BASEEXCHANGERATEID,
                  @CHANGEAGENTID,
                  @CHANGEDATE
              end

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

            return 0;