UFN_REVENUE_GETGROUPRAISEDTOTAL_NO_REVENUE_RULES

Provide revenue amount per team as company, FAF team/household in a given FAF event, excluding revenue rules

Return

Return Type
money

Parameters

Parameter	Parameter Type	Mode
@EVENTID	uniqueidentifier	IN
@GROUPID	uniqueidentifier	IN
@TYPECODE	smallint	IN

Definition

Copy



CREATE function [dbo].[UFN_REVENUE_GETGROUPRAISEDTOTAL_NO_REVENUE_RULES]
(

     @EVENTID        as uniqueidentifier
    ,@GROUPID        as uniqueidentifier
    ,@TYPECODE        as smallint
)
returns money
with execute as caller
as begin

    /*
        Group in the context of this event presents
        the hierarchical structure of constituent.  
        The structure has the following elements:
        Company, household, team and individuals.  
        The group is company, household or team. 
    */

    -- Group constituent id

    declare @GROUPCONSTITUENTID as uniqueidentifier;

    select    @GROUPCONSTITUENTID = [TEAMCONSTITUENTID]    
    from    [dbo].[TEAMEXTENSION]
    where   TEAMEXTENSION.TEAMFUNDRAISINGTEAMID = @GROUPID;

    /*
        Current group members in the current event: 
        Typecode:1 Team members
        Typecode:2 Company members
        Typecode:3 Household members
    */    
    declare @TOTAL as money;

 -- IF @TYPECODE in (1,2)

    select         
        @TOTAL = SUM([dbo].[UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_NO_REVENUE_RULES](REGISTRANT.ID,@EVENTID))    
    from    [dbo].[TEAMEXTENSION] (nolock)
    inner    join [dbo].[TEAMFUNDRAISINGTEAMMEMBER] (nolock)
    on        TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMEXTENSION.TEAMFUNDRAISINGTEAMID
    inner    join [dbo].[TEAMFUNDRAISER] (nolock)
    on        [TEAMFUNDRAISER].ID        = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
    inner    join [dbo].[REGISTRANT] (nolock)
    on        [REGISTRANT].CONSTITUENTID =   TEAMFUNDRAISER.CONSTITUENTID    
    and        [REGISTRANT].EVENTID       =   TEAMEXTENSION.EVENTID
    where    TEAMEXTENSION.EVENTID                = @EVENTID
    and        TEAMEXTENSION.TYPECODE                = @TYPECODE
    and        TEAMEXTENSION.TEAMFUNDRAISINGTEAMID = @GROUPID;    

    -- Adding total amount raised by current group

    return
    (
        isnull(@TOTAL,0) + isnull([dbo].[UFN_REVENUE_CONSTITUENTRECOGNIZEDAMOUNT_NO_REVENUE_RULES](@EVENTID ,@GROUPCONSTITUENTID),0)
    )
end