UFN_SELECTION_CONSTITUENT_ALLDONORS

        CREATE function dbo.UFN_SELECTION_CONSTITUENT_ALLDONORS()
        returns @IDS table (ID uniqueidentifier)
        as 
        begin
            declare @DONORCONSTITUENCYDEFTYPECODE int;
            declare @DONORCONSTITUENCYDEFLASTYEARS int;
            declare @DONORCONSTITUENCYDEFSTARTDATE datetime;
            declare @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION bit;

            select
                @DONORCONSTITUENCYDEFTYPECODE = DONORCONSTITUENCYDEFTYPECODE,
                @DONORCONSTITUENCYDEFLASTYEARS = DONORCONSTITUENCYDEFLASTYEARS,
                @DONORCONSTITUENCYDEFSTARTDATE = DONORCONSTITUENCYDEFSTARTDATE
            from
                dbo.INSTALLATIONINFO;

            select top 1 
                @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION
            from 
                dbo.CONSTITUENCYCRITERIASPOUSE;

            /*
            MAB 11/3/10 Hiding lifecycle functionality
            select top 1
                @DONORCONSTITUENCYDEFTYPECODE = DONORFILTERTYPECODE, 
                @DONORCONSTITUENCYDEFLASTYEARS = DONORPERIOD, 
                @DONORCONSTITUENCYDEFSTARTDATE = DONORASOFDATE, 
                @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = CONSIDERRECOGNITION 
            from 
                dbo.REVENUELIFECYCLECRITERIA 
            */

            declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();

            declare @UPPERBOUND datetime;
            declare @LOWERBOUND datetime;
            set @UPPERBOUND = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

            /*
            MAB 11/3/10 Hiding lifecycle functionality
            if @DONORCONSTITUENCYDEFTYPECODE = 1
            */            
            if @DONORCONSTITUENCYDEFTYPECODE = 0
                set @LOWERBOUND = dateadd(year, 0 - @DONORCONSTITUENCYDEFLASTYEARS, @UPPERBOUND);
            else
                set @LOWERBOUND = dbo.UFN_DATE_GETEARLIESTTIME(@DONORCONSTITUENCYDEFSTARTDATE);

            -- Add lifecycle donors first

            insert into @IDS(ID)
            select
                CONSTITUENTID
            from
                dbo.REVENUELIFECYCLE
                inner join dbo.LIFECYCLETYPE on LIFECYCLETYPE.ID = REVENUELIFECYCLE.LIFECYCLETYPEID
            where   
                CONSTITUENTID is not null and -- Altru allows revenue without donors

                LIFECYCLETYPE.NAME in ('Consecutive years donor', 'First year donor', 'Loyal donor', 'Major donor', 'Mid-level donor', 'Multiple years donor') and
                LIFECYCLETYPE.LIFECYCLEGROUPCODE = 0;

            -- Add any constituents that have not had their lifecycle values calculated yet

            if @MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1
            begin
                insert into @IDS (ID)
                select distinct
                    REVENUERECOGNITION.CONSTITUENTID
                from
                    dbo.REVENUE
                    left join REVENUESPLIT on REVENUESPLIT.REVENUEID=REVENUE.ID
                    inner join dbo.REVENUERECOGNITION with (nolock) on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
                where
                    REVENUERECOGNITION.CONSTITUENTID is not null and -- Altru allows revenue without donors

                    (
                        REVENUE.TRANSACTIONTYPECODE = 1 
                        or (REVENUE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0,3) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)))
                        or (REVENUE.TRANSACTIONTYPECODE = 5 and REVENUESPLIT.TYPECODE = 0)
                    )  --TMV 05/21/2007 CR273655-042407 Only consider gift payment, pledge, or recurring gift payment for donor constituency.

                    and
                    (
                        (DATE between @LOWERBOUND and @UPPERBOUND)
                        or 
                        (@LOWERBOUND is null and DATE < @UPPERBOUND)
                    )
                    and
                    REVENUERECOGNITION.CONSTITUENTID not in (select ID from @IDS)
                union 
                select distinct
                    REVENUE.CONSTITUENTID
                from
                    dbo.REVENUE
                    left join REVENUESPLIT on REVENUESPLIT.REVENUEID=REVENUE.ID
                where
                    REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                    (   
                  REVENUE.TRANSACTIONTYPECODE = 1 
                        or (REVENUE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0,3) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)))
                        or (REVENUE.TRANSACTIONTYPECODE = 5 and REVENUESPLIT.TYPECODE = 0)
                    )  --TMV 05/21/2007 CR273655-042407 Only consider gift payment, pledge, or recurring gift payment for donor constituency.

                    and
                    (
                        (DATE between @LOWERBOUND and @UPPERBOUND)
                        or 
                        (@LOWERBOUND is null and DATE < @UPPERBOUND)
                    )
                    and
                    REVENUE.CONSTITUENTID not in (select ID from @IDS)
            end
            else
            begin
                insert into @IDS (ID)
                select distinct
                    REVENUE.CONSTITUENTID
                from
                    dbo.REVENUE
                    left join REVENUESPLIT on REVENUESPLIT.REVENUEID=REVENUE.ID
                where
                    REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                    (
                        REVENUE.TRANSACTIONTYPECODE = 1 or 
                        (REVENUE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0,3) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0))) or 
                        (REVENUE.TRANSACTIONTYPECODE = 5 and REVENUESPLIT.TYPECODE = 0)
                    )  --TMV 05/21/2007 CR273655-042407 Only consider gift payment, pledge, or recurring gift payment for donor constituency.

                    and
                    (
                        (DATE between @LOWERBOUND and @UPPERBOUND)
                        or 
                        (@LOWERBOUND is null and DATE < @UPPERBOUND)
                    )
                    and
                    REVENUE.CONSTITUENTID not in (select ID from @IDS)
            end
            return
        end