UFN_DESIGNATIONLEVEL_GETDONORS

            CREATE function [dbo].[UFN_DESIGNATIONLEVEL_GETDONORS]
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @STARTDATE datetime,
                @ENDDATE datetime,
                @CURRENTAPPUSERID uniqueidentifier = null
            )            
            returns @DONORS table
                (
                    CONSTITUENTID uniqueidentifier NOT NULL,
                    CONSTITUENTNAME nvarchar(154) NOT NULL,
                    CONSTITUENTKEYNAME nvarchar(100) NOT NULL,
                    REVENUEID uniqueidentifier NOT NULL,
                    DATE datetime NOT NULL,
                    AMOUNT money NOT NULL
                )
            as
            begin
                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                        @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                insert @DONORS
                select  REVENUE.CONSTITUENTID,
                        NF.NAME NAME,
                        C.KEYNAME,
                        REVENUE.ID,
                        REVENUE.DATE,
                        REVENUE.BASEAMOUNT AMOUNT
                from dbo.DESIGNATIONLEVEL DL
                inner join dbo.DESIGNATION D on (D.DESIGNATIONLEVEL1ID = DL.ID or
                             D.DESIGNATIONLEVEL2ID = DL.ID or
                             D.DESIGNATIONLEVEL3ID = DL.ID or             
                             D.DESIGNATIONLEVEL4ID = DL.ID or
                             D.DESIGNATIONLEVEL5ID = DL.ID)
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.DESIGNATIONID = D.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                inner join dbo.CONSTITUENT C on REVENUE.CONSTITUENTID = C.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                where 
                (DL.ID = @DESIGNATIONLEVELID) and
                REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) 
                and REVENUESPLIT.DELETEDON is null 
                and REVENUESPLIT.TYPECODE != 1
                and REVENUE.DELETEDON is null
                and
                        (REVENUE.DATE >= @STARTDATE or @STARTDATE is null) and
                        (REVENUE.DATE <= @ENDDATE or @ENDDATE is null) and
                        (REVENUE.TYPECODE in (1,3,7))
                  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
                                or dbo.UFN_DESIGNATION_USERHASSITEACCESS(D.ID, @CURRENTAPPUSERID) = 1
                                or @CURRENTAPPUSERID is null)
                --RDB 11/8/2010 - refactored or statement into union to gain some performance on big DBs

                union all
                select  REVENUE.CONSTITUENTID,
                        NF.NAME NAME,
                        C.KEYNAME,
                        REVENUE.ID,
                        REVENUE.DATE,
                        REVENUE.BASEAMOUNT AMOUNT                 
                from dbo.DESIGNATIONLEVEL DL
                inner join dbo.DESIGNATION D on (D.DESIGNATIONLEVEL1ID = DL.ID or
                             D.DESIGNATIONLEVEL2ID = DL.ID or
                             D.DESIGNATIONLEVEL3ID = DL.ID or
                             D.DESIGNATIONLEVEL4ID = DL.ID or
                             D.DESIGNATIONLEVEL5ID = DL.ID)
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.DESIGNATIONID = D.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                inner join dbo.CONSTITUENT C on REVENUE.CONSTITUENTID = C.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                where (DL.ID = @DESIGNATIONLEVELID) and
                REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) 
                and REVENUESPLIT.DELETEDON is null 
                and REVENUESPLIT.TYPECODE != 1
                and REVENUE.DELETEDON is null
                and 
                        (REVENUE.DATE >= @STARTDATE or @STARTDATE is null) and
                        (REVENUE.DATE <= @ENDDATE or @ENDDATE is null) and
                        (REVENUE.TYPECODE = 0 and (REVENUESPLIT_EXT.APPLICATIONCODE in (0,3) or (REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)))
                  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
                                or dbo.UFN_DESIGNATION_USERHASSITEACCESS(D.ID, @CURRENTAPPUSERID) = 1
                                or @CURRENTAPPUSERID is null)
                order by C.KEYNAME;

               return;
            end