UFN_PLEDGE_GETREVALUATIONGAINLOSS

            CREATE function dbo.UFN_PLEDGE_GETREVALUATIONGAINLOSS
            (
                @PLEDGEID uniqueidentifier
            ) 
            returns @GAINLOSS table(
                BASEGAINLOSS money,
                ORGANIZATIONGAINLOSS money,
                ISGAIN bit
            )
            with execute as caller
            as begin

                if dbo.UFN_REVENUE_HASNEEDEDRATES(@PLEDGEID) = 1
                begin

                    declare @CURRENTDATE datetime = getdate();

                    --Calculate gain/loss

                    insert into @GAINLOSS(
                        BASEGAINLOSS,
                        ORGANIZATIONGAINLOSS
                    )
                    select 
                        dbo.UFN_PLEDGE_GETREVALUEDBASEBALANCEASOF(@PLEDGEID, @CURRENTDATE, 0) - dbo.UFN_PLEDGE_GETREVALUEDBASEBALANCEASOF(@PLEDGEID, @CURRENTDATE, 1),
                        dbo.UFN_PLEDGE_GETREVALUEDORGANIZATIONBALANCEASOF(@PLEDGEID, @CURRENTDATE, 0) - dbo.UFN_PLEDGE_GETREVALUEDORGANIZATIONBALANCEASOF(@PLEDGEID, @CURRENTDATE, 1);

                    --If both base and org gain/loss are not zero and one is positive while the 

                    --    other is negative, add a row for just org gain/loss

                    insert into @GAINLOSS(
                        BASEGAINLOSS,
                        ORGANIZATIONGAINLOSS
                    )
                    select
                        0, 
                        ORGANIZATIONGAINLOSS
                    from @GAINLOSS
                    where (BASEGAINLOSS * ORGANIZATIONGAINLOSS) < 0

                    if ( --We added an extra row above...

                        select count(*)
                        from @GAINLOSS
                    ) > 1
                    begin
                        --Clear org gain/loss out of the first row (making it base gain/loss only) and set

                        --    the ISGAIN flag.

                        update @GAINLOSS
                        set 
                            ISGAIN = 
                                case
                                    when BASEGAINLOSS > 0
                                        then 1
                                    else 0
                                end,
                            BASEGAINLOSS = abs(BASEGAINLOSS),
                            ORGANIZATIONGAINLOSS = 0
                        where BASEGAINLOSS <> 0 and ORGANIZATIONGAINLOSS <> 0;

                        --Set the ISGAIN flag on the org gain/loss only row.

                        update @GAINLOSS
                        set 
                            ISGAIN = 
                                case
                                    when ORGANIZATIONGAINLOSS > 0
                                        then 1
                                    else 0
                                end,
                            ORGANIZATIONGAINLOSS = abs(ORGANIZATIONGAINLOSS)
                        where BASEGAINLOSS = 0 and ORGANIZATIONGAINLOSS <> 0;                        
                    end
                    else
                    begin
                        --Set the ISGAIN flag on the combined row.

                        update @GAINLOSS
                        set 
                            ISGAIN = 
                                case
                                    when BASEGAINLOSS > 0 or ORGANIZATIONGAINLOSS > 0
                                        then 1
                                    else 0
                                end,
                            BASEGAINLOSS = abs(BASEGAINLOSS),
                            ORGANIZATIONGAINLOSS = abs(ORGANIZATIONGAINLOSS);
                    end

                    --Remove rows if there was no gain/loss

                    delete @GAINLOSS
                    where BASEGAINLOSS = 0 and ORGANIZATIONGAINLOSS = 0;
                end

                return;
            end