Patch 107 for version 2.94 causes slow performance on the Recognition History tab

In version 2.94, the recognition History tab on the Revenue and Recognition page of a constituent record loads slowly or times out after applying Patch 107.

Patch 107 makes changes to how this tab performs.  The following scritp reverts the tab to the Patch 102 version:

exec usp_loadspec '<DataListSpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:d1p1="bb_appfx_commontypes" xmlns="bb_appfx_datalist" ID="1e25e348-03ff-4341-8b46-0c55567084d9" Name="Constituent Expanded Recognition History List" Description="This datalist returns a filtered list of revenue associated with a constituent. " Author="Blackbaud Product Development" d1p1:SecurityUIFolder="Constituent\Revenue">
  <d1p1:InstalledProductList>
    <d1p1:InstalledProduct ID="3117d2c8-7f46-42f2-abeb-b654f2f63046" />
    <d1p1:InstalledProduct ID="42c15648-749e-4859-a56d-3a6474814cc7" />
  </d1p1:InstalledProductList>
  <ResourceFile AssemblyName="Blackbaud.AppFx.Fundraising.Catalog.dll" ClassName="Blackbaud.AppFx.Fundraising.Catalog.ConstituentRecognitionHistoryExpanded.DataList" />
  <RSSFeed style="list">
    <Channel>
      <ViewDataFormID>0c836902-a398-47a0-91eb-8b66e434148e</ViewDataFormID>
      <Title Caption="Recognition History: {0.EN_US}" ResourceKey="RSS_TITLE">
        <FieldArguments>
          <Field>NAME</Field>
        </FieldArguments>
      </Title>
      <Description Caption="All recognition credits for &quot;{0.EN_US}&quot;" ResourceKey="RSS_DESCRIPTION">
        <FieldArguments>
          <Field>NAME</Field>
        </FieldArguments>
      </Description>
      <Image>
        <ImageKey>catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.fa_Revenue.png</ImageKey>
        <Title Caption="Constituent Recognition History" ResourceKey="RSS_IMAGETITLE" />
      </Image>
      <Groups>
        <Group Field="Type" />
      </Groups>
      <Categories>
        <Category Caption="Recognition History" ResourceKey="RSS_CHANNELCATEGORY" />
      </Categories>
    </Channel>
    <Item>
      <IDField>ID</IDField>
      <Title Caption="{0:d} - {1:c} - {2.EN_US}">
        <FieldArguments>
          <Field>DATE</Field>
          <Field>AMOUNT</Field>
          <Field>DETAIL</Field>
        </FieldArguments>
      </Title>
      <PublicationDateField>DATE</PublicationDateField>
      <DefaultLink>
        <ContextID>
          <ListContextID />
        </ContextID>
        <PageID>77b53b34-4271-448d-980d-bba1fe8030ce</PageID>
        <TabID>11316005-b40e-4544-a0bd-90be5753a032</TabID>
      </DefaultLink>
    </Item>
  </RSSFeed>
  <SPDataList SPName="USP_DATALIST_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY">
    <d1p1:CreateProcedureSQL>
     
create procedure dbo.USP_DATALIST_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY
(
  @CONSTITUENTID uniqueidentifier,
  @GROUPBY tinyint = 0,  -- 0=Commitment, 1=Transaction, null=None
  @TRANSACTIONTYPEOPTIONCODE int = null,  -- -1=All, otherwise revenue.transactiontypecode
  @REVENUETYPEOPTIONCODE int = null,  -- -1=All, otherwise revenuesplit.revenuetypecode
  @DATEFILTER tinyint = 16,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @INCLUDEGROUPMEMBERREVENUE bit = 0,
  @RECOGNITIONFILTERID uniqueidentifier = null,
  @CURRENCYCODE tinyint = 0,
  @CAMPAIGNFILTERMODE tinyint = 0,
  @CAMPAIGNSSELECTED xml = null
)
with execute as OWNER
as

  set nocount on; 
  begin

    declare
      @STARTDATE datetime,
      @ENDDATE datetime,
      @CURRENCYID uniqueidentifier,
      @ORGANIZATIONCURRENCYID uniqueidentifier,
      @CURRENCYROUNDINGTYPECODE tinyint,
      @CURRENCYISOCURRENCYCODE nvarchar(3) = null,
      @CURRENCYDECIMALDIGITS tinyint = 0,
      @CURRENCYSYMBOL nvarchar(5) = null,
      @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0,
      @ISUK bit;

    if @TRANSACTIONTYPEOPTIONCODE = -1
      set @TRANSACTIONTYPEOPTIONCODE = null;

    if @REVENUETYPEOPTIONCODE = -1
      set @REVENUETYPEOPTIONCODE = null;

    exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER, @STARTDATE output, @ENDDATE output;

    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    if @CURRENCYCODE = 1
      set @CURRENCYID = @ORGANIZATIONCURRENCYID

    if @CURRENCYCODE = 3
    begin
      set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
      if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
        set @CURRENCYCODE = 1
    end

    select
      @CURRENCYISOCURRENCYCODE = CURRENCY.ISO4217,
      @CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
      @CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
      @CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
      @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE        
    from dbo.CURRENCY
    where ID = @CURRENCYID;

    set @ISUK = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS(''9568A6C2-F7AA-45fd-8F54-21FE9654EE2D'');

    /* Get RevSplit IDs */
    if object_id(''tempdb..#TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS'') is not null
      drop table #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS;

    create table #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS
    ( 
      ID uniqueidentifier,
      SPLITID uniqueidentifier,
      REVENUEID uniqueidentifier,
      TRANSACTIONTYPECODE tinyint
    );

    declare @SQL nvarchar(max) = '''';
    declare @RECOGNITIONCREDITS_SQL nvarchar(max) = '''';
     
    if @CAMPAIGNFILTERMODE != 0
    begin
        set @SQL = ''
            declare @CAMPAIGNFILTERTABLE table (ID uniqueidentifier)
            insert into @CAMPAIGNFILTERTABLE
            select T.c.value(''''(ID)[1]'''',''''uniqueidentifier'''')
            from @CAMPAIGNSSELECTED.nodes(''''/CAMPAIGNSSELECTED/ITEM'''') T(c);'' + char(13);
        set @RECOGNITIONCREDITS_SQL = replace(@SQL, ''@CAMPAIGNFILTERTABLE'', ''@CAMPAIGNFILTERTABLE_RC'');
    end
     
    select @SQL = @SQL + ''
      insert into #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS(ID, SPLITID, REVENUEID, TRANSACTIONTYPECODE)
      select distinct
  REVENUERECOGNITION.ID, FINANCIALTRANSACTIONLINEITEM.ID, FINANCIALTRANSACTION.ID, FINANCIALTRANSACTION.TYPECODE
      from dbo.REVENUERECOGNITION '' + char(13);
   
    select @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + replace(@SQL, ''REVENUERECOGNITION'', ''RECOGNITIONCREDIT'')
   
    --BBNT\RyanDow 2012-04-20
    --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains
    --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)
    --If this logic is changed, the other places that use revenue/recognition filters will need to be updated
    declare @REVENUEFILTERID uniqueidentifier
    declare @REVENUEFILTERFUNCTION nvarchar(250)
    declare @PLEDGERECOGNITIONCODE tinyint
    if @RECOGNITIONFILTERID is not null
    begin
        select
      @REVENUEFILTERID = RECOGNITIONFILTER.REVENUEFILTERID,
      @PLEDGERECOGNITIONCODE = RECOGNITIONFILTER.PLEDGERECOGNITIONCODE,
      @REVENUEFILTERFUNCTION = REVENUEFILTER.FUNCTIONNAME
     from
      dbo.RECOGNITIONFILTER
      left join dbo.REVENUEFILTER on REVENUEFILTER.ID = RECOGNITIONFILTER.REVENUEFILTERID
     where
      RECOGNITIONFILTER.ID = @RECOGNITIONFILTERID

     if @REVENUEFILTERID is not null and @REVENUEFILTERFUNCTION is not null and @REVENUEFILTERFUNCTION &lt;&gt; ''''
     begin
      select @SQL = @SQL + ''
       inner join '' + @REVENUEFILTERFUNCTION + ''() FILTERED on FILTERED.ID = REVENUERECOGNITION.REVENUESPLITID '' + CHAR(13);
     end
       
        set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + ''
            inner join dbo.UFN_RECOGNITIONCREDIT_VALIDFILTER(@RECOGNITIONFILTERID) FILTERED on RECOGNITIONCREDIT.ID = FILTERED.ID '' + char(13);
    end
   
    set @SQL = @SQL + ''
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID '' + char(13)
   
    --BBNT\RyanDow 2012-04-20
    --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains
    --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)
    --If this logic is changed, the other places that use revenue/recognition filters will need to be updated
    if @RECOGNITIONFILTERID is not null
    begin
     select @SQL = @SQL + ''
        left join dbo.RECOGNITIONFILTERRECOGNTIONTYPE on RECOGNITIONFILTERRECOGNTIONTYPE.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID
         and RECOGNITIONFILTERRECOGNTIONTYPE.RECOGNITIONFILTERID = @RECOGNITIONFILTERID '' + char(13)
   
     if @PLEDGERECOGNITIONCODE &lt;&gt; 1
     begin
      select @SQL = @SQL + ''
      left join dbo.INSTALLMENTSPLITPAYMENT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
            left join dbo.FINANCIALTRANSACTION as PLEDGE on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLEDGE.ID '' + CHAR(13)
     end
    end
   
    select @SQL = @SQL + ''
        where REVENUERECOGNITION.CONSTITUENTID = @CONSTITUENTID
        and FINANCIALTRANSACTION.DELETEDON is null
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null '' + char(13);
   
    set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + ''
        left join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
        left join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
        left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        left join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
      where RECOGNITIONCREDIT.CONSTITUENTID = @CONSTITUENTID
        and ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is not null))'' + char(13);

    if @STARTDATE is not null
    begin
      set @SQL = @SQL + ''and REVENUERECOGNITION.EFFECTIVEDATE &gt;= @STARTDATE '' + char(13);
      set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + ''and RECOGNITIONCREDIT.EFFECTIVEDATE &gt;= @STARTDATE '' + char(13);
    end
   
    if @ENDDATE is not null
    begin
      set @SQL = @SQL + ''and REVENUERECOGNITION.EFFECTIVEDATE &lt;= @ENDDATE '' + char(13);
      set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + ''and RECOGNITIONCREDIT.EFFECTIVEDATE &lt;= @ENDDATE '' + char(13);
    end
   
    if @TRANSACTIONTYPEOPTIONCODE is not null
    begin
      set @SQL = @SQL + ''and FINANCIALTRANSACTION.TYPECODE = @TRANSACTIONTYPEOPTIONCODE '' + char(13);
      --We want to show internal sponsor credits under ''Donor challenge claim'' even though they are actually tied to payment
      set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + ''and (FINANCIALTRANSACTION.TYPECODE is null or (FINANCIALTRANSACTION.TYPECODE = 0 and @TRANSACTIONTYPEOPTIONCODE = 8))'' + char(13);     
    end
   
    if @REVENUETYPEOPTIONCODE is not null
    begin
      set @SQL = @SQL + ''and REVENUESPLIT_EXT.TYPECODE = @REVENUETYPEOPTIONCODE '' + char(13);
      set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + ''and (REVENUESPLIT_EXT.TYPECODE is null or REVENUESPLIT_EXT.TYPECODE = @REVENUETYPEOPTIONCODE) '' + char(13);
    end
   
    if @CAMPAIGNFILTERMODE != 0
    begin
        set @SQL = @SQL +
            ''and exists (
                select 1
                from dbo.REVENUESPLITCAMPAIGN
                    inner join @CAMPAIGNFILTERTABLE CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
                where REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            ) '' + char(13);
           
        set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL +
            ''and ((FINANCIALTRANSACTIONLINEITEM.ID is null)
              or exists (
                  select 1
                  from dbo.REVENUESPLITCAMPAIGN
                      inner join @CAMPAIGNFILTERTABLE_RC CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
                  where REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            ))'' + char(13);
    end

    --BBNT\RyanDow 2012-04-20
    --Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains
    --Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)
    --If this logic is changed, the other places that use revenue/recognition filters will need to be updated
    if @RECOGNITIONFILTERID is not null
    begin
     if @PLEDGERECOGNITIONCODE &lt;&gt; 1
     begin
         select @SQL = @SQL + ''
          and
          (
              (FINANCIALTRANSACTION.TYPECODE &lt;&gt; 0 or PLEDGE.CONSTITUENTID &lt;&gt; REVENUERECOGNITION.CONSTITUENTID)
              or (REVENUESPLIT_EXT.APPLICATIONCODE &lt;&gt; 2)
          ) '' + char(13)
     end
 
     select @SQL = @SQL + ''
         and
         (
          RECOGNITIONFILTERRECOGNTIONTYPE.ID is not null
             or not exists(select top 1 ID from dbo.RECOGNITIONFILTERRECOGNTIONTYPE RSUB where RSUB.RECOGNITIONFILTERID = @RECOGNITIONFILTERID)
         ) '' + char(13)
    end

    exec sp_executesql @SQL, N''@CONSTITUENTID uniqueidentifier, @RECOGNITIONFILTERID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime, @TRANSACTIONTYPEOPTIONCODE int, @REVENUETYPEOPTIONCODE int, @CAMPAIGNSSELECTED xml'',
    @CONSTITUENTID, @RECOGNITIONFILTERID, @STARTDATE, @ENDDATE, @TRANSACTIONTYPEOPTIONCODE, @REVENUETYPEOPTIONCODE, @CAMPAIGNSSELECTED;

    exec sp_executesql @RECOGNITIONCREDITS_SQL, N''@CONSTITUENTID uniqueidentifier, @RECOGNITIONFILTERID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime, @TRANSACTIONTYPEOPTIONCODE int, @REVENUETYPEOPTIONCODE int, @CAMPAIGNSSELECTED xml'',
    @CONSTITUENTID, @RECOGNITIONFILTERID, @STARTDATE, @ENDDATE, @TRANSACTIONTYPEOPTIONCODE, @REVENUETYPEOPTIONCODE, @CAMPAIGNSSELECTED;
   
    /*DELETE FOR SECURITY AND SITEFILTER*/
    delete FILTERED
    from #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS FILTERED
    left join dbo.RECOGNITIONCREDIT on FILTERED.ID = RECOGNITIONCREDIT.ID
    where not exists  (
        select top 1 REVSITES.SITEID
        from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FILTERED.SPLITID) REVSITES
        where
          -- Using a case statement since the standard site extension filters
          -- resulted in a poor plan
          case
            when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
            when exists (
                select 1
                from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
                where (RECOGNITIONCREDIT.ID is null and (SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                  or (RECOGNITIONCREDIT.ID is not null and (SITEID=dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID) or (SITEID is null and dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID) is null)))
              ) then 1
            else 0
          end = 1
          and (
            @SITEFILTERMODE = 0
              or exists(
                select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID
                from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
                where ((RECOGNITIONCREDIT.ID is null) and UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                  or (UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID))
              )
          )
      );
     
    declare @RECOGNITIONINCURRENCY table (
      RECOGNITIONID uniqueidentifier,
      SPLITID uniqueidentifier,
      REVENUEID uniqueidentifier,
      TRANSACTIONTYPECODE tinyint,
      AMOUNT money,
      REVENUESPLITAMOUNT money,
      CURRENCYID uniqueidentifier,
      ISRECOGNITIONCREDIT bit
    );

    insert into @RECOGNITIONINCURRENCY
    select
      FILTERED.ID RECOGNITIONID,
      FILTERED.SPLITID,
      FILTERED.REVENUEID,
      FILTERED.TRANSACTIONTYPECODE,
      --Just pull the base amounts for now and set the currencies later
      REVENUERECOGNITION.AMOUNT,
      FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT REVENUESPLITAMOUNT,
      case
        when @CURRENCYCODE = 0
          then REVENUERECOGNITION.BASECURRENCYID
        else @CURRENCYID
      end CURRENCYID,
      0 as ISRECOGNITIONCREDIT
    from #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS FILTERED
    inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.ID = FILTERED.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = FILTERED.SPLITID

    union all

    --insert other recognition credits
    select
      FILTERED.ID RECOGNITIONID,
      FILTERED.SPLITID,
      FILTERED.REVENUEID,
      FILTERED.TRANSACTIONTYPECODE,
      --Just pull the base amounts for now and set the currencies later
      RECOGNITIONCREDIT.AMOUNT,
      FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT REVENUESPLITAMOUNT,
      case
        when @CURRENCYCODE = 0
          then RECOGNITIONCREDIT.BASECURRENCYID
        else @CURRENCYID
      end CURRENCYID,
      1 as ISRECOGNITIONCREDIT
    from #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS FILTERED
    inner join dbo.RECOGNITIONCREDIT on RECOGNITIONCREDIT.ID = FILTERED.ID
    left join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = FILTERED.SPLITID
    where ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is not null));

    --Update the amounts to multicurrency if we aren''t using the base currency
    if @CURRENCYCODE &lt;&gt; 0
    begin
     update
      RIC
  set
   RIC.AMOUNT = REVENUERECOGNITIONINCURRENCY.AMOUNTINCURRENCY
  from
   @RECOGNITIONINCURRENCY RIC
        inner join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(
          @CURRENCYID,
          @ORGANIZATIONCURRENCYID,
          @CURRENCYDECIMALDIGITS,
          @CURRENCYROUNDINGTYPECODE
        ) as REVENUERECOGNITIONINCURRENCY on REVENUERECOGNITIONINCURRENCY.ID = RIC.RECOGNITIONID
        where
          RIC.ISRECOGNITIONCREDIT = 0;

        update
   RIC
  set
   RIC.AMOUNT = RECOGNITIONCREDITINCURRENCY.AMOUNTINCURRENCY
  from
   @RECOGNITIONINCURRENCY RIC
        inner join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(
          @CURRENCYID,
          @ORGANIZATIONCURRENCYID,
          @CURRENCYDECIMALDIGITS,
          @CURRENCYROUNDINGTYPECODE
        ) as RECOGNITIONCREDITINCURRENCY on RECOGNITIONCREDITINCURRENCY.ID = RIC.RECOGNITIONID
        where
          RIC.ISRECOGNITIONCREDIT = 1;

  update
   RIC
&nbs

Environment

 2.94.1524.107

Was this article helpful?