UFN_FAF_COMPANYINDIVIDUALLIST

CREATE function dbo.UFN_FAF_COMPANYINDIVIDUALLIST
(
@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.TEAMFUNDRAISINGTEAMMEMBER TM --get individuals under the company

      on FNTEAM.TEAMID = TM.TEAMFUNDRAISINGTEAMID
    inner join dbo.TEAMFUNDRAISER TF
        on TM.TEAMFUNDRAISERID = TF.ID
        --and TF.CONSTITUENTID <> @CONSTITUENTID  --exclude self

    inner join dbo.CONSTITUENT C
        on TF.CONSTITUENTID = C.ID
    left outer join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --exclude company leaders

        on FNTEAM.TEAMID = TC.TEAMFUNDRAISINGTEAMID
        and C.ID = TC.CONSTITUENTID
    where FNTEAM.TEAMTYPECODE = 2  --company

      and TC.TEAMFUNDRAISINGTEAMID is null
   )