USP_DATALIST_CONSTITUENTGROUP_EXPANDED_RECOGNITIONHISTORY

CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUP_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,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null
)
with execute as OWNER
as

  set nocount on;  
begin

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

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

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

      if @STARTDATE is null and @ENDDATE is null
        exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER, @STARTDATE output, @ENDDATE output;
      else
      begin
        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
      end

      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 + '
        with CONSTITS_CTE
        as
        (
        select  
          @CONSTITUENTID as CONSTITUENTID,
        null as STARTDATE,
        null as ENDDATE
        union all
        select
          GROUPMEMBER.MEMBERID as CONSTITUENTID,
          GROUPMEMBERDATERANGE.DATEFROM STARTDATE,
          GROUPMEMBERDATERANGE.DATETO ENDDATE
        from dbo.GROUPMEMBER
        left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
        where
          GROUPMEMBER.GROUPID = @CONSTITUENTID
      )
      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 <> ''
        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 FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        inner join CONSTITS_CTE CONSTITS
        on CONSTITS.CONSTITUENTID = REVENUERECOGNITION.CONSTITUENTID ' + 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 <> 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
          FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
          and
          (@STARTDATE is null or REVENUERECOGNITION.EFFECTIVEDATE >= @STARTDATE) 
          and 
      (@ENDDATE is null or REVENUERECOGNITION.EFFECTIVEDATE <= @ENDDATE)
          and
          (CONSTITS.STARTDATE is null or REVENUERECOGNITION.EFFECTIVEDATE >= CONSTITS.STARTDATE)
          and
          (CONSTITS.ENDDATE is null or REVENUERECOGNITION.EFFECTIVEDATE <= CONSTITS.ENDDATE)' + 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 FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        inner join CONSTITS_CTE CONSTITS
        on CONSTITS.CONSTITUENTID = RECOGNITIONCREDIT.CONSTITUENTID
        where
          (@STARTDATE is null or RECOGNITIONCREDIT.EFFECTIVEDATE >= @STARTDATE) 
          and 
          (@ENDDATE is null or RECOGNITIONCREDIT.EFFECTIVEDATE <= @ENDDATE)
          and
          (CONSTITS.STARTDATE is null or RECOGNITIONCREDIT.EFFECTIVEDATE >= CONSTITS.STARTDATE)
          and
          (CONSTITS.ENDDATE is null or RECOGNITIONCREDIT.EFFECTIVEDATE <= CONSTITS.ENDDATE)
          and
          ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is not null))' + char(13);

    if @TRANSACTIONTYPEOPTIONCODE is not null
    begin
      set @SQL = @SQL + 'and FINANCIALTRANSACTION.TYPECODE = @TRANSACTIONTYPEOPTIONCODE ' + char(13);
      --we want to filter donor challenge internal sponsor credits under donor challenge even though they are tied to payment

      set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + 'and ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) 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 ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) 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 ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) 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 <> 1
        begin
            select @SQL = @SQL + '
                and
                (
                        -- Find pledges

                        (FINANCIALTRANSACTION.TYPECODE = 1 and REVENUESPLIT_EXT.APPLICATIONCODE = 0)
                        or
                        -- Find payments without pledge recognition

                        (
                            (FINANCIALTRANSACTION.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 2)
                            and
                            (not exists (select top 1 RR.ID from dbo.REVENUERECOGNITION as RR inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = RR.REVENUESPLITID where RR.CONSTITUENTID = @CONSTITUENTID and FTLI.FINANCIALTRANSACTIONID = PLEDGE.ID))
                        )
                ) ' + 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,
      CONSTITUENTID uniqueidentifier,
      TRANSACTIONTYPECODE tinyint,
      AMOUNT money,
      REVENUESPLITAMOUNT money,
      CURRENCYID uniqueidentifier,
      ISRECOGNITIONCREDIT bit
    )

    insert into @RECOGNITIONINCURRENCY
    select 
      FILTERED.ID RECOGNITIONID,
      FILTERED.SPLITID,
      FILTERED.REVENUEID,
      REVENUERECOGNITION.CONSTITUENTID,
      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,
      RECOGNITIONCREDIT.CONSTITUENTID,
      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 <> 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
        set
            RIC.REVENUESPLITAMOUNT = REVENUESPLITINCURRENCY.AMOUNTINCURRENCY
        from
            @RECOGNITIONINCURRENCY RIC
        left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
          @CURRENCYID, 
          @ORGANIZATIONCURRENCYID, 
          @CURRENCYDECIMALDIGITS, 
          @CURRENCYROUNDINGTYPECODE
        ) as REVENUESPLITINCURRENCY on REVENUESPLITINCURRENCY.ID = RIC.SPLITID;
    end;

    with RECOGNITION_CTE as (
      select 
        RECOGNITIONINCURRENCY.RECOGNITIONID,
        RECOGNITIONINCURRENCY.SPLITID,
        RECOGNITIONINCURRENCY.REVENUEID, 
        RECOGNITIONINCURRENCY.CONSTITUENTID,
        case 
          when @GROUPBY = 0 then dbo.UFN_REVENUESPLIT_GETCOMMITMENTID(FINANCIALTRANSACTIONLINEITEM.ID, REVENUESPLIT_EXT.APPLICATIONCODE) 
          else null
        end COMMITMENTREVENUEID,
        RECOGNITIONINCURRENCY.AMOUNT,
        case 
          when @ISUK = 0 then null
          else
            (
              case 
                when RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT > 0 then
                  case RECOGNITIONINCURRENCY.TRANSACTIONTYPECODE 
                    when 0 then 
                      case 
                        when RECOGNITIONINCURRENCY.AMOUNT > RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT 
                          then dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(RECOGNITIONINCURRENCY.SPLITID, 1) + RECOGNITIONINCURRENCY.AMOUNT 
                        else RECOGNITIONINCURRENCY.AMOUNT/RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT * dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(RECOGNITIONINCURRENCY.SPLITID, 1) + RECOGNITIONINCURRENCY.AMOUNT
                      end
                    when 1 then 
                      case 
                        when RECOGNITIONINCURRENCY.AMOUNT > RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT 
                          then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT_2(RECOGNITIONINCURRENCY.SPLITID, RECOGNITIONINCURRENCY.CURRENCYID) - RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT + RECOGNITIONINCURRENCY.AMOUNT 
                        else RECOGNITIONINCURRENCY.AMOUNT/RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT * (dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT_2(RECOGNITIONINCURRENCY.SPLITID, RECOGNITIONINCURRENCY.CURRENCYID) - RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT) + RECOGNITIONINCURRENCY.AMOUNT
                      end
                    else RECOGNITIONINCURRENCY.AMOUNT 
                  end
                else REVENUESPLITAMOUNT 
              end
            )
        end as [GROSSAMOUNT],
        RECOGNITIONINCURRENCY.TRANSACTIONTYPECODE,
        REVENUESPLIT_EXT.APPLICATION,
        REVENUESPLIT_EXT.TYPE REVENUETYPE,
        REVENUESPLIT_EXT.DESIGNATIONID,
        case when FINANCIALTRANSACTIONLINEITEM.DELETEDON is null then FINANCIALTRANSACTIONLINEITEM.DESCRIPTION else null end REVENUESPLITDESCRIPTION,
        RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT
      from @RECOGNITIONINCURRENCY RECOGNITIONINCURRENCY
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = RECOGNITIONINCURRENCY.SPLITID
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID

    ),
    SUMMEDREVENUE_CTE as (
      select 
                RECOGNITION_CTE.CONSTITUENTID,
        null as RECOGNITIONID,
        REVENUEID,
        null as PARENTID,
        dbo.UDA_BUILDLIST(distinct RECOGNITION_CTE.APPLICATION) APPLICATION,
        dbo.UDA_BUILDLIST(distinct RECOGNITION_CTE.REVENUETYPE) REVENUETYPE,
        null RECOGNITIONCREDITTYPES,
        null DESIGNATIONS,
        null REVENUECATEGORIES,
        null CAMPAIGNS,
        null SITES,
        sum(coalesce(RECOGNITION_CTE.AMOUNT,0)) AMOUNT,
        sum(coalesce(RECOGNITION_CTE.GROSSAMOUNT,0)) GROSSAMOUNT,
        sum(coalesce(RECOGNITION_CTE.REVENUESPLITAMOUNT,0)) REVENUESPLITAMOUNT
      from RECOGNITION_CTE
      where @GROUPBY = 1
      group by 
        REVENUEID, RECOGNITION_CTE.CONSTITUENTID
      having COUNT(*) > 1

      union all

      select
                RECOGNITION_CTE.CONSTITUENTID,
        null RECOGNITIONID,
        REVENUEID,
        null as PARENTID,
        dbo.UDA_BUILDLIST(distinct RECOGNITION_CTE.APPLICATION) APPLICATION,
        dbo.UDA_BUILDLIST(distinct RECOGNITION_CTE.REVENUETYPE) REVENUETYPE,
        dbo.UDA_BUILDLIST(distinct REVENUERECOGNITIONTYPECODE.DESCRIPTION) RECOGNITIONCREDITTYPES,
        dbo.UDA_BUILDLIST(case when DESIGNATIONID is null then REVENUESPLITDESCRIPTION else DESIGNATION.NAME end) DESIGNATIONS,
        dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) REVENUECATEGORIES,
        (
          select dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
          from dbo.REVENUESPLITCAMPAIGN
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID
            inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
          where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = RECOGNITION_CTE.REVENUEID
        ) CAMPAIGNS,
        (
          select dbo.UDA_BUILDLIST(SITE.NAME)
          from dbo.UFN_SITEID_MAPFROM_REVENUEID(RECOGNITION_CTE.REVENUEID) RSITE
          inner join dbo.SITE on SITE.ID = RSITE.SITEID
        ) SITES,
        sum(coalesce(RECOGNITION_CTE.AMOUNT,0)) AMOUNT,
        sum(coalesce(RECOGNITION_CTE.GROSSAMOUNT,0)) GROSSAMOUNT,
        sum(coalesce(RECOGNITION_CTE.REVENUESPLITAMOUNT,0)) REVENUESPLITAMOUNT
      from RECOGNITION_CTE
        inner join dbo.REVENUERECOGNITION on RECOGNITION_CTE.RECOGNITIONID = REVENUERECOGNITION.ID
        left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
        left join dbo.DESIGNATION on DESIGNATION.ID = RECOGNITION_CTE.DESIGNATIONID
        left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = RECOGNITION_CTE.SPLITID
        left join dbo.GLREVENUECATEGORYMAPPING on GLREVENUECATEGORYMAPPING.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
      where @GROUPBY = 0
        and RECOGNITION_CTE.TRANSACTIONTYPECODE <> 0
      group by 
        RECOGNITION_CTE.REVENUEID, RECOGNITION_CTE.CONSTITUENTID

      union all

      select
        RECOGNITION_CTE.CONSTITUENTID,
        RECOGNITION_CTE.RECOGNITIONID,
        REVENUEID,
        case @GROUPBY 
          when 0 then COMMITMENTREVENUEID 
          when 1 then REVENUEID 
          else null
        end as PARENTID,
        RECOGNITION_CTE.APPLICATION,
        RECOGNITION_CTE.REVENUETYPE,
        REVENUERECOGNITIONTYPECODE.DESCRIPTION RECOGNITIONCREDITTYPES,              
        case 
          when DESIGNATIONID is null then REVENUESPLITDESCRIPTION 
          else DESIGNATION.NAME 
        end DESIGNATIONS,
        GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME REVENUECATEGORIES,
        (
          select dbo.UDA_BUILDLIST(CAMPAIGN.NAME)
          from dbo.REVENUESPLITCAMPAIGN
            inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
          where REVENUESPLITCAMPAIGN.REVENUESPLITID = RECOGNITION_CTE.SPLITID
        ) CAMPAIGNS,
        (
          select dbo.UDA_BUILDLIST(SITE.NAME)
          from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RECOGNITION_CTE.SPLITID) RSITE
            inner join dbo.SITE on SITE.ID = RSITE.SITEID
        ) SITES,
        RECOGNITION_CTE.AMOUNT,
        RECOGNITION_CTE.GROSSAMOUNT,
        RECOGNITION_CTE.REVENUESPLITAMOUNT
      from RECOGNITION_CTE
        inner join dbo.REVENUERECOGNITION on RECOGNITION_CTE.RECOGNITIONID = REVENUERECOGNITION.ID
        left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
        left join dbo.DESIGNATION on DESIGNATION.ID = RECOGNITION_CTE.DESIGNATIONID
        left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = RECOGNITION_CTE.SPLITID
        left join dbo.GLREVENUECATEGORYMAPPING on GLREVENUECATEGORYMAPPING.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
      where RECOGNITION_CTE.TRANSACTIONTYPECODE = 0
        or coalesce(@GROUPBY, -1) <> 0

      union all

      select 
        RECOGNITION_CTE.CONSTITUENTID,
        RECOGNITION_CTE.RECOGNITIONID,
        REVENUEID,
        case @GROUPBY 
          when 0 then COMMITMENTREVENUEID 
          when 1 then REVENUEID 
          else null
        end as PARENTID,
        RECOGNITION_CTE.APPLICATION,
        RECOGNITION_CTE.REVENUETYPE,
        REVENUERECOGNITIONTYPECODE.DESCRIPTION RECOGNITIONCREDITTYPES,              
        case 
          when RECOGNITION_CTE.DESIGNATIONID is null then RECOGNITION_CTE.REVENUESPLITDESCRIPTION 
          else DESIGNATION.NAME 
        end DESIGNATIONS,
        GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME REVENUECATEGORIES,
        (
          select dbo.UDA_BUILDLIST(CAMPAIGN.NAME)
          from dbo.REVENUESPLITCAMPAIGN
            inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
          where REVENUESPLITCAMPAIGN.REVENUESPLITID = RECOGNITION_CTE.SPLITID
        ) CAMPAIGNS,
        (
          select dbo.UDA_BUILDLIST(SITE.NAME)
          from dbo.SITE 
          where SITE.ID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID)
        ) SITES,
        RECOGNITION_CTE.AMOUNT,
        RECOGNITION_CTE.GROSSAMOUNT,
        RECOGNITION_CTE.REVENUESPLITAMOUNT
      from RECOGNITION_CTE
        inner join dbo.RECOGNITIONCREDIT on RECOGNITION_CTE.RECOGNITIONID = RECOGNITIONCREDIT.ID
        left join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
        left join dbo.REVENUERECOGNITIONTYPECODE on RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
        left join dbo.DESIGNATION on DESIGNATION.ID = RECOGNITIONCREDIT.DESIGNATIONID
        left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = RECOGNITION_CTE.SPLITID
        left join dbo.GLREVENUECATEGORYMAPPING on GLREVENUECATEGORYMAPPING.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
      where (RECOGNITION_CTE.TRANSACTIONTYPECODE = 0
          or coalesce(@GROUPBY, -1) <> 0)
        and ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is not null))
    )
    select       
      case 
        when REVENUERECOGNITION.REVENUESPLITID is null 
        then 
          case 
            when RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is null 
            then SUMMEDREVENUE_CTE.REVENUEID 
            else (select REVENUESPLITID from dbo.DONORCHALLENGEENCUMBERED where ID = RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID) 
          end
        else REVENUERECOGNITION.REVENUESPLITID 
      end ID,
      case when REVENUERECOGNITION.REVENUESPLITID is null 
        then (select REVENUESPLITID from dbo.DONORCHALLENGEENCUMBERED where ID = RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID) 
        else REVENUERECOGNITION.REVENUESPLITID 
      end as REVENUESPLITID,
      case when RECOGNITIONCREDIT.ID is not null 
        then null --Disable go to payment for internal sponsor payment

        else SUMMEDREVENUE_CTE.REVENUEID
      end REVENUEID,
      SUMMEDREVENUE_CTE.PARENTID,
      case 
        when REVENUE_EXT.GIVENANONYMOUSLY = 1 then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.gift_anonymous_16.png' 
        else '' 
      end IMAGEKEY,
      case 
        when REVENUERECOGNITION.REVENUESPLITID is null and RECOGNITIONCREDIT.ID is null then 'F989EA79-EA4D-4FC7-B3F8-6EA606125CCA' 
        else '5DBB948F-BE5E-4E85-B652-CA3AEDE26A4F' 
      end VIEWFORMID,
      coalesce(REVENUERECOGNITION.EFFECTIVEDATE, cast(FINANCIALTRANSACTION.DATE as datetime)) EFFECTIVEDATE,
      C_NF.NAME,
      SUMMEDREVENUE_CTE.AMOUNT,
      SUMMEDREVENUE_CTE.GROSSAMOUNT,
      RECOGNITIONCREDITTYPES as RECOGNITIONCREDITTYPE,     
      case 
        when RECOGNITIONCREDIT.ID is not null 
        then 
          'Donor challenge claim - Internal sponsor'
        else FINANCIALTRANSACTION.TYPE
      end as TRANSACTIONTYPE,
      SUMMEDREVENUE_CTE.APPLICATION,
      SUMMEDREVENUE_CTE.REVENUETYPE,
      SUMMEDREVENUE_CTE.DESIGNATIONS,
      SUMMEDREVENUE_CTE.REVENUECATEGORIES,
      SUMMEDREVENUE_CTE.CAMPAIGNS,
      cast(FINANCIALTRANSACTION.DATE as datetime),
      SUMMEDREVENUE_CTE.REVENUESPLITAMOUNT,
      case 
        when @ISUK = 0 then null
      else
        case @CURRENCYCODE
          when 0 then 
            case
              when SUMMEDREVENUE_CTE.RECOGNITIONID is null
                then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(FINANCIALTRANSACTION.ID, 1,0)
              else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUERECOGNITION.REVENUESPLITID, 1,0)
            end
          when 1 then 
            case
              when SUMMEDREVENUE_CTE.RECOGNITIONID is null
                then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(FINANCIALTRANSACTION.ID, 1,2)
              else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUERECOGNITION.REVENUESPLITID, 1,2)
            end 
          when 3 then 
            case @CURRENCYID
              when case when FINANCIALTRANSACTION.DELETEDON is null then FINANCIALTRANSACTION.TRANSACTIONCURRENCYID else null end then  
                case
                  when SUMMEDREVENUE_CTE.RECOGNITIONID is null
                    then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(FINANCIALTRANSACTION.ID, 1,1)
                  else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUERECOGNITION.REVENUESPLITID,1,1)
                end
              else  
                case
                  when SUMMEDREVENUE_CTE.RECOGNITIONID is null
                    then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNTINCURRENCY(FINANCIALTRANSACTION.ID, 1,@CURRENCYID)
                  else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(REVENUERECOGNITION.REVENUESPLITID,1,@CURRENCYID)
                end
            end
        end
      end REVENUESPLITGROSSAMOUNT,
      case 
        when RECOGNITIONCREDIT.ID is not null  
        then 
          (select DONORCHALLENGESPONSORCODE.DESCRIPTION 
            from dbo.DONORCHALLENGEENCUMBERED 
            inner join dbo.DONORCHALLENGE on DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = DONORCHALLENGE.ID
            inner join dbo.DONORCHALLENGESPONSORCODE on DONORCHALLENGE.INTERNALSPONSORCODEID = DONORCHALLENGESPONSORCODE.ID 
            where DONORCHALLENGEENCUMBERED.ID = RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID)
        else DONOR_NF.NAME 
      end,
      SUMMEDREVENUE_CTE.SITES,
      case @CURRENCYCODE
        when 0 then case when FINANCIALTRANSACTION.DELETEDON is null then isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) else null end
        when 1 then @ORGANIZATIONCURRENCYID
        else @CURRENCYID 
      end as DISPLAYCURRENCY
    from SUMMEDREVENUE_CTE 
      inner join dbo.FINANCIALTRANSACTION on SUMMEDREVENUE_CTE.REVENUEID = FINANCIALTRANSACTION.ID
      inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
      left join dbo.REVENUERECOGNITION on SUMMEDREVENUE_CTE.RECOGNITIONID = REVENUERECOGNITION.ID
      left join dbo.RECOGNITIONCREDIT on SUMMEDREVENUE_CTE.RECOGNITIONID = RECOGNITIONCREDIT.ID
      left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SUMMEDREVENUE_CTE.CONSTITUENTID) C_NF
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) DONOR_NF
    order by EFFECTIVEDATE desc, C_NF.NAME, AMOUNT


end