UFN_PROSPECTRESEARCHREQUESTCONSTITUENT_GETADDITIONALCONSTITUENTSINRESEARCHGROUP

Returns all constituents in a given prospect research request's research group that are not already in the request.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@PROSPECTRESEARCHREQUESTID	uniqueidentifier	IN

Definition

Copy



create function dbo.UFN_PROSPECTRESEARCHREQUESTCONSTITUENT_GETADDITIONALCONSTITUENTSINRESEARCHGROUP(@PROSPECTRESEARCHREQUESTID uniqueidentifier)
returns table
as 
return
(
    select
        C.ID as CONSTITUENTID,
        C.NAME as CONSTITUENTNAME
    from
        dbo.CONSTITUENT C
    where
        C.ISORGANIZATION = 0 and C.ISGROUP = 0 and
        C.ID not in (select PRRC.CONSTITUENTID from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT PRRC where PRRC.PROSPECTRESEARCHREQUESTID = @PROSPECTRESEARCHREQUESTID)
        and C.ID in (select RGM.CONSTITUENTID from dbo.RESEARCHGROUPMEMBER RGM where RGM.RESEARCHGROUPID = (select RESEARCHGROUPID from dbo.PROSPECTRESEARCHREQUEST where ID = @PROSPECTRESEARCHREQUESTID))
)