UFN_REVENUE_CONSTITUENTRECOGNIZEDAMOUNTBYDONOR

CREATE function [dbo].[UFN_REVENUE_CONSTITUENTRECOGNIZEDAMOUNTBYDONOR]
(

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

    declare @AMOUNT as money = 0.0;

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

    If the @DONORCONSTITUENTID is null, this will
    return revenue amount raised by the current constituent
        in the current event 
    */
    -- Revenue details

    select 
        @AMOUNT = sum(isnull(REVENUESPLIT.AMOUNT,0)) 
    from    [dbo].[REVENUE]                    (nolock)
    inner    join [dbo].[REVENUESPLIT]        (nolock)
    on        REVENUE.ID = REVENUESPLIT.REVENUEID

    -- Recognized revenue

    inner    join [dbo].[REVENUERECOGNITION] (nolock)
    on        REVENUERECOGNITION.CONSTITUENTID    <> REVENUE.CONSTITUENTID -- exclude self recognition

    and        REVENUERECOGNITION.REVENUESPLITID    = REVENUESPLIT.ID

    -- Current event context

    inner    join [dbo].[EVENT] (nolock)
    on        [EVENT].APPEALID                 = REVENUE.APPEALID
    and        [EVENT].ID                         = @EVENTID

  --AJ 09/29/2010..added to exclude revenue rules

  INNER JOIN    [dbo].UFN_REVENUE_EVENTREVENUEDETAILS (@EVENTID) AG 
    ON AG.CONSTITUENTID= [REVENUE].CONSTITUENTID AND AG.TRANSACTIONTYPE IN ('Donation', 'Offline Donation', 'Pledge', 'Pending gift', 'Recurring gift')

    where   REVENUERECOGNITION.CONSTITUENTID = @CONSTITUENTID
    AND        (@DONORCONSTITUENTID is null OR [REVENUE].CONSTITUENTID = @DONORCONSTITUENTID);

    return(@AMOUNT);
end