UFN_EVENTREGISTRANT_GETBALANCEINCURRENCY_BULK

        CREATE function dbo.UFN_EVENTREGISTRANT_GETBALANCEINCURRENCY_BULK
        (
            @CURRENCYID uniqueidentifier
        )
        returns table
        as
        return (
            --NOTE: UFN_REVENUE_CONSTITUENTSWITHCOMMITMENTS and USP_REVENUEBATCH_CONSTITUENTCOMMITMENT do balance calculations inline. 

            --Any changes to the way balances are calculated needs to be made there as well. 


            select
                [REGISTRANT].[ID],
                case
                    when [CURRENCYPROPERTIES].[ID] = [EVENT].[BASECURRENCYID]
                        then [BALANCE].[AMOUNT]
                    else
                        dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([BALANCE].[AMOUNT], coalesce([LATESTBASEEXCHANGERATE].[RATE],cast((1 / [LATESTINVERSEBASEEXCHANGERATE].[RATE]) as decimal(20,8)),0)), [CURRENCYPROPERTIES].[DECIMALDIGITS], [CURRENCYPROPERTIES].[ROUNDINGTYPECODE])
                end [BALANCEINCURRENCY],
                [REGISTRANT].[EVENTID],
                [REGISTRANT].[CONSTITUENTID],
                [REGISTRANT].[ATTENDED],
                [REGISTRANT].[WILLNOTATTEND],
                [REGISTRANT].[GUESTOFREGISTRANTID],
                [REGISTRANT].[EVENTSEATINGNOTE],
                [REGISTRANT].[BENEFITSWAIVED],
                [REGISTRANT].[ONLINEREGISTRANT],
                [REGISTRANT].[ADDEDBYID],
                [REGISTRANT].[CHANGEDBYID],
                [REGISTRANT].[DATEADDED],
                [REGISTRANT].[DATECHANGED],
                [REGISTRANT].[ISCANCELLED],
                [REGISTRANT].[CUSTOMIDENTIFIER],
                [REGISTRANT].[NOTES],
                [REGISTRANT].[TSLONG],
                [REGISTRANT].[LOOKUPID],
                [EVENT].[BASECURRENCYID] as [EVENTBASECURRENCYID],
                [EVENT].[APPEALID] as [EVENTAPPEALID],
                [BALANCE].[AMOUNT] as BALANCE,
                [EVENT].[MAINEVENTID],
                [REGISTRANT].[ISWALKIN],
                [REGISTRANT].[USERMARKEDATTENDANCE]
            from
                dbo.[REGISTRANT]
                inner join dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
                inner join
                (
                    select [REGISTRANTID], cast(sum([AMOUNT])as money) as [AMOUNT] from
                        (
                                --baseline

                                select [ID] as [REGISTRANTID], 0 as [AMOUNT]
                                from [REGISTRANT]
                            union all
                                --outstanding registrations

                                select [REGISTRANTID], [AMOUNT]
                                from [REGISTRANTREGISTRATION]
                            union all
                                --payments on registrations

                                select [REGISTRANTID], -[AMOUNT]
                                from [EVENTREGISTRANTPAYMENT]
                            union all
                                --credits toward registrations

                                select
                                    [CREDITITEMEVENTREGISTRATION].[REGISTRANTID],
                                    ((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS)
                                from
                                    dbo.FINANCIALTRANSACTIONLINEITEM LI
                                    inner join dbo.CREDITITEM_EXT EXT on EXT.ID = LI.ID
                                    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
                                    inner join dbo.[CREDITITEMEVENTREGISTRATION] on [LI].[ID] = [CREDITITEMEVENTREGISTRATION].[ID]
                                where
                                    FT.TYPECODE = 23 --Refunds

                                    or LI.TYPECODE = 5 and FT.TYPECODE in (5, 99) --Discounts


                        ) as [BALANCEITEMS]
                    group by [REGISTRANTID]
                ) [BALANCE] on [REGISTRANT].[ID] = [BALANCE].[REGISTRANTID]
                cross join
                (
                    select [ID], [DECIMALDIGITS], [ROUNDINGTYPECODE] from
                    dbo.[UFN_CURRENCY_GETPROPERTIES](@CURRENCYID) where @CURRENCYID is not null
                    union all
                    select [ID], [DECIMALDIGITS], [ROUNDINGTYPECODE] from
                    dbo.[UFN_CURRENCY_GETPROPERTIES](dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) where @CURRENCYID is null
                ) as [CURRENCYPROPERTIES]
                outer apply
                (
                    select
                        [RATE]
                    from
                        dbo.[CURRENCYEXCHANGERATE]
                    where
                        [EVENT].[BASECURRENCYID] = [CURRENCYEXCHANGERATE].[FROMCURRENCYID]
                        and [CURRENCYPROPERTIES].[ID] = [CURRENCYEXCHANGERATE].[TOCURRENCYID]
                        and [CURRENCYEXCHANGERATE].[TYPECODE] in (0,1)
                        and [CURRENCYEXCHANGERATE].[ISLATESTRATEFORASOFDATE] = 1
                        and dateadd(ms, 86399996, getdate()) >= [CURRENCYEXCHANGERATE].[ASOFDATESDTZ]
                        and dateadd(ms, 86399996, getdate()) <= [CURRENCYEXCHANGERATE].[NEXTRATEASOFDATEORMAXSDTZ]
                ) [LATESTBASEEXCHANGERATE]
                outer apply
                (
                    select
                        [RATE]
                    from
                        dbo.[CURRENCYEXCHANGERATE]
                    where
                        [CURRENCYPROPERTIES].[ID] = [CURRENCYEXCHANGERATE].[FROMCURRENCYID]
                        and [EVENT].[BASECURRENCYID] = [CURRENCYEXCHANGERATE].[TOCURRENCYID]
                        and [CURRENCYEXCHANGERATE].[TYPECODE] in (0,1)
                        and [CURRENCYEXCHANGERATE].[ISLATESTRATEFORASOFDATE] = 1
                        and dateadd(ms, 86399996, getdate()) >= [CURRENCYEXCHANGERATE].[ASOFDATESDTZ]
                        and dateadd(ms, 86399996, getdate()) <= [CURRENCYEXCHANGERATE].[NEXTRATEASOFDATEORMAXSDTZ]
                ) [LATESTINVERSEBASEEXCHANGERATE]
        )