UFN_FAF_COMPANYTEAMLEADERLIST
This table function return a list of team leaders directly under a company, input parameter is the team captain ID
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @EVENTID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_FAF_COMPANYTEAMLEADERLIST
(
@CONSTITUENTID uniqueidentifier = NULL
,@EVENTID uniqueidentifier = NULL
)
returns table
as
return
(
select
FNTEAM.TEAMNAME,
FNTEAM.TEAMID,
C.keyname,
C.name,
C.ID,
(CASE TE.TYPECODE WHEN 1 THEN 'Team Leaders' ELSE 'Household Leaders' END) AS groupname
from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID) FNTEAM
INNER JOIN dbo.TEAMFUNDRAISINGTEAM TFT --get teams under this company
ON FNTEAM.TEAMID = TFT.PARENTTEAMID
INNER JOIN TEAMEXTENSION TE
ON TFT.ID = TE.TEAMFUNDRAISINGTEAMID
AND TE.TYPECODE IN (1,3) AND TE.STATUSCODE = 0
INNER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC --retrieve team leaders
ON TFT.ID = TC.TEAMFUNDRAISINGTEAMID
INNER JOIN dbo.CONSTITUENT C
ON TC.CONSTITUENTID = C.ID
WHERE FNTEAM.TEAMTYPECODE = 2 --company
)