UFN_FAF_GETTEAMINFO_BY_REGISTRANT

CREATE function dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT
(
@CONSTITUENTID uniqueidentifier
,@EVENTID uniqueidentifier
)
returns table
AS 
return
(
SELECT RG.ID as [RegistrantID]
, TFT.ID as [TEAMID]
, TFT.Name AS [TEAMNAME] 
, TE.TYPECODE [TEAMTYPECODE]
, TFT.PARENTTEAMID as [PARENTTEAMID]
, TE.STATUSCODE [TEAMSTATUSCODE]
, TE.TEAMCONSTITUENTID
FROM dbo.REGISTRANT RG    
inner join dbo.EVENT EV ON EV.ID = RG.EVENTID  
inner join dbo.CONSTITUENT C ON C.ID=RG.CONSTITUENTID 
left outer join dbo.TEAMFUNDRAISER TF on TF.CONSTITUENTID=C.ID AND TF.APPEALID=EV.APPEALID 
left outer join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISERID =TF.ID 
left outer join dbo.TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TFTM.TEAMFUNDRAISINGTEAMID 
left outer join dbo.TEAMEXTENSION TE 
  ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
WHERE C.ID = @CONSTITUENTID
AND EV.ID = @EVENTID     
)