USP_DATALIST_FAFHONORROLL

CREATE procedure dbo.USP_DATALIST_FAFHONORROLL
(
@RECORDID uniqueidentifier,  -- constituent id

@EVENTID uniqueidentifier,
@RECORDTYPE tinyint = 0,
@APPLICATIONCODE bit = -1,
@TOTALROWS int = 50
)
as
    set nocount on;
  declare @TEAMFUNDRAISINGTEAMID uniqueidentifier
  select @TEAMFUNDRAISINGTEAMID=TEAMFUNDRAISINGTEAMID from dbo.TEAMEXTENSION where TEAMCONSTITUENTID = @RECORDID

  DECLARE @RevenueSplit TABLE
  (
    REVENUESPLITID uniqueidentifier,
    CONSTITUENTID uniqueidentifier
  )


  ;With [Groups] (TEAMID, PARENTTEAMID, CONSTITUENTID)
  AS 
  (    select TFT.ID, TFT.PARENTTEAMID, TX.TEAMCONSTITUENTID AS CONSTITUENTID
      from dbo.TEAMFUNDRAISINGTEAM TFT (nolock)
      join dbo.TEAMEXTENSION TX (nolock) ON TFT.ID = TX.TEAMFUNDRAISINGTEAMID
      where TFT.ID = @TEAMFUNDRAISINGTEAMID

      union all

      select TFT.ID, TFT.PARENTTEAMID, TX.TEAMCONSTITUENTID AS CONSTITUENTID
      from [Groups] C 
      join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) ON TFT.parentTEAMID = C.TEAMID
      join dbo.TEAMEXTENSION TX (nolock) on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
  ),
  [Members] 
  AS 
  (
      select TFT.TEAMID, TFT.PARENTTEAMID, TF.CONSTITUENTID
      from [Groups] TFT
      join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM (nolock) on TFT.TEAMID = TFTM.TEAMFUNDRAISINGTEAMID
      join dbo.TEAMFUNDRAISER TF (nolock) on TFTM.TEAMFUNDRAISERID = TF.ID  
  ),
  [All]
  AS
  (
      Select CONSTITUENTID from [Groups] where @RECORDTYPE <> 0
      Union All
      Select CONSTITUENTID from [Members] where @RECORDTYPE <> 0
      Union All
      Select @RECORDID as CONSTITUENTID where @RECORDTYPE = 0
  )
  insert into @RevenueSplit
      select RC.REVENUESPLITID, RC.CONSTITUENTID from [All] A 
          join dbo.REVENUERECOGNITION RC (nolock) on RC.CONSTITUENTID = A.CONSTITUENTID

  select top (@TOTALROWS)
      R.ID as REVENUEID,
          participant.NAME as PARTICIPANTNAME, 
            FTLI.TRANSACTIONAMOUNT AS DONATEAMOUNT,
              case RX.GIVENANONYMOUSLY when 1 Then 'Anonymous' else donor.NAME end AS DONORNAME,
              case RT.ISTRIBUTEANONYMOUS when 1 Then null else TC.[DESCRIPTION] end AS TRIBUTETYPE, 
              case RT.ISTRIBUTEANONYMOUS when 1 Then null else ISNULL(tributee.NAME, TEX.TRIBUTEEFIRSTNAME + ' ' + TEX.TRIBUTEELASTNAME) end AS TRIBUTEENAME, 
              case RT.ISTRIBUTEANONYMOUS when 1 Then null else T.TRIBUTETEXT end as TRIBUTETEXT
      from @RevenueSplit A
      join dbo.CONSTITUENT participant (nolock) on A.CONSTITUENTID = participant.ID
    join dbo.FINANCIALTRANSACTIONLINEITEM FTLI (nolock) on FTLI.ID = A.REVENUESPLITID
      join dbo.REVENUESPLIT_EXT RS (nolock) on FTLI.ID = RS.ID AND RS.TYPECODE = 0 
      join dbo.FINANCIALTRANSACTION R (nolock) on FTLI.FINANCIALTRANSACTIONID = R.ID AND (R.TYPECODE = 0 and RS.APPLICATIONCODE in (0, 7, 17) OR R.TYPECODE IN (1,2) )
    join dbo.REVENUE_EXT RX (nolock) on RX.ID = R.ID 
    join dbo.EVENT E on RX.APPEALID = E.APPEALID and E.ID = @EVENTID
      left join dbo.CONSTITUENT (nolock) donor on donor.ID = R.CONSTITUENTID
      left join dbo.REVENUETRIBUTE RT (nolock) on R.ID = RT.REVENUEID
      left join dbo.TRIBUTE T (nolock) on RT.TRIBUTEID = T.ID
      left join dbo.TRIBUTEEXTENSION TEX (nolock) on TEX.TRIBUTEID = T.ID 
      left join dbo.TRIBUTETYPECODE TC (nolock) on T.TRIBUTETYPECODEID = TC.ID
      left join dbo.CONSTITUENT tributee (nolock) on T.TRIBUTEEID = tributee.ID
      order by R.DATEADDED desc