UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK

CREATE function [dbo].[UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK]
(
  @CURRENCYID uniqueidentifier,
  @ORGANIZATIONCURRENCYID uniqueidentifier,
  @DECIMALDIGITS tinyint,
  @ROUNDINGTYPECODE tinyint
)
returns table
as
return
(
  select
    REVENUE.CONSTITUENTID,
    REVENUE.TYPECODE TRANSACTIONTYPECODE,
    REVENUE.[TYPE] TRANSACTIONTYPE,
    cast(REVENUE.DATE as datetime) [DATE],
    case
      when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
        then REVENUE.ORGAMOUNT
      else REVENUE.BASEAMOUNT
    end [AMOUNTINCURRENCY],
    REVENUE.ID,
    REVENUE_EXT.MAILINGID,
    REVENUE.DATECHANGED,
    REVENUE_EXT.APPEALID
  from dbo.FINANCIALTRANSACTION REVENUE
  inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
  inner join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
  where REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and REVENUE.DELETEDON is null 
  and (
    (@CURRENCYID is null) 
    or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
    or @CURRENCYID = V.BASECURRENCYID
  )

  union all

  select
    REVENUE.CONSTITUENTID,
    REVENUE.TYPECODE TRANSACTIONTYPECODE,
    REVENUE.[TYPE] TRANSACTIONTYPE,
    cast(REVENUE.DATE as datetime) [DATE],
    case
      when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUE.ORGAMOUNT, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
      when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUE.ORGAMOUNT, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
      else 0
    end [AMOUNTINCURRENCY],
    REVENUE.ID,
    REVENUE_EXT.MAILINGID,
    REVENUE.DATECHANGED,
    REVENUE_EXT.APPEALID
  from dbo.FINANCIALTRANSACTION REVENUE
  inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
  inner join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
  outer apply
  (
    select
      RATE
    from dbo.CURRENCYEXCHANGERATE
    where  @ORGANIZATIONCURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID
    and @CURRENCYID = CURRENCYEXCHANGERATE.TOCURRENCYID
    and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
    and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
    and dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
    and dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
  ) LATESTORGANIZATIONEXCHANGERATE
  outer apply
  (
    select
      RATE
    from dbo.CURRENCYEXCHANGERATE
    where @CURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID
    and @ORGANIZATIONCURRENCYID= CURRENCYEXCHANGERATE.TOCURRENCYID
    and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
    and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
    and dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
    and dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
  ) LATESTINVERSEORGANIZATIONEXCHANGERATE
  where REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and REVENUE.DELETEDON is null
  and (
    (@CURRENCYID is not null) 
    and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
    and @CURRENCYID <> V.BASECURRENCYID
  )
)