USP_KPI_EVENTS_TEAMFUNDRAISINGTEAMPERFORMANCE_VALUE

                    CREATE procedure dbo.USP_KPI_EVENTS_TEAMFUNDRAISINGTEAMPERFORMANCE_VALUE
                    (
                        @VALUE money output, 
                        @ASOFDATE datetime, 
                        @TEAMID uniqueidentifier,
                        @CURRENCYID uniqueidentifier = null
                    )               
                    as

                        set nocount on;

                        set @VALUE=0
                        select @ASOFDATE=dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);

                        with CTE_TEAMHIERARCHY(ID)
                        as
                        (
                            select
                                @TEAMID [ID]

                            union all

                            select
                                TEAMFUNDRAISINGTEAM.ID
                            from dbo.TEAMFUNDRAISINGTEAM
                                inner join CTE_TEAMHIERARCHY on TEAMFUNDRAISINGTEAM.PARENTTEAMID = CTE_TEAMHIERARCHY.ID
                        )
                        select
                            @VALUE=coalesce(sum(coalesce(dbo.UFN_REVENUESOLICITOR_GETAMOUNTINCURRENCY(REVENUESOLICITOR.ID, @CURRENCYID),0)),0)
                        from dbo.TEAMFUNDRAISINGTEAM
                            inner join dbo.TEAMFUNDRAISINGTEAMMEMBER on TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID
                            inner join dbo.TEAMFUNDRAISER on TEAMFUNDRAISER.ID=TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
                            inner join dbo.REVENUESOLICITOR on REVENUESOLICITOR.CONSTITUENTID=TEAMFUNDRAISER.CONSTITUENTID
                            inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUESOLICITOR.REVENUESPLITID
                            inner join dbo.REVENUE on REVENUE.APPEALID = TEAMFUNDRAISINGTEAM.APPEALID and REVENUE.ID = REVENUESPLIT.REVENUEID
                        where 
                            TEAMFUNDRAISINGTEAM.ID in (select distinct ID from CTE_TEAMHIERARCHY)
                                and    ((REVENUE.TRANSACTIONTYPECODE = 1) --Pledges

                                    or (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0,3)) --Gifts and recurring gift payments

                                    or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0 and REVENUE.TRANSACTIONTYPECODE = 0) --Event registration donation

                                )
                                and (REVENUE.DATE <= @ASOFDATE or @ASOFDATE is null) --On or before as of date