UFN_FAFEVENT_REVENUEDETAILS

For reports, provide revenue details per constituent in a given FAF event
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@EVENTID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@ISRETAINEDMODE	tinyint	IN
Definition

Copy


CREATE function dbo.UFN_FAFEVENT_REVENUEDETAILS
(
    @EVENTID uniqueidentifier = null,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @ISRETAINEDMODE tinyint = 0 
)
returns @EventRevenueConstituent table 
(
CONSTITUENTID uniqueidentifier
,TRANSACTIONTYPE nvarchar(50)
,[ORIGINAL AMOUNT] money
,[APPLIED AMOUNT] money
,EVENTID uniqueidentifier
,PRIORYEAREVENTID uniqueidentifier
)
as 
begin
    If @ISRETAINEDMODE = 1  --Retained mode: Pull revenue retained

        begin 
            insert @EventRevenueConstituent 
            (
                CONSTITUENTID        
                ,TRANSACTIONTYPE    
                ,[ORIGINAL AMOUNT]  
                ,[APPLIED AMOUNT]    
                ,EVENTID            
                ,PRIORYEAREVENTID    
            )
            select  
                     FG.CONSTITUENTID
                    ,FG.TRANSACTIONTYPE
                    ,sum(case WHEN AA.CONSTITUENTID is not null then [ORIGINAL AMOUNT] else 0 end) as [ORIGINAL AMOUNT]
                    ,sum(case WHEN AA.CONSTITUENTID is not null then [APPLIED AMOUNT] else 0 end) as [APPLIED AMOUNT]                
                    ,FG.ID AS EVENTID,    FG.PRIORYEAREVENTID    
            from    [dbo].[UFN_FAFEVENT_GETREVENUECONSTITUENTS](@EVENTID,@STARTDATE,@ENDDATE,1) FG

            inner join (
                   SELECT ET2.EVENTID AS EVENTID
                  ,C2.ID as CONSTITUENTID
                  FROM CONSTITUENT C2  
                  inner join dbo.REGISTRANT RG2 ON C2.ID=RG2.CONSTITUENTID 
                  inner join dbo.EVENTEXTENSION ET2 ON ET2.EVENTID = RG2.EVENTID  
              ) AA ON AA.CONSTITUENTID=FG.CONSTITUENTID AND FG.PRIORYEAREVENTID=AA.EVENTID

            group by FG.CONSTITUENTID,FG.TRANSACTIONTYPE,FG.ID,FG.PRIORYEAREVENTID
        end
    Else    --Default mode: Pull total revenue

        begin
            insert @EventRevenueConstituent 
            (
                CONSTITUENTID        
                ,TRANSACTIONTYPE    
                ,[ORIGINAL AMOUNT]  
                ,[APPLIED AMOUNT]    
                ,EVENTID            
                ,PRIORYEAREVENTID    
            )
            select  
                     CONSTITUENTID
                    ,TRANSACTIONTYPE
                    ,sum([ORIGINAL AMOUNT]) as [ORIGINAL AMOUNT]
                    ,sum([APPLIED AMOUNT])  as [APPLIED AMOUNT]
                    ,ID AS EVENTID,    PRIORYEAREVENTID    
            from    [dbo].[UFN_FAFEVENT_GETREVENUECONSTITUENTS](@EVENTID,@STARTDATE,@ENDDATE,1)
            group    by CONSTITUENTID,TRANSACTIONTYPE,ID,PRIORYEAREVENTID
        end

    return
end