UFN_GETPROSPECTASSIGNMENTSXML

            CREATE function dbo.UFN_GETPROSPECTASSIGNMENTSXML
            (
                @FUNDRAISER uniqueidentifier
            )
            returns xml
            with execute as caller
            as begin

                if @FUNDRAISER = '00000000-0000-0000-0000-000000000000' set @FUNDRAISER = null; 
                declare @NOW as datetime;
                set @NOW = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                declare @OUTPUT xml;

                set @OUTPUT = ( 
                    select 
                        PROSPECTID,
                        TYPECODE,
                        PROSPECTPLANID,
                        SECONDARYID,
                        PROSPECTTEAMROLECODEID,
                        CURRENTSTARTDATE,
                        ASSIGNTOID,
                        STARTDATE
                    from (

                        --Prospect managers

                        select 
                            PROSPECT.ID as PROSPECTID, 
                            0 as TYPECODE, 
                            null as PROSPECTPLANID, 
                            null as SECONDARYID, 
                            null as PROSPECTTEAMROLECODEID, 
                            --PBI#237207 - Arun Saini

                            --Do not fetch fundraiser data if we are loading unassigned prospects and empty start date in every case

                            case when @FUNDRAISER is null then null else PROSPECT.PROSPECTMANAGERSTARTDATE end CURRENTSTARTDATE,
                            case when @FUNDRAISER is null then null else PROSPECT.PROSPECTMANAGERFUNDRAISERID end ASSIGNTOID,
                            null as STARTDATE
                        from 
                            dbo.PROSPECT
                        where 
                            (
                                --Load unassigned

                                @FUNDRAISER is null 
                                and 
                                (
                                    -- No current prospect manager

                                    PROSPECT.PROSPECTMANAGERFUNDRAISERID is null
                                    or
                                    PROSPECT.PROSPECTMANAGERENDDATE <= @NOW
                                )
                            )
                            or 
                            (
                                -- Current prospect manager matches

                                PROSPECT.PROSPECTMANAGERFUNDRAISERID = @FUNDRAISER
                                and
                                (
                                    PROSPECT.PROSPECTMANAGERENDDATE is null
                                    or
                                    PROSPECT.PROSPECTMANAGERENDDATE >= @NOW
                                )
                            )

                        union all

                        --Team members

                        select 
                            PROSPECTTEAM.PROSPECTID, 
                            1 as TYPECODE, 
                            null as PROSPECTPLANID, 
                            PROSPECTTEAM.ID as SECONDARYID, 
                            PROSPECTTEAM.PROSPECTTEAMROLECODEID, 
                            --PBI#237207 - Arun Saini

                            --Do not fetch fundraiser data if we are loading unassigned prospects and empty start date in every case

                            case when @FUNDRAISER is null then null else PROSPECTTEAM.DATEFROM end CURRENTSTARTDATE,
                            case when @FUNDRAISER is null then null else PROSPECTTEAM.MEMBERID end ASSIGNTOID,
                            null as STARTDATE
                        from 
                            dbo.PROSPECTTEAM
                        where 
                            -- If @FUNDRAISER is null (Load unassigned), do not load rows for team members because they are many-to-one

                            PROSPECTTEAM.MEMBERID = @FUNDRAISER
                            and
                            (
                                PROSPECTTEAM.DATETO is null
                                or
                                PROSPECTTEAM.DATETO >= @NOW
                            )

                        union all

                        --Primary Plan managers

                        select 
                            PROSPECTPLAN.PROSPECTID, 
                            2 as TYPECODE, 
                            PROSPECTPLAN.ID as PROSPECTPLANID, 
                            null as SECONDARYID, 
                            null as PROSPECTTEAMROLECODEID, 
                            --PBI#237207 - Arun Saini

                            --Do not fetch fundraiser data if we are loading unassigned prospects and empty start date in every case

                            case when @FUNDRAISER is null then null else PROSPECTPLAN.PRIMARYMANAGERSTARTDATE end CURRENTSTARTDATE,
                            case when @FUNDRAISER is null then null else PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID end ASSIGNTOID,
                            null as STARTDATE
                        from 
                            dbo.PROSPECTPLAN
                        where
                        PROSPECTPLAN.ISACTIVE = 1 
                        and
                        (
                            (
                                --Load unassigned

                                @FUNDRAISER is null 
                                and 
                                (
                                    PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID is null
                                    or
                                    PROSPECTPLAN.PRIMARYMANAGERENDDATE <= @NOW
                                )
                            )
                            or
                            (
                                PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID = @FUNDRAISER
                                and
                                (
                                    PROSPECTPLAN.PRIMARYMANAGERENDDATE is null
                                    or
                                    PROSPECTPLAN.PRIMARYMANAGERENDDATE >= @NOW
                                )
                            )
                        )

                        union all

                        --Secondary Plan managers

                        select 
                            PROSPECTPLAN.PROSPECTID, 
                            3 as TYPECODE, 
                            PROSPECTPLAN.ID as PROSPECTPLANID, 
                            null as SECONDARYID, 
                            null as PROSPECTTEAMROLECODEID, 
                            --PBI#237207 - Arun Saini

                            --Do not fetch fundraiser data if we are loading unassigned prospects and empty start date in every case                            

                            case when @FUNDRAISER is null then null else PROSPECTPLAN.SECONDARYMANAGERSTARTDATE end CURRENTSTARTDATE,
                            case when @FUNDRAISER is null then null else PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID end ASSIGNTOID,
                            null as STARTDATE
                        from 
                            dbo.PROSPECTPLAN
                        where 
                        PROSPECTPLAN.ISACTIVE = 1 
                        and
                        (
                            (
                                --Load unassigned

                                @FUNDRAISER is null 
                                and 
                                (
                                    PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID is null
                                    or
                                    PROSPECTPLAN.SECONDARYMANAGERENDDATE <= @NOW
                                )
                            )
                            or
                            (
                                PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID = @FUNDRAISER
                                and
                                (
                                    PROSPECTPLAN.SECONDARYMANAGERENDDATE is null
                                    or
                                    PROSPECTPLAN.SECONDARYMANAGERENDDATE >= @NOW
                                )
                            )
                        )
                        union all

                        --Secondary solicitors

                        select 
                            PROSPECTPLAN.PROSPECTID, 
                            4 as TYPECODE, 
                            PROSPECTPLAN.ID as PROSPECTPLANID, 
                            SECONDARYFUNDRAISER.ID as SECONDARYID, 
                            null as PROSPECTTEAMROLECODEID, 
                            --PBI#237207 - Arun Saini

                            --Do not fetch fundraiser data if we are loading unassigned prospects and empty start date in every case                            

                            case when @FUNDRAISER is null then null else SECONDARYFUNDRAISER.DATEFROM end CURRENTSTARTDATE,
                            case when @FUNDRAISER is null then null else SECONDARYFUNDRAISER.FUNDRAISERID end ASSIGNTOID,
                            null as STARTDATE
                        from 
                            dbo.SECONDARYFUNDRAISER
                            inner join dbo.PROSPECTPLAN on SECONDARYFUNDRAISER.PROSPECTPLANID = PROSPECTPLAN.ID
                        where 
                        PROSPECTPLAN.ISACTIVE = 1 
                        and
                        -- If @FUNDRAISER is null (Load unassigned), do not load rows for secondary solicitors because they are many-to-one

                          SECONDARYFUNDRAISER.FUNDRAISERID = @FUNDRAISER
                            and 
                            (
                                SECONDARYFUNDRAISER.DATETO is null 
                                or 
                                SECONDARYFUNDRAISER.DATETO >= @NOW
                            )

                    ) a
                    inner join dbo.CONSTITUENT on a.PROSPECTID = CONSTITUENT.ID
                    inner join dbo.PROSPECTDATERANGE on a.PROSPECTID = PROSPECTDATERANGE.CONSTITUENTID
                    where
                        -- copied from UFN_CONSTITUENT_ISPROSPECT - only show active prospects

                        (PROSPECTDATERANGE.DATEFROM <= dbo.UFN_DATE_GETLATESTTIME(@NOW) or PROSPECTDATERANGE.DATEFROM is null) and
                        (PROSPECTDATERANGE.DATETO >= @NOW or PROSPECTDATERANGE.DATETO is null)
                    order by CONSTITUENT.KEYNAME
                        for xml raw('ITEM'),type,elements,root('ASSIGNMENTS'),binary base64
                );

                return @OUTPUT;
            end