UFN_QUERY_FAFDONATIONS_LISTBUILDER

CREATE function dbo.UFN_QUERY_FAFDONATIONS_LISTBUILDER
(
    @EVENTID         uniqueidentifier
)
returns table 
as

return

select 
        D.REVENUEID as ID, 
        D.DONORNAME, 
        R.NAME AS RECOGNITION,
        D.AMOUNT, 
      D.TRANSACTIONTYPE,
      D.[APPLICATION],
        D.PAYMENTMETHOD, 
        D.[DATE] as DATE,   
        D.LOOKUPID as LOOKUPID,
        D.REVENUESPLITID,
        D.CONSTITUENTID,
      D.DESIGNATIONID
    from 
       (   
         Select R.ID as REVENUEID, RS.ID as REVENUESPLITID, DONS.NAME as DONORNAME, RS.AMOUNT, R.TRANSACTIONTYPECODE, R.TRANSACTIONTYPE, RS.[APPLICATION] ,RPM.PAYMENTMETHOD, 
              R.[DATE], R.LOOKUPID, RR.CONSTITUENTID AS RECOGNITIONCONSTITUENTID, R.CONSTITUENTID, RS.DESIGNATIONID
                  from dbo.EVENT E With (NOLOCK)
                  join dbo.REVENUE R With (NOLOCK) on E.APPEALID = R.APPEALID and E.ID = @EVENTID 
                  join dbo.REVENUESPLIT RS With (NOLOCK) on R.ID = RS.REVENUEID and (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 7, 17) OR R.TRANSACTIONTYPECODE IN (1,2) )
                  join dbo.CONSTITUENT DONS With (NOLOCK) on R.CONSTITUENTID = DONS.ID 
                  left join dbo.REVENUEPAYMENTMETHOD RPM With (NOLOCK) on RPM.REVENUEID = R.ID 
                left join dbo.REVENUERECEIPT RC With (NOLOCK) on RC.REVENUEID = R.ID   
                left join dbo.REVENUERECOGNITION RR With (NOLOCK) on RR.REVENUESPLITID = RS.ID  
                left join dbo.V_SITEID_MAPFROM_DESIGNATIONID RSD (nolock) on RSD.DESIGNATIONID = RS.DESIGNATIONID 

       ) D
       left join
       (
          Select TFT.ID as RECORDID, TFT.NAME, TX.TEAMCONSTITUENTID as CONSTITUENTID
                from dbo.TEAMEXTENSION TX With (NOLOCK)
                join dbo.TEAMFUNDRAISINGTEAM TFT With (NOLOCK) on TX.TEAMFUNDRAISINGTEAMID = TFT.ID
                where TX.EVENTID = @EVENTID

                union all

                select R.ID as RECORDID, C.NAME, C.ID as CONSTITUENTID 
                from dbo.REGISTRANT R With (NOLOCK)
                join dbo.CONSTITUENT C With (NOLOCK) on R.CONSTITUENTID = C.ID 
                where R.EVENTID = @EVENTID

             union all

                select S.ID as RECORDID, C.NAME, C.ID as CONSTITUENTID 
                from dbo.EVENTSPONSOR S With (NOLOCK)
                join dbo.CONSTITUENT C With (NOLOCK) on S.CONSTITUENTID = C.ID 
                where S.EVENTID = @EVENTID and C.ID not in (select CONSTITUENTID from  dbo.REGISTRANT R where R.EVENTID = @EVENTID)

       ) R on D.RECOGNITIONCONSTITUENTID = R.CONSTITUENTID