USP_DATAFORMTEMPLATE_EDIT_REVENUEBENEFITS2

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEBENEFITS2
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @BENEFITS xml,
                        @BENEFITSWAIVED bit,
                        @PERCENTAGEBENEFITS xml,
                        @ADJUSTMATCHINGGIFTCLAIMS tinyint,
                        @UPDATEGIFTFEEOPTION bit
                    )
                as
                begin
                    set nocount on;

                    begin try
                        declare @CHANGEDATE datetime;
                        set @CHANGEDATE = getdate();

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

                        declare @POSTSTATUSCODE tinyint;
                        declare @POSTDATE datetime;
                        declare @REVENUETRANSACTIONCURRENCYID uniqueidentifier;
                        declare @REVENUEBASECURRENCYID uniqueidentifier;
                        declare @REVENUETRANSACTIONCODE tinyint;
                        declare @ORIGINALTOTALBENEFITVALUE money = 0
                        declare @CONSTITUENTID uniqueidentifier;
                        select
                            @ORIGINALTOTALBENEFITVALUE = coalesce(sum(TRANSACTIONTOTALVALUE), 0)
                        from dbo.REVENUEBENEFIT
                        where REVENUEID = @ID;

                        if @UPDATEGIFTFEEOPTION is null
                            set @UPDATEGIFTFEEOPTION = 0;

                        select 
                            @POSTSTATUSCODE = case when FINANCIALTRANSACTION.POSTSTATUSCODE = 3 then 2 when FINANCIALTRANSACTION.POSTSTATUSCODE = 2 then 0 else 1 end,
                            @POSTDATE = FINANCIALTRANSACTION.POSTDATE,
                            @REVENUETRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                            @REVENUEBASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID),
                            @REVENUETRANSACTIONCODE = FINANCIALTRANSACTION.TYPECODE,
                            @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID
                        from 
                            dbo.FINANCIALTRANSACTION
                            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                            inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                        where 
                            FINANCIALTRANSACTION.ID = @ID
                            and FINANCIALTRANSACTION.DELETEDON is null

                        if @BENEFITSWAIVED = 0 -- Grid is not empty(not waived)

                        begin

                            insert into dbo.BENEFITCONSTITUENTDECLINED
                            (
                                BENEFITID,
                                CONSTITUENTID,
                                ADDEDBYID, 
                                CHANGEDBYID, 
                                DATEADDED, 
                                DATECHANGED
                            )
                            select 
                                REVENUEBENEFIT.BENEFITID,
                                @CONSTITUENTID,
                                @CHANGEAGENTID, 
                                @CHANGEAGENTID, 
                                @CHANGEDATE, 
                                @CHANGEDATE                        
                            from dbo.REVENUEBENEFIT
                            where
                                REVENUEBENEFIT.REVENUEID = @ID and
                                -- Benefit has been deleted

                                REVENUEBENEFIT.BENEFITID not in
                                (
                                    select 
                                        T.benefits.value('(BENEFITID)[1]','uniqueidentifier')                        
                                    from @BENEFITS.nodes('/BENEFITS/ITEM') T(benefits)
                                )
                                and
                                -- Record does not already exist

                                REVENUEBENEFIT.BENEFITID not in
                                (
                                    select BCD.BENEFITID
                                    from dbo.BENEFITCONSTITUENTDECLINED BCD
                                    where BCD.CONSTITUENTID = @CONSTITUENTID
                                )
                                and
                                exists
                                -- One of the revenue splits is a membership, that contains this benefit

                                ( 
                                    select 1 from dbo.REVENUEBENEFIT RBSUB
                                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = RBSUB.REVENUEID
                                    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                                    inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                                    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID=MEMBERSHIPLEVEL.ID
                                    inner join dbo.MEMBERSHIPLEVELBENEFIT on MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                                    where 
                                        RBSUB.BENEFITID = REVENUEBENEFIT.BENEFITID and 
                                        RBSUB.REVENUEID = @ID and
                                        MEMBERSHIPLEVELBENEFIT.BENEFITID = REVENUEBENEFIT.BENEFITID and
                                        FINANCIALTRANSACTION.DELETEDON is null and
                                        FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
                                        FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                                )

                            -- If benefit is added constituent is no longer declining it

                            delete from dbo.BENEFITCONSTITUENTDECLINED
                            where
                                CONSTITUENTID = @CONSTITUENTID
                                and BENEFITID in
                                (
                                    select T.c.value('(BENEFITID)[1]','uniqueidentifier')                        
                                    from @BENEFITS.nodes('/BENEFITS/ITEM') T(c)
                                )
                                and BENEFITID not in
                                (
                                    select BENEFITID
                                    from dbo.REVENUEBENEFIT RBSUB
                                    where RBSUB.REVENUEID = @ID
                                )

                        end

                        declare @TOTALBENEFITSXML xml;
                        set @TOTALBENEFITSXML = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);

                        --Remove benefits if they are waived

                        if @BENEFITSWAIVED = 1
                            set @TOTALBENEFITSXML = null;

                        --Update the organization total value, transaction total value, and exchange rates in the @TOTALBENEFITS XML.

                        set @TOTALBENEFITSXML = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2(@TOTALBENEFITSXML,@REVENUETRANSACTIONCURRENCYID,@REVENUEBASECURRENCYID);
                        exec dbo.USP_REVENUE_GETBENEFITS_4_UPDATEFROMXML @ID, @TOTALBENEFITSXML, @CHANGEAGENTID, @CHANGEDATE;

                        declare @TOTALBENEFITS money;
                        declare @ORIGINALRECEIPTAMOUNT money;
                        declare @RECEIPTAMOUNT money;
                        declare @ISPLEDGEPAYMENT bit;
                        select 
                            @TOTALBENEFITS = sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT)
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUEBENEFIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUEBENEFIT_EXT.ID
                        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and REVENUEBENEFIT_EXT.BENEFITTYPECODE = 1;

                        if @BENEFITSWAIVED = 1
                            set @TOTALBENEFITS = 0;

                        -- Calculate the needed adjustment for event applications to receipt amount.

                        declare @EVENTAPPLICATIONADJUSTMENTS table
                        (
                          EVENTAPPLICAITONAMOUNT money,
                          EVENTAPPLICAITONRECEIPTAMOUNT money
                        )            
                        insert into @EVENTAPPLICATIONADJUSTMENTS (
                            EVENTAPPLICAITONAMOUNT,
                            EVENTAPPLICAITONRECEIPTAMOUNT
                        )
                        select  
                            FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
                            dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS(
                                REGISTRANT.ID,
                                FINANCIALTRANSACTION.DATE,
                                FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
                                FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                                isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID),
                                FINANCIALTRANSACTION.BASEEXCHANGERATEID,
                                0,
                                0,
                                FINANCIALTRANSACTION.ID
                            )
                        from dbo.FINANCIALTRANSACTION
                            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                            inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                            inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.REGISTRANT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                            inner join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID
                        where
                            REVENUESPLIT_EXT.TYPECODE = 1 and 
                            REVENUESPLIT_EXT.APPLICATIONCODE = 1 and 
                            FINANCIALTRANSACTION.ID = @ID and
                            FINANCIALTRANSACTION.DELETEDON is null and
                            FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
                            FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and
                            -- Don't include registrations whose fees were waived

                            REGISTRANTREGISTRATION.AMOUNT > 0 and
                            (
                                select sum(AMOUNT) 
                                from dbo.REGISTRANTREGISTRATION 
                                where REGISTRANTREGISTRATION.REGISTRANTID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                            ) > 0;


                        declare @EVENTAPPLICATIONADJUSTMENT money
                        select 
                            @EVENTAPPLICATIONADJUSTMENT = 
                                sum(EVENTAPPLICAITONAMOUNT) - sum(EVENTAPPLICAITONRECEIPTAMOUNT)
                        from @EVENTAPPLICATIONADJUSTMENTS

                        if exists
                        (
                        select top 1 FINANCIALTRANSACTION.ID
                        from dbo.FINANCIALTRANSACTION
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                            inner join dbo.INSTALLMENTPAYMENT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTPAYMENT.PAYMENTID
                        where FINANCIALTRANSACTION.ID = @ID
                        )
                        begin
                            set @ISPLEDGEPAYMENT = 1
                        end
                        else
                        begin
                            set @ISPLEDGEPAYMENT = 0
                        end

                        select  @ORIGINALRECEIPTAMOUNT = REVENUE_EXT.RECEIPTAMOUNT,
                                @RECEIPTAMOUNT = case @ISPLEDGEPAYMENT
                                    when 0 then FINANCIALTRANSACTION.TRANSACTIONAMOUNT - coalesce(@EVENTAPPLICATIONADJUSTMENT, 0) - coalesce(@TOTALBENEFITS, 0)
                                    when 1 then REVENUE_EXT.RECEIPTAMOUNT - coalesce(@EVENTAPPLICATIONADJUSTMENT, 0) - (coalesce(@TOTALBENEFITS, 0) - @ORIGINALTOTALBENEFITVALUE)
                                end
                        from dbo.FINANCIALTRANSACTION
                        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        where FINANCIALTRANSACTION.ID = @ID
                            and FINANCIALTRANSACTION.DELETEDON is null;

                        if @RECEIPTAMOUNT < 0 set @RECEIPTAMOUNT = 0;

                        if @RECEIPTAMOUNT <> @ORIGINALRECEIPTAMOUNT
                            exec dbo.USP_REVENUE_UPDATERERECEIPTS @ID, @CHANGEAGENTID, @CHANGEDATE;                                                                      

                        update dbo.REVENUE_EXT
                            set 
                                BENEFITSWAIVED = @BENEFITSWAIVED,
                                RECEIPTAMOUNT = case when @REVENUETRANSACTIONCODE in (0,7) then @RECEIPTAMOUNT else @ORIGINALRECEIPTAMOUNT end,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CHANGEDATE
                            where ID = @ID;

                        --update gl distributions

                        if @REVENUETRANSACTIONCODE = 0 or @REVENUETRANSACTIONCODE = 1 or @REVENUETRANSACTIONCODE = 5
                        begin
                          delete from dbo.BENEFITGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
                          if @POSTSTATUSCODE <> 2
                          begin
                              exec dbo.USP_SAVE_BENEFITGLDISTRIBUTION_FINANCIALTRANSACTION @ID, @CHANGEAGENTID, @CHANGEDATE              
                          end
                        end

                        -- update installment receipt amounts

                        declare @REMAININGPLEDGEBALANCE money = dbo.UFN_PLEDGE_GETBALANCE(@ID) - @TOTALBENEFITS
                        declare @INSTALLMENTID uniqueidentifier
                        declare @INSTALLMENTAMOUNT money

                        declare cur cursor fast_forward
                        for
                            select INSTALLMENT.ID
                            from dbo.INSTALLMENT
                                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                                left join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                            where REVENUEID = @ID and INSTALLMENTSPLITPAYMENT.ID is null
                            order by SEQUENCE desc

                        open cur
                        fetch next from cur into @INSTALLMENTID
                        while @@FETCH_STATUS = 0
                        begin
                            select @INSTALLMENTAMOUNT = AMOUNT
                            from dbo.INSTALLMENT
                            where ID = @INSTALLMENTID

                            if @REMAININGPLEDGEBALANCE < @INSTALLMENTAMOUNT
                            begin
                                update dbo.INSTALLMENT
                                set RECEIPTAMOUNT = @REMAININGPLEDGEBALANCE
                                where ID = @INSTALLMENTID
                            end

                            set @REMAININGPLEDGEBALANCE = @REMAININGPLEDGEBALANCE - @INSTALLMENTAMOUNT
                            if @REMAININGPLEDGEBALANCE < 0
                                set @REMAININGPLEDGEBALANCE = 0

                            fetch next from cur into @INSTALLMENTID
                        end
                        close cur
                        deallocate cur

                        -- Redefault pledge receipt amounts

                        exec dbo.USP_INSTALLMENTS_DEFAULTRECEIPTAMOUNTS @ID, @CHANGEAGENTID

                    if @ADJUSTMATCHINGGIFTCLAIMS = 2
                    begin
                        exec dbo.USP_MATCHINGGIFTCLAIM_AMOUNT_UPDATE @ID, @TOTALBENEFITS, @ORIGINALTOTALBENEFITVALUE, @ADJUSTMATCHINGGIFTCLAIMS, @CHANGEAGENTID, @CHANGEDATE
                    end
                        if @UPDATEGIFTFEEOPTION = 1 and exists (select 1 from dbo.GIFTFEEOVERRIDEREASONCODE where ISPAYMENTEDITDEFAULT=1 and ACTIVE=1)
                        begin
                            exec dbo.USP_REVENUE_UPDATEPAYMENTGIFTFEE @ID, @CONSTITUENTID, @CHANGEAGENTID

                            delete JOURNALENTRY
                            from dbo.JOURNALENTRY 
                            inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
                            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
                                and JOURNALENTRY_EXT.OUTDATED = 0
                                and JOURNALENTRY_EXT.TABLENAMECODE = 8
                                and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2
                                and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1;

                            if @POSTSTATUSCODE = 0
                            begin
                                declare @ADJUSTMENTDATE datetime = @CHANGEDATE;
                                declare @ADJUSTMENTPOSTDATE datetime = @POSTDATE;
                                declare @ADJUSTMENTPOSTSTATUSCODE tinyint = 1;
                                declare @ADJUSTMENTREASONCODEID uniqueidentifier = null;
                                declare @ADJUSTMENTREASON nvarchar(300) = '';

                                select @ADJUSTMENTDATE = GIFTFEEADJUSTMENT.DATE
                                    ,@ADJUSTMENTPOSTDATE = GIFTFEEADJUSTMENT.POSTDATE
                                    ,@ADJUSTMENTPOSTSTATUSCODE = GIFTFEEADJUSTMENT.POSTSTATUSCODE
                                    ,@ADJUSTMENTREASONCODEID = GIFTFEEADJUSTMENT.REASONCODEID
                                    ,@ADJUSTMENTREASON = GIFTFEEADJUSTMENT.REASON
                                from dbo.GIFTFEEADJUSTMENT
                                where GIFTFEEADJUSTMENT.REVENUEID = @ID and POSTSTATUSCODE <> 0;

                                exec dbo.USP_SAVE_GIFTFEEADJUSTMENT 
                                    @ID, 
                                    null, 
                                    @CHANGEAGENTID, 
                                    @CHANGEDATE, 
                                    @ADJUSTMENTDATE, 
                                    @ADJUSTMENTPOSTDATE, 
                                    @ADJUSTMENTREASON, 
                                    default, 
                                    @ADJUSTMENTREASONCODEID, 
                                    @ADJUSTMENTPOSTSTATUSCODE;
                            end

                            if @POSTSTATUSCODE <> 2
                            begin
                                exec dbo.USP_SAVE_GIFTFEEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;
                            end
                        end
                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1;
                    end catch

                    return 0;
                end