UFN_REVENUE_CONSTITUENTRECOGNIZEDAMOUNTBYDONORDETAIL

Provide recognized revenue detail amount per constituent in a given FAF event by specific donor

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@EVENTID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
@DONORCONSTITUENTID	uniqueidentifier	IN

Definition

Copy



CREATE function [dbo].[UFN_REVENUE_CONSTITUENTRECOGNIZEDAMOUNTBYDONORDETAIL]
(

     @EVENTID                as uniqueidentifier 
    ,@CONSTITUENTID            as uniqueidentifier 
    ,@DONORCONSTITUENTID    as uniqueidentifier
)

RETURNS TABLE    
AS    

RETURN    
 WITH     
 [DONORCONSTITUENT]    
  AS    
 (    
    -- Revenue details

    select REVENUE.DATE, REVENUESPLIT.AMOUNT, REVENUE.APPEALID, EVENT.ID AS EVENTID, EVENT.NAME AS EVENTNAME, REVENUE.CONSTITUENTID
    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')

    where   REVENUERECOGNITION.CONSTITUENTID = @CONSTITUENTID
    AND        (@DONORCONSTITUENTID is null OR [REVENUE].CONSTITUENTID = @DONORCONSTITUENTID) 
 )    
 SELECT * FROM [DONORCONSTITUENT]