UFN_FAF_TEAMMEMBERLIST

This function takes the registrantID and return a list of members from the team that registrant belongs to

Return

Return Type
table

Parameters

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

Definition

Copy



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

   returns table  
   as  

   return  
   (  
    select  
     FNTEAM.TEAMNAME,  
     FNTEAM.TEAMID,
     C.keyname, 
     C.name,
     C.ID 
    from  
     dbo.TEAMFUNDRAISINGTEAMMEMBER TM
     INNER JOIN dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID) FNTEAM
        ON TM.TEAMFUNDRAISINGTEAMID = FNTEAM.TEAMID
        AND FNTEAM.TEAMTYPECODE = 1  --team

     INNER JOIN dbo.TEAMFUNDRAISER TF
        ON TM.TEAMFUNDRAISERID = TF.ID
     INNER JOIN dbo.CONSTITUENT C
        ON TF.CONSTITUENTID = C.ID
     left outer join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --exclude team leaders

        on FNTEAM.TEAMID = TC.TEAMFUNDRAISINGTEAMID
        and C.ID = TC.CONSTITUENTID
     WHERE C.ID <> @CONSTITUENTID
      and TC.TEAMFUNDRAISINGTEAMID is null

     UNION  --Also retrieve household leaders when team has households


    select 
     FNTEAM.TEAMNAME,  
     FNTEAM.TEAMID,
     C.keyname, 
     C.name,
     C.ID 
    from TEAMFUNDRAISINGTEAM TFT
    INNER JOIN TEAMEXTENSION TE
        ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID
        AND TE.TYPECODE = 3  --households

    INNER JOIN dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID) FNTEAM
        ON TFT.PARENTTEAMID = FNTEAM.teamID
        AND FNTEAM.TEAMTYPECODE = 1  --team

    INNER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC
        ON TFT.ID = TC.TEAMFUNDRAISINGTEAMID
    INNER JOIN dbo.CONSTITUENT C
        ON TC.CONSTITUENTID = C.ID  
    WHERE C.ID <> @CONSTITUENTID
)