UFN_DESIGNATIONLEVEL_GETPLEDGEBALANCE

            CREATE function dbo.UFN_DESIGNATIONLEVEL_GETPLEDGEBALANCE
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null
            ) 
            returns money
            with execute as caller
            as begin


                -- THIS FUNCTION IS POSSIBLY DEPRECATED



                declare @RESULT money;
                set @RESULT = 0;

                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                        @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                with DESIGNATIONS_CTE as (
                    select ID 
                    from dbo.DESIGNATION D
                    where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID or 
                          D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
                    )

                select @RESULT = 
                    coalesce(sum(RDS.AMOUNT), 0)
                    -
                    (
                    coalesce(( --Subtract payments of the above pledges

                        select coalesce(sum(PAYSPLIT.AMOUNT), 0)
                        from dbo.REVENUESPLIT PAYSPLIT
                        inner join DESIGNATIONS_CTE PAYD on PAYSPLIT.DESIGNATIONID = PAYD.ID
                        inner join dbo.REVENUE PAY on PAY.ID = PAYSPLIT.REVENUEID
                        where
                            PAYSPLIT.ID IN
                                (SELECT IP.PAYMENTID
                                    FROM DBO.INSTALLMENTPAYMENT IP
                                    inner join dbo.REVENUE PLEDGE on IP.PLEDGEID = PLEDGE.ID
                                    inner join dbo.REVENUESPLIT PLEDGESPLIT on PLEDGE.ID = PLEDGESPLIT.REVENUEID
                                    inner join DESIGNATIONS_CTE PD on PLEDGESPLIT.DESIGNATIONID = PD.ID
                                    WHERE (PLEDGE.DATE >= @STARTDATE or @STARTDATE is null) and
                                        (PLEDGE.DATE <= @ENDDATE or @ENDDATE is null))
                             and
                            (PAY.DATE >= @STARTDATE or @STARTDATE is null) and
                            (PAY.DATE <= @ENDDATE or @ENDDATE is null)
                    ), 0)
                    +
                    coalesce(( --Subtract Writeoffs of the above pledges

                        select sum(WOS.AMOUNT)
                        from dbo.WRITEOFFSPLIT WOS
                        inner join DESIGNATIONS_CTE WOSD on WOS.DESIGNATIONID = WOSD.ID
                        inner join dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
                        inner join dbo.REVENUE R on WO.REVENUEID = R.ID
                        where 
                            (R.DATE >= @STARTDATE or @STARTDATE is null) and
                            (R.DATE <= @ENDDATE or @ENDDATE is null) and
                            R.TRANSACTIONTYPECODE in (1,3,4,6,8)
                    ), 0))

                from dbo.REVENUESPLIT RDS
                inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
                inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                where
                    (R.DATE >= @STARTDATE or @STARTDATE is null) and
                    (R.DATE <= @ENDDATE or @ENDDATE is null) and
                    R.TRANSACTIONTYPECODE in (1,3,4,6,8)
                return @RESULT;
            end