UFN_NAMINGOPPORTUNITY_ALLPROSPECTS

            CREATE function dbo.UFN_NAMINGOPPORTUNITY_ALLPROSPECTS(@NAMINGOPPORTUNITYID uniqueidentifier)
            returns table
            as return
            (
                select
                    PROSPECTPLAN.ID as [PROSPECTPLANID],
                    PROSPECT.ID as [PROSPECTID],
                    [PROSPECTCONSTITUENT].NAME as [PROSPECTNAME],
                    (select DESCRIPTION from dbo.PROSPECTPLANTYPECODE where PROSPECTPLANTYPECODE.ID = PROSPECTPLAN.PROSPECTPLANTYPECODEID) as [PLANTYPE],
                    (select DESCRIPTION from dbo.PROSPECTPLANSTATUSCODE where PROSPECTPLANSTATUSCODE.ID = dbo.UFN_PROSPECTPLAN_CURRENTSTATUS(PROSPECTPLAN.ID)) as [PLANSTAGE],
                    (select CONSTITUENT.NAME from dbo.CONSTITUENT where CONSTITUENT.ID = PROSPECT.PROSPECTMANAGERFUNDRAISERID AND (PROSPECT.PROSPECTMANAGERENDDATE is null OR PROSPECT.PROSPECTMANAGERENDDATE >= dbo.UFN_DATE_GETEARLIESTTIME(getdate()))) as [PROSPECTMANAGER],
                    (select CONSTITUENT.NAME from dbo.CONSTITUENT where CONSTITUENT.ID = PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID AND (PROSPECTPLAN.PRIMARYMANAGERENDDATE is null OR PROSPECTPLAN.PRIMARYMANAGERENDDATE >= dbo.UFN_DATE_GETEARLIESTTIME(getdate()))) as [PRIMARYMANAGER],
                    (select CONSTITUENT.NAME from dbo.CONSTITUENT where CONSTITUENT.ID = PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID AND (PROSPECTPLAN.SECONDARYMANAGERENDDATE is null OR PROSPECTPLAN.SECONDARYMANAGERENDDATE >= dbo.UFN_DATE_GETEARLIESTTIME(getdate()))) as [SECONDARYMANAGER],
                    (select dbo.UDA_BUILDLIST(FUNDRAISERNAME) from dbo.UFN_PROSPECTPLANVIEW_SECONDARYFUNDRAISERS(PROSPECTPLAN.ID)) as [SECONDARYFUNDRAISERS],
                    NAMINGOPPORTUNITYMGOPPORTUNITY.DATEADDED as [PUBLICATIONDATE],
                    OPPORTUNITY.ID as [OPPORTUNITYID],
                    OPPORTUNITY.STATUSCODE [OPPORTUNITYSTATUSCODE],
                    OPPORTUNITY.STATUS as [OPPORTUNITYSTATUS]
                from dbo.NAMINGOPPORTUNITYMGOPPORTUNITY
                inner join dbo.OPPORTUNITY on OPPORTUNITY.ID = NAMINGOPPORTUNITYMGOPPORTUNITY.OPPORTUNITYID
                inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
                inner join dbo.CONSTITUENT as [PROSPECTCONSTITUENT] on [PROSPECTCONSTITUENT].ID = PROSPECTPLAN.PROSPECTID
                inner join dbo.PROSPECT on PROSPECT.ID = PROSPECTPLAN.PROSPECTID
                where NAMINGOPPORTUNITYMGOPPORTUNITY.NAMINGOPPORTUNITYID = @NAMINGOPPORTUNITYID
            )