UFN_OPPORTUNITY_GETAMOUNTPAID_INCURRENCY

      CREATE function dbo.UFN_OPPORTUNITY_GETAMOUNTPAID_INCURRENCY
      (
        @OPPORTUNITYID uniqueidentifier,
        @CURRENCYID uniqueidentifier
      ) 
      returns money as
      begin
        declare @ASSOCIATEDGIFTS money;
        declare @ASSOCIATEDPLEDGEPAYMENTS money;
        declare @ASSOCIATEDEVENTREGISTRATIONPAYMENTS money;

        select
          @ASSOCIATEDGIFTS =
            sum (
              case
                when @CURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
                else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FINANCIALTRANSACTIONLINEITEM.ID, @CURRENCYID)
              end
            )
        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 = @OPPORTUNITYID and
          FINANCIALTRANSACTION.DELETEDON is null and
          FINANCIALTRANSACTION.TYPECODE = 0 and
          REVENUESPLIT_EXT.APPLICATIONCODE in (0,6,7); -- gifts, planned gifts, and matching gift claims


        set @ASSOCIATEDGIFTS = coalesce(@ASSOCIATEDGIFTS, 0);

        select
          @ASSOCIATEDPLEDGEPAYMENTS =
            sum (
                case
                  when @CURRENCYID = INSTALLMENTSPLITPAYMENT.APPLICATIONCURRENCYID then INSTALLMENTSPLITPAYMENT.AMOUNT
                  else dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(INSTALLMENTSPLITPAYMENT.ID, @CURRENCYID)
                end
            )
        from (
          select distinct
            FINANCIALTRANSACTION.ID as REVENUEID,
            FINANCIALTRANSACTION.DATEADDED
          from dbo.REVENUEOPPORTUNITY
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUEOPPORTUNITY.ID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
          where
            REVENUEOPPORTUNITY.OPPORTUNITYID = @OPPORTUNITYID and
            FINANCIALTRANSACTION.DELETEDON is null and
            FINANCIALTRANSACTION.TYPECODE in (1, 6)
          ) as OPPORTUNITYPLEDGES
        inner join dbo.INSTALLMENT on INSTALLMENT.REVENUEID = OPPORTUNITYPLEDGES.REVENUEID
        inner join INSTALLMENTSPLIT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
        inner join INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID;

        set @ASSOCIATEDPLEDGEPAYMENTS = coalesce(@ASSOCIATEDPLEDGEPAYMENTS, 0);

        select
          @ASSOCIATEDEVENTREGISTRATIONPAYMENTS =
            sum (
              case
                when @CURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
                else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FINANCIALTRANSACTIONLINEITEM.ID, @CURRENCYID)
              end
            )
        from dbo.EVENTREGISTRATIONOPPORTUNITY
          inner join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRATIONOPPORTUNITY.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
          inner join dbo.FINANCIALTRANSACTIONLINEITEM on EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
          inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        where
          EVENTREGISTRATIONOPPORTUNITY.OPPORTUNITYID = @OPPORTUNITYID and
          FINANCIALTRANSACTION.DELETEDON is null and
          FINANCIALTRANSACTION.TYPECODE = 0;

        set @ASSOCIATEDEVENTREGISTRATIONPAYMENTS = coalesce(@ASSOCIATEDEVENTREGISTRATIONPAYMENTS, 0);

        return @ASSOCIATEDGIFTS + @ASSOCIATEDPLEDGEPAYMENTS + @ASSOCIATEDEVENTREGISTRATIONPAYMENTS;
      end