USP_DATAFORMTEMPLATE_EDIT_PLEDGEADJUST_3

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGEADJUST_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,
                        @ADJUSTMENTDATE datetime,
                        @ADJUSTMENTPOSTDATE datetime,
                        @ADJUSTMENTREASON nvarchar(300)
                    )
                    as
                    begin
                        set nocount on;

                        declare @CURRENTDATE datetime;
                        declare @SUM money;
                        declare @COUNT int;
                        declare @ADJUST bit;
                        declare @ADJUSTMENTID uniqueidentifier;
                        declare @CLEARGLDISTRIBUTION bit;
                        declare @CLEARWRITEOFFGLDISTRIBUTION bit;
                        declare @WRITEOFFADJUSTMENTID uniqueidentifier;
                        declare @ADJUSTEDWRITEOFFS table(WRITEOFFID uniqueidentifier, ADJUSTMENTID uniqueidentifier);
                        declare @REACKNOWLEDGEFIELDCHANGED bit;    

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

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

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

                            /* Check if designations changed */
                            if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1
                            begin
                                set @ADJUST = 1;
                                set @CLEARGLDISTRIBUTION = 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 @ADJUST = 1;
                                    set @CLEARGLDISTRIBUTION = 1;
                                    set @CLEARWRITEOFFGLDISTRIBUTION = 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

                            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

                            /* If there was a change to GL related data log an adjustment */
                            if @ADJUST = 1
                            begin
                                exec dbo.USP_SAVE_ADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;
                            end

                            /* Adjust any write-offs for this pledge. */
                            if @CLEARWRITEOFFGLDISTRIBUTION = 1
                            begin
                                if exists (select GL.ID from dbo.WRITEOFFGLDISTRIBUTION GL inner join dbo.WRITEOFF on GL.WRITEOFFID = WRITEOFF.ID where WRITEOFF.REVENUEID = @ID) 
                                begin
                                    declare @WRITEOFFID uniqueidentifier;

                                    declare WRITEOFFCURSOR cursor local fast_forward for
                                    select WRITEOFF.ID from dbo.WRITEOFF where REVENUEID = @ID;

                                    open WRITEOFFCURSOR;
                                    fetch next from WRITEOFFCURSOR into @WRITEOFFID;

                                    while @@FETCH_STATUS = 0
                                    begin
                                        set @WRITEOFFADJUSTMENTID = null;

                                        exec dbo.USP_SAVE_WRITEOFFADJUSTMENT @WRITEOFFID, @WRITEOFFADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;

                                        --Save adjustment IDs for adjustment history

                                        insert into @ADJUSTEDWRITEOFFS(WRITEOFFID,ADJUSTMENTID) values (@WRITEOFFID,@WRITEOFFADJUSTMENTID);

                                        fetch next from WRITEOFFCURSOR into @WRITEOFFID;    
                                    end

                                    close WRITEOFFCURSOR;
                                    deallocate WRITEOFFCURSOR;
                                end
                            end

                            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 not exists (select 1 from dbo.REVENUEPOSTED where REVENUEPOSTED.ID = @ID)
                                raiserror('You cannot edit a unposted 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 @DONOTPOST bit;
                            set @DONOTPOST = case @POSTSTATUSCODE when 2 then 1 else 0 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.FINANCIALTRANSACTION
                            set
                                DATE = @DATE,
                                POSTSTATUSCODE = case when @DONOTPOST = 0 then 1 else 3 end,
                                POSTDATE = @POSTDATE,
                                TRANSACTIONAMOUNT = @AMOUNT,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where
                                ID = @ID;

              update
                dbo.REVENUE_EXT
              set
                SOURCECODE = @SOURCECODE,
                                APPEALID = @APPEALID,
                                BENEFITSWAIVED = @BENEFITSWAIVED,
                                GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
                                MAILINGID = @MAILINGID,
                                CHANNELCODEID = @CHANNELCODEID,
                                DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE
              where
                ID = @ID


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

                            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
                begin
                                    exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
                  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 @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);

                                exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID, @DATE, @CHANGEAGENTID, @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

                            if @SPLITSCHANGED = 1
                            begin
                                exec dbo.USP_PLEDGE_CLEARANDADDCAMPAIGNS @REVENUEID = @ID,
                                    @OPPORTUNITYID = @OPPORTUNITYID,
                                    @CHANGEAGENTID = @CHANGEAGENTID,
                                    @CHANGEDATE = @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;

                            -- Save the adjustment history for any write-offs

                            if (select count(*) from @ADJUSTEDWRITEOFFS) > 0
                            begin
                                declare @HISTORYWRITEOFFID uniqueidentifier;
                                declare @HISTORYADJUSTMENTID uniqueidentifier;

                                /* Cursor to use for logging history adjustments */
                                declare HISTORYCURSOR cursor local fast_forward for
                                select WRITEOFFID, ADJUSTMENTID from @ADJUSTEDWRITEOFFS

                                open HISTORYCURSOR;
                                fetch next from HISTORYCURSOR into @HISTORYWRITEOFFID, @HISTORYADJUSTMENTID;

                                while @@FETCH_STATUS = 0 
                                begin
                                    if @HISTORYADJUSTMENTID is not null
                                        exec dbo.USP_ADJUSTMENTHISTORY_WRITEOFF_SAVEHISTORY @HISTORYWRITEOFFID, @CHANGEAGENTID, null, @HISTORYADJUSTMENTID;

                                    fetch next from HISTORYCURSOR into @HISTORYWRITEOFFID, @HISTORYADJUSTMENTID;
                                end

                                close HISTORYCURSOR;
                                deallocate HISTORYCURSOR;
                            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