USP_DATAFORMTEMPLATE_EDIT_PLEDGEADJUST_2

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGEADJUST_2
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @DATE datetime,
                        @AMOUNT money,
                        @SENDPLEDGEREMINDER bit,
                        @SPLITS xml,
                        @FREQUENCYCODE tinyint,
                        @NUMBEROFINSTALLMENTS int,
                        @NEXTTRANSACTIONDATE datetime,
                        @INSTALLMENTS xml,
                        @ADJUSTMENTDATE datetime,
                        @ADJUSTMENTPOSTDATE datetime,
                        @ADJUSTMENTREASON nvarchar(300),
                        @SOURCECODE nvarchar(50),
                        @APPEALID uniqueidentifier,
                        @BENEFITS xml,
                        @BENEFITSWAIVED bit,
                        @GIVENANONYMOUSLY bit,
                        @MAILINGID uniqueidentifier,
                        @CHANNELCODEID uniqueidentifier,
                        @DONOTACKNOWLEDGE bit,
                        @PLEDGESUBTYPEID uniqueidentifier,
                        @OPPORTUNITYID uniqueidentifier

                    )
                    as
                    set nocount on;

                    declare @CURRENTDATE datetime;
                    declare @SUM money;
                    declare @COUNT int;                    
                    declare @ADJUST bit
                    declare @ADJUSTMENTID uniqueidentifier;
                    declare @AMOUNTORSPLITSCHANGED bit;
                    declare @CLEARWRITEOFFDISTRIBUTION bit;

                    set @CURRENTDATE = GetDate();
                    set @AMOUNTORSPLITSCHANGED = 0;
                    set @CLEARWRITEOFFDISTRIBUTION = 0;

                    declare @contextCache varbinary(128);

                    --cache current context information

                    set @contextCache = CONTEXT_INFO();

                    --set CONTEXT_INFO to @CHANGEAGENTID

                    set CONTEXT_INFO @CHANGEAGENTID;

                    begin try                            
                        set @ADJUST = 0
                        set @ADJUSTMENTID = null;

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

                        /* Check if designations changed */
                        if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1
                        begin
                            set @ADJUST = 1;
                            set @AMOUNTORSPLITSCHANGED = 1;
                            set @CLEARWRITEOFFDISTRIBUTION = 1;
                        end

                        /* Check if amount changed */
                        if @ADJUST = 0
                            if (select AMOUNT from dbo.REVENUE where ID = @ID) <> @AMOUNT
                            begin
                                set @ADJUST = 1;
                                set @AMOUNTORSPLITSCHANGED = 1;
                            end

                        /* Already adjusted */
                        if @ADJUST = 0
                            if (select COUNT(ADJUSTMENT.ID) from dbo.ADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE = 1) > 0
                                set @ADJUST = 1

                        /* If there was a change to GL related data log an adjustment */
                        if @ADJUST = 1
                        begin
                            -- do not allow the pledge amount to be adjusted less than the applied tribute amount

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

                            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

                            exec dbo.USP_SAVE_ADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;

                        end

                        exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, 1;

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

                        if @COUNT = 0 
                            raiserror('Please enter at least one installment.',13,1);

                        if @SUM <> @AMOUNT
                            raiserror('The sum of the installment amounts must equal the pledge amount.',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);


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

                        if (coalesce((select top 1 REACKNOWLEDGEREVENUE from dbo.ACKNOWLEDGEMENTPREFERENCE), 0)) = 1
                        begin                               
                            -- if a field has changed, mark the revenue letters for this record out of date, if necessary

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

                        end

                        update 
                            dbo.REVENUE
                        set
                            DATE = @DATE,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where 
                            ID = @ID;

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

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

                        exec dbo.USP_REVENUE_GETSPLITS_UPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;        
                        exec dbo.USP_PLEDGE_UPDATEINSTALLMENT @ID, @INSTALLMENTS, @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;

                        if @OPPORTUNITYID is null
                            delete REVENUEOPPORTUNITY
                            from dbo.REVENUEOPPORTUNITY 
                            inner join dbo.REVENUESPLIT
                                on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID
                            where REVENUESPLIT.REVENUEID = @ID
                        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 (0, 1, 2);
                        end

                        exec dbo.USP_PLEDGE_VALIDATE @ID;

                        -- if the designations or amount has changed, clear any unposted user-defined gl distributions for this revenue record and add new distributions

                        if @AMOUNTORSPLITSCHANGED = 1
                        begin

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

                            -- Add new GL distributions

                            exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;

                            --TODO: what to do with writeoff distributions when the amount or splits on the pledge change?

                            --if @CLEARWRITEOFFDISTRIBUTION = 1 

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

                                --exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @ID;

                            --end

                            declare @DEPOSITID uniqueidentifier;
              select @DEPOSITID = DEPOSITID
              from dbo.BANKACCOUNTDEPOSITPAYMENT
              where ID = @ID;
              if @DEPOSITID is not null
                exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @ID, @DEPOSITID, @CHANGEAGENTID, @CURRENTDATE;
                        end


                        if @ADJUST = 1 /*call USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY after the revenue tables are updated */
                            exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @ID, @CHANGEAGENTID, null, @ADJUSTMENTID;
                    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;