UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK3

create function dbo.[UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK3]
(
  -- Include @ENDATE (formerly @ASOFDATE) and @STARTDATE for filtering.

  -- If you want to convert to...

  --    The base currency of each row:

  --        set @CURRENCYCODE = 0

  --        set @ORGANIZATIONCURRENCYID and @ORIGINCODE appropriately

  --    The organization currency:

  --        set @CURRENCYCODE = 1

  --        set @ORGANIZATIONCURRENCYID and @ORIGINCODE appropriately

  --    The transaction currency of each row:

  --        set @CURRENCYCODE = 2

  --        set @ORGANIZATIONCURRENCYID 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 and @ORIGINCODE appropriately


  @CURRENCYID uniqueidentifier,
  @ORGANIZATIONCURRENCYID uniqueidentifier,
  @DECIMALDIGITS tinyint,
  @ROUNDINGTYPECODE tinyint,
  @STARTDATE datetime,
  @ENDDATE datetime,
  @ORIGINCODE tinyint,
  @CURRENCYCODE tinyint,
  @REVENUEID uniqueidentifier
)
returns table
as
return
(
  with REVENUECTE
  as
  (
    select
      REVENUE.ID,
      REVENUE.TRANSACTIONAMOUNT - coalesce(PLEDGEAMOUNT.AMOUNT,0) - coalesce(WRITEOFFAMOUNT.AMOUNT,0)
      as BALANCEINCURRENCY,
      REVENUE.TRANSACTIONCURRENCYID,
      V.BASECURRENCYID,
      cast(REVENUE.DATE as datetime) [DATE],
      REVENUE.TYPECODE TRANSACTIONTYPECODE,
      BASEEXCHANGERATE.RATE as ORIGINALBASERATE,
      ORGEXCHANGERATE.RATE as ORIGINALORGANIZATIONRATE,
      CURRENCY.ID as CURRENCYID,

      -- Fields unused for revaluation, but that we need to include

      -- so this function can replace selects from the revenue table.

      REVENUE.TYPE TRANSACTIONTYPE,
      REVENUE.CONSTITUENTID,
      REVENUE.TRANSACTIONAMOUNT,
      REVENUE.BASEAMOUNT AMOUNT,
      REVENUE.ORGAMOUNT ORGANIZATIONAMOUNT,
      REVENUEINCURRENCY.APPEALID,
      REVENUEINCURRENCY.AMOUNTINCURRENCY

    from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
    inner join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
    cross apply (select V.BASECURRENCYID as ID where @CURRENCYCODE = 0
                 union all
                 select @ORGANIZATIONCURRENCYID where  @CURRENCYCODE = 1
                 union all
                 select REVENUE.TRANSACTIONCURRENCYID where @CURRENCYCODE = 2
                 union all
                 select @CURRENCYID where @CURRENCYCODE is null or @CURRENCYCODE not in (0,1,2)) as CURRENCY
    inner join dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUEINCURRENCY on REVENUE.ID = REVENUEINCURRENCY.ID
    left join dbo.CURRENCYEXCHANGERATE BASEEXCHANGERATE on BASEEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
    left join dbo.CURRENCYEXCHANGERATE ORGEXCHANGERATE on ORGEXCHANGERATE.ID = REVENUE.ORGEXCHANGERATEID
    left join (
      select sum(INSTALLMENTSPLITPAYMENT.AMOUNT) as AMOUNT, INSTALLMENTSPLITPAYMENT.PLEDGEID
      from dbo.INSTALLMENTSPLITPAYMENT
      inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
      inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
      where PAYMENT.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
      and cast(PAYMENT.DATE as datetime) <= isnull(@ENDDATE, cast(PAYMENT.DATE as datetime))
      group by INSTALLMENTSPLITPAYMENT.PLEDGEID) PLEDGEAMOUNT on PLEDGEAMOUNT.PLEDGEID = REVENUE.ID
    left join (
      select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT, WRITEOFF.PARENTID
      from dbo.INSTALLMENTSPLITWRITEOFF
      inner join dbo.FINANCIALTRANSACTION WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
      where WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null
      and cast(WRITEOFF.DATE as datetime) <= isnull(@ENDDATE, cast(WRITEOFF.DATE as datetime))
      group by WRITEOFF.PARENTID) WRITEOFFAMOUNT on WRITEOFFAMOUNT.PARENTID  = REVENUE.ID
    left join dbo.RECURRINGGIFTACTIVITY on RECURRINGGIFTACTIVITY.SOURCEREVENUEID = REVENUE.ID
    left join dbo.FINANCIALTRANSACTIONLINEITEM as RECURRINGGIFTPAYMENTLINEITEM on RECURRINGGIFTPAYMENTLINEITEM.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
    left join dbo.FINANCIALTRANSACTION as RECURRINGGIFTPAYMENT on RECURRINGGIFTPAYMENT.ID = RECURRINGGIFTPAYMENTLINEITEM.FINANCIALTRANSACTIONID
    where REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and REVENUE.DELETEDON is null and REVENUE.ID = @REVENUEID
    and (cast(REVENUE.DATE as datetime) >= isnull(@STARTDATE, cast(REVENUE.DATE as datetime)) and cast(REVENUE.DATE as datetime) <= isnull(@ENDDATE, cast(REVENUE.DATE as datetime))
      or cast(RECURRINGGIFTPAYMENT.DATE as datetime) >= isnull(@STARTDATE, cast(RECURRINGGIFTPAYMENT.DATE as datetime)) and cast(RECURRINGGIFTPAYMENT.DATE as datetime) <= isnull(@ENDDATE, cast(RECURRINGGIFTPAYMENT.DATE as datetime)))
  )

  select    -- Transaction currency

    REVENUECTE.ID, 
    REVENUECTE.BALANCEINCURRENCY,
    REVENUECTE.BALANCEINCURRENCY TRANSACTIONBALANCE,
    REVENUECTE.CURRENCYID,

    REVENUECTE.TRANSACTIONCURRENCYID,
    REVENUECTE.BASECURRENCYID,
    REVENUECTE.DATE,
    REVENUECTE.TRANSACTIONTYPECODE,
    REVENUECTE.TRANSACTIONTYPE,
    REVENUECTE.CONSTITUENTID,
    REVENUECTE.TRANSACTIONAMOUNT,
    REVENUECTE.AMOUNT,
    REVENUECTE.ORGANIZATIONAMOUNT,
    REVENUECTE.APPEALID,
    REVENUECTE.AMOUNTINCURRENCY,
    0 INTERMEDIATEBALANCE
  from REVENUECTE
  where (REVENUECTE.CURRENCYID is null)  
    or @CURRENCYCODE = 2  --We want transaction balance

    or (  --We want base balance and transaction = base currency

      @CURRENCYCODE = 0
      and REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.BASECURRENCYID
    )
    or (  --We want organization balance.

      @CURRENCYCODE = 1
      and (
        (  --The origin is T->B->O, and transaction = base = organization currency

          @ORIGINCODE = 0
          and REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.BASECURRENCYID
          and REVENUECTE.BASECURRENCYID = @ORGANIZATIONCURRENCYID
        )
        or(  --The origin is T->O, and transaction = organization currency

          @ORIGINCODE = 1
          and REVENUECTE.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
        )
      )
    )
    or (  --We want an arbitrary balance and transaction = arbitrary currency

      (@CURRENCYCODE is null or not @CURRENCYCODE in (0,1,2))
      and REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.CURRENCYID
    )
    or REVENUECTE.BALANCEINCURRENCY is null
    or REVENUECTE.BALANCEINCURRENCY = 0


  union all    -- Base or org currency


  select 
    REVENUECTE.ID,
    case
      when REVENUECTE.CURRENCYID = REVENUECTE.BASECURRENCYID
        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.BASERATE,REVENUECTE.ORIGINALBASERATE)), 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.BALANCEINCURRENCY, coalesce(REVALUATION.ORGANIZATIONRATE,REVENUECTE.ORIGINALORGANIZATIONRATE)), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                  else dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.BASERATE, REVENUECTE.ORIGINALBASERATE)), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE), coalesce(REVALUATION.ORGANIZATIONRATE, REVENUECTE.ORIGINALORGANIZATIONRATE)), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                end
            when 1
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.ORGANIZATIONRATE,REVENUECTE.ORIGINALORGANIZATIONRATE)), @DECIMALDIGITS, @ROUNDINGTYPECODE)
          end
      else 0
    end [BALANCEINCURRENCY],
    REVENUECTE.BALANCEINCURRENCY TRANSACTIONBALANCE,
    REVENUECTE.CURRENCYID,

    REVENUECTE.TRANSACTIONCURRENCYID,
    REVENUECTE.BASECURRENCYID,
    REVENUECTE.DATE,
    REVENUECTE.TRANSACTIONTYPECODE,
    REVENUECTE.TRANSACTIONTYPE,
    REVENUECTE.CONSTITUENTID,
    REVENUECTE.TRANSACTIONAMOUNT,
    REVENUECTE.AMOUNT,
    REVENUECTE.ORGANIZATIONAMOUNT,
    REVENUECTE.APPEALID,
    REVENUECTE.AMOUNTINCURRENCY,
    case
      when REVENUECTE.CURRENCYID = @ORGANIZATIONCURRENCYID
        and @ORIGINCODE = 0
        and REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.BASECURRENCYID
        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.BASERATE, REVENUECTE.ORIGINALBASERATE)), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE)
      else 0 
    end INTERMEDIATEBALANCE
  from REVENUECTE
  outer apply(
    select top 1
      REVENUECOMMITMENTREVALUATION.ID,
      COMMITMENTREVALUATION.BASEEXCHANGERATEID,
      COMMITMENTREVALUATION.ORGANIZATIONEXCHANGERATEID,
      BASERATE.RATE BASERATE,
      ORGANIZATIONRATE.RATE ORGANIZATIONRATE
    from dbo.REVENUECOMMITMENTREVALUATION
      inner join dbo.COMMITMENTREVALUATION on REVENUECOMMITMENTREVALUATION.COMMITMENTREVALUATIONID = COMMITMENTREVALUATION.ID
      left join dbo.CURRENCYEXCHANGERATE BASERATE on BASERATE.ID = COMMITMENTREVALUATION.BASEEXCHANGERATEID
      left join dbo.CURRENCYEXCHANGERATE ORGANIZATIONRATE on ORGANIZATIONRATE.ID = COMMITMENTREVALUATION.ORGANIZATIONEXCHANGERATEID
    where REVENUECOMMITMENTREVALUATION.REVENUEID = REVENUECTE.ID
      and COMMITMENTREVALUATION.DATE <= isnull(@ENDDATE, COMMITMENTREVALUATION.DATE)
    order by
      COMMITMENTREVALUATION.DATE desc, 
      COMMITMENTREVALUATION.SEQUENCE desc 
  ) REVALUATION
  left join dbo.CURRENCY BASECURRENCY on BASECURRENCY.ID = REVENUECTE.BASECURRENCYID
  where (REVENUECTE.CURRENCYID is not null)
    and 
    (
      REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.CURRENCYID
      or ( -- We want org balance in a T->B->0 system for gifts where transaction currency = org currency <> base currency.

        @ORIGINCODE = 0
        and @CURRENCYCODE = 1 
        and REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.BASECURRENCYID
      )
    )
    and (
      (  --We want base balance and base <> transaction currency

        (
          @CURRENCYCODE = 0
          or (
            (@CURRENCYCODE is null or @CURRENCYCODE not in (0,1,2))
            and @CURRENCYID = REVENUECTE.BASECURRENCYID
          )
        )
        and REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.BASECURRENCYID
      )
      or (  --We want organization balance.

        (
          @CURRENCYCODE = 1
          or (
            (@CURRENCYCODE is null or @CURRENCYCODE not in (0,1,2))
            and @CURRENCYID = @ORGANIZATIONCURRENCYID
          )
        )
        and (
          (  --The origin is T->B->O, but transaction <> base or base <> organization currency

            @ORIGINCODE = 0
            and (
              REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.BASECURRENCYID
              or REVENUECTE.BASECURRENCYID <> @ORGANIZATIONCURRENCYID
            )
          )
          or(  --The origin is T->O, and transaction <> organization currency

            @ORIGINCODE = 1
            and REVENUECTE.TRANSACTIONCURRENCYID <> @ORGANIZATIONCURRENCYID
          )
        )
      )
    )
    and REVENUECTE.BALANCEINCURRENCY is not null
    and REVENUECTE.BALANCEINCURRENCY <> 0

  union all    -- Arbitrary currency


  select 
    REVENUECTE.ID,
    case
      when LATESTEXCHANGERATE.RATE is not null
        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
      when LATESTINVERSEEXCHANGERATE.RATE is not null
        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
      else 0
    end [BALANCEINCURRENCY],
    REVENUECTE.BALANCEINCURRENCY TRANSACTIONBALANCE,
    REVENUECTE.CURRENCYID,

    REVENUECTE.TRANSACTIONCURRENCYID,
    REVENUECTE.BASECURRENCYID,
    REVENUECTE.DATE,
    REVENUECTE.TRANSACTIONTYPECODE,
    REVENUECTE.TRANSACTIONTYPE,
    REVENUECTE.CONSTITUENTID,
    REVENUECTE.TRANSACTIONAMOUNT,
    REVENUECTE.AMOUNT,
    REVENUECTE.ORGANIZATIONAMOUNT,
    REVENUECTE.APPEALID,
    REVENUECTE.AMOUNTINCURRENCY,
    0 INTERMEDIATEBALANCE
  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 (  --We want an arbitrary balance and arbitrary <> transaction currency

      (@CURRENCYCODE is null or not @CURRENCYCODE in (0,1,2))
      and REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.CURRENCYID
      and REVENUECTE.BASECURRENCYID <> REVENUECTE.CURRENCYID
      and @ORGANIZATIONCURRENCYID <> REVENUECTE.CURRENCYID
    )
    and REVENUECTE.BALANCEINCURRENCY is not null
    and REVENUECTE.BALANCEINCURRENCY <> 0
)