UFN_PLEDGE_GETAMOUNTPAIDINCURRENCY_BULK

CREATE function dbo.UFN_PLEDGE_GETAMOUNTPAIDINCURRENCY_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,
    @ASOFDATE datetime,
    @ORIGINCODE tinyint,
    @CURRENCYCODE tinyint
)
returns table
as
return
(
  with REVENUECTE as (
    select
      PLEDGE.ID as [PLEDGEID],
      SUM(INSTALLMENTSPLITPAYMENT.AMOUNT) [AMOUNTPAID],
      PLEDGE.TRANSACTIONCURRENCYID,
      V.BASECURRENCYID,
      BASEEXCHANGERATE.RATE as BASERATE,
      ORGANIZATIONEXCHANGERATE.RATE as ORGANIZATIONRATE,
      case @CURRENCYCODE
        when 0 then V.BASECURRENCYID
        when 2 then PLEDGE.TRANSACTIONCURRENCYID
        else @CURRENCYID
      end as CURRENCYID,
      cast(PLEDGE.DATE as datetime) as [DATE]
    from
      dbo.INSTALLMENTSPLITPAYMENT
      inner join dbo.FINANCIALTRANSACTION as PLEDGE on PLEDGE.ID = INSTALLMENTSPLITPAYMENT.PLEDGEID
      left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on PLEDGE.ID = V.FINANCIALTRANSACTIONID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
      inner join dbo.FINANCIALTRANSACTION as PAYMENT on PAYMENT.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
      left join dbo.CURRENCYEXCHANGERATE BASEEXCHANGERATE on BASEEXCHANGERATE.ID = PLEDGE.BASEEXCHANGERATEID
      left join dbo.CURRENCYEXCHANGERATE ORGANIZATIONEXCHANGERATE on ORGANIZATIONEXCHANGERATE.ID = PLEDGE.ORGEXCHANGERATEID
    where PLEDGE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and PAYMENT.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and PLEDGE.DELETEDON is null and REVENUESPLIT.DELETEDON is null and PAYMENT.DELETEDON is null and
      cast(PAYMENT.DATE as datetime) <= @ASOFDATE
    group by
      PLEDGE.ID, 
      V.BASECURRENCYID,
      PLEDGE.TRANSACTIONCURRENCYID,
      BASEEXCHANGERATE.RATE,
      ORGANIZATIONEXCHANGERATE.RATE,
      cast(PLEDGE.DATE as datetime)
    )

    -- Transaction currency

    select            
      REVENUECTE.PLEDGEID, 
      REVENUECTE.AMOUNTPAID as AMOUNTPAIDINCURRENCY,
      REVENUECTE.TRANSACTIONCURRENCYID,
      REVENUECTE.BASECURRENCYID
    from 
      REVENUECTE
    where 
      (REVENUECTE.CURRENCYID is null) 
      or REVENUECTE.CURRENCYID = REVENUECTE.TRANSACTIONCURRENCYID

    -- Base or org currency

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

    -- Arbitrary currency

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