UFN_OPPORTUNITY_REVENUECOMMITTEDINCURRENCY

      CREATE function dbo.UFN_OPPORTUNITY_REVENUECOMMITTEDINCURRENCY
      (
        @ID uniqueidentifier,
        @CURRENCYID uniqueidentifier = null
      )
      returns money
      with execute as caller
      as begin

        if @CURRENCYID is null
          select @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        declare @REVENUECOMMITTED money;

        select
          @REVENUECOMMITTED = sum(AMOUNT)
        from (
          -- gifts, pledges, planned gifts, grant awards, and matching gift claims

          select
            case
              when FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = @CURRENCYID then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
              else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FINANCIALTRANSACTIONLINEITEM.ID, @CURRENCYID)
            end as AMOUNT
          from dbo.REVENUEOPPORTUNITY
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUEOPPORTUNITY.ID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
          where
            REVENUEOPPORTUNITY.OPPORTUNITYID = @ID and
            FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
            FINANCIALTRANSACTION.DELETEDON is null and
            (
              FINANCIALTRANSACTION.TYPECODE in (1, 3, 4, 6) or
              (
                FINANCIALTRANSACTION.TYPECODE = 0 and
                REVENUESPLIT_EXT.APPLICATIONCODE = 0
              )
            )

          union all

          -- event registrations

          select
            case
              when EVENT.BASECURRENCYID = @CURRENCYID then REGISTRANTREGISTRATION.AMOUNT
              else dbo.UFN_REGISTRANTREGISTRATION_GETAMOUNTINCURRENCY(REGISTRANTREGISTRATION.ID, @CURRENCYID)
            end as AMOUNT
          from dbo.EVENTREGISTRATIONOPPORTUNITY
            inner join dbo.REGISTRANT on EVENTREGISTRATIONOPPORTUNITY.ID = REGISTRANT.ID
            inner join dbo.REGISTRANTREGISTRATION on REGISTRANT.ID = REGISTRANTREGISTRATION.REGISTRANTID
            inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
          where
            EVENTREGISTRATIONOPPORTUNITY.OPPORTUNITYID = @ID and
            REGISTRANT.GUESTOFREGISTRANTID is null
        ) REVENUECOMMITTED;

        return coalesce(@REVENUECOMMITTED, 0);
      end