USP_GLOBALCHANGE_SETRECURRINGGIFTSTATUS

      CREATE procedure dbo.USP_GLOBALCHANGE_SETRECURRINGGIFTSTATUS
      (
        @IDSETREGISTERID uniqueidentifier, 
        @STATUSCODE tinyint,
        @CHANGEAGENTID uniqueidentifier = null,
        @ASOF as datetime = null,
        @NUMBERADDED int = 0 output,
        @NUMBEREDITED int = 0 output,
        @NUMBERDELETED int = 0 output,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @REASONCODEID uniqueidentifier = null
      )
      as            
        set nocount off;

        -- Prevent existing instances of the global change to set the RGs status to Held when upgrading to 4.0

        -- This will be re-enabled when the Skip functionality is implemented to address this global change.

        if @STATUSCODE = 1
          raiserror('BBERR_HELDSTATUS_NOTSUPPORTED',13,1);

        if @REASONCODEID is null
          raiserror('BBERR_REASONCODE_REQUIRED',13,1);

        set @NUMBERADDED = 0;
        set @NUMBEREDITED = 0;
        set @NUMBERDELETED = 0; 

        declare @CHANGEDATE datetime;
        set @CHANGEDATE = getdate();

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

        declare @IDS table
        (
          ID uniqueidentifier
        );
        insert into @IDS(ID)
        select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID);                

        if @STATUSCODE in(0,1,5)
        begin
          if exists(
            select 
              RS.ID 
            from 
              dbo.REVENUESCHEDULE RS
              inner join @IDS as SELECTION on SELECTION.ID = RS.ID
            where 
              not (RS.NEXTTRANSACTIONDATE is null or RS.NEXTTRANSACTIONDATE <= RS.ENDDATE)
          )
          begin
            raiserror('BBERR_RECURRINGGIFT_INVALIDSTATUSFORENDDATE: You cannot set a recurring gift status to Active, Lapsed, or Held if the next transaction date has passed the end date.',13,1);
            return 1;
          end

          if exists(
            select 
              RS.ID 
            from 
              dbo.REVENUESCHEDULE RS
              inner join @IDS as SELECTION on SELECTION.ID = RS.ID
              inner join dbo.SPONSORSHIPRECURRINGADDITIONALGIFT SRAG1 on SRAG1.REVENUEID = RS.ID
              inner join dbo.SPONSORSHIPRECURRINGADDITIONALGIFT SRAG2 on SRAG2.SPONSORSHIPID = SRAG1.SPONSORSHIPID
              where SRAG2.STATUSCODE in(0,1,5)
              and SRAG2.ID <> SRAG1.ID
          )
          begin
            raiserror('BBERR_RECURRINGGIFT_INVALIDSTATUSFORSPRAG: You cannot set a sponsorship recurring additional gift status to Active, Lapsed, or Held if another sponsorship recurring additional exists on the same sponsorship that is already Active, Lapsed or Held.',13,1);
            return 1;
          end
        end

        begin try
          declare @USERSITES table
          (
            SITEID uniqueidentifier
          );

          insert into @USERSITES(SITEID)
            select SITEID from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, '3269A1D1-31CB-4D28-945C-B7623A3EFCCA', 4);

          declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

          /* JamesWill WI174458 2011-12-02
             The RecurringGiftStatus.Edit form does a lot of work (creating, deleting, fixing up installments, etc.) To recreate that, 
             the most straightforward method is to just cursor over everything this would change and call the SP to do all the work. 

             We ran some performance tests and there's not a huge penalty for this for 10k records. Should this be an issue in the future,
             we will need to figure out how to turn the work that the SP does into set-based stuff. 
          */ 
          declare @RECURRINGGIFTID uniqueidentifier = null;
          declare RECURRINGGIFTS_CURSOR cursor local fast_forward for
            select
              REVENUE.ID
            from 
              dbo.REVENUESCHEDULE
              inner join dbo.REVENUE on REVENUE.ID = REVENUESCHEDULE.ID
              inner join @IDS SELECTION on SELECTION.ID = REVENUESCHEDULE.ID
              inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUESCHEDULE.ID
              inner join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID_BULK() REVENUESITE on REVENUESITE.ID = REVENUESPLIT.ID
              left join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.ID
            where 
              REVENUESCHEDULE.STATUSCODE <> @STATUSCODE
              and REVENUE.TRANSACTIONTYPECODE = 2 
              and SPONSORSHIP.ID is null
              and (
                  @ISSYSADMIN = 1 
                  or exists(    
                    select 1 
                    from 
                      @USERSITES USERSITES 
                    where 
                      USERSITES.SITEID = REVENUESITE.SITEID 
                      or (USERSITES.SITEID is null and REVENUESITE.SITEID is null)
                  )
                );

          open RECURRINGGIFTS_CURSOR;
          fetch next from RECURRINGGIFTS_CURSOR into @RECURRINGGIFTID;
          while @@FETCH_STATUS = 0
          begin
            declare @SKIPPEDINSTALLMENTID uniqueidentifier;

            -- If updating to Active or Lapsed, look for installments to undo.

            if @STATUSCODE in(0,5)
              exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFTSTATUS
                    @ID = @RECURRINGGIFTID,
                    @SKIPPEDINSTALLMENTID = @SKIPPEDINSTALLMENTID output;

            /*this SP is used by both this global change and the RecurringGiftStatus.Edit form. Any changes to "the work" done when changing 
              the status on a recurring gift should be done in USP_RECURRINGGIFT_EDITSTATUS so both the form and the global change pick up 
              the changes. */

            exec dbo.USP_RECURRINGGIFT_EDITSTATUS @RECURRINGGIFTID, @STATUSCODE, @CHANGEAGENTID, @CHANGEDATE, @REASONCODEID, null, @SKIPPEDINSTALLMENTID;
            set @NUMBEREDITED = @NUMBEREDITED + 1;

            fetch next from RECURRINGGIFTS_CURSOR into @RECURRINGGIFTID;
          end

          close RECURRINGGIFTS_CURSOR;
          deallocate RECURRINGGIFTS_CURSOR;
        end try

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