USP_KPI_EVENTS_REVENUE_VALUE

                CREATE procedure dbo.USP_KPI_EVENTS_REVENUE_VALUE
                    @VALUE money output, 
                    @ASOFDATE datetime, 
                    @EVENTID uniqueidentifier,
                    @INCLUDEREGISTRATIONS bit = 1,
                    @INCLUDEDONTATIONS bit = 1,
                    @INCLUDEOTHER bit = 1,
                    @OTHERTYPECODEID uniqueidentifier = null,
                    @INCLUDESUBEVENTS bit = 0,
                    @CURRENCYID uniqueidentifier = null
                as                      
                    set nocount on;
                    set @VALUE=0;

                    declare @REGISTRATIONINCOME money = 0;
                    declare @REFUNDS money = 0;
                    declare @DONATIONINCOME money = 0;
                    declare @OTHERINCOME money = 0;
                    declare @APPEALIDS table (ID uniqueidentifier);
                    declare @DECIMALDIGITS tinyint;
                    declare @ROUNDINGTYPECODE tinyint;
                    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                    select
                        @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                        @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                    from
                        dbo.CURRENCY
                    where
                        CURRENCY.ID = @CURRENCYID;

                    select @ASOFDATE=dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);

                    declare @EVENTS table
                    (
                        ID uniqueidentifier
                    )

                    if @INCLUDESUBEVENTS = 0
                    begin
                        insert into @EVENTS
                        select @EVENTID
                    end
                    else begin
                        insert into @EVENTS
                            select RELATEDEVENT.ID
                            from dbo.EVENTHIERARCHY as RELATEDEVENT
                                inner join dbo.EVENTHIERARCHY as SOURCEEVENT on SOURCEEVENT.ID = @EVENTID
                            where RELATEDEVENT.HIERARCHYPATH.IsDescendantOf(SOURCEEVENT.HIERARCHYPATH) = 1
                    end

                    if (@INCLUDEDONTATIONS = 1) or (@INCLUDEOTHER = 1)
                    begin
                        -- Get all of the team fundraising appeals.

                        insert into @APPEALIDS(ID)
                        select
                            EVENT.APPEALID
                        from
                            dbo.EVENT
                        where
                            (EVENT.APPEALID is not null)
                            and (
                                EVENT.ID in (select ID from @EVENTS)
                            )

                        -- Get all of the other appeals.

                        insert into @APPEALIDS(ID)
                        select
                            EVENTAPPEAL.APPEALID
                        from
                            dbo.EVENTAPPEAL
                        where
                            EVENTAPPEAL.EVENTID in (select ID from @EVENTS)
                    end

                    if @INCLUDEREGISTRATIONS = 1
                    begin
                        select @REGISTRATIONINCOME = coalesce(sum(coalesce(REVENUESPLITAMOUNTINCURRENCY.AMOUNTINCURRENCY,0)),0)
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                            inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.REGISTRANT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                            inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                            inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) REVENUESPLITAMOUNTINCURRENCY on REVENUESPLITAMOUNTINCURRENCY.ID = FINANCIALTRANSACTIONLINEITEM.ID
                        where (EVENT.ID in (select ID from @EVENTS))
                        and (FINANCIALTRANSACTION.DATE <= @ASOFDATE or @ASOFDATE is null)
                        and REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.TYPECODE = 1 and FINANCIALTRANSACTION.TYPECODE in (0,5) --Payment and Order

                        and FINANCIALTRANSACTION.DELETEDON is null;

                        select
                            @REFUNDS = isnull(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS), 0)
                        from
                            dbo.FINANCIALTRANSACTION as FT
                        inner join
                            dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
                        inner join
                            dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
                        inner join
                            dbo.CREDITITEMEVENTREGISTRATION on CREDITITEMEVENTREGISTRATION.ID = LI.ID
                        inner join
                            dbo.REGISTRANT on REGISTRANT.ID = CREDITITEMEVENTREGISTRATION.REGISTRANTID
                        where
                            FT.TYPECODE = 23  -- Refund

                            and REGISTRANT.EVENTID in (select ID from @EVENTS)
                            and (@ASOFDATE is null or FT.CALCULATEDDATE <= @ASOFDATE);

                    end

                    if @INCLUDEDONTATIONS = 1
                    begin
                        declare @RECEIVED money
                        declare @PLEDGEBALANCE money

                        --Donations, pledge payments, and matching gift payments on the given event

                        select @RECEIVED = coalesce(sum(coalesce(REVENUESPLITAMOUNTINCURRENCY.AMOUNTINCURRENCY,0)),0)
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                            inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.REGISTRANT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                            inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                            inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) REVENUESPLITAMOUNTINCURRENCY on REVENUESPLITAMOUNTINCURRENCY.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            left join @APPEALIDS as APPEALIDS on APPEALIDS.ID = REVENUE_EXT.APPEALID
                        where (EVENT.ID in (select ID from @EVENTS))
                            and (FINANCIALTRANSACTION.DATE <= @ASOFDATE or @ASOFDATE is null)
                            and FINANCIALTRANSACTION.TYPECODE = 0
                            and FINANCIALTRANSACTION.DELETEDON is null
                            and ((REVENUE_EXT.APPEALID is not null and REVENUESPLIT_EXT.APPLICATIONCODE in (0,2,7)) or (REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.TYPECODE = 0));

                        select @PLEDGEBALANCE =
                            coalesce(sum(coalesce(REVENUESPLITAMOUNTINCURRENCY.AMOUNTINCURRENCY, 0)),0)  --Pledges and matching gift claims on the given event

                                -(
                                    coalesce ((  --Subtract payments made to pledges and MGCs on the given event

                                        select sum(coalesce(REVENUESPLITAMOUNTINCURRENCY.AMOUNTINCURRENCY, 0))
                                        from dbo.FINANCIALTRANSACTIONLINEITEM PAYSPLIT
                                            inner join dbo.FINANCIALTRANSACTION PAY on PAY.ID = PAYSPLIT.FINANCIALTRANSACTIONID
                                            inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) REVENUESPLITAMOUNTINCURRENCY on REVENUESPLITAMOUNTINCURRENCY.ID = PAYSPLIT.ID
                                        where PAYSPLIT.ID in
                                            (select INSTALLMENTPAYMENT.PAYMENTID
                                            from dbo.INSTALLMENTPAYMENT
                                                inner join dbo.FINANCIALTRANSACTION PLEDGE on PLEDGE.ID = INSTALLMENTPAYMENT.PLEDGEID
                                                inner join dbo.FINANCIALTRANSACTIONLINEITEM PLEDGESPLIT on PLEDGESPLIT.FINANCIALTRANSACTIONID = PLEDGE.ID
                                                inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = PLEDGE.ID
                                                inner join @APPEALIDS as APPEALIDS ON APPEALIDS.ID = REVENUE_EXT.APPEALID
                                            where
                                                (PLEDGE.DATE <= @ASOFDATE or @ASOFDATE is null) 
                                                and    (PAY.DATE <= @ASOFDATE or @ASOFDATE is null)
                                            )
                                    ), 0)
                                    + coalesce((  --Subtract writeoffs on pledges and MGCs on the given event

                                        select sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID, @CURRENCYID))
                                        from dbo.WRITEOFFSPLIT
                                            inner join dbo.WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.WRITEOFFID
                                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = WRITEOFF.REVENUEID
                                            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                                            inner join @APPEALIDS as APPEALIDS ON APPEALIDS.ID = REVENUE_EXT.APPEALID
                                        where 
                                            (FINANCIALTRANSACTION.DATE <= @ASOFDATE or @ASOFDATE is null)  
                                            and FINANCIALTRANSACTION.TYPECODE in (1,3)
                                            and FINANCIALTRANSACTION.DELETEDON is null
                                    ), 0)
                                )
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                            inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.REGISTRANT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                            inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                            inner join @APPEALIDS as APPEALIDS ON APPEALIDS.ID = REVENUE_EXT.APPEALID
                            inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) REVENUESPLITAMOUNTINCURRENCY on REVENUESPLITAMOUNTINCURRENCY.ID = FINANCIALTRANSACTIONLINEITEM.ID
                        where (EVENT.ID in (select ID from @EVENTS))
                            and (FINANCIALTRANSACTION.DATE <= @ASOFDATE or @ASOFDATE is null)
                            and FINANCIALTRANSACTION.TYPECODE in (1,3)
                            and FINANCIALTRANSACTION.DELETEDON is null;

                        set @DONATIONINCOME = @RECEIVED + @PLEDGEBALANCE;
                    end

                    if @INCLUDEOTHER = 1
                    begin
                        --Other payments on the given event

                        select @OTHERINCOME = coalesce(sum(coalesce(REVENUESPLITAMOUNTINCURRENCY.AMOUNTINCURRENCY,0)),0)
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                            inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.REGISTRANT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                            inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                            inner join @APPEALIDS as APPEALIDS ON APPEALIDS.ID = REVENUE_EXT.APPEALID
                            inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) REVENUESPLITAMOUNTINCURRENCY on REVENUESPLITAMOUNTINCURRENCY.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            left join dbo.REVENUESPLITOTHER on REVENUESPLITOTHER.ID = FINANCIALTRANSACTIONLINEITEM.ID
                        where (EVENT.ID in (select ID from @EVENTS))
                            and (FINANCIALTRANSACTION.DATE <= @ASOFDATE or @ASOFDATE is null)
                            and FINANCIALTRANSACTION.TYPECODE = 0
                            and REVENUESPLIT_EXT.APPLICATIONCODE = 4
                            and FINANCIALTRANSACTION.DELETEDON is null
                            and (@OTHERTYPECODEID is null or REVENUESPLITOTHER.OTHERTYPECODEID=@OTHERTYPECODEID);

                    end

                    set @VALUE = @REGISTRATIONINCOME + @DONATIONINCOME + @OTHERINCOME - @REFUNDS;