UFN_PLEDGE_GETSPLITREVALUATIONGAINLOSS

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

                if dbo.UFN_REVENUE_HASNEEDEDRATES(@PLEDGEID) = 1
                begin

                    declare @CURRENTDATE datetime = getdate();

                    --Compute gain/loss for each record.

                    declare @SPLITREVALUATIONGAINLOSSES table(
                        SPLITID uniqueidentifier,
                        BASEGAINLOSS money,
                        ORGANIZATIONGAINLOSS money
                    );
                    insert into @SPLITREVALUATIONGAINLOSSES
                    select
                        SPLITS.ID,
                        (SPLITS.BASEBALANCE - ORGINIALSPLITS.BASEBALANCE),
                        (SPLITS.ORGANIZATIONBALANCE - ORGINIALSPLITS.ORGANIZATIONBALANCE)
                    from dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCES(@PLEDGEID,@CURRENTDATE,0) SPLITS
                        cross apply dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCES(@PLEDGEID,@CURRENTDATE,1) ORGINIALSPLITS
                    where SPLITS.ID = ORGINIALSPLITS.ID;

                    --Delete rows that don't have a gain/loss

                    delete @SPLITREVALUATIONGAINLOSSES
                    where BASEGAINLOSS = 0 and ORGANIZATIONGAINLOSS = 0;

                    --Add rows to be returned if base and org gain/loss are both positive, both negative, or either is zero

                    insert into @SPLITGAINLOSS
                    select 
                        SPLITID,
                        abs(BASEGAINLOSS),
                        abs(ORGANIZATIONGAINLOSS),
                        case
                            when BASEGAINLOSS > 0 or ORGANIZATIONGAINLOSS > 0
                                then 1
                            else 0
                        end
                    from @SPLITREVALUATIONGAINLOSSES
                    where (BASEGAINLOSS * ORGANIZATIONGAINLOSS) >= 0;

                    --Add base gain/loss rows to be returned if base and org are both not zero and one is positive while the other is negative

                    insert into @SPLITGAINLOSS
                    select 
                        SPLITID,
                        abs(BASEGAINLOSS),
                        0,
                        case
                            when BASEGAINLOSS > 0
                                then 1
                            else 0
                        end
                    from @SPLITREVALUATIONGAINLOSSES
                    where (BASEGAINLOSS * ORGANIZATIONGAINLOSS) < 0;

                    --Add org gain/loss rows to be returned if base and org are both not zero and one is positive while the other is negative

                    insert into @SPLITGAINLOSS
                    select 
                        SPLITID,
                        0,
                        abs(ORGANIZATIONGAINLOSS),
                        case
                            when ORGANIZATIONGAINLOSS > 0
                                then 1
                            else 0
                        end
                    from @SPLITREVALUATIONGAINLOSSES
                    where (BASEGAINLOSS * ORGANIZATIONGAINLOSS) < 0;
                end

                return;
            end