UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCYNODESNAMENOGIFTAID_BULK

        CREATE function [dbo].[UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCYNODESNAMENOGIFTAID_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 APPEALCTE
            as
            (
                select 
                    APPEAL.ID APPEALID,
                    case @CURRENCYCODE
                        when 0 then APPEAL.BASECURRENCYID
                        when 1 then @ORGANIZATIONCURRENCYID
                        else @CURRENCYID
                    end as CURRENCYID,
                    REVENUESPLIT_EXT.DESIGNATIONID DESIGNATIONID,
                    FINANCIALTRANSACTION.ID REVENUEID,
                    cast(FINANCIALTRANSACTION.DATE as datetime) [DATE],
                    FINANCIALTRANSACTION.TYPECODE TRANSACTIONTYPECODE,
                    REVENUESPLIT_EXT.ID REVENUESPLITID,
                    REVENUESPLIT_EXT.APPLICATIONCODE,
                    REVENUESPLIT_EXT.TYPECODE,
                    FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT REVENUESPLITTRANSACTIONAMOUNT,
                    FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT REVENUESPLITAMOUNT,
                    FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT REVENUESPLITORGANIZATIONAMOUNT,
                    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID REVENUETRANSACTIONCURRENCYID,
                    isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) REVENUEBASECURRENCYID,
                    WOLI.TRANSACTIONAMOUNT WRITEOFFSPLITTRANSACTIONAMOUNT,
                    WOLI.BASEAMOUNT WRITEOFFSPLITAMOUNT,
                    WOLI.ORGAMOUNT WRITEOFFSPLITORGANIZATIONAMOUNT
                from
                    dbo.FINANCIALTRANSACTION 
                    inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
                    left join dbo.FINANCIALTRANSACTION WRITEOFF on FINANCIALTRANSACTION.ID = WRITEOFF.PARENTID 
                    left join dbo.FINANCIALTRANSACTIONLINEITEM WOLI on WRITEOFF.ID = WOLI.FINANCIALTRANSACTIONID 
                    left join dbo.REVENUESPLIT_EXT WRITEOFFSPLIT on WRITEOFFSPLIT.ID = WOLI.ID and REVENUESPLIT_EXT.DESIGNATIONID = WRITEOFFSPLIT.DESIGNATIONID
                    left join dbo.APPEAL on APPEAL.ID = REVENUE_EXT.APPEALID
                    where FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
                        and (WRITEOFF.ID is null or (WRITEOFF.TYPECODE in (20, 21) and WOLI.DELETEDON is null and WOLI.TYPECODE = 0))
            )
            select            -- Selected currency = base, org currency

                APPEALCTE.APPEALID,
                case
                    when (APPEALCTE.CURRENCYID = APPEALCTE.REVENUEBASECURRENCYID)
                        then APPEALCTE.REVENUESPLITAMOUNT
                    when (APPEALCTE.CURRENCYID = @ORGANIZATIONCURRENCYID) or (APPEALCTE.CURRENCYID is null)
                        then APPEALCTE.REVENUESPLITORGANIZATIONAMOUNT
                end [REVENUESPLITAMOUNTINCURRENCY],
                isnull(case
                    when (APPEALCTE.CURRENCYID = APPEALCTE.REVENUEBASECURRENCYID)
                        then APPEALCTE.WRITEOFFSPLITAMOUNT
                    when (APPEALCTE.CURRENCYID = @ORGANIZATIONCURRENCYID) or (APPEALCTE.CURRENCYID is null)
                        then APPEALCTE.WRITEOFFSPLITORGANIZATIONAMOUNT
                end, 0) [WRITEOFFSPLITAMOUNTINCURRENCY],
                APPEALCTE.DESIGNATIONID,
                APPEALCTE.REVENUEID,
                APPEALCTE.DATE,
                APPEALCTE.TRANSACTIONTYPECODE,
                APPEALCTE.REVENUESPLITID,
                APPEALCTE.APPLICATIONCODE,
                APPEALCTE.TYPECODE
            from 
                APPEALCTE
            where 
                (APPEALCTE.CURRENCYID is null)
                or (APPEALCTE.CURRENCYID = APPEALCTE.REVENUEBASECURRENCYID)
                or (APPEALCTE.CURRENCYID = @ORGANIZATIONCURRENCYID)

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


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

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


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