UFN_REVENUEBATCH_BATCHEVENTREGISTRANT_GETPAYMENTBENEFITS

create function dbo.UFN_REVENUEBATCH_BATCHEVENTREGISTRANT_GETPAYMENTBENEFITS
(
  @REGISTRANTID uniqueidentifier,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null
)
returns table
as
return

  select
    EVENTPRICEBENEFIT.BENEFITID,
    (EVENTPRICEBENEFIT.QUANTITY * BATCHREVENUEREGISTRANTREGISTRATION.QUANTITY) as QUANTITY,
    -- When the registration fee is waived, set the unit value to 0

    case
      when BATCHREVENUEREGISTRANTREGISTRATION.AMOUNT = 0 then
        cast (0 as money)
      else
        EVENTPRICEBENEFIT.UNITVALUE
    end as UNITVALUE,
    EVENTPRICEBENEFIT.DETAILS,
    BENEFIT.NAME as BENEFITNAME,
    case
      when BATCHREVENUEREGISTRANTREGISTRATION.AMOUNT = 0 then
        cast (0 as money)
      when EVENTPRICEBENEFIT.BASECURRENCYID = @TRANSACTIONCURRENCYID then
        EVENTPRICEBENEFIT.UNITVALUE * (EVENTPRICEBENEFIT.QUANTITY * BATCHREVENUEREGISTRANTREGISTRATION.QUANTITY)
      else
        dbo.UFN_CURRENCY_CONVERT
        (
          EVENTPRICEBENEFIT.UNITVALUE * (EVENTPRICEBENEFIT.QUANTITY * BATCHREVENUEREGISTRANTREGISTRATION.QUANTITY),
          dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST
          (
            EVENTPRICEBENEFIT.BASECURRENCYID,
            @TRANSACTIONCURRENCYID,
            getdate(),
            1,
            default
          )
        )
    end as TRANSACTIONTOTALVALUE
  from
    dbo.BATCHREVENUEREGISTRANT
  inner join
    dbo.BATCHREVENUEREGISTRANTREGISTRATION on BATCHREVENUEREGISTRANT.ID = BATCHREVENUEREGISTRANTREGISTRATION.REGISTRANTID
  inner join
    dbo.EVENTPRICE on BATCHREVENUEREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
  inner join
    dbo.EVENTPRICEBENEFIT on EVENTPRICE.ID = EVENTPRICEBENEFIT.EVENTPRICEID
  inner join
    dbo.BENEFIT on EVENTPRICEBENEFIT.BENEFITID = BENEFIT.ID
  where
    REGISTRANTID = @REGISTRANTID
    and
    BENEFITSWAIVED = 0
    and
    EVENTPRICEBENEFIT.BASECURRENCYID = @BASECURRENCYID;