UFN_FUNDRAISER_PROSPECTPLANS_2

        create function dbo.UFN_FUNDRAISER_PROSPECTPLANS_2
        (
            @FUNDRAISERID uniqueidentifier,
            @CHECKSECONDARYFUNDRAISERDATES bit = 0
        ) 
        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 >= dbo.UFN_DATE_GETEARLIESTTIME(getdate())
                        )
                    )
            )
            select 
                0 SEQUENCE, 
                'Primary manager, Secondary manager, Secondary solicitor' FUNDRAISERROLE, 
                ID 
            from dbo.PROSPECTPLAN 
            where 
                PRIMARYMANAGERFUNDRAISERID = @FUNDRAISERID and 
                SECONDARYMANAGERFUNDRAISERID = @FUNDRAISERID 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 
                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
                ) 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
            ) and 
            (
                SECONDARYMANAGERFUNDRAISERID =@FUNDRAISERID 
            ) 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
            ) 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
        ) and
        (
            SECONDARYMANAGERFUNDRAISERID =@FUNDRAISERID
        ) 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
        )