UFN_REVENUE_CONSTITUENTRECOGNIZEDAMOUNT_NO_REVENUE_RULES

create function dbo.UFN_REVENUE_CONSTITUENTRECOGNIZEDAMOUNT_NO_REVENUE_RULES
(

     @EVENTID                as uniqueidentifier 
    ,@CONSTITUENTID            as uniqueidentifier 
)
returns money
with execute as caller
as
begin

  /*
      Revenue amount raised by the current constituent
        in the current event.  Constituent in this 
    context can be individual, team, company or household.  
    Self recognition is excluded. 
  */

  declare @AMOUNT as money = 0.0;

  ;with EVENTRECOGNIZEDREVENUE_CTE        
    as
    (
        select     
             REVENUERECOGNITION.CONSTITUENTID
            ,EVENT_REVENUE.TRANSACTIONTYPE as [REVENUETYPE]
            ,EVENT_REVENUE.[ORIGINAL AMOUNT] as ORIGINALAMOUNT
            ,EVENT_REVENUE.[APPLIED AMOUNT] as APPLIEDAMOUNT
      ,EVENT_REVENUE.REVENUESPLITID
        from    [dbo].[UFN_REVENUE_EVENT](@EVENTID) AS EVENT_REVENUE
        inner    join REVENUERECOGNITION
        on        REVENUERECOGNITION.CONSTITUENTID    <> EVENT_REVENUE.CONSTITUENTID
        and        REVENUERECOGNITION.REVENUESPLITID    = EVENT_REVENUE.REVENUESPLITID
    WHERE REVENUERECOGNITION.CONSTITUENTID = @CONSTITUENTID
    )
    select @AMOUNT = SUM(ISNULL(ORIGINALAMOUNT,0)) from [EVENTRECOGNIZEDREVENUE_CTE];

    return(@AMOUNT);
end