USP_REVENUEBENEFITS_ADDBENEFITS

            CREATE procedure dbo.USP_REVENUEBENEFITS_ADDBENEFITS(
                @REVENUEID uniqueidentifier,
                @RECEIPTAMOUNT money output
            )
            as
            set nocount on;

            declare @CHANGEAGENTID uniqueidentifier;
            declare @CHANGEDATE datetime;

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

            if @CHANGEDATE is null
                set @CHANGEDATE = getdate();

            begin try
                declare @APPEALID uniqueidentifier;
                declare @AMOUNT money;
        declare @GIFTDATE datetime;
        declare @TRANSACTIONCURRENCYID uniqueidentifier;

                --benefits cursor variables

                declare @BENEFITID uniqueidentifier;
                declare @QUANTITY int;
                declare @UNITVALUE money;
                declare @VALUEPERCENT numeric(20,4);
                declare @USEPERCENT bit;
                declare @SEQUENCE int;
                declare @RUNNINGAMOUNT money;
                declare @PERCENTAPPLICABLEAMOUNT money;
        declare @BASECURRENCYID uniqueidentifier;

                select 
                    @APPEALID = APPEALID,
                    @AMOUNT = TRANSACTIONAMOUNT,
          @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
          @GIFTDATE = DATE
                from dbo.REVENUE where @REVENUEID = ID

                declare @BENEFITDETAILS table
                (
                    ID uniqueidentifier,
                    QUANTITY int,
                    UNITVALUE money,
                    VALUEPERCENT numeric(20,4),
                    USEPERCENT bit, 
                    SEQUENCE int,
          BASECURRENCYID uniqueidentifier
                );

                if @APPEALID is not null
                begin
                    --Insert unit and percent appeal benefits

                    insert into @BENEFITDETAILS(
                        ID,
                        QUANTITY,
                        UNITVALUE,
                        VALUEPERCENT,
                        USEPERCENT,
                        SEQUENCE,
            BASECURRENCYID
                    )select 
                        BENEFITID,
                        QUANTITY,
                        VALUE,
                        VALUEPERCENT,
                        USEPERCENT,
                        SEQUENCE,
            BASECURRENCYID
                    from dbo.UFN_APPEAL_GETBENEFITDETAILS_ALL(@APPEALID,@AMOUNT)
                end


                --Insert unit and percent value membership benefits

                insert into @BENEFITDETAILS(
                    ID,
                    QUANTITY,
                    UNITVALUE,
                    VALUEPERCENT,
                    USEPERCENT,
                    SEQUENCE,
          BASECURRENCYID
                )select 
                    BENEFIT.ID,
                    MEMBERSHIPLEVELBENEFIT.QUANTITY,
                    MEMBERSHIPLEVELBENEFIT.UNITVALUE,
                    MEMBERSHIPLEVELBENEFIT.VALUEPERCENT,
                    BENEFIT.USEPERCENT,
                    MEMBERSHIPLEVELBENEFIT.SEQUENCE,
          MEMBERSHIPLEVELBENEFIT.BASECURRENCYID

                from dbo.REVENUESPLIT
                inner join dbo.MEMBERSHIPTRANSACTION on REVENUESPLIT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                inner join dbo.MEMBERSHIPLEVELBENEFIT on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID
                inner join dbo.BENEFIT on MEMBERSHIPLEVELBENEFIT.BENEFITID = BENEFIT.ID
                where REVENUESPLIT.REVENUEID = @REVENUEID

                --set remaining amount to the total revenue amount

                set @RUNNINGAMOUNT = @AMOUNT

                -- Loop through benefits

                declare BENEFITS_CURSOR cursor local fast_forward for
                    select 
                        ID,
                        QUANTITY,
                        UNITVALUE,
                        VALUEPERCENT,
                        USEPERCENT,
                        SEQUENCE,
            BASECURRENCYID
                    from @BENEFITDETAILS
                    order by USEPERCENT, SEQUENCE

                open BENEFITS_CURSOR

                fetch next from BENEFITS_CURSOR into 
                    @BENEFITID,
                    @QUANTITY,
                    @UNITVALUE,
                    @VALUEPERCENT,
                    @USEPERCENT,
                    @SEQUENCE,
          @BASECURRENCYID
                while @@FETCH_STATUS = 0
                begin

                    if @USEPERCENT = 0
                    begin
                        --adjust the remaining amount by subtracting benefit value from remaining amount

            declare @MULTICURRENCYENABLED bit;
            set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
            if @MULTICURRENCYENABLED = 0
            begin
              set @RUNNINGAMOUNT = @RUNNINGAMOUNT - @UNITVALUE;
            end
            else
            begin
              declare @BASEEXCHANGERATEID uniqueidentifier;
          set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID,@TRANSACTIONCURRENCYID,@GIFTDATE,1,null); 
              set @RUNNINGAMOUNT = @RUNNINGAMOUNT - dbo.UFN_CURRENCY_CONVERT(@UNITVALUE,@BASEEXCHANGERATEID);
            end


                        --set applicable amount to the value of the benefit

                        set @PERCENTAPPLICABLEAMOUNT = 0;
                    end
                    else
                    begin
                        --calc percent amount based on remaining amount * percentage value of benefit

                        set @PERCENTAPPLICABLEAMOUNT = @RUNNINGAMOUNT 

                        --adjust remaining amount

                        set @RUNNINGAMOUNT = @RUNNINGAMOUNT - (@PERCENTAPPLICABLEAMOUNT * @VALUEPERCENT / 100);
                    end

                    if @RUNNINGAMOUNT < 0
                        set @RUNNINGAMOUNT = 0;

                    insert into dbo.REVENUEBENEFIT(
                        REVENUEID,
                        BENEFITID,
                        QUANTITY,
                        UNITVALUE,
                        PERCENTAPPLICABLEAMOUNT,
                        VALUEPERCENT,
                        SEQUENCE,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    )select
                        @REVENUEID,
                        @BENEFITID,
                        @QUANTITY,
                        @UNITVALUE,
                        @PERCENTAPPLICABLEAMOUNT,
                        @VALUEPERCENT,
                        @SEQUENCE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE


                    fetch next from BENEFITS_CURSOR into 
                        @BENEFITID,
                        @QUANTITY,
                        @UNITVALUE,
                        @VALUEPERCENT,
                        @USEPERCENT,
                        @SEQUENCE,
            @BASECURRENCYID
                end

                close BENEFITS_CURSOR    
                deallocate BENEFITS_CURSOR

                set @RECEIPTAMOUNT = @RUNNINGAMOUNT

            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch