USP_DATAFORMTEMPLATE_ADJUST_REVENUEBENEFITS_3

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADJUST_REVENUEBENEFITS_3
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @BENEFITS xml,
                        @BENEFITSWAIVED bit,
                        @ADJUSTMENTDATE datetime,
                        @ADJUSTMENTPOSTDATE datetime,
                        @ADJUSTMENTREASON nvarchar(300),
                        @ADJUSTMENTREASONCODEID uniqueidentifier,
                        @PERCENTAGEBENEFITS xml,
                        @ADJUSTMENTPOSTSTATUSCODE 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;
                        select @POSTSTATUSCODE = POSTSTATUSCODE from dbo.ADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE <> 0;

                        declare @ORIGINALTOTALBENEFITVALUE money = 0
                        select
                            @ORIGINALTOTALBENEFITVALUE = coalesce(sum(TRANSACTIONTOTALVALUE), 0)
                        from dbo.REVENUEBENEFIT
                        where REVENUEID = @ID;

                        declare @REVENUETRANSACTIONCURRENCYID uniqueidentifier, @REVENUEBASECURRENCYID uniqueidentifier;
                        declare @CONSTITUENTID uniqueidentifier;

                        select
                            @REVENUETRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
                            @REVENUEBASECURRENCYID = BASECURRENCYID,
                            @CONSTITUENTID = CONSTITUENTID
                        from dbo.REVENUE where ID = @ID;

                        if @POSTSTATUSCODE is null
                        begin
                              select @POSTSTATUSCODE = case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end
                              from dbo.REVENUE
                              left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                              where REVENUE.ID = @ID
                        end

                        declare @ADJUSTBENEFITS bit;
                        declare @CLEARBENEFITSGLDISTRIBUTION bit;
                        declare @BENEFITSADJUSTMENTID uniqueidentifier;
                        set @ADJUSTBENEFITS = 0;
                        set @CLEARBENEFITSGLDISTRIBUTION = 0;

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

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

                        --Remove benefits if they are waived
                        if @BENEFITSWAIVED = 1
                            set @TOTALBENEFITSXML = null;

                        if dbo.UFN_CHECKDETAIL_BENEFITSCHANGEDFORGL(@ID, @TOTALBENEFITSXML) = 1
                        begin
                            set @ADJUSTBENEFITS = 1;
                            set @CLEARBENEFITSGLDISTRIBUTION = 1;
                        end           

                        if @ADJUSTBENEFITS = 0
                              if (select COUNT(ID) from dbo.BENEFITADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE = 1) > 0
                            begin
                                set @ADJUSTBENEFITS = 1;
                            end

                        --we need to save the adjustment before we save the benefits so that the previous value will be correct
                        --in the adjustment
                        if @ADJUSTBENEFITS = 1
                        begin
                            if @ADJUSTMENTREASONCODEID is null
                                raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1)

                            set @BENEFITSADJUSTMENTID = null;
                            exec dbo.USP_SAVE_BENEFITADJUSTMENT_2 @ID, @BENEFITSADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, 
                                  @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID, @ADJUSTMENTPOSTSTATUSCODE, @TOTALBENEFITSXML;
                        end 

                        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.REVENUE on REVENUE.ID = RBSUB.REVENUEID
                                    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                                    inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.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
                                )

                            -- 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 @TOTALBENEFITS money;
                        declare @ORIGINALRECEIPTAMOUNT money;
                        declare @RECEIPTAMOUNT money;
                        declare @ISPLEDGEPAYMENT bit;

                        select @TOTALBENEFITS = sum(TOTALVALUE) from dbo.REVENUEBENEFIT where REVENUEID = @ID;

                        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  
                            REVENUESPLIT.AMOUNT,
                            dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS(
                                REGISTRANT.ID,
                                REVENUE.DATE,
                                REVENUESPLIT.AMOUNT,
                                REVENUE.TRANSACTIONCURRENCYID,
                                REVENUE.BASECURRENCYID,
                                REVENUE.BASEEXCHANGERATEID,
                                0,
                                0,
                                REVENUE.ID
                            )
                        from dbo.REVENUE
                            inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                            inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = REVENUESPLIT.ID
                            inner join dbo.REGISTRANT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                            inner join dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATION.REGISTRANTID = REGISTRANT.ID
                        where
                            REVENUESPLIT.TYPECODE = 1 and 
                            REVENUESPLIT.APPLICATIONCODE = 1 and 
                            REVENUE.ID = @ID 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 = RECEIPTAMOUNT,
                                @RECEIPTAMOUNT = case @ISPLEDGEPAYMENT
                                    when 0 then TRANSACTIONAMOUNT - coalesce(@EVENTAPPLICATIONADJUSTMENT, 0) - coalesce(@TOTALBENEFITS, 0)
                                    when 1 then REVENUE.RECEIPTAMOUNT - coalesce(@EVENTAPPLICATIONADJUSTMENT, 0) - (coalesce(@TOTALBENEFITS, 0) - @ORIGINALTOTALBENEFITVALUE)
                                end
                        from dbo.REVENUE
                        where ID = @ID;

                        if @RECEIPTAMOUNT < 0 set @RECEIPTAMOUNT = 0;

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

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

                        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 @UPDATEGIFTFEEOPTION = 1 and exists (select 1 from dbo.GIFTFEEOVERRIDEREASONCODE where ISPAYMENTEDITDEFAULT=1 and ACTIVE=1)
                        begin
                            exec dbo.USP_REVENUE_UPDATEPAYMENTGIFTFEE @ID, @CONSTITUENTID, @CHANGEAGENTID

                            if @ADJUSTMENTPOSTSTATUSCODE <> 2
                                exec dbo.USP_SAVE_GIFTFEEADJUSTMENT 
                                    @ID, 
                                    null, 
                                    @CHANGEAGENTID, 
                                    @CHANGEDATE, 
                                    @ADJUSTMENTDATE, 
                                    @ADJUSTMENTPOSTDATE, 
                                    @ADJUSTMENTREASON, 
                                    default, 
                                    @ADJUSTMENTREASONCODEID, 
                                    @ADJUSTMENTPOSTSTATUSCODE;

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