USP_DATAFORMTEMPLATE_EDIT_PLANNEDGIFTREVENUE_4

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLANNEDGIFTREVENUE_4
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @DATE datetime,
                        @AMOUNT money,
                        @POSTSTATUSCODE tinyint,
                        @POSTDATE datetime,
                        @SPLITS 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,
                        @PERCENTAGEBENEFITS xml,
                        @BASECURRENCYID uniqueidentifier,
                        @TRANSACTIONCURRENCYID uniqueidentifier,
                        @BASEEXCHANGERATEID uniqueidentifier,
                        @EXCHANGERATE decimal(20,8),
                        @HADSPOTRATE bit,
                        @RATECHANGED bit,
                        @CURRENTAPPUSERID uniqueidentifier = null,
                        @DONOTRECEIPT bit,
                        @RECEIPTAMOUNT money,
                        @UPDATESOLICITORS bit
                    )
                    as
                    begin
                        set nocount on;

                        declare @CURRENTDATE datetime;

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

                            --Business units - Calculate and store business unit ratios for all new splits given

                            --the old splits.

                            declare @BUSINESSUNITSRATIO table (
                                DESIGNATIONID uniqueidentifier
                                ,OVERRIDEBUSINESSUNITS bit
                                ,REASON uniqueidentifier
                                ,BUSINESSUNITCODEID uniqueidentifier
                                ,RATIO float
                                )

                            insert into @BUSINESSUNITSRATIO
                            select REVENUESPLIT_EXT.DESIGNATIONID
                                ,REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS
                                ,REVENUESPLIT_EXT.REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON
                                ,REVENUESPLITBUSINESSUNIT.BUSINESSUNITCODEID
                                ,isnull(REVENUESPLITBUSINESSUNIT.AMOUNT / nullif(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT, 0), 0) as RATIO
                            from dbo.FINANCIALTRANSACTIONLINEITEM
                            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                            inner join dbo.REVENUESPLITBUSINESSUNIT on REVENUESPLITBUSINESSUNIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID


                            set @RECEIPTAMOUNT = coalesce(@RECEIPTAMOUNT, @AMOUNT); --For backwards compatibility with old behavior


                declare @PLANNEDGIFTID uniqueidentifier;
                declare @PLANNEDGIFTADDITIONID uniqueidentifier;
                declare @OLDAMOUNT money;
                declare @OLDDATE datetime;
                declare @SPLITSCHANGED bit;
                declare @DESIGNATIONSCHANGED bit;
                declare @OLDTRANSACTIONAMOUNT money;
                declare @OLDDONOTRECEIPT bit;
                declare @OLDRECEIPTAMOUNT money;

                select @PLANNEDGIFTID = PLANNEDGIFTID, @PLANNEDGIFTADDITIONID = PLANNEDGIFTADDITIONID
                from dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS
                where REVENUEID = @ID

                select
                @OLDAMOUNT = AMOUNT,
                @OLDDATE = DATE,
                @OLDTRANSACTIONAMOUNT = TRANSACTIONAMOUNT,
                @OLDDONOTRECEIPT = DONOTRECEIPT,
                @OLDRECEIPTAMOUNT = RECEIPTAMOUNT
                from dbo.REVENUE where ID = @ID

                set @SPLITSCHANGED = dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS);
                set @DESIGNATIONSCHANGED = dbo.UFN_CHECKDETAIL_DESIGNATIONSCHANGED(@ID, @SPLITS);

                            --Multicurrency - AdamBu 5/21/10 - If the revenue previously used a spot rate, but

                            --    its rate has changed, store the old rate's ID, so we can remove it later.

                            declare @OLDSPOTRATE uniqueidentifier
                            if @HADSPOTRATE = 1 and @RATECHANGED = 1
                            begin
                                select 
                                    @OLDSPOTRATE = BASEEXCHANGERATEID
                                from dbo.REVENUE
                                where ID = @ID
                            end
                            --If the record uses a new spot rate, create it and set the rate ID.

                            if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
                            begin

                                set @BASEEXCHANGERATEID = newid()

                                --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future

                                /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
                                    and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                                begin
                                    raiserror('User does not have the right to add a new spot rate.', 13, 1);
                                    return 1;
                                end*/

                                insert into dbo.CURRENCYEXCHANGERATE(
                                    ID, 
                                    FROMCURRENCYID,
                                    TOCURRENCYID,
                                    RATE,
                                    ASOFDATE,
                                    TYPECODE,
                                    SOURCECODEID,
                                    ADDEDBYID, 
                                    CHANGEDBYID, 
                                    DATEADDED, 
                                    DATECHANGED
                                )
                                values(
                                    @BASEEXCHANGERATEID,
                                    @TRANSACTIONCURRENCYID,
                                    @BASECURRENCYID,
                                    @EXCHANGERATE,
                                    @DATE,
                                    2,
                                    null,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                );
                            end

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

                            declare @PLANNEDGIFTPAYOUTSPLITS xml;
                            set @PLANNEDGIFTPAYOUTSPLITS = (
                                select 
                                    AMOUNT, DESIGNATIONID, TRANSACTIONCURRENCYID
                                from
                                    dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS)
                                    for xml raw('ITEM'),type,elements,root('DESIGNATION'),binary base64
                            )

                            /* You can only edit unposted planned gifts here */
                            if (
                                select count(REVENUE.ID) 
                                from dbo.REVENUE
                                    inner join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                                where REVENUE.ID = @ID
                            ) > 0
                                raiserror('You cannot edit a posted planned gift.', 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 planned gift amount cannot be less than the sum of the tribute amounts applied to this planned gift.', 13, 1)
                            end


                            --Multicurrency - AdamBu 5/21/10 - Retrieve and calculate the necessary multicurrency values.    

                            declare @BASEAMOUNT money;                    
                            declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                            declare @ORGANIZATIONAMOUNT money;
                            declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

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


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

                            declare @CLEARGLDISTRIBUTION bit;
                            set @CLEARGLDISTRIBUTION = 0;

                            if @SPLITSCHANGED = 1
                                set @CLEARGLDISTRIBUTION = 1;


                            -- check to see if amounts, postdate, post status, or exchange rates have changed

                            -- changing AppealID can change the calculated account number

                            if @CLEARGLDISTRIBUTION = 0
                                if (
                                select count(REVENUE.ID) from dbo.REVENUE 
                                where REVENUE.ID = @ID 
                                    and TRANSACTIONAMOUNT = @AMOUNT
                                    and POSTDATE = @POSTDATE 
                                    and ((@POSTSTATUSCODE = 2 and DONOTPOST = 1) or (@POSTSTATUSCODE = 1 and DONOTPOST = 0))
                                    and isnull(APPEALID,'00000000-0000-0000-0000-000000000000') = isnull(@APPEALID,'00000000-0000-0000-0000-000000000000') 
                                    and AMOUNT = @BASEAMOUNT
                                    and ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT
                                    and BASEEXCHANGERATEID = @BASEEXCHANGERATEID
                                    and ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
                                    and TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
                                    and BASECURRENCYID = @BASECURRENCYID
                                ) = 0 
                                begin
                                set @CLEARGLDISTRIBUTION = 1;
                                end

                            --Check to see if revenue category changed.

                            if @CLEARGLDISTRIBUTION = 0
                            begin
                                if (@SPLITS.exist('(/SPLITS/ITEM/CATEGORYCODEID)') = 0)
                                    begin
                                        if (exists (
                                            select 
                                                REVENUECATEGORY.ID
                                            from dbo.REVENUECATEGORY
                                                left outer join dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) as SPLITS on REVENUECATEGORY.ID = SPLITS.ID
                                            where ((REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID <> SPLITS.CATEGORYCODEID) or
                                                ((REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID is null) and (SPLITS.CATEGORYCODEID is not null)) or
                                                ((REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID is not null) and (SPLITS.CATEGORYCODEID is null)))))
                                        begin
                                            set @CLEARGLDISTRIBUTION = 1;
                                        end
                                    end

                                    else if (exists (
                                            select 
                                                REVENUECATEGORY.ID
                                            from 
                                                dbo.REVENUECATEGORY
                                                inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID
                                            where (REVENUESPLIT.REVENUEID = @ID) and
                                                ((REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID <> @CATEGORYCODEID) or
                                                ((REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID is null) and (@CATEGORYCODEID is not null)) or
                                                ((REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID is not null) and (@CATEGORYCODEID is null)))))
                                        begin
                                            set @CLEARGLDISTRIBUTION = 1;
                                        end
                            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
                                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 dbo.REVENUE
                            set
                                DATE = @DATE,
                                DONOTPOST = @DONOTPOST,
                                POSTDATE = @POSTDATE,
                                AMOUNT = @BASEAMOUNT,
                                SOURCECODE = @SOURCECODE,
                                APPEALID = @APPEALID,
                                BENEFITSWAIVED = @BENEFITSWAIVED,
                                GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
                                MAILINGID = @MAILINGID,
                                CHANNELCODEID = @CHANNELCODEID,
                                DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
                                RECEIPTAMOUNT = @RECEIPTAMOUNT,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE,
                                BASECURRENCYID = @BASECURRENCYID,
                                ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
                                TRANSACTIONAMOUNT = @AMOUNT,
                                BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                                ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                                DONOTRECEIPT = @DONOTRECEIPT
                            where ID = @ID;

                            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.INSTALLMENT
                            set 
                                AMOUNT = @BASEAMOUNT,
                                DATE = @DATE,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE,
                                BASECURRENCYID = @BASECURRENCYID,
                                ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
                                TRANSACTIONAMOUNT = @AMOUNT,
                                BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                                ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
                            where REVENUEID = @ID;

                            --Multicurrency - AdamBu 5/21/10 - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.

                            set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
                            exec dbo.USP_REVENUE_GETSPLITS_2_CUSTOMUPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;

                            declare @PAYOUTAMOUNT money;


                            if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7') = 1 
                                and @PLANNEDGIFTADDITIONID is null -- Planned gift additions do not have payout entrees

                                and not exists(select top 1 1 from dbo.PLANNEDGIFTPAYOUT where REVENUEID = @ID)
                            begin
                                declare @ISANONYMOUS bit;
                                declare @VEHICLECODE smallint;
                                declare @GENERATEGLDISTRIBUTIONS bit = case when @DONOTPOST = 1 then 0 else 1 end;
                                select
                                    @ISANONYMOUS = ISANONYMOUS, 
                                    @VEHICLECODE = VEHICLECODE,
                                    @PAYOUTAMOUNT = case VEHICLECODE when 6 then TRANSACTIONLIFEINSURANCEPREMIUM else TRANSACTIONPAYOUTAMOUNT end
                                from dbo.PLANNEDGIFT where ID = @PLANNEDGIFTID;      

                                if (@VEHICLECODE = 0 or @VEHICLECODE = 5 or @VEHICLECODE = 6)
                                    exec USP_ADDPLANNEDGIFTPAYOUT @PLANNEDGIFTID, @PAYOUTAMOUNT, @DATE, @AMOUNT, @CHANGEAGENTID, @ISANONYMOUS, 
                                        @ID, @GENERATEGLDISTRIBUTIONS, @TRANSACTIONCURRENCYID, @BASECURRENCYID, @BASEEXCHANGERATEID;
                            end

                            if @PAYOUTAMOUNT is null
                            begin
                                select 
                                    @PAYOUTAMOUNT = PLANNEDGIFT.TRANSACTIONPAYOUTAMOUNT
                                from
                                    dbo.PLANNEDGIFTPAYOUT
                                    inner join dbo.PLANNEDGIFT on PLANNEDGIFTPAYOUT.ID = PLANNEDGIFT.ID
                                where
                                    PLANNEDGIFTPAYOUT.REVENUEID = @ID;
                            end

                            declare @PGVEHICLECODE tinyint;
                            select @PGVEHICLECODE = VEHICLECODE from dbo.PLANNEDGIFT where ID = @PLANNEDGIFTID;

                            if @PAYOUTAMOUNT is not null and (@PGVEHICLECODE = 0 or @PGVEHICLECODE = 5 or @PGVEHICLECODE = 6)
                            begin
                                --Need to prorate splits.

                                set @PLANNEDGIFTPAYOUTSPLITS = dbo.UFN_PLANNEDGIFTGETSPLITS_XML(@AMOUNT,@PAYOUTAMOUNT,@PLANNEDGIFTPAYOUTSPLITS);            
                                set @PLANNEDGIFTPAYOUTSPLITS = dbo.UFN_PLANNEDGIFTDESIGNATION_CONVERTAMOUNTSINXML(@PLANNEDGIFTPAYOUTSPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);

                                --The payout splits need to match the splits on the revenue.

                                exec dbo.USP_PLANNEDGIFT_GETPAYOUTSPLITS_UPDATEFROMXML @ID, @PLANNEDGIFTPAYOUTSPLITS, @CHANGEAGENTID, @CURRENTDATE;
                            end

                            -- update benefits

                            declare @TOTALBENEFITS xml;
                            set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);
                            set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2(@TOTALBENEFITS,@TRANSACTIONCURRENCYID,@BASECURRENCYID);
                            exec dbo.USP_REVENUE_GETBENEFITS_4_UPDATEFROMXML @ID, @TOTALBENEFITS, @CHANGEAGENTID, @CURRENTDATE, null;

                            -- Delete/Re-default solicitors if prompt returns yes, still update amounts if greater than new total revenue amount - WI 470535 Josh Jones

                            if @UPDATESOLICITORS = 1
                            begin
                                delete REVENUESOLICITOR
                                from dbo.REVENUESOLICITOR
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESOLICITOR.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID

                                exec dbo.USP_PLANNEDGIFTREVENUE_DEFAULTSOLICITORS @PLANNEDGIFTID, @ID, @CHANGEAGENTID, @CURRENTDATE;
                            end
                            else
                            begin
                                update REVENUESOLICITOR
                                set REVENUESOLICITOR.AMOUNT = @AMOUNT
                                from dbo.REVENUESOLICITOR
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESOLICITOR.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                                where REVENUESOLICITOR.AMOUNT > @AMOUNT
                                and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
                            end

                            --assume one installment

                            declare @INSTALLMENTSPLITS xml;
                            set @INSTALLMENTSPLITS = (
                                select distinct
                                    INSTALLMENTSPLIT.ID, 
                                    INSTALLMENT.ID INSTALLMENTID, 
                                    REVENUESPLIT.DESIGNATIONID, 
                                    REVENUESPLIT.TRANSACTIONAMOUNT AMOUNT,
                                    REVENUESPLIT.ID as REVENUESPLITID
                                from dbo.REVENUESPLIT
                                    inner join dbo.INSTALLMENT on INSTALLMENT.REVENUEID = REVENUESPLIT.REVENUEID
                                    left outer join dbo.INSTALLMENTSPLIT 
                                        on REVENUESPLIT.ID = INSTALLMENTSPLIT.REVENUESPLITID  and REVENUESPLIT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
                                where REVENUESPLIT.REVENUEID = @ID
                                for xml raw('ITEM'),type,elements,root('INSTALLMENTSPLITS'),BINARY BASE64
                            );

                            set @INSTALLMENTSPLITS = dbo.UFN_INSTALLMENTSPLIT_CONVERTAMOUNTSINXML(@INSTALLMENTSPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
                            exec dbo.USP_PLEDGE_GETINSTALLMENTSPLITS_2_UPDATEFROMXML @ID, @INSTALLMENTSPLITS, @CHANGEAGENTID, @CURRENTDATE;                        

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

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

                            if @OPPORTUNITYID is null
                                exec dbo.USP_RECORDOPERATION_REVENUEOPPORTUNITYUNLINK @ID, @CHANGEAGENTID;
                            else 
                            begin
                                exec dbo.USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY @ID, @OPPORTUNITYID, @CHANGEAGENTID, @CURRENTDATE

                                if not exists (select top 1 O.ID from dbo.REVENUEOPPORTUNITY O inner join dbo.FINANCIALTRANSACTIONLINEITEM I on O.ID = I.ID where I.FINANCIALTRANSACTIONID = @ID)
                                    exec dbo.USP_DATAFORMTEMPLATE_ADD_REVENUEOPPORTUNITYLINK @REVENUEID = @ID, @OPPORTUNITYID = @OPPORTUNITYID;
                                else
                                    exec dbo.USP_REVENUEOPPORTUNITY_UPDATEOPPORTUNITY @REVENUEID = @ID, @OPPORTUNITYID = @OPPORTUNITYID;

                                exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID, @DATE, @CHANGEAGENTID, @CURRENTDATE
                            end

                            -- update campaigns

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

                            -- clear the user-defined gl distributions

                            if @CLEARGLDISTRIBUTION = 1
                            begin
                                --Clear GL

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

                                -- Add new GL distributions

                                if @POSTSTATUSCODE <> 2
                                    exec dbo.USP_PLANNEDGIFTREVENUE_GENERATEGLDISTRIBUTIONS @ID, @CHANGEAGENTID;
                            end

                            update dbo.FINANCIALTRANSACTION
                                set 
                                POSTDATE = @POSTDATE
                                ,POSTSTATUSCODE =  case @POSTSTATUSCODE when 0 then 2 when 2 then 3 else 1 end 
                                ,CHANGEDBYID = @CHANGEAGENTID
                                ,DATECHANGED = @CURRENTDATE
                            where TYPECODE = 26 and POSTSTATUSCODE != 2 and PARENTID = @ID;

                            update T2
                                set 
                                T2.POSTDATE = @POSTDATE
                                ,T2.POSTSTATUSCODE =  case @POSTSTATUSCODE when 0 then 2 when 2 then 3 else 1 end 
                                ,T2.CHANGEDBYID = @CHANGEAGENTID
                                ,T2.DATECHANGED = @CURRENTDATE
                            from dbo.FINANCIALTRANSACTION T1 
                            join dbo.FINANCIALTRANSACTIONLINEITEM T2 on T1.ID = T2.FINANCIALTRANSACTIONID 
                            where T1.TYPECODE = 26 and T2.TYPECODE in (0,98) and T2.POSTSTATUSCODE != 2 and T1.PARENTID = @ID;




                            if dbo.UFN_PLEDGEPAYMENT_DESIGNATIONSBALANCE(@ID) = 0
                                raiserror('PLEDGEPAYMENT_DESIGNATIONSBALANCE', 13, 10);

                            --Multicurrency - AdamBu 5/21/10 - If we stored an old spot rate earlier, now is the time to

                            --    remove it.

                            if @OLDSPOTRATE is not null
                            begin
                                delete CURRENCYEXCHANGERATE
                                where ID=@OLDSPOTRATE
                            end

                            -- If the rate changed this could cause a recalculation of the payment distributions.

                            if @RATECHANGED = 1
                                exec USP_EDITPLANNEDGIFTREVENUE_RECREATEPAYMENTGLDISTRIBUTIONS @ID, @CHANGEAGENTID

                            exec dbo.USP_PLANNEDGIFTRECONCILE_SAVE
                                @PLANNEDGIFTID,
                                @ID,
                                @PLANNEDGIFTADDITIONID,
                                @OLDAMOUNT,
                                @BASEAMOUNT,
                                @OLDDATE,
                                @DATE,
                                @DESIGNATIONSCHANGED,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @OLDTRANSACTIONAMOUNT,
                                @AMOUNT,
                                @OLDDONOTRECEIPT,
                                @DONOTRECEIPT,
                                @OLDRECEIPTAMOUNT,
                                @RECEIPTAMOUNT,
                                @BASECURRENCYID,
                                @TRANSACTIONCURRENCYID;



                            --Business units - add adjusted splits back.

                            declare @REVENUESPLITBUSINESSUNITID uniqueidentifier;

                            declare BUSINESSUNITS cursor local fast_forward
                            for
                            select REVENUESPLITBUSINESSUNIT.ID
                            from dbo.REVENUESPLITBUSINESSUNIT
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITBUSINESSUNIT.REVENUESPLITID
                            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID

                            open BUSINESSUNITS;

                            begin try
                                fetch next
                                from BUSINESSUNITS
                                into @REVENUESPLITBUSINESSUNITID

                                while (@@FETCH_STATUS = 0)
                                begin
                                    exec dbo.USP_REVENUESPLITBUSINESSUNIT_DELETEBYID_WITHCHANGEAGENTID @REVENUESPLITBUSINESSUNITID
                                        ,@CHANGEAGENTID;

                                    fetch next
                                    from BUSINESSUNITS
                                    into @REVENUESPLITBUSINESSUNITID
                                end

                                close BUSINESSUNITS;

                                deallocate BUSINESSUNITS;
                            end try

                            begin catch
                                close BUSINESSUNITS;

                                deallocate BUSINESSUNITS;

                                exec dbo.USP_RAISE_ERROR;

                                return 1;
                            end catch

                            update REVENUESPLIT_EXT
                            set OVERRIDEBUSINESSUNITS = BUR.OVERRIDEBUSINESSUNITS
                                ,REVENUESPLITBUSINESSUNITOVERRIDECODEID = BUR.REASON
                                ,CHANGEDBYID = @CHANGEAGENTID
                                ,DATECHANGED = @CURRENTDATE
                            from dbo.REVENUESPLIT_EXT
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join @BUSINESSUNITSRATIO as BUR on REVENUESPLIT_EXT.DESIGNATIONID = BUR.DESIGNATIONID
                            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
                                and BUR.OVERRIDEBUSINESSUNITS = 1
                                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                                and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1

                            insert into dbo.REVENUESPLITBUSINESSUNIT (
                                ID
                                ,REVENUESPLITID
                                ,BUSINESSUNITCODEID
                                ,AMOUNT
                                ,BASECURRENCYID
                                ,ADDEDBYID
                                ,CHANGEDBYID
                                ,DATEADDED
                                ,DATECHANGED
                                )
                            select newid()
                                ,FINANCIALTRANSACTIONLINEITEM.ID
                                ,BUR.BUSINESSUNITCODEID
                                ,FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT * BUR.RATIO
                                ,CURRENCYSET.BASECURRENCYID
                                ,@CHANGEAGENTID
                                ,@CHANGEAGENTID
                                ,@CURRENTDATE
                                ,@CURRENTDATE
                            from dbo.FINANCIALTRANSACTIONLINEITEM
                            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                            inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                            inner join @BUSINESSUNITSRATIO BUR on BUR.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
                            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
                                and REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS = 1
                                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                                and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
                                --$0 business units are not allowed, so don't create them

                                and BUR.RATIO != 0
                                and FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT != 0

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

                        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