UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK

CREATE function [dbo].[UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK]
            (
                @CURRENCYID uniqueidentifier,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @DECIMALDIGITS tinyint,
                @ROUNDINGTYPECODE tinyint
            )
            returns table
            as
            return
            (
                select
                    REVENUESPLITGIFTAID.ID,
                    case
                        when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                            then
                                -- The calculation is pulled from the computed column ORGANIZATIONTAXCLAIMAMOUNT in REVENUESPLITGIFTAID

                                case 
                                     when TRANSITIONALTAXCLAIMAMOUNT = 0 then ORGANIZATIONBASETAXCLAIMAMOUNT
                                     when exists (select 1 from dbo.UFN_REVENUESPLITGIFTAID_GETTRANSITIONALRELIEFNOTEXPIRED() where ID = REVENUESPLITGIFTAID.ID) then ORGANIZATIONBASETAXCLAIMAMOUNT + ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT
                                     else ORGANIZATIONBASETAXCLAIMAMOUNT
                                end

                        else
                            -- The calculation is pulled from the computed column TAXCLAIMAMOUNT in REVENUESPLITGIFTAID

                            case 
                                    when TRANSITIONALTAXCLAIMAMOUNT = 0 then BASETAXCLAIMAMOUNT
                                    when exists (select 1 from dbo.UFN_REVENUESPLITGIFTAID_GETTRANSITIONALRELIEFNOTEXPIRED() where ID = REVENUESPLITGIFTAID.ID) then BASETAXCLAIMAMOUNT + TRANSITIONALTAXCLAIMAMOUNT
                                    else BASETAXCLAIMAMOUNT
                            end

                    end [TAXCLAIMAMOUNTINCURRENCY],
                    REVENUESPLITGIFTAID.DATECHANGED as DATEAMOUNTCHANGED
                from
                    dbo.REVENUESPLITGIFTAID
                where (@CURRENCYID is null) 
                    or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                    or @CURRENCYID = REVENUESPLITGIFTAID.BASECURRENCYID

                union all

                select
                    REVENUESPLITGIFTAID.ID,
                    case
                        when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)

                        when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)

                        else
                            0
                    end [TAXCLAIMAMOUNTINCURRENCY],
                    REVENUESPLITGIFTAID.DATECHANGED as DATEAMOUNTCHANGED

                from
                    (
                        select
                            ID,
                            BASECURRENCYID,
                            -- The calculation is pulled from the computed column ORGANIZATIONTAXCLAIMAMOUNT in REVENUESPLITGIFTAID

                            case 
                                when TRANSITIONALTAXCLAIMAMOUNT = 0 then ORGANIZATIONBASETAXCLAIMAMOUNT
                                when exists (select 1 from dbo.UFN_REVENUESPLITGIFTAID_GETTRANSITIONALRELIEFNOTEXPIRED() where ID = REVENUESPLITGIFTAID.ID) then ORGANIZATIONBASETAXCLAIMAMOUNT + ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT
                                else ORGANIZATIONBASETAXCLAIMAMOUNT
                            end as ORGANIZATIONTAXCLAIMAMOUNT,
                            DATECHANGED                    
                        from dbo.REVENUESPLITGIFTAID
                    ) as REVENUESPLITGIFTAID

                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESPLITGIFTAID.ID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                    left outer join dbo.CURRENCYEXCHANGERATE as LATESTORGANIZATIONEXCHANGERATE
                      on
                            @ORGANIZATIONCURRENCYID = LATESTORGANIZATIONEXCHANGERATE.FROMCURRENCYID
                            and @CURRENCYID = LATESTORGANIZATIONEXCHANGERATE.TOCURRENCYID
                            and LATESTORGANIZATIONEXCHANGERATE.TYPECODE in (0,1)
                            and LATESTORGANIZATIONEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
              and dateadd(ms, 86399996, FINANCIALTRANSACTION.[DATE]) >= LATESTORGANIZATIONEXCHANGERATE.ASOFDATESDTZ
              and dateadd(ms, 86399996, FINANCIALTRANSACTION.[DATE]) <= LATESTORGANIZATIONEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
          left outer join dbo.CURRENCYEXCHANGERATE as LATESTINVERSEORGANIZATIONEXCHANGERATE
            on 
                            @CURRENCYID = LATESTINVERSEORGANIZATIONEXCHANGERATE.FROMCURRENCYID
                            and @ORGANIZATIONCURRENCYID= LATESTINVERSEORGANIZATIONEXCHANGERATE.TOCURRENCYID
                            and LATESTINVERSEORGANIZATIONEXCHANGERATE.TYPECODE in (0,1)
                            and LATESTINVERSEORGANIZATIONEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
              and dateadd(ms, 86399996, FINANCIALTRANSACTION.[DATE]) >= LATESTINVERSEORGANIZATIONEXCHANGERATE.ASOFDATESDTZ
              and dateadd(ms, 86399996, FINANCIALTRANSACTION.[DATE]) <= LATESTINVERSEORGANIZATIONEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
                    where (@CURRENCYID is not null) 
                        and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
                        and @CURRENCYID <> REVENUESPLITGIFTAID.BASECURRENCYID    
                        and FINANCIALTRANSACTION.DELETEDON is null
                        and ((FINANCIALTRANSACTION.TYPECODE between 0 and 9) or (FINANCIALTRANSACTION.TYPECODE = 15)) -- revenue types

                        and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0 -- Standard

                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null

            )