UFN_MEMBERSHIPLEVELREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK

            CREATE function [dbo].[UFN_MEMBERSHIPLEVELREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK]
            (
                -- If you want to convert to...

                --    The base currency of each row:

                --        set @CURRENCYCODE = 0

                --        set @ORGANIZATIONCURRENCYID appropriately

                --    The organization currency:

                --        set @CURRENCYCODE = 1

                --        set @ORGANIZATIONCURRENCYID appropriately

                --    An arbitrary currency:

                --        set @CURRENCYCODE = anything but 0 or 1

                --        set @CURRENCYID = the currency's ID

                --        set @DECIMALDIGITS and @ROUNDINGTYPECODE according to the currency

                --        set @ORGANIZATIONCURRENCYID

                @CURRENCYID uniqueidentifier,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @DECIMALDIGITS tinyint,
                @ROUNDINGTYPECODE tinyint,
                @CURRENCYCODE tinyint
            )
            returns table
            as
            return
            (
                with MEMBERSHIPLEVELCTE
                as
                (
                    select 
                        MEMBERSHIPTRANSACTION.ID MEMBERSHIPTRANSACTIONID,
                        MEMBERSHIPLEVEL.ID MEMBERSHIPLEVELID,
                        case @CURRENCYCODE
                            when 0 then MEMBERSHIPLEVEL.BASECURRENCYID
                            when 1 then @ORGANIZATIONCURRENCYID
                            else @CURRENCYID
                        end as CURRENCYID,
                        REVENUESPLIT.ID REVENUESPLITID,
                        REVENUESPLIT.BASEAMOUNT REVENUESPLITAMOUNT,
                        REVENUESPLIT.ORGAMOUNT REVENUESPLITORGANIZATIONAMOUNT,
                        isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) REVENUESPLITBASECURRENCYID,
                        cast(REVENUE.DATE as datetime) DATE
                    from
                        dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                        inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                        inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = REVENUE.ID                        
                        inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
                        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
                        inner join dbo.PDACCOUNTSYSTEM on REVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                        inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                    where
                        REVENUESPLIT.TYPECODE <> 1
                        and REVENUE.DELETEDON is null 
                        and REVENUESPLIT.DELETEDON is null                        
                )
                select            -- Selected currency =  base or org currency

                    MEMBERSHIPLEVELCTE.MEMBERSHIPTRANSACTIONID,
                    MEMBERSHIPLEVELCTE.MEMBERSHIPLEVELID,
                    case
                        when (MEMBERSHIPLEVELCTE.CURRENCYID = MEMBERSHIPLEVELCTE.REVENUESPLITBASECURRENCYID)
                            then MEMBERSHIPLEVELCTE.REVENUESPLITAMOUNT
                        when (MEMBERSHIPLEVELCTE.CURRENCYID = @ORGANIZATIONCURRENCYID) or (MEMBERSHIPLEVELCTE.CURRENCYID is null)
                            then MEMBERSHIPLEVELCTE.REVENUESPLITORGANIZATIONAMOUNT
                    end [REVENUESPLITAMOUNTINCURRENCY],
                    MEMBERSHIPLEVELCTE.REVENUESPLITID
                from 
                    MEMBERSHIPLEVELCTE
                where 
                    (MEMBERSHIPLEVELCTE.CURRENCYID is null)
                    or (MEMBERSHIPLEVELCTE.CURRENCYID = MEMBERSHIPLEVELCTE.REVENUESPLITBASECURRENCYID)
                    or (MEMBERSHIPLEVELCTE.CURRENCYID = @ORGANIZATIONCURRENCYID)

                union all            -- Use membership level base currency and currency <> base, org currency


                select
                    MEMBERSHIPLEVELCTE.MEMBERSHIPTRANSACTIONID,
                    MEMBERSHIPLEVELCTE.MEMBERSHIPLEVELID,
                    case
                        when LATESTEXCHANGERATE.RATE is not null
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(MEMBERSHIPLEVELCTE.REVENUESPLITORGANIZATIONAMOUNT, LATESTEXCHANGERATE.RATE), CURRENCY.DECIMALDIGITS, CURRENCY.ROUNDINGTYPECODE)
                        when LATESTINVERSEEXCHANGERATE.RATE is not null
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(MEMBERSHIPLEVELCTE.REVENUESPLITORGANIZATIONAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), CURRENCY.DECIMALDIGITS, CURRENCY.ROUNDINGTYPECODE)
                        else 0
                    end [REVENUESPLITAMOUNTINCURRENCY],
                    MEMBERSHIPLEVELCTE.REVENUESPLITID
                from MEMBERSHIPLEVELCTE
                    left join dbo.CURRENCY on CURRENCY.ID = MEMBERSHIPLEVELCTE.CURRENCYID
                    outer apply
                    (
                        select
                            RATE
                        from
                            dbo.CURRENCYEXCHANGERATE
                        where
                            CURRENCYEXCHANGERATE.FROMCURRENCYID = @ORGANIZATIONCURRENCYID
                            and CURRENCYEXCHANGERATE.TOCURRENCYID = MEMBERSHIPLEVELCTE.CURRENCYID
                            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                            and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996,MEMBERSHIPLEVELCTE.DATE)
                            and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996,MEMBERSHIPLEVELCTE.DATE)
                    ) LATESTEXCHANGERATE
                    outer apply
                    (
                        select
                            RATE
                        from
                            dbo.CURRENCYEXCHANGERATE
                        where
                            CURRENCYEXCHANGERATE.FROMCURRENCYID = MEMBERSHIPLEVELCTE.CURRENCYID
                            and CURRENCYEXCHANGERATE.TOCURRENCYID = @ORGANIZATIONCURRENCYID
                            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                            and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996,MEMBERSHIPLEVELCTE.DATE)
                            and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996,MEMBERSHIPLEVELCTE.DATE)
                    ) LATESTINVERSEEXCHANGERATE
                where (MEMBERSHIPLEVELCTE.CURRENCYID is not null)
                    and (MEMBERSHIPLEVELCTE.CURRENCYID <> MEMBERSHIPLEVELCTE.REVENUESPLITBASECURRENCYID)
                    and (MEMBERSHIPLEVELCTE.CURRENCYID <> @ORGANIZATIONCURRENCYID)
                    and @CURRENCYCODE = 0

                union all        -- Use arbitrary currency and currency <> base, org currency


                select 
                    MEMBERSHIPLEVELCTE.MEMBERSHIPTRANSACTIONID,
                    MEMBERSHIPLEVELCTE.MEMBERSHIPLEVELID,
                    case
                        when LATESTEXCHANGERATE.RATE is not null
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(MEMBERSHIPLEVELCTE.REVENUESPLITORGANIZATIONAMOUNT, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                        when LATESTINVERSEEXCHANGERATE.RATE is not null
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(MEMBERSHIPLEVELCTE.REVENUESPLITORGANIZATIONAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                        else 0
                    end [REVENUESPLITAMOUNTINCURRENCY],
                    MEMBERSHIPLEVELCTE.REVENUESPLITID
                from MEMBERSHIPLEVELCTE
                    outer apply
                    (
                        select
                            RATE
                        from
                            dbo.CURRENCYEXCHANGERATE
                        where
                            CURRENCYEXCHANGERATE.FROMCURRENCYID = @ORGANIZATIONCURRENCYID
                            and CURRENCYEXCHANGERATE.TOCURRENCYID = MEMBERSHIPLEVELCTE.CURRENCYID
                            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                            and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996,MEMBERSHIPLEVELCTE.DATE)
                            and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996,MEMBERSHIPLEVELCTE.DATE)
                    ) LATESTEXCHANGERATE
                    outer apply
                    (
                        select
                            RATE
                        from
                            dbo.CURRENCYEXCHANGERATE
                        where
                            CURRENCYEXCHANGERATE.FROMCURRENCYID = MEMBERSHIPLEVELCTE.CURRENCYID
                            and CURRENCYEXCHANGERATE.TOCURRENCYID = @ORGANIZATIONCURRENCYID
                            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                            and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996,MEMBERSHIPLEVELCTE.DATE)
                            and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996,MEMBERSHIPLEVELCTE.DATE)
                    ) LATESTINVERSEEXCHANGERATE
                where (MEMBERSHIPLEVELCTE.CURRENCYID is not null)
                    and (MEMBERSHIPLEVELCTE.CURRENCYID <> MEMBERSHIPLEVELCTE.REVENUESPLITBASECURRENCYID)
                    and (MEMBERSHIPLEVELCTE.CURRENCYID <> @ORGANIZATIONCURRENCYID)
                    and @CURRENCYCODE not in (0, 1)
                            )