UFN_FAF_COMPANYLEADERLIST

CREATE function dbo.UFN_FAF_COMPANYLEADERLIST(
@EVENTID uniqueidentifier
)

   returns table  
   as  

   return  
   (  
    select 
     TM.NAME as COMPANYNAME,  
     TM.ID as COMPANYID,
     C.keyname, 
     C.name,
     C.ID 
    from dbo.TEAMEXTENSION TE  
    INNER JOIN dbo.TEAMFUNDRAISINGTEAM TM
        ON TE.TEAMFUNDRAISINGTEAMID = TM.ID
    INNER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --retrieve team leaders

        ON TE.TEAMFUNDRAISINGTEAMID = TC.TEAMFUNDRAISINGTEAMID
    INNER JOIN dbo.CONSTITUENT C
        ON TC.CONSTITUENTID = C.ID  
    WHERE TE.EVENTID = @EVENTID
    AND TE.TYPECODE = 2 AND TE.STATUSCODE = 0
)