UFN_CORPORATESTRUCTURE_GETDONORTOTAL

            CREATE function [dbo].[UFN_CORPORATESTRUCTURE_GETDONORTOTAL]
            (
                @ROOTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null 
            )
            returns int
            as
                begin
                declare @i int;    

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

                select 
                    @i = count(distinct(RELATIONSHIP.RELATIONSHIPCONSTITUENTID))    
                from 
                    dbo.RELATIONSHIP 
                inner join 
                    dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on RELATIONSHIP.RELATIONSHIPTYPECODEID = EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID
                inner join 
                    dbo.CORPORATESTRUCTURESELECTION as CSS on CSS.SELECTEDID = RELATIONSHIP.RECIPROCALCONSTITUENTID
                inner join 
                    dbo.FINANCIALTRANSACTION REVENUE on REVENUE.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                inner join 
                    dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                inner join
                    dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                left join 
                    dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS as PLANNEDGIFTREVENUE on PLANNEDGIFTREVENUE.REVENUEID = REVENUE.ID
                left join 
                    dbo.PLANNEDGIFT on PLANNEDGIFT.ID = PLANNEDGIFTREVENUE.PLANNEDGIFTID
                cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVENUESITE
                where 
                    CSS.ROOTID = @ROOTID
                    and CSS.APPUSERID = @CURRENTAPPUSERID
                    and (@STARTDATE is null or cast(REVENUE.DATE as datetime) >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
                    and (@ENDDATE is null or cast(REVENUE.DATE as datetime) <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
                    and (REVENUE.TYPECODE = 1 or --Pledge

                        (REVENUE.TYPECODE = 4 and PLANNEDGIFT.VEHICLECODE in (0,1,2,5,6,7,8,9)) or --Planned gift

                        (REVENUE.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE in (0, 3, 18, 19))) --Payments                        

                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, REVENUESITE.SITEID) = 1
                    and REVENUE.DELETEDON is null
                    and REVENUESPLIT.DELETEDON is null
                    and REVENUESPLIT.TYPECODE <> 1

                return @i;
                end