UFN_FAF_HOUSEHOLDLEADERLIST

This table function return a list of household leaders, input parameter is household leader or member ID

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN
@EVENTID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_FAF_HOUSEHOLDLEADERLIST
(
@CONSTITUENTID uniqueidentifier = NULL
,@EVENTID uniqueidentifier = NULL
)

   returns table  
   as  

   return  
   (  
    select 
     FNTEAM.TEAMNAME,  
     FNTEAM.TEAMID,
     C.keyname, 
     C.name,
     C.ID 
    from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID) FNTEAM
    INNER JOIN dbo.TEAMFUNDRAISINGTEAM TFT
        ON FNTEAM.TEAMID = TFT.ID
    INNER JOIN TEAMEXTENSION TE  -- get those household

        ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID  
        AND TE.TYPECODE = 3  
    INNER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --retrieve their leaders

        ON TFT.ID = TC.TEAMFUNDRAISINGTEAMID
    INNER JOIN dbo.CONSTITUENT C
        ON TC.CONSTITUENTID = C.ID  
    WHERE FNTEAM.TEAMTYPECODE = 3 
)