UFN_FAFADDRESSBOOK_PROSPECTS_LIST

Get the List of prospects contacts from FAF address book

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN
@CLIENTUSERID	int	IN

Definition

Copy



CREATE FUNCTION [dbo].[UFN_FAFADDRESSBOOK_PROSPECTS_LIST]
(
     @EVENTID        AS uniqueidentifier
    ,@CLIENTUSERID    AS INT
)
RETURNS TABLE
AS
RETURN
    WITH 
    [FAFADDRESSBOOKCONTACTS]
    AS
    (
  SELECT   A.ID AS CONTACTID  
    ,A.FIRSTNAME  
    ,A.LASTNAME  
    ,A.CONSTITUENTID  
  FROM [dbo].[ADDRESSBOOKFAF] A (NOLOCK)  
  LEFT JOIN [dbo].[UFN_FAFADDRESSBOOK_TEAMMEMBERS_LIST](@EVENTID,@CLIENTUSERID) TM    
  ON  A.CONSTITUENTID = TM.CONSTITUENTID          
  LEFT JOIN [dbo].UFN_FAFADDRESSBOOK_DONORS_LIST(@EVENTID,@CLIENTUSERID) D    
  ON  A.CONSTITUENTID = D.CONSTITUENTID  
  LEFT JOIN [dbo].UFN_FAFADDRESSBOOK_HOUSEHOLDMEMBERS_LIST(@EVENTID,@CLIENTUSERID) HH    
  ON  A.CONSTITUENTID = HH.CONSTITUENTID  
    LEFT JOIN [dbo].UFN_FAFADDRESSBOOK_PARTICIPANTS_LIST(@EVENTID,@CLIENTUSERID) P    
  ON  A.CONSTITUENTID = P.CONSTITUENTID    
  WHERE A.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERID))    
    AND  D.CONSTITUENTID  IS NULL  
    AND  TM.CONSTITUENTID IS NULL      
    AND  HH.CONSTITUENTID IS NULL  
    AND  P.CONSTITUENTID IS NULL          
    )
    SELECT * FROM  [FAFADDRESSBOOKCONTACTS];