USP_BENEFIT_USERMODIFIEDBENEFITS

    create procedure dbo.USP_BENEFIT_USERMODIFIEDBENEFITS
    (
        @APPLICATIONINFO nvarchar(60), 
        @REVENUESTREAMS xml, 
        @APPEALID uniqueidentifier, 
        @AMOUNT money, 
        @DATE datetime, 
        @TRANSACTIONCURRENCYID uniqueidentifier, 
        @BASECURRENCYID uniqueidentifier, 
        @REVENUEBENEFITS xml, 
        @REVENUEPERCENTAGEBENEFITS xml, 
        @EXCHANGERATE decimal(20,8), 
        @BASEEXCHANGERATEID uniqueidentifier,
        @USERMODIFIEDBENEFITS bit output
    )
    as
    begin

    --Logic for this procedure is used from the reference Blackbaud.AppFx.Fundraising.UIModel\TransactionDataLoader.vb\SetUserModifiedBenefits().

    --This procedure sets USERMODIFIEDBENEFITS bit after comparing benefits same as it is being done in RUB.


        declare @DEFAULTUNITBENEFITCOUNT int;
        declare @DEFAULTPERCENTAGEBENEFITCOUNT int;
        declare @ROWUNITBENEFITCOUNT int;
        declare @ROWPERCENTBENEFITCOUNT int;
        declare @ACTUALUNITBENEFITCOUNT int;
        declare @ACTUALPERCENTBENEFITCOUNT int;
        declare @BENEFITS xml;
        declare @PERCENTAGEBENEFITS xml;

        declare @UNITBENEFIT table 
        (
            BENEFITID uniqueidentifier,
            QUANTITY int,
            UNITVALUE money
        );

        declare @PERCENTAGEBENEFIT table 
        (
            BENEFITID uniqueidentifier,
            VALUEPERCENT decimal
        );

        declare @ROWUNITBENEFITS as table
        (
            BENEFITID uniqueidentifier,
            QUANTITY smallint,
            UNITVALUE money,
            TOTALVALUE money,
            DETAILS nvarchar(100),
            SEQUENCE int
        );

        declare @ROWPERCENTAGEBENEFITS as table
        (
            BENEFITID uniqueidentifier,
            VALUEPERCENT int,
            TOTALVALUE money,
            DETAILS nvarchar(100),
            SEQUENCE int
        );

        --Get all default benefits. This includes benefits from appeals, event registrations, and memberships.

        if @APPEALID is not null
        begin 

        --Get Membership Defaults.

            if exists(select 1 from [dbo].[UFN_APPEAL_GETMEMBERSHIPDEFAULTS](@APPEALID, @AMOUNT))
            begin

                declare @MEMBERSHIPLEVELID uniqueidentifier;
                select @MEMBERSHIPLEVELID=MEMBERSHIPLEVELID from [dbo].[UFN_APPEAL_GETMEMBERSHIPDEFAULTS](@APPEALID, @AMOUNT);

            --Get Membership Level Benefits.

                insert into @UNITBENEFIT (BENEFITID, QUANTITY, UNITVALUE) 
                (
                    select BENEFITID, QUANTITY, UNITVALUE from 
                    dbo.MEMBERSHIPLEVELBENEFIT 
                    where dbo.MEMBERSHIPLEVELBENEFIT.USEPERCENT = 0 and dbo.MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID
                );

                insert into @PERCENTAGEBENEFIT (BENEFITID, VALUEPERCENT) 
                (
                    select BENEFITID, VALUEPERCENT from 
                    dbo.MEMBERSHIPLEVELBENEFIT 
                    where dbo.MEMBERSHIPLEVELBENEFIT.USEPERCENT = 1 and dbo.MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID
                );

            end
        end


        -- Get Revenue batch benefits, it includes event registrant and appeal benefits.

        exec dbo.[USP_REVENUEBATCH_GETDEFAULTBENEFITS] 
            @APPLICATIONINFO = @APPLICATIONINFO, 
            @REVENUESTREAMS = @REVENUESTREAMS, 
            @APPEALID = @APPEALID, 
            @AMOUNT = @AMOUNT, 
            @DATE = @DATE, 
            @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID, 
            @BASECURRENCYID = @BASECURRENCYID, 
            @BENEFITS = @BENEFITS output, 
            @PERCENTAGEBENEFITS = @PERCENTAGEBENEFITS output, 
            @EXCHANGERATE = @EXCHANGERATE, 
            @BASEEXCHANGERATEID = @BASEEXCHANGERATEID;

        --Insert benefits .

        insert into @UNITBENEFIT 
        select T.c.value('(BENEFITID)[1]','uniqueidentifier') AS 'BENEFITID',
        T.c.value('(QUANTITY)[1]','int') AS 'QUANTITY',
        T.c.value('(UNITVALUE)[1]','money') AS 'UNITVALUE' from @BENEFITS.nodes('/BENEFITS/ITEM') T(c);

        insert into @PERCENTAGEBENEFIT 
        select T.c.value('(BENEFITID)[1]','uniqueidentifier') AS 'BENEFITID',
        T.c.value('(VALUEPERCENT)[1]','decimal(20, 4)') AS 'VALUEPERCENT' from @PERCENTAGEBENEFITS.nodes('/PERCENTAGEBENEFITS/ITEM') T(c);

        -- Get the benefits from Revenue batch benefits grid.

        insert into @ROWUNITBENEFITS
        select T.c.value('(BENEFITID)[1]','uniqueidentifier') AS 'BENEFITID',
        T.c.value('(QUANTITY)[1]','int') AS 'QUANTITY',
        T.c.value('(UNITVALUE)[1]','money') AS 'UNITVALUE',
        T.c.value('(TOTALVALUE)[1]','money') AS 'TOTALVALUE',
        T.c.value('(DETAILS)[1]','nvarchar(100)') AS 'DETAILS',
        T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE' from @REVENUEBENEFITS.nodes('/BENEFITS/ITEM') T(c);

        insert into @ROWPERCENTAGEBENEFITS 
        select T.c.value('(BENEFITID)[1]','uniqueidentifier') AS 'BENEFITID',
        T.c.value('(VALUEPERCENT)[1]','decimal(20, 4)') AS 'VALUEPERCENT',
        T.c.value('(TOTALVALUE)[1]','money') AS 'TOTALVALUE',
        T.c.value('(DETAILS)[1]','nvarchar(100)') AS DETAILS,
        T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE' from @REVENUEPERCENTAGEBENEFITS.nodes('/PERCENTAGEBENEFITS/ITEM') T(c);

        --Count all the default benefits.

        select @DEFAULTUNITBENEFITCOUNT = count(1) from @UNITBENEFIT;
        select @DEFAULTPERCENTAGEBENEFITCOUNT = count(1) from @PERCENTAGEBENEFIT;

        --Count all the Row benefits. Row benefits are benefits from Revenue batch benefits grid.

        select @ROWUNITBENEFITCOUNT = count(1) from @ROWUNITBENEFITS;
        select @ROWPERCENTBENEFITCOUNT = count(1) from @ROWPERCENTAGEBENEFITS;

        -- Compare benefits.

        if(@DEFAULTUNITBENEFITCOUNT = @ROWUNITBENEFITCOUNT) and (@DEFAULTPERCENTAGEBENEFITCOUNT = @ROWPERCENTBENEFITCOUNT) 
        begin
            select @ACTUALUNITBENEFITCOUNT = count(1) 
            from @ROWUNITBENEFITS RUB
            inner join @UNITBENEFIT UB on RUB.BENEFITID = UB.BENEFITID and RUB.QUANTITY = UB.QUANTITY and RUB.UNITVALUE = UB.UNITVALUE;

            select @ACTUALPERCENTBENEFITCOUNT = count(1) 
            from @ROWPERCENTAGEBENEFITS RPB
            inner join @PERCENTAGEBENEFIT PB on RPB.BENEFITID = PB.BENEFITID and RPB.VALUEPERCENT = PB.VALUEPERCENT;

            -- Set USERMODIFIEDBENEFITS field to true, if the benefits are different else set it to false.

            if ((@ACTUALUNITBENEFITCOUNT = @DEFAULTUNITBENEFITCOUNT ) and (@ACTUALPERCENTBENEFITCOUNT = @DEFAULTPERCENTAGEBENEFITCOUNT))
                    set @USERMODIFIEDBENEFITS = 0;
            else
                    set @USERMODIFIEDBENEFITS = 1;
        end
        else
            set @USERMODIFIEDBENEFITS = 1;
        end