UFN_FAFEVENT_TRANSACTIONS

CREATE function [dbo].[UFN_FAFEVENT_TRANSACTIONS]  
(  
  @EVENTID  as uniqueidentifier  -- FAF event id

)  
returns table  
as  
return      
      with [FAF_EVENT_TRANSACTIONS_CTE]        
      as
      (
            -- Raised by participant

            select      
                   REVENUERECOGNITION.CONSTITUENTID
                  ,REVENUEEVENT.TRANSACTIONTYPE
                  ,REVENUEEVENT.[RTRANSACTIONTYPE]
                  ,REVENUEEVENT.[ORIGINAL AMOUNT]
                  -- Link back pledge payment to participant as recognition

                  ,isnull(case 
                       when REVENUEEVENT.TRANSACTIONTYPE='Pledge' then 
                                (select SUM(amount) from dbo.INSTALLMENTSPLITPAYMENT (nolock) where PLEDGEID=REVENUESPLIT.REVENUEID )
                        else REVENUEEVENT.[APPLIED AMOUNT] 
                      end ,0
                  ) as [APPLIED AMOUNT]
                  ,REVENUEEVENT.CONSTITUENTID               as [DONORID]
                  ,REVENUESPLIT.DATEADDED                   as [DATE]
            from  [dbo].[UFN_REVENUE_EVENT](@EVENTID) as REVENUEEVENT
            inner join REVENUERECOGNITION (nolock)
            on          REVENUERECOGNITION.CONSTITUENTID    <> REVENUEEVENT.CONSTITUENTID
            and         REVENUERECOGNITION.REVENUESPLITID   =  REVENUEEVENT.REVENUESPLITID
            inner join REVENUESPLIT (nolock)
            on          REVENUESPLIT.ID = REVENUEEVENT.REVENUESPLITID

            union all   

            -- Raised by participant to self

            select      
                  RC.CONSTITUENTID
                   -- REVENUEEVENT.CONSTITUENTID

                  ,REVENUEEVENT.TRANSACTIONTYPE
                  ,REVENUEEVENT.[RTRANSACTIONTYPE]
                  ,REVENUEEVENT.[ORIGINAL AMOUNT]
                  ,REVENUEEVENT.[APPLIED AMOUNT]
                  ,REVENUEEVENT.CONSTITUENTID               as [DONORID]
                  ,REVENUESPLIT.DATEADDED                   as [DATE]
            from  [dbo].[UFN_REVENUE_EVENT](@EVENTID) as REVENUEEVENT   
            inner join  REVENUESPLIT (nolock)
            on          REVENUESPLIT.ID = REVENUEEVENT.REVENUESPLITID   
            inner join dbo.REVENUERECOGNITION RC on RC.REVENUESPLITID = REVENUESPLIT.ID 
                       and  RC.CONSTITUENTID  =REVENUEEVENT.CONSTITUENTID
      )
      -- Transactions totals aggregates grouped by transaction type

      select      
            [DONORID]
            ,[CONSTITUENTID]        as [RECEIVERID]
            ,sum([ORIGINAL AMOUNT]) as [ORIGINALAMOUNT]
            ,sum([APPLIED AMOUNT])  as [APPLIEDAMOUNT]
            ,[TRANSACTIONTYPE]            as [APPLICATIONTYPE]          
            ,max([DATE])                  as [DATE]         
      from  [FAF_EVENT_TRANSACTIONS_CTE]
      group by 
             [DONORID]
            ,[CONSTITUENTID]
            ,[TRANSACTIONTYPE]                        
            ,[DATE];