USP_DATALIST_MKTAPPEALUNRESOLVEDRESPONSESSUMMARY

CREATE procedure dbo.[USP_DATALIST_MKTAPPEALUNRESOLVEDRESPONSESSUMMARY]
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENCYCODE tinyint = 1
)
as
  set nocount on;

  declare @APPEALIDLIST nvarchar(max);
  declare @APPEALDESCLIST nvarchar(max);
  declare @APPEALID nvarchar(100);
  declare @APPEALDESC nvarchar(255);
  declare @UNRESOLVEDTABLE table([RESPONDERS] int,
                                 [RESPONSES] int,
                                 [TOTALGIFTAMOUNT] money,
                                 [AVERAGEGIFTAMOUNT] money,
                                 [ORGANIZATIONTOTALGIFTAMOUNT] money,
                                 [ORGANIZATIONAVERAGEGIFTAMOUNT] money);

  /* Get multicurrency values for the given effort */
  declare @ISO4217 nvarchar(100);
  declare @DECIMALDIGITS tinyint;
  declare @CURRENCYSYMBOL nvarchar(5);
  declare @SYMBOLDISPLAYSETTINGCODE tinyint;

  select
    @ISO4217 = [CURRENCY].[ISO4217],
    @DECIMALDIGITS = [CURRENCY].[DECIMALDIGITS],
    @CURRENCYSYMBOL = [CURRENCY].[CURRENCYSYMBOL],
    @SYMBOLDISPLAYSETTINGCODE = [CURRENCY].[SYMBOLDISPLAYSETTINGCODE]
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENTATION].[BASECURRENCYID] end
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

  declare APPEALCURSOR cursor local fast_forward for
    select [APPEALID], [APPEALDESCRIPTION]
    from dbo.[MKTSEGMENTATIONACTIVATE]
    where [SEGMENTATIONID] = @SEGMENTATIONID;

  open APPEALCURSOR;
  fetch next from APPEALCURSOR into @APPEALID, @APPEALDESC;

  while (@@FETCH_STATUS = 0)
  begin
    if @APPEALIDLIST is not null
      set @APPEALIDLIST = @APPEALIDLIST + ', ';
    if @APPEALDESCLIST is not null
      set @APPEALDESCLIST = @APPEALDESCLIST + ', ';

    set @APPEALIDLIST = isnull(@APPEALIDLIST,'') + @APPEALID;
    set @APPEALDESCLIST = isnull(@APPEALDESCLIST,'') + @APPEALDESC;

    fetch next from APPEALCURSOR into @APPEALID, @APPEALDESC;
  end;

  close APPEALCURSOR;
  deallocate APPEALCURSOR;

  insert into @UNRESOLVEDTABLE
    exec dbo.[USP_MKTSEGMENTATION_GETUNRESOLVEDRESPONSECOUNTS] @SEGMENTATIONID;

  --Return the results...

  select
    @APPEALIDLIST as [APPEALID],
    @APPEALDESCLIST as [APPEALDESCRIPTION],
    [RESPONDERS],
    [RESPONSES],
    case when @CURRENCYCODE = 1 then [ORGANIZATIONTOTALGIFTAMOUNT] else [TOTALGIFTAMOUNT] end as [TOTALGIFTAMOUNT],
    case when @CURRENCYCODE = 1 then [ORGANIZATIONAVERAGEGIFTAMOUNT] else [AVERAGEGIFTAMOUNT] end as [AVERAGEGIFTAMOUNT],
    (select [NAME] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID) as [MAILINGNAME], 
    dbo.[UFN_MKTSEGMENTATION_ISAPPEALMAILING](@SEGMENTATIONID) as [ISAPPEALMAILING],
    @ISO4217 as [CURRENCYISOCURRENCYCODE],
    @DECIMALDIGITS as [CURRENCYDECIMALDIGITS],
    @CURRENCYSYMBOL as [CURRENCYSYMBOL],
    @SYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from @UNRESOLVEDTABLE;

  return 0;