UFN_ADDRESSBOOKFAF_GETGENERALGROUPDONORS

CREATE function dbo.UFN_ADDRESSBOOKFAF_GETGENERALGROUPDONORS
(
  @GROUPID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,
  @CLIENTUSERSID int,
  @ROLECODE tinyint -- 0: company leader, 1: team leader, 2: team member, 3: head of household, 4: household member

)
returns table
as 
return
With DonorList
as 
(
    select 
      dC.ID 
      from dbo.REVENUERECOGNITION RC
      join dbo.REVENUESPLIT RS on RC.REVENUESPLITID = RS.ID and RS.APPLICATIONCODE in (0, 2, 3)
      join dbo.REVENUE R on R.ID = RS.REVENUEID 
      join dbo.TEAMEXTENSION TX on RC.CONSTITUENTID = TX.TEAMCONSTITUENTID
      join dbo.CONSTITUENT dC on dC.ID = R.CONSTITUENTID
      where TX.TEAMFUNDRAISINGTEAMID = @GROUPID
      and dC.ID not in (select CONSTITUENTID from dbo.ADDRESSBOOKFAF where CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCURRENTCLIENTUSERID(@CLIENTUSERSID)) and CONSTITUENTID is not null)
      and dC.ID <> @CONSTITUENTID
      and @ROLECODE in (0,1,3,4)
)
select ID from [DonorList]