USP_KPI_CAMPAIGNTEAM_GOALAMOUNTTOTAL

                    CREATE procedure dbo.USP_KPI_CAMPAIGNTEAM_GOALAMOUNTTOTAL(
                        @VALUE money output, 
                        @ASOFDATE datetime, 
                        @CAMPAIGNID uniqueidentifier,
                        @ORGTEAMIDSET uniqueidentifier,
                        @CURRENCYID uniqueidentifier = null
                    ) as begin
                        set nocount on;

                        declare @IDS as table(ID uniqueidentifier);
                        insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGTEAMIDSET, null;

                        with FUNDRAISERS_CTE as (
                            select 
                                ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID, 
                                ORGANIZATIONPOSITIONHOLDER.DATEFROM, 
                                case
                                    when datediff(day, @ASOFDATE, ORGANIZATIONPOSITIONHOLDER.DATETO) > 0 then @ASOFDATE
                                    else coalesce(ORGANIZATIONPOSITIONHOLDER.DATETO,@ASOFDATE)
                                end DATETO
                            from dbo.ORGANIZATIONPOSITIONHOLDER                                
                        )                        
                        select
                            @VALUE = coalesce(sum(dbo.UFN_REVENUESOLICITOR_GETAMOUNTINCURRENCY(REVENUESOLICITOR.ID, @CURRENCYID)), 0)
                        from dbo.REVENUESOLICITOR
                            inner join dbo.REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
                            inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID    
                            inner join FUNDRAISERS_CTE on REVENUESOLICITOR.CONSTITUENTID = FUNDRAISERS_CTE.CONSTITUENTID
                            left outer join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                        where (
                                REVENUE.TRANSACTIONTYPECODE in (1,6)  --Pledges or grant awards

                                    or (
                                        REVENUE.TRANSACTIONTYPECODE = 0  --Payments...

                                            and not REVENUESPLIT.APPLICATIONCODE in (2,7,8)  --sans pledge, MGC, and grant award payments

                                    )
                                    or(
                                        REVENUE.TRANSACTIONTYPECODE = 4  --Planned gifts with a payment

                                            and exists (
                                                select 1
                                                from INSTALLMENTPAYMENT PGINSTALLMENT
                                                    inner join REVENUESPLIT PGREVENUESPLIT on PGREVENUESPLIT.ID = PGINSTALLMENT.PAYMENTID
                                                    inner join REVENUE PGREVENUE on PGREVENUE.ID = PGREVENUESPLIT.REVENUEID
                                                where PGINSTALLMENT.PLEDGEID=REVENUE.ID 
                                                    and (PGREVENUE.DATE >= FUNDRAISERS_CTE.DATEFROM or FUNDRAISERS_CTE.DATEFROM is null)
                                                    and (PGREVENUE.DATE <= FUNDRAISERS_CTE.DATETO or FUNDRAISERS_CTE.DATETO is null)
                                            )
                                    )
                            )
                            and REVENUESPLITCAMPAIGN.CAMPAIGNID = @CAMPAIGNID
                            and (FUNDRAISERS_CTE.DATEFROM <= REVENUE.DATE or FUNDRAISERS_CTE.DATEFROM is null)
                            and (REVENUE.DATE <= FUNDRAISERS_CTE.DATETO or FUNDRAISERS_CTE.DATETO is null);

                    end