UFN_PLEDGE_GETINSTALLMENTSPLITREVALUATIONGAINLOSS

            CREATE function dbo.UFN_PLEDGE_GETINSTALLMENTSPLITREVALUATIONGAINLOSS
            (
                @PLEDGEID uniqueidentifier
            ) 
            returns @INSTALLMENTSPLITGAINLOSS table(
                INSTALLMENTSPLITID 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 @INSTALLMENTSPLITREVALUATIONGAINLOSSES table(
                        INSTALLMENTSPLITID uniqueidentifier,
                        BASEGAINLOSS money,
                        ORGANIZATIONGAINLOSS money
                    );
                    insert into @INSTALLMENTSPLITREVALUATIONGAINLOSSES
                    select
                        INSTALLMENTSPLITS.ID,
                        (INSTALLMENTSPLITS.BASEBALANCE - ORGINIALINSTALLMENTSPLITS.BASEBALANCE),
                        (INSTALLMENTSPLITS.ORGANIZATIONBALANCE - ORGINIALINSTALLMENTSPLITS.ORGANIZATIONBALANCE)
                    from dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTSPLITBALANCES(@PLEDGEID,@CURRENTDATE,0) INSTALLMENTSPLITS
                        cross apply dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTSPLITBALANCES(@PLEDGEID,@CURRENTDATE,1) ORGINIALINSTALLMENTSPLITS
                    where INSTALLMENTSPLITS.ID = ORGINIALINSTALLMENTSPLITS.ID;    

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

                    delete @INSTALLMENTSPLITREVALUATIONGAINLOSSES
                    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 @INSTALLMENTSPLITGAINLOSS
                    select 
                        INSTALLMENTSPLITID,
                        abs(BASEGAINLOSS),
                        abs(ORGANIZATIONGAINLOSS),
                        case
                            when BASEGAINLOSS > 0 or ORGANIZATIONGAINLOSS > 0
                                then 1
                            else 0
                        end
                    from @INSTALLMENTSPLITREVALUATIONGAINLOSSES
                    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 @INSTALLMENTSPLITGAINLOSS
                    select 
                        INSTALLMENTSPLITID,
                        abs(BASEGAINLOSS),
                        0,
                        case
                            when BASEGAINLOSS > 0
                                then 1
                            else 0
                        end
                    from @INSTALLMENTSPLITREVALUATIONGAINLOSSES
                    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 @INSTALLMENTSPLITGAINLOSS
                    select 
                        INSTALLMENTSPLITID,
                        0,
                        abs(ORGANIZATIONGAINLOSS),
                        case
                            when ORGANIZATIONGAINLOSS > 0
                                then 1
                            else 0
                        end
                    from @INSTALLMENTSPLITREVALUATIONGAINLOSSES
                    where (BASEGAINLOSS * ORGANIZATIONGAINLOSS) < 0;

                end

                return;
            end