USP_DATAFORMTEMPLATE_EDIT_PLEDGE_3

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGE_3
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @DATE datetime,
                        @AMOUNT money,
                        @POSTSTATUSCODE tinyint,
                        @POSTDATE datetime,
                        @SENDPLEDGEREMINDER bit,
                        @SPLITS xml,
                        @FREQUENCYCODE tinyint,
                        @NUMBEROFINSTALLMENTS int,
                        @NEXTTRANSACTIONDATE datetime,
                        @INSTALLMENTS xml,
                        @SOURCECODE nvarchar(50),
                        @APPEALID uniqueidentifier,
                        @BENEFITS xml,
                        @BENEFITSWAIVED bit,
                        @GIVENANONYMOUSLY bit,
                        @MAILINGID uniqueidentifier,
                        @CHANNELCODEID uniqueidentifier,
                        @DONOTACKNOWLEDGE bit,
                        @PLEDGESUBTYPEID uniqueidentifier,
                        @OPPORTUNITYID uniqueidentifier,
                        @REFERENCE nvarchar(255),
                        @CATEGORYCODEID uniqueidentifier
                    )
                    as
                    begin
                        set nocount on;

                        declare @CURRENTDATE datetime;
                        declare @SUM money;
                        declare @COUNT int;

                        declare @contextCache varbinary(128);

                        --cache current context information
                        set @contextCache = CONTEXT_INFO();

                        --set CONTEXT_INFO to @CHANGEAGENTID
                        set CONTEXT_INFO @CHANGEAGENTID;

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

                            set @CURRENTDATE = GetDate();

                            exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT;

                            select 
                                @SUM = sum(AMOUNT),
                                @COUNT = count(AMOUNT)
                            from 
                                dbo.UFN_INSTALLMENT_GETINSTALLMENTS_FROMITEMLISTXML(@INSTALLMENTS);

                            if @COUNT = 0
                                raiserror('INSTALLMENTCOUNT',13,1);

                            if @SUM <> @AMOUNT
                                raiserror('INSTALLMENTSUM',13,1);

                            /* You can only edit unposted pledges here */
                            if exists (select 1 from dbo.REVENUEPOSTED where REVENUEPOSTED.ID = @ID)
                                raiserror('You cannot edit a posted pledge.', 13, 1)

                            if dbo.UFN_INSTALLMENT_DATESOVERLAP(@INSTALLMENTS) = 1
                                raiserror('Installment dates are out of sequence.',13,1);

                            if (select count(INSTALLMENT.ID) from dbo.INSTALLMENT 
                                inner join dbo.UFN_INSTALLMENT_GETINSTALLMENTS_FROMITEMLISTXML(@INSTALLMENTS) XMLINST
                                on INSTALLMENT.ID = XMLINST.ID
                                where XMLINST.AMOUNT < XMLINST.APPLIED) > 0
                                raiserror('PLEDGEPAYMENT_INSTALLMENTAPPLIED',13,1);

                            declare @TRIBUTEAMOUNT money;
                            select @TRIBUTEAMOUNT = sum(AMOUNT) from dbo.REVENUETRIBUTE where REVENUEID = @ID;

                            -- do not allow the gift amount to be adjusted less than the applied tribute amount
                            if (@TRIBUTEAMOUNT is not null) and (@AMOUNT < @TRIBUTEAMOUNT)
                            begin
                                raiserror('The pledge amount cannot be less than the sum of the tribute amounts applied to this pledge.', 13, 1)
                            end

                            declare @DONOTPOST bit;
                            set @DONOTPOST = case @POSTSTATUSCODE when 2 then 1 else 0 end;

                            declare @REACKNOWLEDGEFIELDCHANGED bit;    
                            declare @CLEARGLDISTRIBUTION bit;
                            declare @CLEARWRITEOFFGLDISTRIBUTION bit;

                            set @REACKNOWLEDGEFIELDCHANGED = 0;    
                            set @CLEARGLDISTRIBUTION = 0;
                            set @CLEARWRITEOFFGLDISTRIBUTION = 0;

                            -- check to see if designations have changed
                            if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1
                            begin
                                set @CLEARGLDISTRIBUTION = 1;
                                set @CLEARWRITEOFFGLDISTRIBUTION = 1;
                                set @REACKNOWLEDGEFIELDCHANGED = 1;
                            end    

                            declare @OLDCATEGORYCODEID uniqueidentifier
                            select top 1 @OLDCATEGORYCODEID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
                            from dbo.REVENUECATEGORY
                            inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID
                            where REVENUESPLIT.REVENUEID = @ID

                            /* Check if revenue category changed */
                            if (@CATEGORYCODEID is null and @OLDCATEGORYCODEID is not null) 
                            or (@CATEGORYCODEID is not null and @OLDCATEGORYCODEID is null)
                            or (@CATEGORYCODEID <> @OLDCATEGORYCODEID)
                            begin
                                set @CLEARGLDISTRIBUTION = 1;
                                set @CLEARWRITEOFFGLDISTRIBUTION = 1;
                            end

                            -- check to see if amount, postdate, or post status has changed
                            if @CLEARGLDISTRIBUTION = 0
                                if (
                                    select count(REVENUE.ID) from dbo.REVENUE 
                                    where REVENUE.ID = @ID 
                                    and AMOUNT = @AMOUNT
                                    and POSTDATE = @POSTDATE 
                                    and ((@POSTSTATUSCODE = 2 and DONOTPOST = 1) or (@POSTSTATUSCODE = 1 and DONOTPOST = 0))
                                ) = 0 
                                begin
                                    set @CLEARGLDISTRIBUTION = 1;
                                end                                                    


                            -- check to see if the revenue record needs to be re-acknowledged                            
                            if (coalesce((select top 1 REACKNOWLEDGEREVENUE from dbo.ACKNOWLEDGEMENTPREFERENCE), 0)) = 1
                            begin                                    
                                -- check to see if amount have changed
                                if @REACKNOWLEDGEFIELDCHANGED = 0
                                    if (select count(REVENUE.ID) from dbo.REVENUE where REVENUE.ID = @ID and AMOUNT = @AMOUNT) = 0
                                        set @REACKNOWLEDGEFIELDCHANGED = 1;

                                -- if a field has changed, mark the revenue letters for this record out of date, if necessary
                                if @REACKNOWLEDGEFIELDCHANGED = 1
                                    exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID, @CHANGEAGENTID;     
                            end

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

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

                            if exists (select ID from dbo.REVENUEPOSTED where ID = @ID) begin
                                if @POSTSTATUSCODE <> 0
                                    delete dbo.REVENUEPOSTED where ID = @ID;
                            end
                            else begin
                                if @POSTSTATUSCODE = 0
                                    insert into dbo.REVENUEPOSTED(ID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                                        values(@ID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
                            end

                            update
                                dbo.REVENUESCHEDULE
                            set
                                FREQUENCYCODE = @FREQUENCYCODE,
                                NUMBEROFINSTALLMENTS = @COUNT,
                                NEXTTRANSACTIONDATE = @NEXTTRANSACTIONDATE,
                                PLEDGESUBTYPEID = @PLEDGESUBTYPEID,
                                SENDPLEDGEREMINDER = @SENDPLEDGEREMINDER,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            from 
                                dbo.REVENUESCHEDULE 
                            where
                                REVENUESCHEDULE.ID = @ID;

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

                            -- 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_PLEDGE_UPDATEINSTALLMENT @ID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE

                            exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @ID, @CATEGORYCODEID, @CHANGEAGENTID, @CURRENTDATE;

                            --MMR not sure we still need this?
                            exec dbo.USP_PLEDGE_FIXDEPENDENTSPLITS @ID, @CHANGEAGENTID, @CURRENTDATE; 
                            exec dbo.USP_REVENUE_GETBENEFITS_UPDATEFROMXML @ID, @BENEFITS, @CHANGEAGENTID, @CURRENTDATE;
                            exec dbo.USP_REVENUE_UPDATESOLICITORS @ID, @CHANGEAGENTID, @CURRENTDATE;                            

                            -- clear the user-defined gl distributions
                            if @CLEARGLDISTRIBUTION = 1
                            begin

                                -- Clear GL
                                delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;    

                                -- Add new GL distributions
                                if @POSTSTATUSCODE <> 2
                                    exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;

                                if @CLEARWRITEOFFGLDISTRIBUTION = 1 and exists (select GL.ID from dbo.WRITEOFFGLDISTRIBUTION GL inner join dbo.WRITEOFF on GL.WRITEOFFID = WRITEOFF.ID where WRITEOFF.REVENUEID = @ID) 
                                begin
                                    delete from dbo.WRITEOFFGLDISTRIBUTION where WRITEOFFID in (select WO.ID from dbo.WRITEOFF WO where WO.REVENUEID = @ID) and OUTDATED = 0;

                                    -- Add new writeoff GL distributions
                                    if @POSTSTATUSCODE <> 2
                                        exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
                                end

                            end

                            exec dbo.USP_PLEDGE_VALIDATE @ID;

                            if @OPPORTUNITYID is null
                            begin
                                delete REVENUEOPPORTUNITY
                                from dbo.REVENUEOPPORTUNITY 
                                inner join dbo.REVENUESPLIT
                                    on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID
                                where REVENUESPLIT.REVENUEID = @ID                                
                            end
                            else 
                            begin
                                insert into dbo.REVENUEOPPORTUNITY (ID, OPPORTUNITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                    select ID, @OPPORTUNITYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                                    from dbo.REVENUESPLIT where REVENUEID = @ID
                                    and not exists(select top 1 REVENUEOPPORTUNITY.ID from REVENUEOPPORTUNITY where REVENUEOPPORTUNITY.ID = REVENUESPLIT.ID);

                                update
                                    dbo.OPPORTUNITY
                                set
                                    STATUSCODE = 3, -- accepted
                                    ASKDATE = case when ASKDATE is null then dbo.UFN_DATE_GETEARLIESTTIME(getdate()) else ASKDATE end,
                                    RESPONSEDATE = case when RESPONSEDATE is null then dbo.UFN_DATE_GETEARLIESTTIME(getdate()) else RESPONSEDATE end,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = getdate()
                                where
                                    ID = @OPPORTUNITYID
                                and
                                    STATUSCODE in (4, 5);
                            end

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

                            --Gift Aid is for UK only
                            if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
                            begin
                                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, 1, @SPLITSDECLININGGIFTAID; --revenue transaction type code of pledge is 1
                            end
                        end try

                        begin catch
                            --reset CONTEXT_INFO to previous value
                            if not @contextCache is null
                              set CONTEXT_INFO @contextCache;

                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        --reset CONTEXT_INFO to previous value
                        if not @contextCache is null
                          set CONTEXT_INFO @contextCache;

                        return 0;
                    end