UFN_PLANNEDGIFTDESIGNATION_GETNETPRESENTVALUE_MULTIPLE

        CREATE function dbo.UFN_PLANNEDGIFTDESIGNATION_GETNETPRESENTVALUE_MULTIPLE
        (
            @rows xml,
            @giftamount money,
            @netpresentvalue money
        )
        returns @return table(ID uniqueidentifier, NETPRESENTVALUE money)--, NETPRESENTVALUE_PERCENT money)

        begin    

               declare @rowCount int
            declare @currentRow int
            declare @npvSum money
            declare @adjValue money
            declare @adjFactor money
            declare @sumAmount money

            declare @returnTable table
            (
                ID uniqueidentifier,
                AMOUNT money,
                NETPRESENTVALUE money,
                ROWNUMBER int identity(1,1)
            );

            insert into @returnTable select
            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',  
            T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
            0.0
            FROM @rows.nodes('/DESIGNATION/ITEM') T(c)  


            select @currentRow=1, @rowCount=MAX(ROWNUMBER) from @returnTable
            select @sumAmount=SUM(AMOUNT) from @returnTable

                    --Update the return table with NETPRESENTVALUE


                        update @RETURNTABLE set
                        NETPRESENTVALUE =
                        case 
                                when (NPVPCTCALCULATIONNEED is not null)
                                then
                                    case 
                                            when NPVPCTCALCULATIONNEED = 1
                                            then ROUND((ROUND((RT.AMOUNT / @GIFTAMOUNT),10) * @NETPRESENTVALUE),10)
                                            else ((@NETPRESENTVALUE / @GIFTAMOUNT) * RT.AMOUNT)
                                    end
                        end
                        from
                        (
                            select 
                                RT.ID DSGID,
                                RT.AMOUNT,
                                case
                                --Bug 147335 When there is no gift amount, net present value does not apply

                                    when (@GIFTAMOUNT is null or @GIFTAMOUNT = 0)
                                        then null
                                    when ((select (@NETPRESENTVALUE % @GIFTAMOUNT)) = 0)
                                        then 0
                                    else
                                                1
                                end as NPVPCTCALCULATIONNEED
                            from
                                @RETURNTABLE RT
                        ) RT
                                where
                        ID = RT.DSGID;

            ------ fine tune the amounts for distributing it correctly, if needed


            select @npvSum = SUM(NETPRESENTVALUE) from @returnTable
            set @adjValue = @netPresentValue - @npvSum

            set @adjFactor = (@adjValue / @rowCount) 
            set @npvSum = 0

            if (@sumAmount = @giftAmount) and @adjFactor <> 0.0
            begin
                while @currentRow <= @rowCount
                begin

                    select @adjValue = ROUND(RT.NETPRESENTVALUE + @adjFactor,2) from @returnTable RT
                    where RT.ROWNUMBER = @currentRow

                    set @npvSum = @npvSum + @adjValue

                    if @currentRow = @rowCount
                    begin
                        if @npvSum <> @netPresentValue
                        begin
                            set @adjValue = @adjValue + (@netPresentValue - @npvSum)
                        end
                    end

                    update @returnTable
                    set NETPRESENTVALUE = @adjValue
                    from @returnTable rt
                    where rt.ROWNUMBER = @currentRow

                    set @currentRow = @currentRow + 1;

                end
            end
            -----return the result

            insert into @return
                select ID,NETPRESENTVALUE from @returnTable 

            return ;

        end