UFN_FUNDRAISER_PROSPECTPLANS_3

        create function dbo.UFN_FUNDRAISER_PROSPECTPLANS_3
        (
            @FUNDRAISERID uniqueidentifier,
            @CHECKSECONDARYFUNDRAISERDATES bit = 0,
            @CURRENTDATEEARLIESTTIME datetime
        ) 
        returns table
        as return 
            with SECONDARYFUNDRAISER_CTE as
            (
                select 
                    SECONDARYFUNDRAISER.PROSPECTPLANID
                from dbo.SECONDARYFUNDRAISER
                where 
                    FUNDRAISERID = @FUNDRAISERID and 
                    (
                        coalesce(@CHECKSECONDARYFUNDRAISERDATES, 0) = 0 or
                        (
                            DATETO is null or 
                            DATETO >= @CURRENTDATEEARLIESTTIME
                        )
                    )
            )
            select 
                0 SEQUENCE, 
                'Primary manager, Secondary manager, Secondary solicitor' FUNDRAISERROLE, 
                ID 
            from dbo.PROSPECTPLAN 
            where 
                PRIMARYMANAGERFUNDRAISERID = @FUNDRAISERID and 
                SECONDARYMANAGERFUNDRAISERID = @FUNDRAISERID and
                --Manager's end date is not specified or it has not yet expired(end date is >= current date)

                (PRIMARYMANAGERENDDATE is null or PRIMARYMANAGERENDDATE >= @CURRENTDATEEARLIESTTIME) and
                (SECONDARYMANAGERENDDATE is null or SECONDARYMANAGERENDDATE >= @CURRENTDATEEARLIESTTIME) and
                exists 
                (
                    select 1 
                    from SECONDARYFUNDRAISER_CTE 
                    where 
                        PROSPECTPLANID = PROSPECTPLAN.ID
                )

            union all

            select 
                1 SEQUENCE, 
                'Primary manager, Secondary manager' FUNDRAISERROLE, 
                ID
            from dbo.PROSPECTPLAN 
            where 
                PRIMARYMANAGERFUNDRAISERID = @FUNDRAISERID and 
                SECONDARYMANAGERFUNDRAISERID = @FUNDRAISERID and 
                --Manager's end date is not specified or it has not yet expired(end date is >= current date)

                (PRIMARYMANAGERENDDATE is null or PRIMARYMANAGERENDDATE >= @CURRENTDATEEARLIESTTIME) and
                (SECONDARYMANAGERENDDATE is null or SECONDARYMANAGERENDDATE >= @CURRENTDATEEARLIESTTIME) and
                not exists 
                (
                    select 1 
                    from SECONDARYFUNDRAISER_CTE 
                    where 
                        PROSPECTPLANID = PROSPECTPLAN.ID
                )

            union all

            select 
                2 SEQUENCE, 
                'Primary manager, Secondary solicitor' FUNDRAISERROLE, 
                ID 
            from dbo.PROSPECTPLAN 
            where 
                PRIMARYMANAGERFUNDRAISERID = @FUNDRAISERID and
                (
                    SECONDARYMANAGERFUNDRAISERID is null or 
                    SECONDARYMANAGERFUNDRAISERID <> @FUNDRAISERID or
                    SECONDARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME
                ) and 
                (
                    --Manager's end date is not specified or it has not yet expired(end date is >= current date)

                    PRIMARYMANAGERENDDATE is null or
                    PRIMARYMANAGERENDDATE >= @CURRENTDATEEARLIESTTIME
                ) and
                exists 
                (
                    select 1 
                    from SECONDARYFUNDRAISER_CTE 
                    where 
                        PROSPECTPLANID = PROSPECTPLAN.ID
                )

        union all

        select 
            3 SEQUENCE,
            'Secondary manager, Secondary solicitor'FUNDRAISERROLE,
            ID 
            from dbo.PROSPECTPLAN 
            where 
            (
                PRIMARYMANAGERFUNDRAISERID is null or 
                PRIMARYMANAGERFUNDRAISERID!=@FUNDRAISERID or
                PRIMARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME
            ) and 
            (
                SECONDARYMANAGERFUNDRAISERID =@FUNDRAISERID 
            ) and 
            (
                --Manager's end date is not specified or it has not yet expired(end date is >= current date)

                SECONDARYMANAGERENDDATE is null or
                SECONDARYMANAGERENDDATE >= @CURRENTDATEEARLIESTTIME
            ) and
            exists
            (
                select 1 
                from SecondaryFundraiser_CTE 
                where 
                    PROSPECTPLANID=PROSPECTPLAN.ID
            )

        union all

        select 
            4 SEQUENCE, 
            'Primary manager' FUNDRAISERROLE, 
            ID 
        from dbo.PROSPECTPLAN 
        where 
            PRIMARYMANAGERFUNDRAISERID =@FUNDRAISERID and 
            (
                SECONDARYMANAGERFUNDRAISERID is null or 
                SECONDARYMANAGERFUNDRAISERID!=@FUNDRAISERID or
                SECONDARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME
            ) and 
            (
                --Manager's end date is not specified or it has not yet expired(end date is >= current date)

                PRIMARYMANAGERENDDATE is null or 
                PRIMARYMANAGERENDDATE >= @CURRENTDATEEARLIESTTIME
            ) and
            not exists 
            (
                select 1 
                from SecondaryFundraiser_CTE 
                where 
                    PROSPECTPLANID=PROSPECTPLAN.ID
            )

        union all

        select 
            5 SEQUENCE,
            'Secondary manager' FUNDRAISERROLE,
            ID 
        from dbo.PROSPECTPLAN 
        where 
        (
            PRIMARYMANAGERFUNDRAISERID is null or 
            PRIMARYMANAGERFUNDRAISERID!=@FUNDRAISERID or
            PRIMARYMANAGERENDDATE < @CURRENTDATEEARLIESTTIME
        ) and
        (
            SECONDARYMANAGERFUNDRAISERID =@FUNDRAISERID
            and
            (
                --Manager's end date is not specified or it has not yet expired(end date is >= current date)

                SECONDARYMANAGERENDDATE is null or 
                SECONDARYMANAGERENDDATE >= @CURRENTDATEEARLIESTTIME
            ) 
        ) and 
        not exists
        (
            select 1 
            from SecondaryFundraiser_CTE 
            where 
                PROSPECTPLANID=PROSPECTPLAN.ID
        )

        union all

        select 
            6 SEQUENCE, 
            'Secondary solicitor' FUNDRAISERROLE, 
            ID 
        from dbo.PROSPECTPLAN 
        where 
        (
            PRIMARYMANAGERFUNDRAISERID is null or 
            PRIMARYMANAGERFUNDRAISERID!=@FUNDRAISERID
        ) 
        and 
        (
            SECONDARYMANAGERFUNDRAISERID is null or 
            SECONDARYMANAGERFUNDRAISERID!=@FUNDRAISERID
        ) 
        and exists 
        (
            select 1 
            from SecondaryFundraiser_CTE 
            where 
                PROSPECTPLANID=PROSPECTPLAN.ID
        )