UFN_PROSPECTRESEARCHREQUESTCONSTITUENT_GETADDITIONALCONSTITUENTSINEVENT

Returns all constituents in a given prospect research request's event 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_GETADDITIONALCONSTITUENTSINEVENT(@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 R.CONSTITUENTID from dbo.REGISTRANT R where R.EVENTID = (select EVENTID from dbo.PROSPECTRESEARCHREQUEST where ID = @PROSPECTRESEARCHREQUESTID))
)