UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTSINCURRENCY_BULK

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

                --    The base currency of each row:

                --        set @CURRENCYCODE = 0

                --        set @ORGANIZATIONCURRENCYID, @ASOFDATE, and @ORIGINCODE appropriately

                --    The transaction currency of each row:

                --        set @CURRENCYCODE = 2

                --        set @ORGANIZATIONCURRENCYID, @ASOFDATE and @ORIGINCODE appropriately

                --    The organization currency:

                --        set @CURRENCYCODE = anything but 0 or 2

                --        set @CURRENCYID = the org currency's ID

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

                --        set @ORGANIZATIONCURRENCYID, @ASOFDATE and @ORIGINCODE appropriately

                --    An arbitrary currency:

                --        set @CURRENCYCODE = anything but 0 or 2

                --        set @CURRENCYID = the currency's ID

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

                --        set @ORGANIZATIONCURRENCYID, @ASOFDATE and @ORIGINCODE appropriately

                @CURRENCYID uniqueidentifier,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @DECIMALDIGITS tinyint,
                @ROUNDINGTYPECODE tinyint,
                @ORIGINCODE tinyint,
                @CURRENCYCODE tinyint
            )
            returns table
            as
            return
            (
                with INSTALLMENTSPLITWRITEOFF_CTE as
                (
                    select
                        INSTALLMENTSPLITWRITEOFF.ID,
                        INSTALLMENTSPLITWRITEOFF.WRITEOFFID,
                        INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID,
                        INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT,
                        INSTALLMENTSPLITWRITEOFF.TRANSACTIONCURRENCYID,
                        INSTALLMENTSPLITWRITEOFF.BASECURRENCYID,
                        cast(REVENUE.DATE as datetime) [DATE],
                        BASEEXCHANGERATE.RATE [BASERATE],
                        ORGANIZATIONEXCHANGERATE.RATE [ORGANIZATIONRATE],
                        case @CURRENCYCODE
                            when 0 then INSTALLMENTSPLITWRITEOFF.BASECURRENCYID
                            when 2 then INSTALLMENTSPLITWRITEOFF.TRANSACTIONCURRENCYID
                            else @CURRENCYID
                        end CURRENCYID
                    from
                        dbo.INSTALLMENTSPLITWRITEOFF
                        inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFF.ID = INSTALLMENTSPLITWRITEOFF.WRITEOFFID
                        inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = WRITEOFF.PARENTID
                        left join dbo.CURRENCYEXCHANGERATE BASEEXCHANGERATE on BASEEXCHANGERATE.ID = INSTALLMENTSPLITWRITEOFF.BASEEXCHANGERATEID
                        left join dbo.CURRENCYEXCHANGERATE ORGANIZATIONEXCHANGERATE on ORGANIZATIONEXCHANGERATE.ID = INSTALLMENTSPLITWRITEOFF.ORGANIZATIONEXCHANGERATEID
                        where WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and REVENUE.DELETEDON is null 
                )
                -- transaction currency

                select
                    INSTALLMENTSPLITWRITEOFF_CTE.ID,
                    INSTALLMENTSPLITWRITEOFF_CTE.WRITEOFFID,
                    INSTALLMENTSPLITWRITEOFF_CTE.INSTALLMENTSPLITID,
                    INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONAMOUNT [AMOUNTINCURRENCY]
                from
                    INSTALLMENTSPLITWRITEOFF_CTE
                where
                    INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID is null
                or
                    INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID = INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONCURRENCYID

                -- base or org currency

                union all
                select
                    INSTALLMENTSPLITWRITEOFF_CTE.ID,
                    INSTALLMENTSPLITWRITEOFF_CTE.WRITEOFFID,
                    INSTALLMENTSPLITWRITEOFF_CTE.INSTALLMENTSPLITID,
                    case
                        when INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID = INSTALLMENTSPLITWRITEOFF_CTE.BASECURRENCYID then 
                            dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONAMOUNT, INSTALLMENTSPLITWRITEOFF_CTE.BASERATE), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE)
                        when INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID = @ORGANIZATIONCURRENCYID then
                            case @ORIGINCODE
                                when 0 then
                                    case
                                        when INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONCURRENCYID = INSTALLMENTSPLITWRITEOFF_CTE.BASECURRENCYID then 
                                            dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONAMOUNT, INSTALLMENTSPLITWRITEOFF_CTE.ORGANIZATIONRATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                                        else
                                            dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONAMOUNT, INSTALLMENTSPLITWRITEOFF_CTE.BASERATE), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE), INSTALLMENTSPLITWRITEOFF_CTE.ORGANIZATIONRATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                                    end
                                when 1 then
                                    dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONAMOUNT, INSTALLMENTSPLITWRITEOFF_CTE.ORGANIZATIONRATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                            end
                        else 0
                    end [AMOUNTINCURRENCY]
                from
                    INSTALLMENTSPLITWRITEOFF_CTE
                    left join dbo.CURRENCY BASECURRENCY on BASECURRENCY.ID = INSTALLMENTSPLITWRITEOFF_CTE.BASECURRENCYID
                where
                    (INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID is not null)
                    and (INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID <> INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONCURRENCYID)
                    and (INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID = @ORGANIZATIONCURRENCYID
                        or INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID = INSTALLMENTSPLITWRITEOFF_CTE.BASECURRENCYID)

                -- arbitrary currency

                union all
                select
                    INSTALLMENTSPLITWRITEOFF_CTE.ID,
                    INSTALLMENTSPLITWRITEOFF_CTE.WRITEOFFID,
                    INSTALLMENTSPLITWRITEOFF_CTE.INSTALLMENTSPLITID,
                    case
                        when LATESTEXCHANGERATE.RATE is not null then
                            dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONAMOUNT, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                        when LATESTINVERSEEXCHANGERATE.RATE is not null then
                            dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)    
                        else 0
                    end [AMOUNTINCURRENCY]
                from
                    INSTALLMENTSPLITWRITEOFF_CTE
                    outer apply (
                        select
                            RATE
                        from
                            dbo.CURRENCYEXCHANGERATE
                        where
                            CURRENCYEXCHANGERATE.FROMCURRENCYID = INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONCURRENCYID
                            and CURRENCYEXCHANGERATE.TOCURRENCYID = INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID
                            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                            and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996,INSTALLMENTSPLITWRITEOFF_CTE.DATE)
                            and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996,INSTALLMENTSPLITWRITEOFF_CTE.DATE)
                    ) LATESTEXCHANGERATE
                    outer apply (
                        select
                            RATE
                        from
                            dbo.CURRENCYEXCHANGERATE
                        where
                            CURRENCYEXCHANGERATE.FROMCURRENCYID = INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID
                            and CURRENCYEXCHANGERATE.TOCURRENCYID = INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONCURRENCYID
                            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                            and    CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996,INSTALLMENTSPLITWRITEOFF_CTE.DATE)
                            and CURRENCYEXCHANGERATE.NEXTRATEASOFDATESDTZ >= dateadd(ms, 86399996,INSTALLMENTSPLITWRITEOFF_CTE.DATE)
                    ) LATESTINVERSEEXCHANGERATE
                where
                    INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID is not null
                    and INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID <> INSTALLMENTSPLITWRITEOFF_CTE.TRANSACTIONCURRENCYID
                    and INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID <> INSTALLMENTSPLITWRITEOFF_CTE.BASECURRENCYID
                    and INSTALLMENTSPLITWRITEOFF_CTE.CURRENCYID <> @ORGANIZATIONCURRENCYID
            )