UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK

CREATE function dbo.[UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK]
(
  @CURRENCYID uniqueidentifier,
  @ORGANIZATIONCURRENCYID uniqueidentifier,
  @DECIMALDIGITS tinyint,
  @ROUNDINGTYPECODE tinyint
)
returns table
as
return
(
  select
    [WRITEOFFSPLIT].[ID],
    [WRITEOFFSPLIT].FINANCIALTRANSACTIONID [WRITEOFFID],
    [REVENUESPLIT_EXT].[DESIGNATIONID],
    case
      when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID) then [WRITEOFFSPLIT].[ORGAMOUNT]
      else [WRITEOFFSPLIT].[BASEAMOUNT]
    end as [AMOUNTINCURRENCY],
    [WRITEOFF].[PARENTID] as [REVENUEID]
  from dbo.FINANCIALTRANSACTION [WRITEOFF] with (nolock)
  inner join dbo.FINANCIALTRANSACTIONLINEITEM [WRITEOFFSPLIT] with (nolock) on [WRITEOFF].[ID] = [WRITEOFFSPLIT].[FINANCIALTRANSACTIONID]
  inner join dbo.REVENUESPLIT_EXT on WRITEOFFSPLIT.ID = REVENUESPLIT_EXT.ID
  inner join dbo.FINANCIALTRANSACTION [REVENUE] with (nolock) on [REVENUE].[ID] = [WRITEOFF].[PARENTID]
  inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on WRITEOFF.ID = V.FINANCIALTRANSACTIONID
  where 
    WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null and (
    (@CURRENCYID is null) or
    (@CURRENCYID = @ORGANIZATIONCURRENCYID) or
    (@CURRENCYID = V.BASECURRENCYID))

  union all

  select
    [WRITEOFFSPLIT].[ID],
    [WRITEOFFSPLIT].FINANCIALTRANSACTIONID [WRITEOFFID],
    [REVENUESPLIT_EXT].[DESIGNATIONID],
    case
      when [LATESTORGANIZATIONEXCHANGERATE].[RATE] is not null
        then dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([WRITEOFFSPLIT].[ORGAMOUNT], [LATESTORGANIZATIONEXCHANGERATE].[RATE]), @DECIMALDIGITS, @ROUNDINGTYPECODE)

      when [LATESTINVERSEORGANIZATIONEXCHANGERATE].[RATE] is not null
        then dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([WRITEOFFSPLIT].[ORGAMOUNT], cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].[RATE]) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
      else
        0
    end [AMOUNTINCURRENCY],
    [WRITEOFF].[PARENTID] as [REVENUEID]
  from dbo.FINANCIALTRANSACTION [WRITEOFF] with (nolock)
  inner join dbo.FINANCIALTRANSACTIONLINEITEM [WRITEOFFSPLIT] with (nolock) on [WRITEOFF].[ID] = [WRITEOFFSPLIT].[FINANCIALTRANSACTIONID]
  inner join dbo.REVENUESPLIT_EXT on WRITEOFFSPLIT.ID = REVENUESPLIT_EXT.ID
  inner join dbo.FINANCIALTRANSACTION [REVENUE] with (nolock) on [REVENUE].[ID] = [WRITEOFF].[PARENTID]
  inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on WRITEOFF.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.ASOFDATE and
        dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAX
    ) [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.ASOFDATE and
        dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAX
    ) [LATESTINVERSEORGANIZATIONEXCHANGERATE]
    where 
      WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null and (
      (@CURRENCYID is not null) and
      (@CURRENCYID <> @ORGANIZATIONCURRENCYID) and
      (@CURRENCYID <> V.BASECURRENCYID))
      )