USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFT_2

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFT_2
                (
                    @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
                )

                as

                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
                    if @FINDERNUMBER is null
                        set @FINDERNUMBER = 0;

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

                    /*
                    CR283444-091907 AV the schedule should be able to use any start date for semi-monthly
                    The change is also to keep pledge and rec. gift in sync.
                    --if semi-monthly ensure nexttransaction date starts on 1st or 15th
                    if @FREQUENCYCODE = 7 and not (day(@STARTDATE) = 1 or day(@STARTDATE) = 15)
                        raiserror('RECURRINGEDIT_ERR_SEMI_MONTHLY_DATE', 13, 1)
                    */
                    exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS=@SPLITS, @REVENUEAMOUNT=@AMOUNT, @REVENUEID=@ID;

                    /*
                    CR240045-040406 MMR
                    Removing this limitation for now 
                    --next transaction date cannot be before activity
                    select @MAXACTIVITYDATE = max(SCHEDULEDATE) from RECURRINGGIFTACTIVITY
                        where SOURCEREVENUEID = @ID;

                    if coalesce(@MAXACTIVITYDATE, @STARTDATE) > @STARTDATE
                    begin
                        set @ERROR = 'The schedule cannot start before the last activity date (' +  convert(varchar(10),@MAXACTIVITYDATE, 101) + ').'
                        raiserror(@ERROR, 13, 1)
                    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.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

                        --Update Revenue

                        update dbo.REVENUE
                        set
                            DATE = @DATE,
                            AMOUNT = @AMOUNT,
                            RECEIPTAMOUNT = @AMOUNT,
                            FINDERNUMBER = @FINDERNUMBER,
                            SOURCECODE = @SOURCECODE,
                            APPEALID = @APPEALID,
                            GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
                            MAILINGID = @MAILINGID,
                            CHANNELCODEID = @CHANNELCODEID,
                            DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @ID;

                        exec dbo.USP_REVENUEREFERENCE_EDIT @ID, @REFERENCE, @CHANGEAGENTID;

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

                        --only update start date only if value changed

                        update dbo.REVENUESCHEDULE
                        set
                            STARTDATE = case when STARTDATE = NEXTTRANSACTIONDATE then @STARTDATE else STARTDATE end,
                            NEXTTRANSACTIONDATE = @STARTDATE,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @ID
                            and (STARTDATE <> @STARTDATE
                                or (@STARTDATE is null and STARTDATE is not null)
                                or (@STARTDATE is not null and STARTDATE is null));

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

                        exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @ID, @CATEGORYCODEID, @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)

                            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;