USP_DATALIST_DESIGNATIONLEVELDONORS_3

CREATE procedure [dbo].[USP_DATALIST_DESIGNATIONLEVELDONORS_3] (
  @ID uniqueidentifier = null
  ,@DONORSEARCHTEXT nvarchar(100) = null
  ,@DESIGNATIONID uniqueidentifier = null
  ,@DATERANGE tinyint = 29
  ,@FROM datetime = null
  ,@TO datetime = null
  ,@TRANSACTIONTYPEOPTIONCODE int = null
  ,@REVENUEFILTERID uniqueidentifier = null
  ,@CAMPAIGNFILTERMODE tinyint = 0
  ,@CAMPAIGNSSELECTED xml = null
  )
  with execute as owner
as
set nocount off;

--********************************************************************

--DUPLICATING THE INPUT PARAMETERS AND USING THE DUPLICATES IN THE SQL

--IS INTENTIONAL AND RESULTS IN A BETTER QUERY PLAN (http://stackoverflow.com/questions/421275/different-execution-plan-when-executing-statement-directly-and-from-stored-proce)

--********************************************************************

declare @ID2 uniqueidentifier = @ID
  ,@DONORSEARCHTEXT2 nvarchar(100) = @DONORSEARCHTEXT
  ,@DESIGNATIONID2 uniqueidentifier = @DESIGNATIONID
  ,@DATERANGE2 tinyint = @DATERANGE
  ,@FROM2 datetime = @FROM
  ,@TO2 datetime = @TO
  ,@TRANSACTIONTYPEOPTIONCODE2 int = @TRANSACTIONTYPEOPTIONCODE
  ,@REVENUEFILTERID2 uniqueidentifier = @REVENUEFILTERID
  ,@CAMPAIGNFILTERMODE2 tinyint = @CAMPAIGNFILTERMODE
  ,@CAMPAIGNSSELECTED2 xml = @CAMPAIGNSSELECTED
  ,@ABORT bit = 0;
declare @debugging bit = 0
  ,@debugMsg nvarchar(1000)
  ,@debugStartTime datetime = getdate();

if isnull(@TRANSACTIONTYPEOPTIONCODE2, - 1) = - 1
  set @TRANSACTIONTYPEOPTIONCODE2 = null;

if @DATERANGE2 is null
  set @DATERANGE2 = 10;--all dates


if @DATERANGE2 = 0
begin
  set @FROM2 = dbo.UFN_DATE_GETEARLIESTTIME(@FROM2);
  set @TO2 = dbo.UFN_DATE_GETLATESTTIME(@TO2);
end
else
  exec [dbo].[USP_RESOLVEDATEFILTER] @DATERANGE2
    ,@FROM2 output
    ,@TO2 output;

--NOTE: please note that this routine DOES NOT drop any temporary tables - this means that if you

--      change the structure of any of these temp tables you WILL NEED TO ADD A REVISION to drop

--      the temp tables before the spec is reloaded

--if object_id('tempdb..#TMP_DONOR_INFO_FILTEREDIDS') is not null

--  drop table #TMP_DONOR_INFO_FILTEREDIDS;

if object_id('tempdb..#TMP_DONOR_INFO_FILTEREDIDS') is null
begin
  create table #TMP_DONOR_INFO_FILTEREDIDS (
    [SPID] int
    ,[ID] uniqueidentifier
    ,[RECORDTYPE] smallint primary key (
      [SPID]
      ,[ID]
      ,[RECORDTYPE]
      ) --0=DESIGNATIONID, 1=CAMPAIGNID, 2=CONSTITUENTID

    );
end
else
begin
  delete
  from #TMP_DONOR_INFO_FILTEREDIDS
  where SPID = @@SPID;
end

if @DESIGNATIONID2 is not null
  insert into #TMP_DONOR_INFO_FILTEREDIDS
  select @@SPID
    ,ID
    ,0
  from dbo.DESIGNATION with (nolock)
  where DESIGNATION.ID = @DESIGNATIONID2
else
  insert into #TMP_DONOR_INFO_FILTEREDIDS
  select @@SPID
    ,ID
    ,0
  from dbo.DESIGNATION with (nolock)
  where (
      DESIGNATION.DESIGNATIONLEVEL1ID = @ID2
      or DESIGNATION.DESIGNATIONLEVEL2ID = @ID2
      or DESIGNATION.DESIGNATIONLEVEL3ID = @ID2
      or DESIGNATION.DESIGNATIONLEVEL4ID = @ID2
      or DESIGNATION.DESIGNATIONLEVEL5ID = @ID2
      );

if @@ROWCOUNT < 1
  set @ABORT = 1

if @CAMPAIGNFILTERMODE2 != 0
begin
  insert into #TMP_DONOR_INFO_FILTEREDIDS (
    SPID
    ,ID
    ,RECORDTYPE
    )
  select distinct @@SPID
    ,T.c.value('(ID)[1]', 'uniqueidentifier')
    ,1
  from @CAMPAIGNSSELECTED2.nodes('/CAMPAIGNSSELECTED/ITEM') T(c);

  if @@ROWCOUNT < 1
    set @ABORT = 1
end

exec dbo.USP_DONOR_INFO_HELPER_GETCONSTITS @DONORSEARCHTEXT = @DONORSEARCHTEXT2 output
  ,@ABORT = @ABORT output;

--if object_id('tempdb..#TMP_DONOR_INFO_DATA_TO_AGGREGATE') is not null

--  drop table #TMP_DONOR_INFO_DATA_TO_AGGREGATE;

if object_id('tempdb..#TMP_DONOR_INFO_DATA_TO_AGGREGATE') is null
begin
  create table #TMP_DONOR_INFO_DATA_TO_AGGREGATE (
    [SPID] int
    ,[TRANSACTIONID] uniqueidentifier
    ,[LINEITEMID] uniqueidentifier
    ,[DATE] datetime
    ,[DESIGNATIONID] uniqueidentifier
    ,[AMOUNT] money
    ,[SOURCELINEITEMID] uniqueidentifier unique (
      [SPID]
      ,[SOURCELINEITEMID]
      ,[LINEITEMID]
      )
    ,[PAYMENTID] uniqueidentifier
    ,[APPLICATION] nvarchar(50) collate DATABASE_DEFAULT
    ,[MEETSCRITERIA] bit
    ,[TYPECODE] smallint 
    );
    CREATE CLUSTERED INDEX IX_#TMP_DONOR_INFO_DATA_TO_AGGREGATE ON #TMP_DONOR_INFO_DATA_TO_AGGREGATE (SPID, TRANSACTIONID, TYPECODE, LINEITEMID, DESIGNATIONID, MEETSCRITERIA)
end
else
begin
  delete
  from #TMP_DONOR_INFO_DATA_TO_AGGREGATE
  where SPID = @@SPID;
end

--if object_id('tempdb..#TMP_DONOR_INFO_COMMITMENTAPPS') is not null

--  drop table #TMP_DONOR_INFO_COMMITMENTAPPS;

if object_id('tempdb..#TMP_DONOR_INFO_COMMITMENTAPPS') is null
begin
  create table #TMP_DONOR_INFO_COMMITMENTAPPS (
    [SPID] int
    ,[TYPECODE] tinyint
    ,[APPLICATIONCODE] tinyint primary key (
      [SPID]
      ,[TYPECODE]
      ,[APPLICATIONCODE]
      )
    );
end
else
begin
  delete
  from #TMP_DONOR_INFO_COMMITMENTAPPS
  where SPID = @@SPID;
end

insert into #TMP_DONOR_INFO_COMMITMENTAPPS
select @@SPID
  ,1
  ,2
where isnull(@TRANSACTIONTYPEOPTIONCODE2, 1) = 1 --pledges


union all

select @@SPID
  ,6
  ,8
where isnull(@TRANSACTIONTYPEOPTIONCODE2, 6) = 6 --grant awards


union all

select @@SPID
  ,4
  ,6
where isnull(@TRANSACTIONTYPEOPTIONCODE2, 4) = 4 --planned gifts


union all

select @@SPID
  ,3
  ,7
where isnull(@TRANSACTIONTYPEOPTIONCODE2, 3) = 3 --matching gift claims


union all

select @@SPID
  ,7
  ,12
where isnull(@TRANSACTIONTYPEOPTIONCODE2, 7) = 7 --auction donation


union all

select @@SPID
  ,15
  ,19
where isnull(@TRANSACTIONTYPEOPTIONCODE2, 15) = 15 --membership installment plan


union all

select @@SPID
  ,8
  ,13
where isnull(@TRANSACTIONTYPEOPTIONCODE2, 8) = 8 --donor challenge claim


--if object_id('tempdb..#TMP_DATA_DONOR_INFO_RESULTS') is not null

--  drop table #TMP_DATA_DONOR_INFO_RESULTS;

if object_id('tempdb..#TMP_DATA_DONOR_INFO_RESULTS') is null
begin
  create table #TMP_DATA_DONOR_INFO_RESULTS (
    [SPID] int
    ,[TRANSACTIONID] uniqueidentifier
    ,[CONSTITUENTID] uniqueidentifier primary key (
      [SPID]
      ,[TRANSACTIONID]
      ,[CONSTITUENTID]
      ,[TYPECODE]
      )
    ,[DATE] datetime
    ,[TRANSACTIONTYPE] nvarchar(50) collate DATABASE_DEFAULT
    ,[AMOUNT] money
    ,[TOTALPAID] money
    ,[BALANCE] money
    ,[NUMPAYMENTS] int
    ,[LASTPAYMENTDATE] datetime
    ,[ORIGTRANSACTIONAMOUNT] money
    ,[WRITEOFFS] money
    ,[DESIGNATION] nvarchar(1000) collate DATABASE_DEFAULT
    ,[CAMPAIGNS] nvarchar(1000) collate DATABASE_DEFAULT
    ,[ORIGTRANSACTIONCURRENCYID] uniqueidentifier
    ,[TRANSACTIONCURRENCYID] uniqueidentifier
    ,[ISSPLIT] bit
    ,[GIVENANONYMOUSLY] bit
    ,[TYPECODE] smallint
    ,[APPLICATION] nvarchar(50) collate DATABASE_DEFAULT
    );
end
else
begin
  delete
  from #TMP_DATA_DONOR_INFO_RESULTS
  where SPID = @@SPID;
end

declare @SQL nvarchar(max) = '';
declare @FILTERCRITERIA nvarchar(1000) = '';

if @DONORSEARCHTEXT2 is not null
  set @FILTERCRITERIA += ' and exists(select 1 from #TMP_DONOR_INFO_FILTEREDIDS FILTER where FILTER.ID=#CONSTITUENTID AND FILTER.RECORDTYPE=2 AND FILTER.SPID=@@SPID)'

if @CAMPAIGNFILTERMODE2 != 0
  set @FILTERCRITERIA += ' and exists(select 1 from dbo.REVENUESPLITCAMPAIGN RSC inner join #TMP_DONOR_INFO_FILTEREDIDS FILTER on RSC.CAMPAIGNID = FILTER.ID AND FILTER.RECORDTYPE=1 AND FILTER.SPID=@@SPID where RSC.REVENUESPLITID = #SPLITID)'

if @REVENUEFILTERID2 is not null
begin
  if @ABORT = 0
    and not exists (
      select 1
      from dbo.UFN_REVENUEFILTER_BYID(@REVENUEFILTERID2)
      )
    set @ABORT = 1
  set @FILTERCRITERIA += ' and exists(select 1 from dbo.UFN_REVENUEFILTER_BYID(@REVENUEFILTERID2) FILTER where FILTER.ID=#SPLITID)'
end

set @FILTERCRITERIA = '(#DATE between @FROM2 and @TO2)' + @FILTERCRITERIA
set nocount off;

if @ABORT = 0
begin
  begin try
    if isnull(@TRANSACTIONTYPEOPTIONCODE2, - 1) not in (
        0
        ,2
        )
    begin
      set @SQL = 
        '
    --payments that meet criteria 

    --WI#461782:Patton Hilliard - incorrect pledge payment totals

    --changed dynamic SQL below to account for adjusted pledges

    WITH ISP_CTE AS (
      SELECT ISP.PAYMENTID, ISP.PLEDGEID, SUM(ISP.AMOUNT) AMOUNT from dbo.INSTALLMENTSPLITPAYMENT ISP GROUP BY ISP.PAYMENTID, ISP.PLEDGEID
    )
    insert into #TMP_DONOR_INFO_DATA_TO_AGGREGATE
    select 
      [SPID]=                @@SPID
      ,[TRANSACTIONID]=        COMMITMENT.ID
      ,[LINEITEMID]=        PAYMENTLINEITEM.ID                            
      ,[DATE]=                PAYMENT.[DATE]                             
      ,[DESIGNATIONID]=        DESIGNATIONFILTER.ID         
      ,[AMOUNT]=            -ISP.AMOUNT
      ,[SOURCELINEITEMID]=    COMMITMENTLINEITEM.ID                          
      ,[PAYMENTID]=            PAYMENT.ID                                 
      ,[APPLICATION]=        null                                       
      ,[MEETSCRITERIA]=        1
      ,[TYPECODE]=            PAYMENT.TYPECODE
    from 
      dbo.FINANCIALTRANSACTION                        PAYMENT                        with (NOLOCK)
      inner join dbo.FINANCIALTRANSACTIONLINEITEM    PAYMENTLINEITEM                with (NOLOCK) on PAYMENT.ID=PAYMENTLINEITEM.FINANCIALTRANSACTIONID and PAYMENT.TYPECODE=0 and PAYMENT.DELETEDON is null
      inner join ISP_CTE                            ISP                            with (NOLOCK) on PAYMENTLINEITEM.ID=ISP.PAYMENTID and PAYMENTLINEITEM.DELETEDON is null
      inner join dbo.REVENUESPLIT_EXT                PAYMENTAPPINFO                with (NOLOCK) on PAYMENTLINEITEM.ID = PAYMENTAPPINFO.ID
      inner join #TMP_DONOR_INFO_FILTEREDIDS        DESIGNATIONFILTER            with (NOLOCK) on PAYMENTAPPINFO.DESIGNATIONID = DESIGNATIONFILTER.ID and DESIGNATIONFILTER.RECORDTYPE=0 and DESIGNATIONFILTER.SPID=@@SPID
      inner join dbo.FINANCIALTRANSACTION            COMMITMENT                    with (NOLOCK) on ISP.PLEDGEID=COMMITMENT.ID and COMMITMENT.DELETEDON is null
      inner join #TMP_DONOR_INFO_COMMITMENTAPPS        APPFILTER                    with (NOLOCK) on COMMITMENT.TYPECODE=APPFILTER.TYPECODE and PAYMENTAPPINFO.APPLICATIONCODE=APPFILTER.APPLICATIONCODE and APPFILTER.SPID=@@SPID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM    COMMITMENTLINEITEM            with (NOLOCK) on COMMITMENT.ID=COMMITMENTLINEITEM.FINANCIALTRANSACTIONID and COMMITMENTLINEITEM.DELETEDON is null 
      inner join dbo.REVENUESPLIT_EXT                COMMITMENTLINEITEMAPPINFO    with (NOLOCK) on COMMITMENTLINEITEM.ID=COMMITMENTLINEITEMAPPINFO.ID and COMMITMENTLINEITEMAPPINFO.DESIGNATIONID = PAYMENTAPPINFO.DESIGNATIONID
    where ' 
        + replace(replace(replace(@FILTERCRITERIA, '#CONSTITUENTID', 'PAYMENT.CONSTITUENTID'), '#SPLITID', 'PAYMENTLINEITEM.ID'), '#DATE', 'PAYMENT.[DATE]')
      set @SQL += 
        ';
    --writeoffs that meet criteria 

    with ISCTE as (
        select ISPLIT.PLEDGEID, ISPLIT.REVENUESPLITID, ISWO.WRITEOFFID, ISPLIT.DESIGNATIONID, SUM(ISWO.TRANSACTIONAMOUNT) AMOUNT from 
          dbo.INSTALLMENTSPLITWRITEOFF ISWO with (NOLOCK) 
          inner join dbo.INSTALLMENTSPLIT ISPLIT with (NOLOCK) on ISWO.INSTALLMENTSPLITID=ISPLIT.ID
          inner join #TMP_DONOR_INFO_FILTEREDIDS DESIGNATIONFILTER with (NOLOCK) on ISPLIT.DESIGNATIONID = DESIGNATIONFILTER.ID and DESIGNATIONFILTER.RECORDTYPE=0 and DESIGNATIONFILTER.SPID=@@SPID     
          group by ISPLIT.PLEDGEID, ISPLIT.REVENUESPLITID, ISWO.WRITEOFFID, ISPLIT.DESIGNATIONID
    )
    insert into #TMP_DONOR_INFO_DATA_TO_AGGREGATE
    select 
      [SPID]=                @@SPID
      ,[TRANSACTIONID]=        COMMITMENT.ID
      ,[LINEITEMID]=        WRITEOFFLINEITEM.ID 
      ,[DATE]=                WRITEOFF.[DATE] --REVISIT question whether this should be getting the breakdown from the installmentsplits... 

      ,[DESIGNATIONID]=        ISWO.DESIGNATIONID
      ,[AMOUNT]=            -ISWO.AMOUNT
      ,[SOURCELINEITEMID]=    COMMITMENTLINEITEM.ID 
      ,[PAYMENTID]=            null
      ,[APPLICATION]=        null
      ,[MEETSCRITERIA]=        1
      ,[TYPECODE]=            WRITEOFF.TYPECODE
    from           
        ISCTE                                        ISWO with (NOLOCK)
        inner join dbo.FINANCIALTRANSACTION            WRITEOFF with (NOLOCK) on ISWO.WRITEOFFID=WRITEOFF.ID and WRITEOFF.DELETEDON is null
        inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTLINEITEM on ISWO.REVENUESPLITID=COMMITMENTLINEITEM.ID and COMMITMENTLINEITEM.DELETEDON is null
        inner join dbo.FINANCIALTRANSACTION            COMMITMENT with (NOLOCK) on ISWO.PLEDGEID =COMMITMENT.ID and COMMITMENT.DELETEDON is null
        inner join #TMP_DONOR_INFO_COMMITMENTAPPS    APPFILTER with (NOLOCK) on COMMITMENT.TYPECODE=APPFILTER.TYPECODE and APPFILTER.SPID=@@SPID     
        inner join dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFLINEITEM with (NOLOCK) on WRITEOFF.ID=WRITEOFFLINEITEM.FINANCIALTRANSACTIONID and WRITEOFFLINEITEM.DELETEDON is null
        inner join dbo.REVENUESPLIT_EXT                WRITEOFFAPPINFO with (NOLOCK) on WRITEOFFLINEITEM.ID=WRITEOFFAPPINFO.ID and ISWO.DESIGNATIONID =WRITEOFFAPPINFO.DESIGNATIONID 
    where WRITEOFF.TYPECODE=20
      and ' 
        + replace(replace(replace(@FILTERCRITERIA, '#CONSTITUENTID', 'COMMITMENT.CONSTITUENTID'), '#SPLITID', 'WRITEOFFLINEITEM.ID'), '#DATE', 'WRITEOFF.[DATE]')
      set @SQL += 
        '
    union all 
    select 
      [SPID]=                @@SPID
      ,[TRANSACTIONID]=        COMMITMENT.ID
      ,[LINEITEMID]=        WRITEOFFLINEITEM.ID 
      ,[DATE]=                WRITEOFF.[DATE] --REVISIT question whether this should be getting the breakdown from the installmentsplits... 

      ,[DESIGNATIONID]=        DESIGNATIONFILTER.ID
      ,[AMOUNT]=            -WRITEOFFLINEITEM.TRANSACTIONAMOUNT
      ,[SOURCELINEITEMID]=    COMMITMENTLINEITEM.ID 
      ,[PAYMENTID]=            null
      ,[APPLICATION]=        null
      ,[MEETSCRITERIA]=        1
      ,[TYPECODE]=            WRITEOFF.TYPECODE
    from           
      dbo.FINANCIALTRANSACTION                        WRITEOFF with (NOLOCK)
      inner join dbo.FINANCIALTRANSACTIONLINEITEM    WRITEOFFLINEITEM with (NOLOCK) on WRITEOFF.ID=WRITEOFFLINEITEM.FINANCIALTRANSACTIONID and WRITEOFF.DELETEDON is null
      inner join dbo.REVENUESPLIT_EXT                WRITEOFFLINEITEMAPPINFO with (NOLOCK) on WRITEOFFLINEITEM.ID = WRITEOFFLINEITEMAPPINFO.ID and WRITEOFFLINEITEM.DELETEDON is null
      inner join #TMP_DONOR_INFO_FILTEREDIDS        DESIGNATIONFILTER with (NOLOCK) on WRITEOFFLINEITEMAPPINFO.DESIGNATIONID = DESIGNATIONFILTER.ID and DESIGNATIONFILTER.RECORDTYPE=0 and DESIGNATIONFILTER.SPID=@@SPID        
      inner join dbo.FINANCIALTRANSACTION            COMMITMENT with (NOLOCK) on WRITEOFF.PARENTID=COMMITMENT.ID and COMMITMENT.DELETEDON is null and COMMITMENT.TYPECODE=7 --Auction donations writeoffs don't follow same pattern

      inner join dbo.FINANCIALTRANSACTIONLINEITEM    COMMITMENTLINEITEM with (NOLOCK) on COMMITMENT.ID = COMMITMENTLINEITEM.FINANCIALTRANSACTIONID and COMMITMENTLINEITEM.DELETEDON is null
      inner join dbo.REVENUESPLIT_EXT                COMMITMENTLINEITEMAPPINFO with (NOLOCK) on COMMITMENTLINEITEM.ID=COMMITMENTLINEITEMAPPINFO.ID and COMMITMENTLINEITEMAPPINFO.DESIGNATIONID=DESIGNATIONFILTER.ID
    where ' 
        + case 
          when isnull(@TRANSACTIONTYPEOPTIONCODE2, 7) = 7
            then '1'
          else '0'
          end + '=1 and WRITEOFF.TYPECODE=20
      and ' + replace(replace(replace(@FILTERCRITERIA, '#CONSTITUENTID', 'COMMITMENT.CONSTITUENTID'), '#SPLITID', 'WRITEOFFLINEITEM.ID'), '#DATE', 'WRITEOFF.[DATE]')
      set @SQL += 
        '
    --Get parents that meet criteria

    union all
    select 
      [SPID]=@@SPID
      ,[TRANSACTIONID]=COMMITMENT.ID
      ,[LINEITEMID]=COMMITMENTLINEITEM.ID 
      ,[DATE]=COMMITMENT.[DATE]
      ,[DESIGNATIONID]=DESIGNATIONFILTER.ID
      ,[AMOUNT]=COMMITMENTLINEITEM.TRANSACTIONAMOUNT
      ,[SOURCELINEITEMID]=null
      ,[PAYMENTID]=null
      ,[APPLICATION]=COMMITMENTLINEITEMAPPINFO.APPLICATION
      ,[MEETSCRITERIA]=1
      ,[TYPECODE]=COMMITMENT.TYPECODE
    from 
      dbo.FINANCIALTRANSACTION                        COMMITMENT with (NOLOCK)
      inner join dbo.FINANCIALTRANSACTIONLINEITEM    COMMITMENTLINEITEM with (NOLOCK) on COMMITMENT.ID=COMMITMENTLINEITEM.FINANCIALTRANSACTIONID and COMMITMENT.DELETEDON is null
      inner join dbo.REVENUESPLIT_EXT                COMMITMENTLINEITEMAPPINFO with (NOLOCK) on COMMITMENTLINEITEM.ID = COMMITMENTLINEITEMAPPINFO.ID and COMMITMENTLINEITEM.DELETEDON is null
      inner join #TMP_DONOR_INFO_FILTEREDIDS        DESIGNATIONFILTER with (NOLOCK) on COMMITMENTLINEITEMAPPINFO.DESIGNATIONID = DESIGNATIONFILTER.ID and DESIGNATIONFILTER.RECORDTYPE=0 and DESIGNATIONFILTER.SPID=@@SPID
      inner join #TMP_DONOR_INFO_COMMITMENTAPPS        APPFILTER with (NOLOCK) on COMMITMENT.TYPECODE=APPFILTER.TYPECODE and APPFILTER.SPID=@@SPID
    where ' 
        + replace(replace(replace(@FILTERCRITERIA, '#CONSTITUENTID', 'COMMITMENT.CONSTITUENTID'), '#SPLITID', 'COMMITMENTLINEITEM.ID'), '#DATE', 'COMMITMENT.[DATE]') + ';'

      if @debugging = 1
      begin
        set @debugMsg = 'BEFORE exec dynamic SQL to get matching temp items ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end;

      exec sp_executeSQL @SQL
        ,N'@FROM2 datetime, @TO2 datetime, @REVENUEFILTERID2 uniqueidentifier'
        ,@FROM2
        ,@TO2
        ,@REVENUEFILTERID2;

      if @debugging = 1
      begin
        set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' exec dynamic SQL to get matching temp items ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end;

      if @debugging = 1
      begin
        set @debugMsg = 'BEFORE parents of the payments and writeoffs added above that DONT meet criteria ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end;

      --now that we have what actually meets the criteria, we have to get the parents and payments that are linked but don't meet criteria

      --parents of the payments and writeoffs added above that DONT meet criteria

      with COM_CTE
      as (
        select T.SOURCELINEITEMID
        from #TMP_DONOR_INFO_DATA_TO_AGGREGATE T
        where T.SPID = @@SPID
        group by T.SOURCELINEITEMID
        )
      merge #TMP_DONOR_INFO_DATA_TO_AGGREGATE as target
      using (
        select [TRANSACTIONID] = COMMITMENT.ID
          ,[LINEITEMID] = COMMITMENTSPLIT.ID
          ,[DATE] = COMMITMENT.[DATE]
          ,[DESIGNATIONID] = isnull(SPLITAPPLICATIONINFO.DESIGNATIONID, '00000000-0000-0000-0000-000000000000') --membership installments may not have designationids, but we need an ID since it is part of the PK

          ,[AMOUNT] = COMMITMENTSPLIT.TRANSACTIONAMOUNT
          ,[APPLICATION] = SPLITAPPLICATIONINFO.APPLICATION
          ,[TYPECODE] = COMMITMENT.TYPECODE
        from COM_CTE T with (readuncommitted)
        inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTSPLIT with (readuncommitted) on T.SOURCELINEITEMID = COMMITMENTSPLIT.ID
          and COMMITMENTSPLIT.DELETEDON is null
        inner join dbo.REVENUESPLIT_EXT SPLITAPPLICATIONINFO with (readuncommitted) on COMMITMENTSPLIT.ID = SPLITAPPLICATIONINFO.ID
        inner join dbo.FINANCIALTRANSACTION COMMITMENT with (readuncommitted) on COMMITMENTSPLIT.FINANCIALTRANSACTIONID = COMMITMENT.ID
          and COMMITMENT.DELETEDON is null
        ) as source
        on target.LINEITEMID = source.[LINEITEMID]
          and target.SPID = @@SPID
      when not matched
        then
          insert (
            [SPID]
            ,[TRANSACTIONID]
            ,[LINEITEMID]
            ,[DATE]
            ,[DESIGNATIONID]
            ,[AMOUNT]
            ,[SOURCELINEITEMID]
            ,[PAYMENTID]
            ,[APPLICATION]
            ,[MEETSCRITERIA]
            ,[TYPECODE]
            )
          values (
            @@SPID
            ,source.[TRANSACTIONID]
            ,source.[LINEITEMID]
            ,source.[DATE]
            ,source.[DESIGNATIONID]
            ,source.[AMOUNT]
            ,null
            ,null
            ,source.[APPLICATION]
            ,0
            ,[TYPECODE]
            );

      if @debugging = 1
      begin
        set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' finished parents of the payments and writeoffs added above that DONT meet criteria insertion ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end;

      if @debugging = 1
      begin
        set @debugMsg = 'BEFORE payments that DONT meet criteria but are needed ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end;

      --payments that DONT meet criteria but are needed

      with ISP_CTE
      as (
        select ISP.PAYMENTID
          ,ISP.PLEDGEID
          ,SUM(ISP.AMOUNT) AMOUNT
        from dbo.INSTALLMENTSPLITPAYMENT ISP
        where exists (
            select 1
            from #TMP_DONOR_INFO_DATA_TO_AGGREGATE T with (readuncommitted)
            where T.TRANSACTIONID = ISP.PLEDGEID
              and T.SPID = @@SPID
            )
        group by ISP.PAYMENTID
          ,ISP.PLEDGEID
        )
      merge #TMP_DONOR_INFO_DATA_TO_AGGREGATE as target
      using (
        select [TRANSACTIONID] = SOURCETRAN.ID
          ,[LINEITEMID] = PAYMENTLINEITEM.ID
          ,[DATE] = PAYMENT.[DATE]
          ,[DESIGNATIONID] = DESIGNATION.ID
          ,[AMOUNT] = - ISP.AMOUNT
          ,[SOURCELINEITEMID] = SOURCELINEITEM.ID
          ,[PAYMENTID] = PAYMENT.ID
          ,[TYPECODE] = PAYMENT.TYPECODE
        from dbo.FINANCIALTRANSACTION PAYMENT with (readuncommitted)
        inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLINEITEM with (readuncommitted) on PAYMENT.ID = PAYMENTLINEITEM.FINANCIALTRANSACTIONID
          and PAYMENT.DELETEDON is null
        inner join ISP_CTE ISP with (readuncommitted) on PAYMENTLINEITEM.ID = ISP.PAYMENTID
          and PAYMENTLINEITEM.DELETEDON is null
        inner join dbo.REVENUESPLIT_EXT SPLITAPPLICATIONINFO with (readuncommitted) on PAYMENTLINEITEM.ID = SPLITAPPLICATIONINFO.ID
        inner join #TMP_DONOR_INFO_FILTEREDIDS DESIGNATION with (readuncommitted) on SPLITAPPLICATIONINFO.DESIGNATIONID = DESIGNATION.ID
          and DESIGNATION.RECORDTYPE = 0
          and DESIGNATION.SPID = @@SPID
        inner join dbo.FINANCIALTRANSACTION SOURCETRAN with (readuncommitted) on ISP.PLEDGEID = SOURCETRAN.ID
          and SOURCETRAN.DELETEDON is null
        inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCELINEITEM with (readuncommitted) on SOURCETRAN.ID = SOURCELINEITEM.FINANCIALTRANSACTIONID
          --and PAYMENTLINEITEM.SOURCELINEITEMID = SOURCELINEITEM.ID --must do this to prevent *magic money* from being applied to multiple commitments via the INSTALLMENTSPLITPAYMENT table

          and SOURCELINEITEM.DELETEDON is null
        --inner join #TMP_DONOR_INFO_DATA_TO_AGGREGATE T with (readuncommitted) on SOURCETRAN.ID=T.TRANSACTIONID and SOURCETRAN.TYPECODE=T.TYPECODE and T.SPID=@@SPID

        inner join dbo.REVENUESPLIT_EXT SOURCELINEITEMAPPINFO with (nolock) on SOURCELINEITEM.ID = SOURCELINEITEMAPPINFO.ID
          and (
            PAYMENTLINEITEM.SOURCELINEITEMID = SOURCELINEITEMAPPINFO.ID
            or (
              SOURCELINEITEMAPPINFO.DESIGNATIONID = DESIGNATION.ID
              and PAYMENTLINEITEM.SOURCELINEITEMID is null
              )
            )
        inner join #TMP_DONOR_INFO_COMMITMENTAPPS APPS with (readuncommitted) on SPLITAPPLICATIONINFO.APPLICATIONCODE = APPS.APPLICATIONCODE
          and SOURCETRAN.TYPECODE = APPS.TYPECODE
          and APPS.SPID = @@SPID
        where PAYMENT.TYPECODE = 0
        ) as source
        on target.LINEITEMID = source.[LINEITEMID]
          and target.SPID = @@SPID
      when not matched
        then
          insert (
            [SPID]
            ,[TRANSACTIONID]
            ,[LINEITEMID]
            ,[DATE]
            ,[DESIGNATIONID]
            ,[AMOUNT]
            ,[SOURCELINEITEMID]
            ,[PAYMENTID]
            ,[APPLICATION]
            ,[MEETSCRITERIA]
            ,[TYPECODE]
            )
          values (
            @@SPID
            ,source.[TRANSACTIONID]
            ,source.[LINEITEMID]
            ,source.[DATE]
            ,source.[DESIGNATIONID]
            ,source.[AMOUNT]
            ,source.[SOURCELINEITEMID]
            ,source.[PAYMENTID]
            ,null
            ,0
            ,source.[TYPECODE]
            );

      if @debugging = 1
      begin
        set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' finished payments that DONT meet criteria but are needed insertion ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end;

      if @debugging = 1
      begin
        set @debugMsg = 'BEFORE writeoffs that DONT meet criteria but are needed ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end;

      --writeoffs that DONT meet criteria but are needed

      with ISCTE
      as (
        select ISPLIT.PLEDGEID
          ,ISPLIT.REVENUESPLITID
          ,ISWO.WRITEOFFID
          ,ISPLIT.DESIGNATIONID
          ,SUM(ISWO.TRANSACTIONAMOUNT) AMOUNT
        from dbo.INSTALLMENTSPLITWRITEOFF ISWO with (readuncommitted)
        inner join dbo.INSTALLMENTSPLIT ISPLIT with (readuncommitted) on ISWO.INSTALLMENTSPLITID = ISPLIT.ID
        inner join #TMP_DONOR_INFO_FILTEREDIDS DESIGNATION with (readuncommitted) on ISPLIT.DESIGNATIONID = DESIGNATION.ID
          and DESIGNATION.RECORDTYPE = 0
          and DESIGNATION.SPID = @@SPID
        where exists (
            select 1
            from #TMP_DONOR_INFO_DATA_TO_AGGREGATE T with (readuncommitted)
            where T.TRANSACTIONID = ISPLIT.PLEDGEID
              and T.SPID = @@SPID
            )
        group by ISPLIT.PLEDGEID
          ,ISPLIT.REVENUESPLITID
          ,ISWO.WRITEOFFID
          ,ISPLIT.DESIGNATIONID
        )
      merge #TMP_DONOR_INFO_DATA_TO_AGGREGATE as target
      using (
        select [TRANSACTIONID] = COMMITMENT.ID
          ,[LINEITEMID] = WRITEOFFLINEITEM.ID
          ,[DATE] = WRITEOFF.[DATE] --REVISIT question whether this should be getting the breakdown from the installmentsplits... 

          ,[DESIGNATIONID] = ISCTE.DESIGNATIONID
          ,[AMOUNT] = - ISCTE.AMOUNT
          ,[SOURCELINEITEMID] = COMMITMENTLINEITEM.ID
          ,[PAYMENTID] = null
          ,[APPLICATION] = null
          ,[MEETSCRITERIA] = 1
          ,[TYPECODE] = WRITEOFF.TYPECODE
        from ISCTE with (readuncommitted)
        inner join dbo.FINANCIALTRANSACTION WRITEOFF with (readuncommitted) on ISCTE.WRITEOFFID = WRITEOFF.ID
          and WRITEOFF.DELETEDON is null
        inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTLINEITEM on ISCTE.REVENUESPLITID = COMMITMENTLINEITEM.ID
          and COMMITMENTLINEITEM.DELETEDON is null
        inner join dbo.FINANCIALTRANSACTION COMMITMENT on ISCTE.PLEDGEID = COMMITMENT.ID
          and COMMITMENT.DELETEDON is null
        inner join dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFLINEITEM on WRITEOFF.ID = WRITEOFFLINEITEM.FINANCIALTRANSACTIONID
          and WRITEOFFLINEITEM.DELETEDON is null
        inner join dbo.REVENUESPLIT_EXT WRITEOFFAPPINFO on WRITEOFFLINEITEM.ID = WRITEOFFAPPINFO.ID
          and ISCTE.DESIGNATIONID = WRITEOFFAPPINFO.DESIGNATIONID
        inner join #TMP_DONOR_INFO_COMMITMENTAPPS APPS with (readuncommitted) on COMMITMENT.TYPECODE = APPS.TYPECODE
          and APPS.SPID = @@SPID
        where WRITEOFF.TYPECODE = 20

        union all

        select [TRANSACTIONID] = COMMITMENT.ID
          ,[LINEITEMID] = WRITEOFFLINEITEM.ID
          ,[DATE] = WRITEOFF.[DATE] --REVISIT question whether this should be getting the breakdown from the installmentsplits... 

          ,[DESIGNATIONID] = DESIGNATION.ID
          ,[AMOUNT] = - WRITEOFFLINEITEM.TRANSACTIONAMOUNT
          ,[SOURCELINEITEMID] = COMMITMENTLINEITEM.ID
          ,[PAYMENTID] = null
          ,[APPLICATION] = null
          ,[MEETSCRITERIA] = 1
          ,[TYPECODE] = WRITEOFF.TYPECODE
        from dbo.FINANCIALTRANSACTION WRITEOFF with (readuncommitted)
        inner join dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFLINEITEM with (readuncommitted) on WRITEOFF.ID = WRITEOFFLINEITEM.FINANCIALTRANSACTIONID
          and WRITEOFF.DELETEDON is null
        inner join dbo.REVENUESPLIT_EXT WRITEOFFAPPINFO with (readuncommitted) on WRITEOFFLINEITEM.ID = WRITEOFFAPPINFO.ID
          and WRITEOFFLINEITEM.DELETEDON is null
        inner join #TMP_DONOR_INFO_FILTEREDIDS DESIGNATION with (readuncommitted) on WRITEOFFAPPINFO.DESIGNATIONID = DESIGNATION.ID
          and DESIGNATION.RECORDTYPE = 0
          and DESIGNATION.SPID = @@SPID
        inner join dbo.FINANCIALTRANSACTION COMMITMENT with (readuncommitted) on WRITEOFF.PARENTID = COMMITMENT.ID
          and COMMITMENT.DELETEDON is null
          and COMMITMENT.TYPECODE = 7 --auction donations break pattern

        inner join dbo.FINANCIALTRANSACTIONLINEITEM COMMITMENTLINEITEM with (readuncommitted) on COMMITMENT.ID = COMMITMENTLINEITEM.FINANCIALTRANSACTIONID
          and COMMITMENTLINEITEM.DELETEDON is null
        inner join dbo.REVENUESPLIT_EXT COMMITMENTSPLITAPPINFO with (readuncommitted) on COMMITMENTLINEITEM.ID = COMMITMENTSPLITAPPINFO.ID
          and COMMITMENTSPLITAPPINFO.DESIGNATIONID = DESIGNATION.ID
        where isnull(@TRANSACTIONTYPEOPTIONCODE2, 7) = 7
          and WRITEOFF.TYPECODE = 20
          and exists (
            select 1
            from #TMP_DONOR_INFO_DATA_TO_AGGREGATE T with (readuncommitted)
            where T.TRANSACTIONID = COMMITMENT.ID
              and T.SPID = @@SPID
            )
        ) as source
        on target.LINEITEMID = source.[LINEITEMID]
          and target.SPID = @@SPID
      when not matched
        then
          insert (
            [SPID]
            ,[TRANSACTIONID]
            ,[LINEITEMID]
            ,[DATE]
            ,[DESIGNATIONID]
            ,[AMOUNT]
            ,[SOURCELINEITEMID]
            ,[PAYMENTID]
            ,[APPLICATION]
            ,[MEETSCRITERIA]
            ,[TYPECODE]
            )
          values (
            @@SPID
            ,source.[TRANSACTIONID]
            ,source.[LINEITEMID]
            ,source.[DATE]
            ,source.[DESIGNATIONID]
            ,source.[AMOUNT]
            ,source.[SOURCELINEITEMID]
            ,null
            ,null
            ,1
            ,source.[TYPECODE]
            );

      if @debugging = 1
      begin
        set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' finished writeoffs that DONT meet criteria but are needed insertion ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end

      if @debugging = 1
      begin
        set @debugMsg = 'BEFORE Adding Results ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end

      exec dbo.[USP_DONOR_INFO_HELPER_ADDTORESULTS] ''
        ,@FROM2
        ,@TO2
        ,@REVENUEFILTERID2;

      if @debugging = 1
      begin
        set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' Adding Results insertion ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end
    end

    --recurring gift

    if isnull(@TRANSACTIONTYPEOPTIONCODE2, 2) = 2
    begin
      --Get Recurring Gift Payments    

      set @SQL = 
        'insert into #TMP_DONOR_INFO_DATA_TO_AGGREGATE
    select 
      [SPID]=                @@SPID
      ,[TRANSACTIONID]=        RGA.SOURCEREVENUEID 
      ,[LINEITEMID]=        PAYMENTLINEITEM.ID 
      ,[DATE]=                PAYMENT.[DATE]
      ,[DESIGNATIONID]=        DESIGNATION.ID 
      ,[AMOUNT]=            -RGA.AMOUNT 
      ,[SOURCELINEITEMID]=    null 
      ,[PAYMENTID]=            PAYMENTLINEITEM.FINANCIALTRANSACTIONID 
      ,[APPLICATION]=        ''Donation'' 
      ,[MEETSCRITERIA]=        1 
      ,[TYPECODE]=            PAYMENT.TYPECODE
    from dbo.RECURRINGGIFTACTIVITY RGA 
      inner join dbo.REVENUESPLIT_EXT                PAYMENTAPPLICATION with (NOLOCK) on PAYMENTAPPLICATION.ID = RGA.PAYMENTREVENUEID and PAYMENTAPPLICATION.APPLICATIONCODE in(3,5) 
      inner join dbo.FINANCIALTRANSACTIONLINEITEM    PAYMENTLINEITEM with (NOLOCK) on PAYMENTLINEITEM.ID = PAYMENTAPPLICATION.ID and PAYMENTLINEITEM.DELETEDON is null
      inner join dbo.FINANCIALTRANSACTION            PAYMENT with (NOLOCK) on PAYMENT.ID = PAYMENTLINEITEM.FINANCIALTRANSACTIONID and PAYMENT.DELETEDON is null and PAYMENT.TYPECODE = 0 
      inner join #TMP_DONOR_INFO_FILTEREDIDS        DESIGNATION with (NOLOCK) on PAYMENTAPPLICATION.DESIGNATIONID = DESIGNATION.ID and DESIGNATION.RECORDTYPE=0 and DESIGNATION.SPID=@@SPID
    where ' 
        + replace(replace(replace(@FILTERCRITERIA, '#CONSTITUENTID', 'PAYMENT.CONSTITUENTID'), '#SPLITID', 'PAYMENTLINEITEM.ID'), '#DATE', 'PAYMENT.[DATE]')

      if @debugging = 1
      begin
        set @debugMsg = 'BEFORE get recurring gifts ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end

      exec dbo.[USP_DONOR_INFO_HELPER_ADDTORESULTS] @SQL
        ,@FROM2
        ,@TO2
        ,@REVENUEFILTERID2;

      if @debugging = 1
      begin
        set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' get recurring gifts ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end
    end

    if isnull(@TRANSACTIONTYPEOPTIONCODE2, 0) = 0
    begin
      -- Get EVERYTHING ELSE

      set @SQL = 
        'insert into #TMP_DONOR_INFO_DATA_TO_AGGREGATE
    select 
      [SPID]=                @@SPID
      ,[TRANSACTIONID]=        PAYMENT.ID
      ,[LINEITEMID]=        PAYMENTLINEITEM.ID 
      ,[DATE]=                PAYMENT.[DATE]
      ,[DESIGNATIONID]=        DESIGNATION.ID 
      ,[AMOUNT]=            PAYMENTLINEITEM.TRANSACTIONAMOUNT
      ,[SOURCELINEITEMID]=    null 
      ,[PAYMENTID]=            null
      ,[APPLICATION]=        PAYMENTAPPLICATION.APPLICATION
      ,[MEETSCRITERIA]=        1 
      ,[TYPECODE]=            PAYMENT.TYPECODE
    from 
      dbo.FINANCIALTRANSACTION PAYMENT with (NOLOCK)
      inner join dbo.FINANCIALTRANSACTIONLINEITEM    PAYMENTLINEITEM with (NOLOCK) on PAYMENT.ID=PAYMENTLINEITEM.FINANCIALTRANSACTIONID and PAYMENT.DELETEDON is null and PAYMENTLINEITEM.DELETEDON is null and PAYMENT.TYPECODE=0 
      inner join dbo.REVENUESPLIT_EXT                PAYMENTAPPLICATION with (NOLOCK) on PAYMENTLINEITEM.ID = PAYMENTAPPLICATION.ID 
      inner join #TMP_DONOR_INFO_FILTEREDIDS        DESIGNATION with (NOLOCK) on PAYMENTAPPLICATION.DESIGNATIONID = DESIGNATION.ID and DESIGNATION.RECORDTYPE=0 and DESIGNATION.SPID=@@SPID
    where (PAYMENTAPPLICATION.APPLICATIONCODE in (0,1,4,9,10,11,12,15,16,18) or (PAYMENTAPPLICATION.APPLICATIONCODE=7 and not exists(select 1 from dbo.INSTALLMENTSPLITPAYMENT where PAYMENTID=PAYMENTLINEITEM.ID))) and ' 
        + replace(replace(replace(@FILTERCRITERIA, '#CONSTITUENTID', 'PAYMENT.CONSTITUENTID'), '#SPLITID', 'PAYMENTLINEITEM.ID'), '#DATE', 'PAYMENT.[DATE]')

      if @debugging = 1
      begin
        set @debugMsg = 'BEFORE get payments ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end

      exec dbo.[USP_DONOR_INFO_HELPER_ADDTORESULTS] @SQL
        ,@FROM2
        ,@TO2
        ,@REVENUEFILTERID2;

      if @debugging = 1
      begin
        set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' get payments ';
        set @debugStartTime = getdate();

        raiserror (
            @debugMsg
            ,0
            ,1
            )
        with nowait;
      end
    end
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;

    return
  end catch;
end

if @debugging = 1
begin
  set @debugMsg = 'BEFORE generating results ';
  set @debugStartTime = getdate();

  raiserror (
      @debugMsg
      ,0
      ,1
      )
  with nowait;
end;

/** NOTE: What the following update statements do, could have been done when originally populating
    the RESULTS temp table, or could be done in the subsequent output selection, BUT they are split out 
    here into separate update statements because it leads to better query plans and performance **/
update RESULTS
set ORIGTRANSACTIONAMOUNT = PLOA.TRANSACTIONAMOUNT
  ,ORIGTRANSACTIONCURRENCYID = PLOA.TRANSACTIONCURRENCYID
from #TMP_DATA_DONOR_INFO_RESULTS RESULTS
inner join dbo.PLEDGEORIGINALAMOUNT PLOA on RESULTS.TRANSACTIONID = PLOA.ID
where RESULTS.SPID = @@SPID
  and RESULTS.TYPECODE = 1;

update RESULTS
set ORIGTRANSACTIONAMOUNT = PYOA.TRANSACTIONAMOUNT
  ,ORIGTRANSACTIONCURRENCYID = PYOA.TRANSACTIONCURRENCYID
from #TMP_DATA_DONOR_INFO_RESULTS RESULTS
inner join dbo.PAYMENTORIGINALAMOUNT PYOA on RESULTS.TRANSACTIONID = PYOA.ID
where RESULTS.SPID = @@SPID
  and RESULTS.TYPECODE = 0;

if @debugging = 1
begin
  set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' updated original transaction amounts ';
  set @debugStartTime = getdate();

  raiserror (
      @debugMsg
      ,0
      ,1
      )
  with nowait;
end;

--whether this is split is based on the entire financial transaction, not just what we are extracting

with ISSPLIT_CTE
as (
  select [TRANSACTIONID] = FTL.FINANCIALTRANSACTIONID
    ,[ISSPLIT] = cast(case 
        when count(distinct SPLIT.DESIGNATIONID) > 1
          then 1
        else 0
        end as bit)
  from dbo.FINANCIALTRANSACTIONLINEITEM FTL
  inner join dbo.REVENUESPLIT_EXT SPLIT on FTL.ID = SPLIT.ID
  group by FTL.FINANCIALTRANSACTIONID
  )
update RESULTS
set ISSPLIT = T.ISSPLIT
from #TMP_DATA_DONOR_INFO_RESULTS RESULTS
inner join ISSPLIT_CTE T on RESULTS.TRANSACTIONID = T.TRANSACTIONID
where RESULTS.SPID = @@SPID
  and RESULTS.ISSPLIT = 0

if @debugging = 1
begin
  set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' updated issplit ';
  set @debugStartTime = getdate();

  raiserror (
      @debugMsg
      ,0
      ,1
      )
  with nowait;
end;

select [TRANSACTIONID] = RESULTS.[TRANSACTIONID]
  ,[CONSTITUENTID] = [CONSTITUENTID]
  ,[DONOR] = C.FORMATTEDNAME + ' - Lookup ID: ' + C.LOOKUPID
  ,[DATE] = RESULTS.[DATE]
  ,[TRANSACTIONTYPE] = RESULTS.[TRANSACTIONTYPE]
  ,[AMOUNT] = RESULTS.[AMOUNT]
  ,[TOTALPAID] = RESULTS.[TOTALPAID]
  ,[WRITEOFFS] = RESULTS.[WRITEOFFS]
  ,[BALANCE] = RESULTS.[BALANCE]
  ,[NUMPAYMENTS] = RESULTS.[NUMPAYMENTS]
  ,[LASTPAYMENTDATE] = RESULTS.[LASTPAYMENTDATE]
  ,[ORIGTRANSACTIONAMOUNT] = RESULTS.[ORIGTRANSACTIONAMOUNT]
  ,[DESIGNATION] = RESULTS.[DESIGNATION]
  ,[CAMPAIGNS] = RESULTS.[CAMPAIGNS]
  ,[IMAGEKEY] = case 
    when RESULTS.ISSPLIT = 1
      then case 
          when RESULTS.GIVENANONYMOUSLY = 1
            then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.split_gift_anonymous_16.png'
          else 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.split_gift_16.png'
          end
    else case 
        when RESULTS.GIVENANONYMOUSLY = 1
          then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.gift_anonymous_16.png'
        else null
        end
    end
  ,[ORIGTRANSACTIONCURRENCYID] = RESULTS.[ORIGTRANSACTIONCURRENCYID]
  ,[DESIGNATIONLEVELID] = @ID2
  ,null
  ,[ISORIGINALFUNDING] = isnull(DLR.ISORIGINALFUNDING, 0)
  ,[ISBENEFACTOR] = isnull(C.ISBENEFACTOR, 0)
  ,[TYPECODE] = RESULTS.TYPECODE
  ,[DETAILID] = cast(RESULTS.TRANSACTIONID as char(36)) + ';' + cast(@ID2 as char(36))
  ,[VIEWFORMID] = case RESULTS.TYPECODE
    when 0 --PAYMENT

      then 'b1bad889-4460-45aa-a7e5-4f1f6eeec9f3'
    when 1 --PLEDGE

      then '52cb69a9-5800-4b78-abb6-d6bf99a92f30'
    when 6 --GRANT AWARD

      then '52cb69a9-5800-4b78-abb6-d6bf99a92f30'
    when 15 --MEMBERSHIP INSTALLMENT PLAN

      then '52cb69a9-5800-4b78-abb6-d6bf99a92f30'
    when 2 --RECURRING GIFT

      then 'd0032bf4-4713-47db-a3aa-744979ae9d5b'
    when 3 --MATCHING GIFT CLAIM

      then '156d44e6-a256-4574-9829-739058268beb'
    when 4 --PLANNED GIFT 

      then '156d44e6-a256-4574-9829-739058268beb'
    when 7 --AUCTION DONATION

      then '6e2bf7fd-5641-43ba-a1bb-b1ffa8384d25'
    when 8 --DONOR CHALLENGE CLAIM

      then '156d44e6-a256-4574-9829-739058268beb'
    else '74f1a311-2ed9-4c0b-b666-9bf318748d9b'
    end
  ,[APPLICATION] = RESULTS.[APPLICATION]
  ,[TRANSACTIONCURRENCYID] = RESULTS.[TRANSACTIONCURRENCYID]
from #TMP_DATA_DONOR_INFO_RESULTS RESULTS
inner join (
  select case 
      when ISORGANIZATION = 1
        then case KEYNAMEPREFIX
            when ''
              then KEYNAME
            else KEYNAME + ', ' + KEYNAMEPREFIX
            end
      else RTRIM(RTRIM(RTRIM(KEYNAME + ' ') + ', ' + FIRSTNAME) + ' ' + dbo.UFN_MAKEINITIALS(MIDDLENAME))
      end FORMATTEDNAME
    ,(CAST(isnull(KEYNAME, '') as nchar(100)) + CAST(isnull(KEYNAMEPREFIX, '') as nchar(50)) + CAST(isnull(FIRSTNAME, '') as nchar(50)) + CAST(isnull(MIDDLENAME, '') as nchar(50)) + CAST(isnull(LOOKUPID, '') as nchar(100))) NAMEFORSORT
    ,CONSTITUENT.ID
    ,CONSTITUENT.LOOKUPID
    ,DLD.ISBENEFACTOR
  from dbo.CONSTITUENT
  left join dbo.DESIGNATIONLEVELDONORINFORMATION DLD on DLD.CONSTITUENTID = CONSTITUENT.ID
    and DLD.DESIGNATIONLEVELID = @ID2
  ) C on RESULTS.CONSTITUENTID = C.ID
left join dbo.DESIGNATIONLEVELREVENUEINFORMATION DLR on RESULTS.TRANSACTIONID = DLR.REVENUEID
  and DLR.DESIGNATIONLEVELID = @ID2
where RESULTS.SPID = @@SPID
order by RESULTS.[DATE] desc
  ,RESULTS.AMOUNT desc
  ,C.NAMEFORSORT

if @debugging = 1
begin
  set @debugMsg = CONVERT(varchar, (getdate() - @debugStartTime), 108) + ' generating results ';
  set @debugStartTime = getdate();

  raiserror (
      @debugMsg
      ,0
      ,1
      )
  with nowait;
end;