UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK

      CREATE function dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK
      (
        @CURRENCYID uniqueidentifier,
        @ORGANIZATIONCURRENCYID uniqueidentifier,
        @DECIMALDIGITS tinyint,
        @ROUNDINGTYPECODE tinyint
      )
      returns table
      as
      return
      (
        select
          RECOGNITIONCREDIT.ID,
          DONORCHALLENGEENCUMBERED.REVENUESPLITID,
          RECOGNITIONCREDIT.CONSTITUENTID,
          RECOGNITIONCREDIT.EFFECTIVEDATE,
          case
            when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
              then RECOGNITIONCREDIT.ORGANIZATIONAMOUNT

            else
              RECOGNITIONCREDIT.AMOUNT 
          end [AMOUNTINCURRENCY],
          [RECOGNITIONCREDIT].[DATEADDED],
          [RECOGNITIONCREDIT].[USERRECOGNITIONTYPECODEID],
          [RECOGNITIONCREDIT].[TSLONG],
          FINANCIALTRANSACTION.[ID] as [REVENUEID],
          8 as [TRANSACTIONTYPECODE],   /* By hard coding 8, we force internal credit matches to be treated the same as external ones */
          FINANCIALTRANSACTION.[CONSTITUENTID] as [REVENUECONSTITUENTID],
          FINANCIALTRANSACTION.[DATE],
          FINANCIALTRANSACTION.[DATEADDED] as [REVENUEDATEADDED],
          REVENUESPLIT_EXT.[APPLICATIONCODE],
          REVENUESPLIT_EXT.[TYPECODE] as [REVENUESPLITTYPECODE],
          [RECOGNITIONCREDIT].[DESIGNATIONID],
          FINANCIALTRANSACTIONLINEITEM.[TSLONG] as [REVENUESPLITTSLONG],
          FINANCIALTRANSACTION.[TYPE] as [TRANSACTIONTYPE],
          case
            when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
              then null

            else
              RECOGNITIONCREDIT.ORGANIZATIONEXCHANGERATEID
          end [ORGANIZATIONEXCHANGERATEID],
          RECOGNITIONCREDIT.ORGANIZATIONAMOUNT,
          RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE
        from
          dbo.RECOGNITIONCREDIT with (nolock)
          left join dbo.DONORCHALLENGEENCUMBERED with (nolock) on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
          left join dbo.FINANCIALTRANSACTIONLINEITEM with (nolock) on FINANCIALTRANSACTIONLINEITEM.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
          left join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID= FINANCIALTRANSACTION.ID
          left join dbo.REVENUESPLIT_EXT with (nolock) on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        where 
            (
              (@CURRENCYID is null) 
              or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
              or (@CURRENCYID = RECOGNITIONCREDIT.BASECURRENCYID)
            )
            and (
              (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) 
              or (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is not null)
            )
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is NULL
            and FINANCIALTRANSACTION.DELETEDON is NULL
            and ((FINANCIALTRANSACTION.TYPECODE between 0 and 9) or (FINANCIALTRANSACTION.TYPECODE = 15))

        union all

        select
          RECOGNITIONCREDIT.ID,
          DONORCHALLENGEENCUMBERED.REVENUESPLITID,
          RECOGNITIONCREDIT.CONSTITUENTID,
          RECOGNITIONCREDIT.EFFECTIVEDATE,
          case
            when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(RECOGNITIONCREDIT.ORGANIZATIONAMOUNT, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)

            when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(RECOGNITIONCREDIT.ORGANIZATIONAMOUNT, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
            else
              0
          end [AMOUNTINCURRENCY],
          [RECOGNITIONCREDIT].[DATEADDED],
          [RECOGNITIONCREDIT].[USERRECOGNITIONTYPECODEID],
          [RECOGNITIONCREDIT].[TSLONG],
          FINANCIALTRANSACTION.[ID] as [REVENUEID],
          8 as [TRANSACTIONTYPECODE], /* By hard coding 8, we force internal credit matches to be treated the same as external ones */
          FINANCIALTRANSACTION.[CONSTITUENTID] as [REVENUECONSTITUENTID],
          FINANCIALTRANSACTION.[DATE],
          FINANCIALTRANSACTION.[DATEADDED] as [REVENUEDATEADDED],
          REVENUESPLIT_EXT.[APPLICATIONCODE],
          REVENUESPLIT_EXT.[TYPECODE] as [REVENUESPLITTYPECODE],
          [RECOGNITIONCREDIT].[DESIGNATIONID],
          FINANCIALTRANSACTIONLINEITEM.[TSLONG] as [REVENUESPLITTSLONG],
          FINANCIALTRANSACTION.[TYPE] as [TRANSACTIONTYPE],
          case
            when RECOGNITIONCREDIT.ORGANIZATIONEXCHANGERATEID is not null
              then RECOGNITIONCREDIT.ORGANIZATIONEXCHANGERATEID

            when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
              then [LATESTORGANIZATIONEXCHANGERATE].ID

            when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
              then [LATESTINVERSEORGANIZATIONEXCHANGERATE].ID
            else
              null
          end [ORGANIZATIONEXCHANGERATEID],
          RECOGNITIONCREDIT.ORGANIZATIONAMOUNT,
          RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE
        from
          dbo.RECOGNITIONCREDIT with (nolock)
          inner join dbo.DONORCHALLENGEENCUMBERED with (nolock) on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
          inner join dbo.FINANCIALTRANSACTIONLINEITEM with (nolock) on FINANCIALTRANSACTIONLINEITEM.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
          inner join dbo.REVENUESPLIT_EXT with (nolock) on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
          inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
          left outer join dbo.CURRENCYEXCHANGERATE as LATESTORGANIZATIONEXCHANGERATE
            on @ORGANIZATIONCURRENCYID = LATESTORGANIZATIONEXCHANGERATE.FROMCURRENCYID
              and @CURRENCYID = LATESTORGANIZATIONEXCHANGERATE.TOCURRENCYID
              and LATESTORGANIZATIONEXCHANGERATE.TYPECODE in (0,1)
              and LATESTORGANIZATIONEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
              and dateadd(ms, 86399996, FINANCIALTRANSACTION.[DATE]) >= LATESTORGANIZATIONEXCHANGERATE.ASOFDATESDTZ
              and dateadd(ms, 86399996, FINANCIALTRANSACTION.[DATE]) <= LATESTORGANIZATIONEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
          left outer join dbo.CURRENCYEXCHANGERATE as LATESTINVERSEORGANIZATIONEXCHANGERATE
            on               @CURRENCYID = LATESTINVERSEORGANIZATIONEXCHANGERATE.FROMCURRENCYID
              and @ORGANIZATIONCURRENCYID= LATESTINVERSEORGANIZATIONEXCHANGERATE.TOCURRENCYID
              and LATESTINVERSEORGANIZATIONEXCHANGERATE.TYPECODE in (0,1)
              and LATESTINVERSEORGANIZATIONEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
              and dateadd(ms, 86399996, FINANCIALTRANSACTION.DATE) >= LATESTINVERSEORGANIZATIONEXCHANGERATE.ASOFDATESDTZ
              and dateadd(ms, 86399996, FINANCIALTRANSACTION.DATE) <= LATESTINVERSEORGANIZATIONEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
          where ((@CURRENCYID is not null) 
              and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
              and (@CURRENCYID <> RECOGNITIONCREDIT.BASECURRENCYID))            
            and (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1) --only donor challenge recognition credits have a REVENUE.DATE right now

            and FINANCIALTRANSACTION.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and ((FINANCIALTRANSACTION.TYPECODE between 0 and 9) or (FINANCIALTRANSACTION.TYPECODE = 15))
        )