USP_DATALIST_MKTINDIRECTRESPONSESSUMMARY

CREATE procedure dbo.[USP_DATALIST_MKTINDIRECTRESPONSESSUMMARY]
(
  @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 @BASECURRENCYID uniqueidentifier;

  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;

  --Return the results...

  select
    (select [NAME] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID) as [MAILINGNAME],
    @APPEALIDLIST as [APPEALID],
    @APPEALDESCLIST as [APPEALDESCRIPTION],
    [INDIRECTRESPONDERS] as [RESPONDERS],
    [INDIRECTRESPONSES] as [RESPONSES],
    case when @CURRENCYCODE = 1 then [ORGANIZATIONINDIRECTTOTALGIFTAMOUNT] else [INDIRECTTOTALGIFTAMOUNT] end as [TOTALGIFTAMOUNT],
    case when @CURRENCYCODE = 1 then [ORGANIZATIONINDIRECTAVERAGEGIFTAMOUNT] else [INDIRECTAVERAGEGIFTAMOUNT] end as [AVERAGEGIFTAMOUNT],
    dbo.[UFN_MKTSEGMENTATION_ISAPPEALMAILING](@SEGMENTATIONID) as [ISAPPEALMAILING],
    [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],
    [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from dbo.[MKTSEGMENTATIONACTIVE]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENTATIONACTIVE].[BASECURRENCYID] end
  where [MKTSEGMENTATIONACTIVE].[ID] = @SEGMENTATIONID;

  return 0;