UFN_SELECTION_CONSTITUENT_MAJORDONORS

        CREATE function dbo.UFN_SELECTION_CONSTITUENT_MAJORDONORS()
        returns table
        as return
        (

            with CONSTITUENCYCRITERIASPOUSE_CTE as
            (
                select top 1
                    MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT, 
                    MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT,
                    MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION
                from dbo.CONSTITUENCYCRITERIASPOUSE
            )
            select distinct REVENUEANDRECOGNITION.CONSTITUENTID as ID 
            from
            (
                select
                    REVENUE.CONSTITUENTID,
                    REVENUE.ORGAMOUNT as AMOUNT
                from dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                inner join dbo.REVENUE_EXT with (nolock) on REVENUE.ID = REVENUE_EXT.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock) on FTLI.FINANCIALTRANSACTIONID = REVENUE.ID
                where 
                    REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                    REVENUE.DELETEDON is null and FTLI.DELETEDON is null and
                    FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())

                union all

                select
                    REVENUERECOGNITION.CONSTITUENTID,
                    REVENUERECOGNITION.ORGANIZATIONAMOUNT as AMOUNT
                from dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
                inner join dbo.REVENUE_EXT with (nolock) on REVENUE.ID = REVENUE_EXT.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                inner join dbo.REVENUESPLIT_EXT with (nolock) on REVENUESPLIT.ID = REVENUESPLIT_EXT.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.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID and
                    REVENUE.DELETEDON is null and
                    REVENUESPLIT.DELETEDON is null and
                    REVENUESPLIT.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())

                union all

                select
                    RECOGNITIONCREDIT.CONSTITUENTID,
                    RECOGNITIONCREDIT.ORGANIZATIONAMOUNT as AMOUNT
                from dbo.RECOGNITIONCREDIT with (nolock)
                inner join dbo.DONORCHALLENGEENCUMBERED with (nolock) on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock) on FTLI.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
                where
                    RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and
                    FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())

            ) as REVENUEANDRECOGNITION
            cross join CONSTITUENCYCRITERIASPOUSE_CTE
            where 
                MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1
            group by 
                REVENUEANDRECOGNITION.CONSTITUENTID,
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT,
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT
            having 
            (
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT <> 0 and 
                max(REVENUEANDRECOGNITION.AMOUNT) >= CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT
            ) or 
            (
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT <> 0 and 
                sum(REVENUEANDRECOGNITION.AMOUNT) >= CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT
            )

            union all

            select distinct CONSTITUENTID as ID 
            from dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
            inner join dbo.REVENUE_EXT with (nolock) on REVENUE.ID = REVENUE_EXT.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock) on FTLI.FINANCIALTRANSACTIONID = REVENUE.ID
            cross join CONSTITUENCYCRITERIASPOUSE_CTE
            where 
                REVENUE.CONSTITUENTID is not null and -- Altru allows revenue without donors

                MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 0 and
                REVENUE.DELETEDON is null and
                FTLI.DELETEDON is null and
                FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT())
            group by 
                CONSTITUENTID,
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT,
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT
            having 
            (
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT <> 0 and 
                max(FTLI.ORGAMOUNT) >= CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLARGESTGIFTAMOUNT
            ) or 
            (
                CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT <> 0 and 
                sum(FTLI.ORGAMOUNT) >= CONSTITUENCYCRITERIASPOUSE_CTE.MAJORDONORCONSTITUENCYDEFLIFETIMEAMOUNT
            )


        )