UFN_DESIGNATIONLEVEL_GETMAXGIFT

            create function dbo.UFN_DESIGNATIONLEVEL_GETMAXGIFT
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null
            ) 
            returns money
            with execute as caller
            as begin
                declare @RESULT money;

                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 = max(SPLITAMOUNT)
                from (
                    select (RDS.AMOUNT - coalesce(WOS.AMOUNT, 0)) AS SPLITAMOUNT,
                        RDS.DESIGNATIONID AS DESIGNATIONID
                    from dbo.REVENUESPLIT RDS
                        inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                        left join dbo.WRITEOFF WO on R.ID = WO.REVENUEID
                        left join dbo.WRITEOFFSPLIT WOS on WO.ID = WOS.WRITEOFFID
                    where
                        (RDS.DESIGNATIONID = WOS.DESIGNATIONID OR WOS.DESIGNATIONID IS NULL) AND
                        (R.DATE >= @STARTDATE or @STARTDATE is null) and
                        (R.DATE <= @ENDDATE or @ENDDATE is null) and 
                                    R.TYPECODE <> 2
                    ) as T1
                inner join DESIGNATIONS_CTE on T1.DESIGNATIONID = DESIGNATIONS_CTE.ID

                return @RESULT;
            end