USP_REPORT_EVENTREGISTRATIONPAYMENT

            CREATE procedure dbo.USP_REPORT_EVENTREGISTRATIONPAYMENT
            (
                @EVENTID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @INCLUDESUBEVENTS bit = 0,
                @CURRENCYCODE tinyint = 0
            )
            as
            set nocount on;

            begin try
                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                declare @SELECTEDCURRENCYID uniqueidentifier;
                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;

                --0 == use base of main event, anything else == use org currency

                if @CURRENCYCODE = 0 
                begin
                    select 
                        @SELECTEDCURRENCYID = EVENT.BASECURRENCYID,
                        @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                        @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                    from dbo.EVENT
                    inner join dbo.CURRENCY on CURRENCY.ID = EVENT.BASECURRENCYID
                    where EVENT.ID = @EVENTID;
                end
                else
                begin
                    select 
                        @SELECTEDCURRENCYID = CURRENCY.ID,
                        @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                        @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                    from dbo.CURRENCY 
                    where CURRENCY.ID = @ORGANIZATIONCURRENCYID;
                end 

                --Pull events and (if necessary) sub events. Include the base currency of the event so we don't need to look it up later

                declare @EVENTS table (ID uniqueidentifier, CURRENCYID uniqueidentifier);
                if @INCLUDESUBEVENTS = 0
                    insert into @EVENTS(ID, CURRENCYID)
                        select
                            EVENT.ID,
                            EVENT.BASECURRENCYID
                        from dbo.EVENT
                        where EVENT.ID = @EVENTID;
                else
                    insert into @EVENTS(ID, CURRENCYID)
                        select 
                            RELATEDEVENT.ID, 
                            EVENT.BASECURRENCYID
                        from dbo.EVENTHIERARCHY as RELATEDEVENT
                        inner join dbo.EVENTHIERARCHY as SOURCEEVENT on SOURCEEVENT.ID = @EVENTID
                        inner join dbo.EVENT on EVENT.ID = RELATEDEVENT.ID
                        where RELATEDEVENT.HIERARCHYPATH.IsDescendantOf(SOURCEEVENT.HIERARCHYPATH) = 1;

                declare @PAID money = 0;
                declare @UNPAID money = 0; 

                --Use UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK to convert the payment amounts for @PAID. This is the most accurate accounting of how much we took in

                select @PAID = coalesce    (
                                            (
                                                select sum(RSA.AMOUNTINCURRENCY)
                                                from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
                                                inner join dbo.EVENTREGISTRANTPAYMENT ERP on ERP.PAYMENTID = FTLI.ID
                                                inner join dbo.REGISTRANT R on R.ID = ERP.REGISTRANTID
                                                left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS,@ROUNDINGTYPECODE) as RSA on RSA.ID = FTLI.ID  
                                                where R.EVENTID in (select ID from @EVENTS)
                                            )
                                        ,0) - 
                               coalesce (
                                            (
                                                select sum(FTLI.TRANSACTIONAMOUNT)
                                                from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
                                                left join dbo.FINANCIALTRANSACTIONLINEITEM REFUND on REFUND.SOURCELINEITEMID = FTLI.ID
                                                inner join dbo.FINANCIALTRANSACTION FT on REFUND.FINANCIALTRANSACTIONID = FT.ID
                                                inner join dbo.EVENTREGISTRANTPAYMENT ERP on ERP.PAYMENTID = FTLI.ID
                                                inner join dbo.REGISTRANT R on R.ID = ERP.REGISTRANTID
                                                where R.EVENTID in (select ID from @EVENTS) and FT.TYPECODE = 23
                                            )
                                        ,0);

                select
                    @UNPAID = sum(coalesce(REGISTRANT.BALANCEINCURRENCY,0))
                from
                    dbo.UFN_EVENTREGISTRANT_GETBALANCEINCURRENCY_BULK(@SELECTEDCURRENCYID) as REGISTRANT
    where
                    REGISTRANT.EVENTID = @EVENTID;

                --Return our information to the report

                select
                    @PAID PAID,
                    @UNPAID UNPAID,
                    CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    CURRENCYPROPERTIES.DECIMALDIGITS
                from dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;