USP_GLOBALCHANGE_SPONSORSHIPADJUSTRATE

CREATE procedure dbo.USP_GLOBALCHANGE_SPONSORSHIPADJUSTRATE
(
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output,
    @SPONSORSHIPPROGRAMID uniqueidentifier,
    @IDSETREGISTERID uniqueidentifier,
    @NEWRATE tinyint = 0,
    @AMOUNT money,
    @REVENUEDEVELOPMENTFUNCTIONCODEID uniqueidentifier,
    @MARKETINGOPTION bit = 0,
    @SOURCECODE nvarchar(50) = null,
    @APPEALID uniqueidentifier = null,
    @MAILINGID uniqueidentifier = null,
    @CHANNELCODEID uniqueidentifier = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @UPDATERECOGNITIONCREDITS bit = 0
)
as            
    set nocount off;

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate();
    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0; 

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

  if @TRANSACTIONCURRENCYID is null
    set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    begin try
    declare @REVENUEID uniqueidentifier;
    declare @RESETRECOGNITIONCREDITS tinyint = 0; -- no update

    declare @TempTbl table(ID uniqueidentifier)

    if @UPDATERECOGNITIONCREDITS = 1
      set @RESETRECOGNITIONCREDITS = 2; -- update based on defaults


    if(@SPONSORSHIPPROGRAMID is not null)
      insert into @TempTbl(ID) 
      select REVENUESPLIT.REVENUEID
      from dbo.SPONSORSHIP
      inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
      where SPONSORSHIP.SPONSORSHIPPROGRAMID = @SPONSORSHIPPROGRAMID
      and SPONSORSHIP.STATUSCODE = 1
      and REVENUESPLIT.REVENUEID is not null
      and REVENUESPLIT.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID;
    else
      insert into @TempTbl(ID)
      select REVENUESPLIT.REVENUEID
      from dbo.SPONSORSHIP S
      inner join dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID) IDSET on S.ID = IDSET.ID
      inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = S.REVENUESPLITID
      where REVENUESPLIT.REVENUEID is not null
      and S.STATUSCODE = 1
      and REVENUESPLIT.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID;


    declare SPONSORSHIP_CURSOR cursor local fast_forward for
      select ID from @TempTbl;

    open SPONSORSHIP_CURSOR;
    fetch next from SPONSORSHIP_CURSOR into @REVENUEID;

    while (@@FETCH_STATUS = 0)
        begin
      declare @REVENUERID uniqueidentifier = null
      declare    @CONSTITUENTID uniqueidentifier = null
      declare    @CONSTITUENTNAME nvarchar(255) = null
      declare    @DATE datetime = null
      declare    @REVENUESPLITAMOUNT money = null
      declare    @SPLITS xml = null
      declare    @FREQUENCYCODE tinyint = null
      declare @ENDDATE datetime = null
      declare @STARTDATE datetime = null
      declare @FINDERNUMBER bigint = null
      declare @SOURCECODEX nvarchar(50) = null
      declare @APPEALIDX uniqueidentifier = null
      declare @GIVENANONYMOUSLY bit = null
      declare @MAILINGIDX uniqueidentifier = null
      declare @CHANNELCODEIDX uniqueidentifier = null
      declare @DONOTACKNOWLEDGE bit = null
      declare @MAXSOLICITORAMOUNT money = null
      declare @TSLONG bigint = 0
      declare @SINGLEDESIGNATIONID uniqueidentifier = null
      declare @FINDERNUMBERVALID bit = null
      declare @REFERENCE nvarchar(255) = null
      declare @CATEGORYCODEID uniqueidentifier = null
      declare @SENDREMINDER bit
      declare @NEXTINSTALLMENTID uniqueidentifier;
            declare @BASEEXCHANGERATEID uniqueidentifier;
            declare @EXCHANGERATE decimal(20,8);
            declare @HADSPOTRATE bit;
            declare @RATECHANGED bit;

      exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFT_2  
        @REVENUEID,
        0,
        @REVENUERID out,
        @CONSTITUENTID out,
        @CONSTITUENTNAME out,
        @DATE out,
        @REVENUESPLITAMOUNT out,
   @SPLITS out,
        @FREQUENCYCODE out,
        @ENDDATE out,
        @STARTDATE out,
        @FINDERNUMBER out,
        @SOURCECODEX out,
        @APPEALIDX out,
        @GIVENANONYMOUSLY out,
        @MAILINGIDX out,
        @CHANNELCODEIDX out,
        @DONOTACKNOWLEDGE out,
        @MAXSOLICITORAMOUNT out,
        @TSLONG out,
        @SINGLEDESIGNATIONID out,
        @FINDERNUMBERVALID out,
        @REFERENCE out,
        @CATEGORYCODEID out,
        @SENDREMINDER out,
        @NEXTINSTALLMENTID out,
                @BASEEXCHANGERATEID = @BASEEXCHANGERATEID output,
                @EXCHANGERATE = @EXCHANGERATE output,
                @HADSPOTRATE = @HADSPOTRATE output,
                @RATECHANGED = @RATECHANGED output;

          if @MARKETINGOPTION = 0 --retain original values

          begin
              set @SOURCECODE = @SOURCECODEX;
              set @APPEALID = @APPEALIDX;
              set @MAILINGID = @MAILINGIDX;
              set @CHANNELCODEID = @CHANNELCODEIDX;
          end
          else
              set @FINDERNUMBER = 0;

      if @NEWRATE = 0
        set @REVENUESPLITAMOUNT = @AMOUNT
      if @NEWRATE = 1
        set @REVENUESPLITAMOUNT = @REVENUESPLITAMOUNT + @AMOUNT

      set @SPLITS.modify('replace value of (/SPLITS/ITEM/AMOUNT/text())[1] with sql:variable("@REVENUESPLITAMOUNT")')

      begin try
        exec dbo.USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFT_5
          @REVENUEID,
          @CHANGEAGENTID,
          @DATE,
          @REVENUESPLITAMOUNT,
          @SPLITS,
          @FREQUENCYCODE,
          @ENDDATE,
          @STARTDATE,
          @FINDERNUMBER,
          @SOURCECODE,
          @APPEALID,
          @GIVENANONYMOUSLY,
          @MAILINGID,
          @CHANNELCODEID,
          @DONOTACKNOWLEDGE,
          @REFERENCE,
          @CATEGORYCODEID,
          @SENDREMINDER,
          @NEXTINSTALLMENTID,
          @REVENUEDEVELOPMENTFUNCTIONCODEID,
          @TRANSACTIONCURRENCYID,
          @BASEEXCHANGERATEID,
                    @EXCHANGERATE,
                    @HADSPOTRATE,
                    @RATECHANGED,
          @RESETRECOGNITIONCREDITS;
      end try
      begin catch

      end catch
            set @NUMBEREDITED = @NUMBEREDITED + 1

            fetch next from SPONSORSHIP_CURSOR into
                @REVENUEID
        end
        close SPONSORSHIP_CURSOR;
        deallocate SPONSORSHIP_CURSOR;
    end try
    begin catch
    close SPONSORSHIP_CURSOR;
        deallocate SPONSORSHIP_CURSOR;
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch