USP_EDIT_RECURRINGGIFTDETAILS_3

          CREATE PROCEDURE USP_EDIT_RECURRINGGIFTDETAILS_3
              @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @AMOUNT money,
                    @SPLITS xml,
                    @FREQUENCYCODE tinyint,
                    @ENDDATE datetime,
                    @STARTDATE datetime,
                    @NEXTINSTALLMENTID uniqueidentifier,
          @USEEXISTINGCURRENCY bit,
          @INSTALLMENTBEGINDATE datetime

          AS
          BEGIN
              SET NOCOUNT ON;

                  declare @CURRENTDATE datetime
                declare @MAXACTIVITYDATE datetime
                declare @ERROR varchar(100)

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

                set @CURRENTDATE = getdate();        

                declare @SUM money 

              begin try

              declare @DATE datetime
              select @DATE=[DATE] from dbo.REVENUE where REVENUE.ID=@ID 

              if @AMOUNT < 0 
                            raiserror('The amount cannot be negative.', 13, 1) 

                        if @STARTDATE < @DATE
                            raiserror('The schedule cannot start before the gift date.', 13, 1);

              exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS=@SPLITS, @REVENUEAMOUNT=@AMOUNT, @REVENUEID=@ID;

              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.REVENUE where REVENUE.ID = @ID and AMOUNT = @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


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

          update dbo.REVENUE_EXT
          set
            RECEIPTAMOUNT = @AMOUNT,
            CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
          where ID = @ID
          */

          update dbo.FINANCIALTRANSACTION
                    set
                        DATE = @DATE,
                        TRANSACTIONAMOUNT = @AMOUNT,
            BASEAMOUNT= @AMOUNT,
            ORGAMOUNT = @AMOUNT,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @ID
          and (TRANSACTIONAMOUNT <> @AMOUNT
          or BASEAMOUNT <> @AMOUNT
          or ORGAMOUNT <> @AMOUNT
          or DATE <> @DATE 
          or (@DATE is null and DATE is not null)
                    or (@DATE is not null and DATE is null));

         --marked installmentchange value to 1, if amount is updated successfully.
          if @@ROWCOUNT = 1
                    set @INSTALLMENTSCHANGED = 1;

          --Update Revenue
                    update dbo.REVENUE_EXT
                    set
                        RECEIPTAMOUNT = @AMOUNT,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @ID
          and RECEIPTAMOUNT <> @AMOUNT;

          --marked installmentchange value to 1, if amount is updated successfully.
          if @@ROWCOUNT = 1
                        set @INSTALLMENTSCHANGED = 1;

                    --only update schedule if a value changed
                    update dbo.REVENUESCHEDULE
                    set
                        FREQUENCYCODE = @FREQUENCYCODE,
                        ENDDATE = @ENDDATE,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @ID
                        and (FREQUENCYCODE <> @FREQUENCYCODE
                            or ENDDATE <> @ENDDATE
                            or (@ENDDATE is null and ENDDATE is not null)
                            or (@ENDDATE is not null and ENDDATE is null));

          if @@ROWCOUNT = 1
                        set @INSTALLMENTSCHANGED = 1

                    --only update start date only if value changed
                    --TAY:  Per MR, reset STARTDATE on edit.
                    update dbo.REVENUESCHEDULE
                    set
                      SCHEDULESEEDDATE = @STARTDATE,
                        NEXTTRANSACTIONDATE = @STARTDATE,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @ID
                    and (SCHEDULESEEDDATE <> @STARTDATE or 
                    NEXTTRANSACTIONDATE <> @STARTDATE);

          if @@ROWCOUNT = 1
                    set @INSTALLMENTSCHANGED = 1

          --update installment begin date if user has changed the date and there are no paid installments exist.   
          update dbo.REVENUESCHEDULE
                    set
                        STARTDATE = @INSTALLMENTBEGINDATE,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @ID
                    and STARTDATE <> @INSTALLMENTBEGINDATE 
                    and not exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @ID and STATUSCODE =2);


                    if @@ROWCOUNT = 1
                    set @INSTALLMENTSCHANGED = 1 

                    if not exists(select 'x' from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @ID)
                    begin
                        exec dbo.USP_RECURRINGGIFT_FIXMISSINGINSTALLMENTSCHEDULE @ID, @CHANGEAGENTID, @CURRENTDATE
                    end
                    else if @INSTALLMENTSCHANGED = 1
                    begin
                        if @NEXTINSTALLMENTID is not null
                        begin
                            --Cache CONTEXT INFO
                            declare @contextCache varbinary(128);
                            set @contextCache = CONTEXT_INFO();
                            set CONTEXT_INFO @CHANGEAGENTID;

            --Multicurrency - Bug#800737 
            declare @BASEAMOUNT money;
            declare @ORGANIZATIONCURRENCYID uniqueidentifier;
            declare @ORGANIZATIONAMOUNT money;
            declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
            declare @BASETOORGANIZATIONEXCHANGERATE uniqueidentifier;
            declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
            declare @BASECURRENCYID uniqueidentifier;
            declare @TRANSACTIONCURRENCYID  uniqueidentifier;
            declare @BASEEXCHANGERATEID uniqueidentifier;
          select 
                @BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID)       
            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  @BASEEXCHANGERATEID = BASEEXCHANGERATEID,@TRANSACTIONCURRENCYID =TRANSACTIONCURRENCYID from REVENUE where REVENUE.ID = @ID;  

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



                            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;

                            -- fix date for semi-monthly
                            if @FREQUENCYCODE = 7
                            begin
                                if datepart(day, @STARTDATE) < 15
                                    set @STARTDATE = dateadd(day, (15 - datepart(day, @STARTDATE)), @STARTDATE);
                                else
                                begin
                                    set @STARTDATE = dateadd(month, 1, @STARTDATE)
                                    set @STARTDATE = dateadd(day, (-1 * datepart(day, @STARTDATE)) + 1, @STARTDATE);
                                end             
                            end

                            -- add next installment
              if(isnull(@USEEXISTINGCURRENCY,0) =0)
              begin             

                            insert into dbo.RECURRINGGIFTINSTALLMENT (ID, REVENUEID, AMOUNT, DATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values (newid(), @ID, @AMOUNT, @STARTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
                        end
              else 
              begin
                -- add next installment with multi currency symbol and exchange rates
                  insert into dbo.RECURRINGGIFTINSTALLMENT (ID, REVENUEID, AMOUNT, DATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID)
                  values (newid(), @ID, @BASEAMOUNT, @STARTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);
              end

                        end

                        -- add any additional necessary installments
                        exec dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS @ID, @CHANGEAGENTID, @CURRENTDATE, @AMOUNT

                        exec dbo.USP_RECURRINGGIFT_SETNEXTTRANSACTIONDATE @ID, @CHANGEAGENTID
                    end


             -- 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],
                                        SPLITS.[TYPECODE],
                                        case when SPLITS.[DECLINESGIFTAID] is null then REVENUESPLITGIFTAID.DECLINESGIFTAID else SPLITS.DECLINESGIFTAID end DECLINESGIFTAID
                                    from dbo.UFN_REVENUE_GETSPLITS_FROMITEMLISTXML(@SPLITS) SPLITS
                                    left join dbo.REVENUESPLITGIFTAID on SPLITS.ID = REVENUESPLITGIFTAID.ID
                                    for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64)

                    exec dbo.USP_REVENUE_GETSPLITS_UPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;
                    exec dbo.USP_REVENUE_UPDATESOLICITORS @ID, @CHANGEAGENTID, @CURRENTDATE;

                    if @SPLITSCHANGED = 1
                    begin
                        exec dbo.USP_REVENUE_CLEARANDADDCAMPAIGNS @REVENUEID = @ID, 
                            @CHANGEAGENTID = @CHANGEAGENTID,
                            @CHANGEDATE = @CURRENTDATE
                    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
                            select @CREDITTYPECODEID = CREDITCARD.CREDITTYPECODEID 
                            from dbo.REVENUE
                            inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                            left join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
                            left join dbo.CREDITCARD on CREDITCARD.ID = REVENUESCHEDULE.CREDITCARDID
                            where REVENUE.ID = @ID;

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

                        declare @APPEALID uniqueidentifier
                        select @APPEALID = APPEALID
                        from dbo.REVENUE 
                        where REVENUE.ID = @ID;

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

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

              return 0;

            END