USP_REPORT_APPEALPERFORMANCE_BYMAILING_WITHIDSET

/*
Generated by Blackbaud Application Framework
Date: 11/11/2014 4:27:01 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
*/
CREATE procedure dbo.[USP_REPORT_APPEALPERFORMANCE_BYMAILING_WITHIDSET] (@IDSETID uniqueidentifier = null, @STARTDATE datetime, @ENDDATE datetime, @CATEGORYID uniqueidentifier, @BUSINESSUNITID uniqueidentifier, @REPORTCODEID uniqueidentifier, @SITEID uniqueidentifier, @REPORTUSERID nvarchar(128), @APPEALID uniqueidentifier, @CURRENCYCODE tinyint, @ALTREPORTUSERID nvarchar(128))
with execute as owner
as
  set nocount on;

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_REPORT_APPEALPERFORMANCE_BYMAILING] @STARTDATE, @ENDDATE, @CATEGORYID, @BUSINESSUNITID, @REPORTCODEID, @SITEID, @REPORTUSERID, @APPEALID, @CURRENCYCODE, @ALTREPORTUSERID;
    end
  else
    begin
      if not exists(select [ID] from dbo.[IDSETREGISTER] where [ID] = @IDSETID)
        raiserror('ID set does not exist in the database.', 15, 1);

      declare @DBOBJECTNAME nvarchar(max);
      declare @DBOBJECTTYPE smallint;
      declare @SQLTOEXEC nvarchar(max);

      select
        @DBOBJECTNAME = [DBOBJECTNAME],
        @DBOBJECTTYPE = [OBJECTTYPE]
      from dbo.[IDSETREGISTER]
      where [ID] = @IDSETID;

      if left(@DBOBJECTNAME, 1) <> '['
        set @DBOBJECTNAME = '[' + @DBOBJECTNAME + ']';

      if @DBOBJECTTYPE = 1
        begin
          set @DBOBJECTNAME = @DBOBJECTNAME + '(';
          set @DBOBJECTNAME = @DBOBJECTNAME + ')';
        end
      else
        begin
          if @DBOBJECTTYPE = 2
            set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @IDSETID) + ''')';
        end

      set @SQLTOEXEC = cast(N'set nocount on;
  set transaction isolation level read uncommitted;

  declare @CURRENTAPPUSERID uniqueidentifier = dbo.[UFN_APPUSER_GETREPORTAPPUSERID](@REPORTUSERID, @ALTREPORTUSERID);
  declare @ISSYSADMIN as bit = dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID);
  declare @SELECTEDCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;

  if @STARTDATE is not null
    set @STARTDATE = dbo.[UFN_DATE_GETEARLIESTTIME](@STARTDATE);
  if @ENDDATE is not null
    set @ENDDATE = dbo.[UFN_DATE_GETLATESTTIME](@ENDDATE);

  if @CURRENCYCODE = 0
    set @SELECTEDCURRENCYID = null;
  else if @CURRENCYCODE = 2
    set @SELECTEDCURRENCYID = dbo.[UFN_APPUSER_GETBASECURRENCY](@CURRENTAPPUSERID);
  else
    begin
      set @CURRENCYCODE = 1;
      set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
    end

  select
    @DECIMALDIGITS = [DECIMALDIGITS],
    @ROUNDINGTYPECODE = [ROUNDINGTYPECODE]
  from dbo.[CURRENCY]
  where [ID] = @SELECTEDCURRENCYID;



  declare @APPEALS table (
    [ID] uniqueidentifier not null primary key,
    [NAME] nvarchar(100) not null,
    [GOAL] money,
    [CURRENCYID] uniqueidentifier,
    [CURRENCYISO] nvarchar(3),
    [CURRENCYDECIMALDIGITS] tinyint,
    [CURRENCYSYMBOL] nvarchar(5),
    [CURRENCYSYMBOLDISPLAYSETTINGCODE] tinyint
  );

  --All appeals that meet the criteria

  insert into @APPEALS ([ID], [NAME], [GOAL], [CURRENCYID], [CURRENCYISO], [CURRENCYDECIMALDIGITS], [CURRENCYSYMBOL], [CURRENCYSYMBOLDISPLAYSETTINGCODE])
    select distinct
      [APPEAL].[ID],
      [APPEAL].[NAME],
      [APPEAL].[GOALINCURRENCY],
      [CURRENCYPROPERTIES].[ID] as [CURRENCYID],
      [CURRENCYPROPERTIES].[ISO4217] as [CURRENCYISO],
      [CURRENCYPROPERTIES].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
      [CURRENCYPROPERTIES].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],
      [CURRENCYPROPERTIES].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
    from dbo.[UFN_APPEAL_GETGOALINCURRENCY_BULK2](@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, 1) as [APPEAL]
     inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_29d791dc_9d27_48cf_a168_b8c1f35aaf6d] on [APPEAL].[ID] = [IDSET_29d791dc_9d27_48cf_a168_b8c1f35aaf6d].[ID] 
    left join dbo.[APPEALBUSINESSUNIT] on [APPEALBUSINESSUNIT].[APPEALID] = [APPEAL].[ID]
    outer apply dbo.[UFN_CURRENCY_GETPROPERTIES](isnull(@SELECTEDCURRENCYID, [APPEAL].[BASECURRENCYID])) as [CURRENCYPROPERTIES]
    where (@REPORTCODEID is null or [APPEAL].[APPEALREPORT1CODEID] = @REPORTCODEID)
    and (@CATEGORYID is null or [APPEAL].[APPEALCATEGORYCODEID] = @CATEGORYID)
    and (@BUSINESSUNITID is null or [APPEALBUSINESSUNIT].[BUSINESSUNITCODEID] = @BUSINESSUNITID)
    and (@SITEID is null or [APPEAL].[SITEID] = @SITEID)
    and (@ISSYSADMIN = 1 or dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [APPEAL].[SITEID]) = 1)
    and (@APPEALID is null or [APPEAL].[ID] = @APPEALID);




  if object_id(''tempdb..#APPEALPLEDGESPAID'') is not null
    drop table #APPEALPLEDGESPAID;

  create table #APPEALPLEDGESPAID (
    [APPEALID] uniqueidentifier not null,
    [MAILINGID] uniqueidentifier,
    [PLEDGEPAIDAMOUNT] money
  );

  --Paid pledges that match the appeal and meets the date criteria

  insert into #APPEALPLEDGESPAID ([APPEALID], [MAILINGID], [PLEDGEPAIDAMOUNT])
    select
      [REVENUE_EXT].[APPEALID],
      [REVENUE_EXT].[MAILINGID],
      isnull(sum([REVENUESPLITAMOUNTS].[REVENUESPLITAMOUNTINCURRENCY]), 0)
    from dbo.[INSTALLMENTSPLITPAYMENT]
    inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [INSTALLMENTSPLITPAYMENT].[PAYMENTID]
    inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID]
    inner join @APPEALS as [APPEALS] on [APPEALS].[ID] = [REVENUE_EXT].[APPEALID]
    inner join dbo.[UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK](@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) as [REVENUESPLITAMOUNTS] on [REVENUESPLITAMOUNTS].[REVENUESPLITID] = [INSTALLMENTSPLITPAYMENT].[PAYMENTID]
    where 
      (@STARTDATE is null or [REVENUESPLITAMOUNTS].[DATE] >= @STARTDATE)
      and (@ENDDATE is null or [REVENUESPLITAMOUNTS].[DATE] <= @ENDDATE)
    group by [REVENUE_EXT].[APPEALID], [REVENUE_EXT].[MAILINGID];

  --Check if we are a SQL Server Enterprise server before creating the proper index...

  if serverproperty(''engineedition'') = 3  --Enterprise

    create nonclustered index [IX_APPEALPLEDGESPAID_APPEALID_MAILINGID] on #APPEALPLEDGESPAID ([APPEALID], [MAILINGID]) include ([PLEDGEPAIDAMOUNT]) with (DATA_COMPRESSION=PAGE);
  else
    create nonclustered index [IX_APPEALPLEDGESPAID_APPEALID_MAILINGID] on #APPEALPLEDGESPAID ([APPEALID], [MAILINGID]) include ([PLEDGEPAIDAMOUNT]);




  if object_id(''tempdb..#APPEALREVENUESPLIT'') is not null
    drop table #APPEALREVENUESPLIT;

  create table #APPEALREVENUESPLIT (
    [APPEALID] uniqueidentifier not null,
    [REVENUEID] uniqueidentifier not null,
    [CONSTITUENTID] uniqueidentifier not null,
    [ISPLEDGE] bit not null,
    [REVENUESPLITAMOUNTINCURRENCY] money,
    [WRITEOFFSPLITAMOUNTINCURRENCY] money
  );

  --Revenue that matches the appeal and meets the date criteria

  insert into #APPEALREVENUESPLIT ([APPEALID], [REVENUEID], [CONSTITUENTID], [ISPLEDGE], [REVENUESPLITAMOUNTINCURRENCY], [WRITEOFFSPLITAMOUNTINCURRENCY])
    select
      [APPEALS].[ID] as [APPEALID],
      [REVENUESPLITAMOUNTS].[REVENUEID],
      [FINANCIALTRANSACTION].[CONSTITUENTID],
      (case when [TRANSACTIONTYPECODE] in (1,3,4,8) then 1 else 0 end) as [ISPLEDGE],
      [REVENUESPLITAMOUNTS].[REVENUESPLITAMOUNTINCURRENCY],
      [REVENUESPLITAMOUNTS].[WRITEOFFSPLITAMOUNTINCURRENCY]
    from @APPEALS as [APPEALS]
    inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[APPEALID] = [APPEALS].[ID]
    inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [REVENUE_EXT].[ID]
    inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [FINANCIALTRANSACTION].[ID]
    inner join dbo.[REVENUESPLIT_EXT] on [REVENUESPLIT_EXT].[ID] = [FINANCIALTRANSACTIONLINEITEM].[ID]
    inner join dbo.[UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK](@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) as [REVENUESPLITAMOUNTS] on [REVENUESPLITAMOUNTS].[REVENUESPLITID] = [REVENUESPLIT_EXT].[ID]
    where ( 
      --Revenue (excluding pledge payments) 

      ([FINANCIALTRANSACTION].[TYPECODE] = 0 and [REVENUESPLIT_EXT].[APPLICATIONCODE] not in (2,3)) 
      or 
      --Pledges 

      [FINANCIALTRANSACTION].[TYPECODE] in (1,3,4,8) 
    ) 
    and (@STARTDATE is null or [FINANCIALTRANSACTION].[DATE] >= @STARTDATE) 
    and (@ENDDATE is null or [FINANCIALTRANSACTION].[DATE] <= @ENDDATE); 




  with [APPEALREVENUE_CTE] ([APPEALID], [REVENUEID], [CONSTITUENTID], [RECEIVEDAMOUNT], [PLEDGEDAMOUNT], [PLEDGEWRITEOFFAMOUNT]) as
  (
    select
      [APPEALID],
      [REVENUEID],
      [CONSTITUENTID],
      (case when [ISPLEDGE] = 1 then 0 else isnull(sum([REVENUESPLITAMOUNTINCURRENCY]), 0) end) as [RECEIVEDAMOUNT],
      (case when [ISPLEDGE] = 1 then isnull(sum([REVENUESPLITAMOUNTINCURRENCY]), 0) else 0 end) as [PLEDGEDAMOUNT],
      (case when [ISPLEDGE] = 1 then isnull(sum([WRITEOFFSPLITAMOUNTINCURRENCY]), 0) else 0 end) as [PLEDGEWRITEOFFAMOUNT]
    from #APPEALREVENUESPLIT as [ARS]
    group by [APPEALID], [REVENUEID], [CONSTITUENTID], [ISPLEDGE]
  ),
  [APPEALMAILINGREVENUE_CTE] ([APPEALID], [MAILINGID], [REVENUEID], [CONSTITUENTID], [RECEIVEDAMOUNT], [PLEDGEDAMOUNT], [PLEDGEWRITEOFFAMOUNT]) as
  (
    --Segment revenue: Matches to a segment within a mailing

    select
      [APPEALREVENUE_CTE].[APPEALID],
      [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
      [APPEALREVENUE_CTE].[REVENUEID],
      [APPEALREVENUE_CTE].[CONSTITUENTID],
      [APPEALREVENUE_CTE].[RECEIVEDAMOUNT],
      [APPEALREVENUE_CTE].[PLEDGEDAMOUNT],
      [APPEALREVENUE_CTE].[PLEDGEWRITEOFFAMOUNT]
    from [APPEALREVENUE_CTE]
    inner join dbo.[REVENUESEGMENT] on [REVENUESEGMENT].[REVENUEID] = [APPEALREVENUE_CTE].[REVENUEID]
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [REVENUESEGMENT].[SEGMENTID]

    union all

    --Indirect revenue: Matches to the mailing only (no segment)

    select
      [APPEALREVENUE_CTE].[APPEALID],
      [REVENUE_EXT].[MAILINGID],
      [APPEALREVENUE_CTE].[REVENUEID],
      [APPEALREVENUE_CTE].[CONSTITUENTID],
      [APPEALREVENUE_CTE].[RECEIVEDAMOUNT],
      [APPEALREVENUE_CTE].[PLEDGEDAMOUNT],
      [APPEALREVENUE_CTE].[PLEDGEWRITEOFFAMOUNT]
    from [APPEALREVENUE_CTE]
    inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [APPEALREVENUE_CTE].[REVENUEID]
    where not exists(select * from dbo.[REVENUESEGMENT] where [REVENUEID] = [APPEALREVENUE_CTE].[REVENUEID])
    and [REVENUE_EXT].[MAILINGID] is not null
  ),
  [MAILINGREVENUE_CTE] ([APPEALID], [MAILINGID], [TOTALRECEIVED], [TOTALPLEDGED], [TOTALPLEDGESPAID], [TOTALPLEDGESWRITTENOFF], [NUMBEROFDONORS], [NUMBEROFGIFTS]) as
  (
    --Revenue totals for revenue that matches to a mailing

    select
      [APPEALS].[ID] as [APPEALID],
      [MKTSEGMENTATION].[ID] as [MAILINGID],
      isnull(sum([APPEALMAILINGREVENUE_CTE].[RECEIVEDAMOUNT]), 0) as [TOTALRECEIVED],
      isnull(sum([APPEALMAILINGREVENUE_CTE].[PLEDGEDAMOUNT]), 0) as [TOTALPLEDGED],
      (case when isnull(sum([APPEALMAILINGREVENUE_CTE].[PLEDGEDAMOUNT]), 0) > 0
         --Don''t need the sum() here because it is already grouped in the temp table...

         then isnull((select [PLEDGEPAIDAMOUNT] from #APPEALPLEDGESPAID where [APPEALID] = [APPEALS].[ID] and [MAILINGID] = [MKTSEGMENTATION].[ID]), 0)
         else 0
       end) as [TOTALPLEDGESPAID],
      isnull(sum([APPEALMAILINGREVENUE_CTE].[PLEDGEWRITEOFFAMOUNT]), 0) as [TOTALPLEDGESWRITTENOFF],
      count(distinct [APPEALMAILINGREVENUE_CTE].[CONSTITUENTID]) as [NUMBEROFDONORS],
      count([APPEALMAILINGREVENUE_CTE].[REVENUEID]) as [NUMBEROFGIFTS]
    from @APPEALS as [APPEALS]
    inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] = cast([APPEALS].[ID] as nvarchar(36)) and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = ''DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0''
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] and [MKTSEGMENTATION].[ACTIVE] = 1  /*Activated mailings only*/
    left join [APPEALMAILINGREVENUE_CTE] on [APPEALMAILINGREVENUE_CTE].[APPEALID] = [APPEALS].[ID] and [APPEALMAILINGREVENUE_CTE].[MAILINGID] = [MKTSEGMENTATION].[ID]
    group by [APPEALS].[ID], [MKTSEGMENTATION].[ID]
  ),
  [NONMAILINGREVENUE_CTE] ([APPEALID], [TOTALRECEIVED], [TOTALPLEDGED], [TOTALPLEDGESPAID], [TOTALPLEDGESWRITTENOFF], [NUMBEROFDONORS], [NUMBEROFGIFTS]) as
  (
    --Revenue totals for revenue that does NOT match to any mailing

    select
      [APPEALS].[ID],
      isnull(sum([APPEALREVENUE_CTE].[RECEIVEDAMOUNT]), 0) as [TOTALRECEIVED],
      isnull(sum([APPEALREVENUE_CTE].[PLEDGEDAMOUNT]), 0) as [TOTALPLEDGED],
      (case when isnull(sum([APPEALREVENUE_CTE].[PLEDGEDAMOUNT]), 0) > 0
         then isnull((select sum([PLEDGEPAIDAMOUNT]) from #APPEALPLEDGESPAID where [APPEALID] = [APPEALS].[ID] and [MAILINGID] is null), 0)
         else 0
       end) as [TOTALPLEDGESPAID],
      isnull(sum([APPEALREVENUE_CTE].[PLEDGEWRITEOFFAMOUNT]), 0) as [TOTALPLEDGESWRITTENOFF],
      count(distinct [APPEALREVENUE_CTE].[CONSTITUENTID]) as [NUMBEROFDONORS],
      count([APPEALREVENUE_CTE].[REVENUEID]) as [NUMBEROFGIFTS]
    from @APPEALS as [APPEALS]
    left join [APPEALREVENUE_CTE] on [APPEALREVENUE_CTE].[APPEALID] = [APPEALS].[ID]
    left join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [APPEALREVENUE_CTE].[REVENUEID]
    where [REVENUE_EXT].[MAILINGID] is null
    and not exists(select * from dbo.[REVENUESEGMENT] where [REVENUEID] = [APPEALREVENUE_CTE].[REVENUEID])
    group by [APPEALS].[ID]
  ),
  [APPEALTOTALS] ([APPEALID], [NUMBEROFDONORS]) as
  (
    select
      [APPEALID],
      count(distinct [CONSTITUENTID]) as [NUMBEROFDONORS]
    from [APPEALREVENUE_CTE]
    group by [APPEALID]
  )
  --Revenue that matches to a mailing

  select
    [MAILINGREVENUE_CTE].[APPEALID],
    [APPEALS].[NAME] as [APPEALNAME], 
    [MKTSEGMENTATION].[NAME] as [MAILINGNAME], 
    [APPEALS].[GOAL],
    ([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGED] - [MAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF]) as [TOTALRAISED],
    ([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGESPAID]) as [TOTALRECEIVED],
    isnull([MKTSEGMENTATIONACTIVE].[QUANTITY], 0) as [SOLICITED],
    cast(isnull((case when [MKTSEGMENTATIONACTIVE].[QUANTITY] > 0 then cast([MAILINGREVENUE_CTE].[NUMBEROFGIFTS] as decimal(19,4)) / cast([MKTSEGMENTATIONACTIVE].[QUANTITY] as decimal(19,4)) else 0 end), 0) as decimal(19,4)) as [RESPONSERATE],
    [MAILINGREVENUE_CTE].[NUMBEROFDONORS],
    [MAILINGREVENUE_CTE].[NUMBEROFGIFTS],
    ''http://www.blackbaud.com/APPEALID?APPEALID='' + convert(nvarchar(36), [MAILINGREVENUE_CTE].[APPEALID]) as [APPEALLINK],
    ''http://www.blackbaud.com/'' + (case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 4 then ''PUBLICMEDIAEFFORTID'' else ''MAILINGID'' end) + ''?MAILINGID='' + convert(nvarchar(36), [MKTSEGMENTATION].[ID]) as [MAILINGLINK],
    cast((case when [APPEALMAILING].[ID] is not null then 1 else 0 end) as bit) as [ISAPPEALMAILING],
    ''http://www.blackbaud.com/APPEALMAILINGID?APPEALMAILINGID='' + convert(nvarchar(36), [APPEALMAILING].[ID]) as [APPEALMAILINGLINK],
    [APPEALS].[CURRENCYID],
    [APPEALS].[CURRENCYISO],
    [APPEALS].[CURRENCYDECIMALDIGITS],
    [APPEALS].[CURRENCYSYMBOL],
    [APPEALS].[CURRENCYSYMBOLDISPLAYSETTINGCODE],
    [MKTSEGMENTATION].[ID] as [MAILINGID],
    isnull([MAILINGAMOUNTS].[TOTALCOSTINCURRENCY], 0) as [TOTALCOST],
    ([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGED] - [MAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF] - isnull([MAILINGAMOUNTS].[TOTALCOSTINCURRENCY], 0)) as [TOTALPROFIT],
    (case when isnull([MAILINGAMOUNTS].[TOTALCOSTINCURRENCY], 0) <> 0
       then cast((([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGED] - [MAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF] - [MAILINGAMOUNTS].[TOTALCOSTINCURRENCY]) / [MAILINGAMOUNTS].[TOTALCOSTINCURRENCY]) as money)
       else cast(0 as money)
     end) as [ROI],
    (case when ([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGED] - [MAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF]) <> 0
       then cast((isnull([MAILINGAMOUNTS].[TOTALCOSTINCURRENCY], 0) / ([MAILINGREVENUE_CTE].[TOTALRECEIVED] + [MAILINGREVENUE_CTE].[TOTALPLEDGED] - [MAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF])) as money)
       else cast((case when isnull([MAILINGAMOUNTS].[TOTALCOSTINCURRENCY], 0) = 0 then 0 else 1 end) as money)
     end) as [COSTPERDOLLARRAISED],
    [APPEALTOTALS].[NUMBEROFDONORS] as [APPEALTOTALDONORS]
  from [MAILINGREVENUE_CTE]
  left join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MAILINGREVENUE_CTE].[MAILINGID]
  left join dbo.[UFN_MKTSEGMENTATION_GETAMOUNTSINCURRENCY_BULK](@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [MAILINGAMOUNTS] on [MAILINGAMOUNTS].[ID] = [MKTSEGMENTATION].[ID]
  left join @APPEALS as [APPEALS] on [APPEALS].[ID] = [MAILINGREVENUE_CTE].[APPEALID]
  left join dbo.[APPEALMAILING] on [APPEALMAILING].[ID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONACTIVE] on [MKTSEGMENTATIONACTIVE].[ID] = [MKTSEGMENTATION].[ID]
  left join [APPEALTOTALS] on [APPEALTOTALS].[APPEALID] = [MAILINGREVENUE_CTE].[APPEALID]

  union all

  --Revenue that does NOT match to any mailing

  select
    [NONMAILINGREVENUE_CTE].[APPEALID],
    [APPEALS].[NAME] as [APPEALNAME], 
    ''(No mailing)'' as [MAILINGNAME],
    [APPEALS].[GOAL],
    ([NONMAILINGREVENUE_CTE].[TOTALRECEIVED] + [NONMAILINGREVENUE_CTE].[TOTALPLEDGED] - [NONMAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF]) as [TOTALRAISED],
    ([NONMAILINGREVENUE_CTE].[TOTALRECEIVED] + [NONMAILINGREVENUE_CTE].[TOTALPLEDGESPAID]) as [TOTALRECEIVED],
    0 as [SOLICITED],
    0 as [RESPONSERATE],
    [NONMAILINGREVENUE_CTE].[NUMBEROFDONORS],
    [NONMAILINGREVENUE_CTE].[NUMBEROFGIFTS],
    ''http://www.blackbaud.com/APPEALID?APPEALID='' + convert(nvarchar(36), [NONMAILINGREVENUE_CTE].[APPEALID]) as [APPEALLINK],
    null as [MAILINGLINK],
    cast(0 as bit) as [ISAPPEALMAILING],
    null as [APPEALMAILINGLINK],
    [APPEALS].[CURRENCYID],
    [APPEALS].[CURRENCYISO],
    [APPEALS].[CURRENCYDECIMALDIGITS],
    [APPEALS].[CURRENCYSYMBOL],
    [APPEALS].[CURRENCYSYMBOLDISPLAYSETTINGCODE],
    null as [MAILINGID],
    0 as [TOTALCOST],
    ([NONMAILINGREVENUE_CTE].[TOTALRECEIVED] + [NONMAILINGREVENUE_CTE].[TOTALPLEDGED] - [NONMAILINGREVENUE_CTE].[TOTALPLEDGESWRITTENOFF]) as [TOTALPROFIT],
    0 as [ROI],
    0 as [COSTPERDOLLARRAISED],
    [APPEALTOTALS].[NUMBEROFDONORS] as [APPEALTOTALDONORS]
  from [NONMAILINGREVENUE_CTE]
  left join @APPEALS as [APPEALS] on [APPEALS].[ID] = [NONMAILINGREVENUE_CTE].[APPEALID]
  left join [APPEALTOTALS] on [APPEALTOTALS].[APPEALID] = [NONMAILINGREVENUE_CTE].[APPEALID]

  order by [APPEALNAME], [MAILINGNAME];



  drop table #APPEALREVENUESPLIT;
  drop table #APPEALPLEDGESPAID;' as nvarchar(max));

      exec @r = sp_executesql @SQLTOEXEC, N'@STARTDATE datetime, @ENDDATE datetime, @CATEGORYID uniqueidentifier, @BUSINESSUNITID uniqueidentifier, @REPORTCODEID uniqueidentifier, @SITEID uniqueidentifier, @REPORTUSERID nvarchar(128), @APPEALID uniqueidentifier, @CURRENCYCODE tinyint, @ALTREPORTUSERID nvarchar(128)', @STARTDATE = @STARTDATE, @ENDDATE = @ENDDATE, @CATEGORYID = @CATEGORYID, @BUSINESSUNITID = @BUSINESSUNITID, @REPORTCODEID = @REPORTCODEID, @SITEID = @SITEID, @REPORTUSERID = @REPORTUSERID, @APPEALID = @APPEALID, @CURRENCYCODE = @CURRENCYCODE, @ALTREPORTUSERID = @ALTREPORTUSERID;
    end

  return @r;