USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFT_9

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFT_9
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @DATE datetime,
    @AMOUNT money,
    @SPLITS xml,
    @FREQUENCYCODE tinyint,
    @ENDDATE datetime,
    @STARTDATE datetime,
    @FINDERNUMBER bigint,
    @SOURCECODE nvarchar(50),
    @APPEALID uniqueidentifier,
    @GIVENANONYMOUSLY bit,
    @MAILINGID uniqueidentifier,
    @CHANNELCODEID uniqueidentifier,
    @DONOTACKNOWLEDGE bit,
    @REFERENCE nvarchar(255),
    @CATEGORYCODEID uniqueidentifier,
    @SENDREMINDER bit,
    @NEXTINSTALLMENTID uniqueidentifier,
    @REVENUEDEVELOPMENTFUNCTIONCODEID uniqueidentifier,
    @TRANSACTIONCURRENCYID uniqueidentifier,
    @BASEEXCHANGERATEID uniqueidentifier,
    @EXCHANGERATE decimal(20,8),
    @HADSPOTRATE bit,
    @RATECHANGED bit,
    @UPDATERECOGNITIONOPTION tinyint,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SCHEDULESEEDDATE datetime,
    @ISMEMBERSHIPRECURRING bit,
    @PREVIOUSSCHEDULESEEDDATE datetime,
    @LASTACTIVITYDATE date
)

as
set nocount on;

declare @CURRENTDATE datetime;
declare @MAXACTIVITYDATE datetime;
declare @ERROR varchar(100);
declare @BASECURRENCYID uniqueidentifier;

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

set @CURRENTDATE = getdate();        

declare @SUM money 

begin try
    if @FINDERNUMBER is null
        set @FINDERNUMBER = 0;
    else if @FINDERNUMBER <> 0
        begin
            if dbo.[UFN_MKTFINDERNUMBER_VALIDATE_MOD10](@FINDERNUMBER) = 0
                raiserror('BBERR_FINDERNUMBER_FAILEDCHECKDIGIT', 13, 1);

            if dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION](@FINDERNUMBER) is null
                raiserror('BBERR_FINDERNUMBER_INVALID', 13, 1);
        end

    if @AMOUNT < 0 
    raiserror('BBERR_RECURRINGGIFT_AMOUNTPOSITIVE', 13, 1);

    --Make sure @DATE <= @STARTDATE <= @SCHEDULESEEDDATE

    if @STARTDATE < @DATE
    raiserror('BBERR_RECURRINGGIFT_STARTDATEBEFOREDATE', 13, 1);
    if @SCHEDULESEEDDATE < @STARTDATE
    raiserror('BBERR_RECURRINGGIFT_NEXTTRANSACTIONBEFORESTARTDATE', 13, 1);

    -- ensure that installments are not being adjusted before the last activity date

    if @SCHEDULESEEDDATE <= @LASTACTIVITYDATE
      raiserror('BBERR_RECURRINGGIFT_NEXTTRANSACTIONBEFORELASTACTIVITY', 13, 1);

    --Business units - AdiSa 6/13/10 - Calculate and store business unit ratios for all new splits given

    --the old splits.

    declare @BUSINESSUNITSRATIO table (DESIGNATIONID uniqueidentifier, OVERRIDEBUSINESSUNITS bit, REASON uniqueidentifier, BUSINESSUNITCODEID uniqueidentifier, RATIO float)
    insert into @BUSINESSUNITSRATIO
    select
        REVENUESPLIT_EXT.DESIGNATIONID,
        REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS,
        REVENUESPLIT_EXT.REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON,
        REVENUESPLITBUSINESSUNIT.BUSINESSUNITCODEID,
        REVENUESPLITBUSINESSUNIT.AMOUNT/REVENUESPLIT.BASEAMOUNT as RATIO
    from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
    inner join dbo.REVENUESPLITBUSINESSUNIT on REVENUESPLITBUSINESSUNIT.REVENUESPLITID = REVENUESPLIT.ID
    where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS = 1 and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1

    --get initial value of given anonymously to determine how to handle recognitions

    declare @INITIALGIVENANONYMOUSLY bit;

    --get the previous values for fields that are tracked in the RG amendment table

    declare @HASAMOUNTFREQUNCYEDITS bit = 0;
    declare @SCHEDULESEEDDATECHANGED bit = 0;
    declare @HASMARKETINGEDITS bit = 0;
    declare @PREVIOUSAMOUNT money;
    declare @PREVIOUSORGAMOUNT money;
    declare @PREVIOUSORGEXCHANGERATEID uniqueidentifier;
    declare @PREVIOUSBASEAMOUNT money;
    declare @PREVIOUSBASEEXCHANGERATEID uniqueidentifier;
    declare @PREVIOUSFREQUENCYCODE tinyint;
    declare @PREVIOUSSTATUSCODE tinyint;
    declare @DESIGNATIONS xml;

    declare @PREVIOUSFINDERNUMBER bigint;
    declare @PREVIOUSSOURCECODE nvarchar(50);
    declare @PREVIOUSAPPEALID uniqueidentifier;
    declare @PREVIOUSMAILINGID uniqueidentifier;
    declare @PREVIOUSCHANNELCODEID uniqueidentifier;
    declare @ACTIVITYTIMELINECODE tinyint;
    -- Cache the old split and recognition values for recognition updates

    declare @OLDSPLITS xml = dbo.UFN_REVENUE_GETSPLITS_2_TOITEMLISTXML(@ID);
    declare @OLDRECOGNITIONS xml = dbo.UFN_REVENUE_GETRECOGNITIONS_FORREVENUE_TOITEMLISTXML(@ID);

    select 
        @BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID),
        @INITIALGIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY,
        @PREVIOUSSOURCECODE = REVENUE_EXT.SOURCECODE,
        @PREVIOUSFINDERNUMBER = REVENUE_EXT.FINDERNUMBER,
        @PREVIOUSAPPEALID = REVENUE_EXT.APPEALID,
        @PREVIOUSMAILINGID = REVENUE_EXT.MAILINGID,
        @PREVIOUSCHANNELCODEID = REVENUE_EXT.CHANNELCODEID
    from dbo.REVENUE_EXT 
    left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE_EXT.ID = V.FINANCIALTRANSACTIONID
    where REVENUE_EXT.ID = @ID

    select
        @PREVIOUSFREQUENCYCODE = FREQUENCYCODE,
        @PREVIOUSSTATUSCODE = STATUSCODE
    from dbo.REVENUESCHEDULE
    where ID = @ID

    select
        @PREVIOUSAMOUNT = TRANSACTIONAMOUNT, 
        @PREVIOUSORGAMOUNT = ORGAMOUNT,
        @PREVIOUSORGEXCHANGERATEID = ORGEXCHANGERATEID, 
        @PREVIOUSBASEAMOUNT = BASEAMOUNT,
        @PREVIOUSBASEEXCHANGERATEID = BASEEXCHANGERATEID
    from dbo.FINANCIALTRANSACTION
    where ID = @ID

    --Multicurrency - RSC 4/16/10 - If the revenue previously used a spot rate, but

    --its rate has changed, store the old rate's ID, so we can remove it later.

    declare @OLDSPOTRATE uniqueidentifier
    if @HADSPOTRATE = 1 and @RATECHANGED = 1
    begin
        select @OLDSPOTRATE = BASEEXCHANGERATEID from dbo.FINANCIALTRANSACTION where ID = @ID;
    end

    --If the record uses a new spot rate, create it and set the rate ID.

    -- WI 365571 AlexLa The rate coming in from UI model has a 1 at the end, not 0.  This wasn't working in 2.94 or 3.0.

    -- I don't see that we are clearing out the old rate even though we are storing it above.  This needs to be revisited.

    if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
    begin
        set @BASEEXCHANGERATEID = newid();

        insert into dbo.CURRENCYEXCHANGERATE(
            ID, 
            FROMCURRENCYID,
            TOCURRENCYID,
            RATE,
            ASOFDATE,
            TYPECODE,
            SOURCECODEID,
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        values(
            @BASEEXCHANGERATEID,
            @TRANSACTIONCURRENCYID,
            @BASECURRENCYID,
            @EXCHANGERATE,
            @DATE,
            2,
            null,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );
    end

    if @ISMEMBERSHIPRECURRING = 1
    begin
        exec dbo.USP_MEMBERSHIPPLEDGE_VALIDATESPLITS @SPLITS=@SPLITS, @REVENUEAMOUNT=@AMOUNT, @REVENUEID=@ID, @TRANSACTIONCURRENCYID=@TRANSACTIONCURRENCYID;
    end
    else
    begin
        exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS=@SPLITS, @REVENUEAMOUNT=@AMOUNT, @REVENUEID=@ID, @TRANSACTIONCURRENCYID=@TRANSACTIONCURRENCYID;
    end

    declare @SPLITSCHANGED bit
    set @SPLITSCHANGED = dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS)

    -- check to see if the revenue record needs to be re-acknowledged                            

    if (coalesce((select top 1 REACKNOWLEDGEREVENUE from dbo.ACKNOWLEDGEMENTPREFERENCE), 0)) = 1
    begin
        declare @FIELDCHANGED bit;    
        set @FIELDCHANGED = 0;            

        -- check to see if amount have changed

        if (select count(REVENUE.ID) from dbo.FINANCIALTRANSACTION REVENUE where REVENUE.ID = @ID and TRANSACTIONAMOUNT = @AMOUNT) = 0
            set @FIELDCHANGED = 1;

        -- check to see if designations have changed

        if @FIELDCHANGED = 0
            if @SPLITSCHANGED = 1
                set @FIELDCHANGED = 1;

        -- if a field has changed, mark the revenue letters for this record out of date, if necessary

        if @FIELDCHANGED = 1
            exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID, @CHANGEAGENTID;     
    end

    declare @INSTALLMENTSCHANGED bit

    select @INSTALLMENTSCHANGED = count(*)
    from dbo.FINANCIALTRANSACTION REVENUE
    where REVENUE.ID = @ID
    and REVENUE.TRANSACTIONAMOUNT <> @AMOUNT;

    --Multicurrency - SlyyMu 8/12/10 replaced the previous multicurrency section done by (RSC 4/16/10) with the UFN_CURRENCY_GETCURRENCYVALUES()                 

    declare @BASEAMOUNT money;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @ORGANIZATIONAMOUNT money;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
    declare @BASETOORGANIZATIONEXCHANGERATE uniqueidentifier;
    declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';

    exec dbo.USP_CURRENCY_GETCURRENCYVALUES
        @AMOUNT,
        @DATE,
        @BASECURRENCYID,
        @BASEEXCHANGERATEID,
        @TRANSACTIONCURRENCYID output,
        @BASEAMOUNT output,
        @ORGANIZATIONCURRENCYID output,
        @ORGANIZATIONAMOUNT output,
        @ORGANIZATIONEXCHANGERATEID output,
        1,
        @BASETOORGANIZATIONEXCHANGERATE;            


    if @REVENUEDEVELOPMENTFUNCTIONCODEID is not null
    begin
        declare @REVENUEDEVELOPMENTFUNCTIONID uniqueidentifier;
        set @REVENUEDEVELOPMENTFUNCTIONID = newid();

        insert into dbo.REVENUEDEVELOPMENTFUNCTION (
            ID,
            REVENUEID,
            REVENUEDEVELOPMENTFUNCTIONCODEID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        values (
            @REVENUEDEVELOPMENTFUNCTIONID,
            @ID,
            @REVENUEDEVELOPMENTFUNCTIONCODEID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        )
    end

    -- capture amendments to the RG record

    if @PREVIOUSAMOUNT <> @AMOUNT or @PREVIOUSFREQUENCYCODE <> @FREQUENCYCODE 
       set @HASAMOUNTFREQUNCYEDITS = 1;

    if @PREVIOUSSCHEDULESEEDDATE <> @SCHEDULESEEDDATE
       set @SCHEDULESEEDDATECHANGED = 1

    if @PREVIOUSFINDERNUMBER <> @FINDERNUMBER or @PREVIOUSSOURCECODE <> @SOURCECODE or
           isnull(@PREVIOUSAPPEALID,@EMPTYGUID) <> isnull(@APPEALID,@EMPTYGUID) or 
           isnull(@PREVIOUSMAILINGID,@EMPTYGUID) <> isnull(@MAILINGID,@EMPTYGUID) or
           isnull(@PREVIOUSCHANNELCODEID,@EMPTYGUID) <> isnull(@CHANNELCODEID,@EMPTYGUID)
       set @HASMARKETINGEDITS = 1;

     if @SPLITSCHANGED = 1
     begin

       --process the designations      

        exec dbo.USP_REVENUE_GETSPLITS_MERGEDESIGNATIONSTOXML @SPLITS, @OLDSPLITS, @DESIGNATIONS output;

     end

     --insert records into the recurring gift amendment table.

     if @SPLITSCHANGED = 1 or @HASMARKETINGEDITS = 1 or @HASAMOUNTFREQUNCYEDITS = 1 or @REVENUEDEVELOPMENTFUNCTIONCODEID is not null or @SCHEDULESEEDDATECHANGED = 1
     begin
       insert into dbo.RECURRINGGIFTAMENDMENT(ID, FINANCIALTRANSACTIONID,AMENDMENTTYPECODE, DATE, REVENUEDEVELOPMENTFUNCTIONID,
                                              TRANSACTIONAMOUNT,PREVIOUSTRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,
                                              ORGANIZATIONAMOUNT,PREVIOUSORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,PREVIOUSORGANIZATIONEXCHANGERATEID,
                       BASEAMOUNT,PREVIOUSBASEAMOUNT,BASECURRENCYID,BASEEXCHANGERATEID,PREVIOUSBASEEXCHANGERATEID,
                                              FREQUENCYCODE,PREVIOUSFREQUENCYCODE,DESIGNATIONS,SOURCECODE,FINDERNUMBER,APPEALID,
                                              MAILINGID,CHANNELCODEID,NEXTTRANSACTIONDATE,PREVIOUSNEXTTRANSACTIONDATE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
       values(newid(),@ID,2,@CURRENTDATE,@REVENUEDEVELOPMENTFUNCTIONID,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @AMOUNT else 0 end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @PREVIOUSAMOUNT else 0 end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @TRANSACTIONCURRENCYID else null end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @ORGANIZATIONAMOUNT else 0 end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @PREVIOUSORGAMOUNT else 0 end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @ORGANIZATIONEXCHANGERATEID else null end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @PREVIOUSORGEXCHANGERATEID else null end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @BASEAMOUNT else 0 end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @PREVIOUSBASEAMOUNT else 0 end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @BASECURRENCYID else null end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @BASEEXCHANGERATEID else null end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @PREVIOUSBASEEXCHANGERATEID else null end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @FREQUENCYCODE else 99 end,
             case when @HASAMOUNTFREQUNCYEDITS = 1 then @PREVIOUSFREQUENCYCODE else 99 end,
             case when @SPLITSCHANGED = 1 then @DESIGNATIONS else null end,
             case when @HASMARKETINGEDITS = 1 then @SOURCECODE else '' end,
             case when @HASMARKETINGEDITS = 1 then @FINDERNUMBER else 0 end,
             case when @HASMARKETINGEDITS = 1 then @APPEALID else null end,
             case when @HASMARKETINGEDITS = 1 then @MAILINGID else null end,
             case when @HASMARKETINGEDITS = 1 then @CHANNELCODEID else null end,
             case when @SCHEDULESEEDDATECHANGED = 1 then @SCHEDULESEEDDATE else null end,
             case when @SCHEDULESEEDDATECHANGED = 1 then @PREVIOUSSCHEDULESEEDDATE else null end,
             @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)
     end

    -- make sure we aren't trying to modify revenue in a locked/closed deposit/bank

    exec dbo.USP_EDIT_REVENUE_VALIDATE_DEPOSIT_STATUS @ID;

    --Update Revenue

    if @AMOUNT < 0
        raiserror ('CK_REVENUE_RECEIPTAMOUNTNOTNEGATIVE', 16, 1);

    update dbo.FINANCIALTRANSACTION set
        DATE = @DATE
        ,BASEAMOUNT = @BASEAMOUNT
        ,ORGAMOUNT = @ORGANIZATIONAMOUNT
        ,TRANSACTIONAMOUNT = @AMOUNT
        ,BASEEXCHANGERATEID = @BASEEXCHANGERATEID
        ,ORGEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
        ,CHANGEDBYID = @CHANGEAGENTID
        ,DATECHANGED = @CURRENTDATE
    where ID = @ID;

    update dbo.REVENUE_EXT set
        RECEIPTAMOUNT = @AMOUNT
        ,FINDERNUMBER = @FINDERNUMBER
        ,SOURCECODE = @SOURCECODE
        ,APPEALID = @APPEALID
        ,GIVENANONYMOUSLY = @GIVENANONYMOUSLY
        ,MAILINGID = @MAILINGID
        ,CHANNELCODEID = @CHANNELCODEID
        ,DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE
        ,REFERENCE = ISNULL(@REFERENCE, '')
        ,CHANGEDBYID = @CHANGEAGENTID
        ,DATECHANGED = @CURRENTDATE
    where ID = @ID;

    --only update schedule or start date if value changed

    --TAY:  Per MR, reset STARTDATE on edit.

    update dbo.REVENUESCHEDULE
    set
        SCHEDULESEEDDATE = isnull(@SCHEDULESEEDDATE,SCHEDULESEEDDATE),
        FREQUENCYCODE = @FREQUENCYCODE,
        STARTDATE = @STARTDATE,
        ENDDATE = @ENDDATE,
        NEXTTRANSACTIONDATE = @SCHEDULESEEDDATE, -- this is not right if there is a partially paid/written-off installment, it gets changed back later

        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where
        ID = @ID
        and 
        (
            SCHEDULESEEDDATE <> @SCHEDULESEEDDATE
            or NEXTTRANSACTIONDATE <> @SCHEDULESEEDDATE
            or (@SCHEDULESEEDDATE is not null and SCHEDULESEEDDATE is null)
            or case when @SCHEDULESEEDDATE is null then 0 else 1 end <> case when NEXTTRANSACTIONDATE is null then 0 else 1 end
            or FREQUENCYCODE <> @FREQUENCYCODE
            or ENDDATE <> @ENDDATE
            or (@ENDDATE is null and ENDDATE is not null)
            or (@ENDDATE is not null and ENDDATE is null)
            or STARTDATE <> @STARTDATE
            or (@STARTDATE is null and STARTDATE is not null)
            or (@STARTDATE is not null and STARTDATE is null)
        );

    if @@ROWCOUNT = 1
        set @INSTALLMENTSCHANGED = 1;

    if not exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @ID) and @PREVIOUSSTATUSCODE <> 3
    begin
        exec dbo.USP_RECURRINGGIFT_FIXMISSINGINSTALLMENTSCHEDULE @ID, @CHANGEAGENTID, @CURRENTDATE
    end
    else if @INSTALLMENTSCHANGED = 1
    begin
        -- if any activity exists on the RG, then the RG date must be <= the first installment

        if exists(select 'x'
                  from dbo.RECURRINGGIFTINSTALLMENT
                  left join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID
                  left join dbo.RECURRINGGIFTINSTALLMENTWRITEOFF on RECURRINGGIFTINSTALLMENTWRITEOFF.RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID
                  where REVENUEID = @ID
                  and (RECURRINGGIFTINSTALLMENTPAYMENT.ID is not null or RECURRINGGIFTINSTALLMENTWRITEOFF.ID is not null))
        begin
          declare @FIRSTINSTALLMENTDATE date;

          select @FIRSTINSTALLMENTDATE = min(DATE)
          from dbo.RECURRINGGIFTINSTALLMENT
          where REVENUEID = @ID;

          if @STARTDATE > @FIRSTINSTALLMENTDATE
            raiserror('CK_RECURRINGGIFTINSTALLMENT_VALIDDATE', 13, 1);
        end
        -- for backwards compatibility only

        else if exists(select 1 from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = @ID and SCHEDULEDATE < @DATE)
            raiserror('CK_RECURRINGGIFTINSTALLMENT_VALIDDATE', 13, 1);

        if @NEXTINSTALLMENTID is not null or not exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @ID)
        begin
            --Cache CONTEXT INFO

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

            delete from dbo.RECURRINGGIFTINSTALLMENT
            where ID = @NEXTINSTALLMENTID
            or (REVENUEID = @ID and
                DATE >= (select DATE from dbo.RECURRINGGIFTINSTALLMENT where ID = @NEXTINSTALLMENTID));

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @SCHEDULESEEDDATE is not null
            begin
              -- fix date for semi-monthly

              if @FREQUENCYCODE = 7
              begin
                  if datepart(day, @SCHEDULESEEDDATE) <> 1 and datepart(day, @SCHEDULESEEDDATE) <> 15
                      --Use the old version of UFN_REVENUE_GETNEXTTRANSACTIONDATE for this purpose because it doesn't take a revenue ID and does exactly what is needed.

                      set @SCHEDULESEEDDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE(@FREQUENCYCODE, @SCHEDULESEEDDATE);
              end

              -- add next installment

              insert into dbo.RECURRINGGIFTINSTALLMENT (ID, REVENUEID, AMOUNT, DATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID)
              values (newid(), @ID, @BASEAMOUNT, @SCHEDULESEEDDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);
            end
        end

        -- add any additional necessary installments

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

        exec dbo.USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE
          @ID,
          8,
          null,
          null,
          null,
          @BASEAMOUNT,
          @BASECURRENCYID,
          @ORGANIZATIONAMOUNT,
          @ORGANIZATIONEXCHANGERATEID,
          @AMOUNT,
          @TRANSACTIONCURRENCYID,
          @BASEEXCHANGERATEID,     
          @CHANGEAGENTID,
          @CURRENTDATE;
    end

    --update reminder

    update dbo.REVENUESCHEDULE
    set SENDPLEDGEREMINDER = @SENDREMINDER,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where ID = @ID
    and SENDPLEDGEREMINDER <> @SENDREMINDER;

    declare @ISSPONSORSHIPRECURRINGADDITIONALGIFT bit = 0;
    set @ISSPONSORSHIPRECURRINGADDITIONALGIFT =  dbo.UFN_RECURRINGGIFT_ISSPONSORSHIPRECURRINGADDITIONALGIFT(@ID); 

    -- Set the IDs for any blank entries in the splits XML so that it can associate the declines with gift aid

    -- flag with the generated splits.  Also, pull in the existing value for declines gift aid if it wasn't passed

    -- in the xml.

    set @SPLITS = (    select 
                        case when SPLITS.[ID] is null or SPLITS.[ID] = '00000000-0000-0000-0000-000000000000' then newid() else SPLITS.[ID] end [ID],
                        SPLITS.[AMOUNT],
                        SPLITS.[APPLICATIONCODE],
                        SPLITS.[DESIGNATIONID],
                        case @ISSPONSORSHIPRECURRINGADDITIONALGIFT 
                    when 0 then SPLITS.[TYPECODE]
                else 17
                end as TYPECODE,
                        case when (@SPLITS.exist('(/SPLITS/ITEM/CATEGORYCODEID)') = 0) then @CATEGORYCODEID else SPLITS.[CATEGORYCODEID] end as [CATEGORYCODEID],
                        case when SPLITS.[DECLINESGIFTAID] is null then REVENUESPLITGIFTAID.DECLINESGIFTAID else SPLITS.DECLINESGIFTAID end DECLINESGIFTAID
                    from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) SPLITS
                    left join dbo.REVENUESPLITGIFTAID on SPLITS.ID = REVENUESPLITGIFTAID.ID
                    for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64)


    --JamesWill WI176241 Check to see if the revenue category should be copied around 

    declare @REVENUECATEGORYCOUNT int = 0;
    declare @REVENUECATEGORYID uniqueidentifier = null; 

    select top 1 @REVENUECATEGORYID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
    from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
    inner join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = REVENUESPLIT.ID
    where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1;

    select @REVENUECATEGORYCOUNT = count(distinct REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID)
    from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
  inner join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = REVENUESPLIT.ID
    where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1;

    --Only set the revenue category on the splits if we can correctly infer what that should be 

    --(i.e., all the existing splits have the same category). So there should only be 1 distinct category

    --(If there are 0, that means that there shouldn't be a revenue category but also that there aren't currently any. 

    --so we shouldn't need to delete anything).

    if @REVENUECATEGORYCOUNT <> 1 
        set @REVENUECATEGORYID = null;

    declare @SPLITSAMOUNTMODIFIED table
    (
        ID uniqueidentifier
    );

    insert into @SPLITSAMOUNTMODIFIED (ID)
    select
        SPLITS.ID
    from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) SPLITS
    left join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = SPLITS.ID and LI.DELETEDON is null and LI.TYPECODE !=1
    where
        LI.ID is null or
        SPLITS.AMOUNT != LI.BASEAMOUNT;

    --Multicurrency - RSC 4/16/10 - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.

    set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
    exec dbo.USP_REVENUE_GETSPLITS_2_CUSTOMUPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;

    -- Update recognition based on user selected option

    exec dbo.USP_REVENUE_UPDATERECOGNITION @ID, @OLDSPLITS, @UPDATERECOGNITIONOPTION, @CHANGEAGENTID, @CURRENTDATE, @OLDRECOGNITIONS;

    exec dbo.USP_REVENUE_UPDATESOLICITORS @ID, @CHANGEAGENTID, @CURRENTDATE, @OLDSPLITS;

    --JamesWill WI176241 Check to see if the revenue category should be copied to newly created splits 

    if not @REVENUECATEGORYID is null
    begin
        insert into dbo.REVENUECATEGORY(ID, GLREVENUECATEGORYMAPPINGID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select 
            REVENUESPLIT.ID,
            @REVENUECATEGORYID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE 
        from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT 
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
        left join dbo.REVENUECATEGORY [CATEGORY] on [CATEGORY].ID = REVENUESPLIT.ID
        where [CATEGORY].ID is null and REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1;
    end

  if @CATEGORYCODEID is null
  begin
    exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @ID, @CATEGORYCODEID, @CHANGEAGENTID, @CURRENTDATE
  end


    if @SPLITSCHANGED = 1
    begin
        exec dbo.USP_REVENUE_CLEARANDADDCAMPAIGNS @REVENUEID = @ID, 
            @CHANGEAGENTID = @CHANGEAGENTID,
            @CHANGEDATE = @CURRENTDATE
    end

    --Select the value for default anonymous recognition setting

    declare @DEFAULTANONYMOUSRECOGNITION bit = 0;
    select @DEFAULTANONYMOUSRECOGNITION = DEFAULTANONYMOUSRECOGNITION
    from dbo.RECOGNITIONDEFAULT

    --if GIVENANONYMOUSLY has toggled, fix recognitions

    if (@INITIALGIVENANONYMOUSLY = 1 and @GIVENANONYMOUSLY = 0) or (@INITIALGIVENANONYMOUSLY = 0 and @GIVENANONYMOUSLY = 1 and @DEFAULTANONYMOUSRECOGNITION = 1)
    begin
        if not exists(  
            select RR.ID 
                from dbo.REVENUERECOGNITION RR
            inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
            inner join dbo.REVENUESPLIT_EXT on RS.ID = REVENUESPLIT_EXT.ID
                where 
            RS.FINANCIALTRANSACTIONID = @ID and RS.DELETEDON is null and RS.TYPECODE != 1
        )
                --anonymous to named: add default recognition credits

            exec dbo.USP_REVENUE_CREATERECOGNITIONS @ID, @CHANGEAGENTID, @CURRENTDATE;
    end
    else if @INITIALGIVENANONYMOUSLY = 0 and @GIVENANONYMOUSLY = 1 and @DEFAULTANONYMOUSRECOGNITION = 0
    begin
        --named to anonymous: remove all recognition credits

        delete dbo.REVENUERECOGNITION 
            where ID in (
                select RR.ID 
                    from dbo.REVENUERECOGNITION RR
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
                        inner join dbo.REVENUESPLIT_EXT on RS.ID = REVENUESPLIT_EXT.ID
                    where 
                        RS.FINANCIALTRANSACTIONID = @ID and RS.DELETEDON is null and RS.TYPECODE != 1);
    end

    --Gift Aid is for UK only

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
    begin
        --Because the edit form does not include these values, we must retrieve them before updating the splits so that we can

        --  get the proper Gift Aid qualification status

        declare @PAYMENTMETHODCODE tinyint;
        declare @CREDITTYPECODEID uniqueidentifier;
        select @PAYMENTMETHODCODE = PAYMENTMETHODCODE from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID;
        if @PAYMENTMETHODCODE = 2
        begin
            select @CREDITTYPECODEID = CREDITCARD.CREDITTYPECODEID 
            from dbo.REVENUEPAYMENTMETHOD
                left join dbo.REVENUESCHEDULE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUESCHEDULE.ID
                left join dbo.CREDITCARD on CREDITCARD.ID = REVENUESCHEDULE.CREDITCARDID
            where REVENUEPAYMENTMETHOD.REVENUEID = @ID;
        end
        else if @PAYMENTMETHODCODE = 3 and dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = -1
        begin
            --Bug 138736 - AdamBu 2/2/11 - Error on invalid paperless mandate setup.

            raiserror('BBERR_MULTIPLE_PAPERLESSMANDATECONFIGS', 13, 1);
            return 1;
        end

        declare @SPLITSDECLININGGIFTAID xml
        set @SPLITSDECLININGGIFTAID = (    
            select
                ID as REVENUESPLITID
            from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS)
            where DECLINESGIFTAID = 1
            for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64
        )

        exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 2, @SPLITSDECLININGGIFTAID; --recurring gift is transaction type code 2

    end

    --Business units - AdiSa 8/12/10 - add adjusted splits back.

    declare @REVENUESPLITBUSINESSUNITID uniqueidentifier;
    declare BUSINESSUNITS cursor local fast_forward for
    select 
        REVENUESPLITBUSINESSUNIT.ID
    from dbo.REVENUESPLITBUSINESSUNIT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLITBUSINESSUNIT.REVENUESPLITID
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
    where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1;
    open BUSINESSUNITS;

    begin try
        fetch next from BUSINESSUNITS into @REVENUESPLITBUSINESSUNITID
        while (@@FETCH_STATUS = 0)
        begin
            exec dbo.USP_REVENUESPLITBUSINESSUNIT_DELETEBYID_WITHCHANGEAGENTID @REVENUESPLITBUSINESSUNITID, @CHANGEAGENTID;
        fetch next from BUSINESSUNITS into @REVENUESPLITBUSINESSUNITID
        end
        close BUSINESSUNITS;
        deallocate BUSINESSUNITS;
    end try
    begin catch
        close BUSINESSUNITS;
        deallocate BUSINESSUNITS;
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    update dbo.REVENUESPLIT_EXT set
    OVERRIDEBUSINESSUNITS = BUR.OVERRIDEBUSINESSUNITS
    ,REVENUESPLITBUSINESSUNITOVERRIDECODEID = BUR.REASON
        ,CHANGEDBYID = @CHANGEAGENTID
        ,DATECHANGED = @CURRENTDATE
    from dbo.REVENUESPLIT_EXT
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = REVENUESPLIT_EXT.ID
    inner join @BUSINESSUNITSRATIO as BUR on REVENUESPLIT_EXT.DESIGNATIONID = BUR.DESIGNATIONID
    where BUR.OVERRIDEBUSINESSUNITS = 1 and LI.FINANCIALTRANSACTIONID = @ID and LI.DELETEDON is null and LI.TYPECODE != 1;

    insert into dbo.REVENUESPLITBUSINESSUNIT
    (
        ID,
        REVENUESPLITID,
        BUSINESSUNITCODEID,
        AMOUNT,
        BASECURRENCYID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        newid(),
        REVENUESPLIT_EXT.ID,
        BUR.BUSINESSUNITCODEID,
        LI.BASEAMOUNT * BUR.RATIO,
        V.BASECURRENCYID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from dbo.REVENUESPLIT_EXT
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = REVENUESPLIT_EXT.ID
        inner join @BUSINESSUNITSRATIO BUR on BUR.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
        inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on LI.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
    where LI.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS=1 and LI.DELETEDON is null and LI.TYPECODE != 1;

    exec dbo.USP_REVENUESPLIT_APPLYBUSINESSUNITS @ID, @CHANGEAGENTID, @CURRENTDATE;
end try

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

return 0;