UFN_REVENUE_GETCOMPANYRAISEDTOTAL_NO_REVENUE_RULES

create function [dbo].[UFN_REVENUE_GETCOMPANYRAISEDTOTAL_NO_REVENUE_RULES]
(
     @COMPANYID    as uniqueidentifier
    ,@EVENTID    as uniqueidentifier
)
returns money
with execute as caller
as 
begin

    declare @TOTAL as money = 0.0;

    ;with EVENTCOMPANYREVENUE_CTE
    as
    (
        select                         
            case 
                when TEAMEXTENSION.TYPECODE=3 
                    then isnull(sum([dbo].[UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL_NO_REVENUE_RULES](TEAMFUNDRAISINGTEAM.ID,@EVENTID)),0) 
                when TEAMEXTENSION.TYPECODE=1 
                    then isnull(sum([dbo].[UFN_REVENUE_GETTEAMRAISEDTOTAL_NO_REVENUE_RULES](TEAMFUNDRAISINGTEAM.ID,@EVENTID)),0)
                else 0
            end    as TOTAL
         from    TEAMFUNDRAISINGTEAM (nolock)
         inner    join TEAMEXTENSION  (nolock)
         on        TEAMEXTENSION.TEAMFUNDRAISINGTEAMID=TEAMFUNDRAISINGTEAM.ID 
         where    TEAMFUNDRAISINGTEAM.PARENTTEAMID=@COMPANYID
         and    TEAMEXTENSION.TYPECODE in (1,3)
         group    by TEAMEXTENSION.TYPECODE
    )
    select    @TOTAL = SUM(TOTAL) 
    from    EVENTCOMPANYREVENUE_CTE; 

    -- Adding amount raised to the company

    return (
        isnull(@TOTAL,0) + [dbo].[UFN_REVENUE_GETGROUPRAISEDTOTAL_NO_REVENUE_RULES](@EVENTID ,@COMPANYID ,2)
    );
end