USP_DATALIST_GROUPRECOGINITIONTRANSACTIONS

CREATE procedure dbo.USP_DATALIST_GROUPRECOGINITIONTRANSACTIONS
(
  @CONTEXTID uniqueidentifier,
  @TRANSACTIONTYPECODE tinyint = null,
  @NAME nvarchar(210) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

  ;With [TopLevelConstituent] (TEAMID, PARENTTEAMID, CONSTITUENTID)
  as 
  (    
      select TFT.ID, TFT.PARENTTEAMID, TF.CONSTITUENTID from TEAMFUNDRAISINGTEAMMEMBER TFTM
      join TEAMFUNDRAISINGTEAM TFT ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID
      join TEAMEXTENSION TX on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
      join TEAMFUNDRAISER TF on TFTM.TEAMFUNDRAISERID = TF.ID             
  ),
  [NextLevelConstituent]
  as (
    select TEAMID as PARENTTEAMID, TEAMID, CONSTITUENTID
        from [TopLevelConstituent]

      Union All

      select NL.PARENTTEAMID, TL.TEAMID, TL.CONSTITUENTID 
      from [NextLevelConstituent] NL
          join [TopLevelConstituent] TL on NL.TEAMID = TL.PARENTTEAMID
  ),
  [AllConstituent] (PARENTTEAMID, CONSTITUENTID,EVENTID)
  as (
    select PARENTTEAMID, CONSTITUENTID, TX.EVENTID from [NextLevelConstituent] NLC
    left join TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = NLC.PARENTTEAMID    
    where NLC.PARENTTEAMID = @CONTEXTID 

    union 

    select TX.TEAMFUNDRAISINGTEAMID, TX.TEAMCONSTITUENTID, TX.EVENTID
    from TEAMEXTENSION TX 
      where TX.TEAMFUNDRAISINGTEAMID = @CONTEXTID 
  )

    select
      R.ID,
      CDONOR.ID as DONORCONSTITUENTID,
      CDONOR.NAME, 
      RS.AMOUNT,
      R.[DATE],
      R.TRANSACTIONTYPE,
      R.LOOKUPID, 
      RPM.PAYMENTMETHOD,
      RS.[TYPE],
      RS.APPLICATION

    from
      [AllConstituent] AL
    join EVENT E on AL.EVENTID = E.ID
    join REVENUERECOGNITION RR on AL.CONSTITUENTID = RR.CONSTITUENTID
    join REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
    join REVENUE R on RS.REVENUEID = R.ID AND R.APPEALID = E.APPEALID and (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 1, 7, 15, 17) OR R.TRANSACTIONTYPECODE IN (1,2) )
    left join REVENUEPAYMENTMETHOD RPM on RPM.REVENUEID = R.ID
    left join CONSTITUENT CDONOR on CDONOR.ID = R.CONSTITUENTID    
    where (R.TRANSACTIONTYPECODE = @TRANSACTIONTYPECODE or @TRANSACTIONTYPECODE is null)
    and (CDONOR.NAME like '%' + @NAME + '%' or @NAME is null)
    and (
          @SITEFILTERMODE = 0
          or (
              exists(
                      select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVENUESITE
                      where REVENUESITE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
                    )
            )
        )
  -- Check site security
    and (
         exists(
            select 1 from  dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVENUESITE
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVENUESITE].[SITEID] or (SITEID is null and [REVENUESITE].[SITEID] is null))) )
        )