USP_DATALIST_MKTLISTPROFILEMAILINGS

CREATE procedure dbo.USP_DATALIST_MKTLISTPROFILEMAILINGS
(
  @LISTID uniqueidentifier,
  @CURRENCYCODE tinyint = 1 /* 0 = base, 1 = organization */
)
as
  set nocount on;

  select 
    [MKTSEGMENTATION].[ID] as [ID],
    [MKTSEGMENTATION].[NAME] as [MAILNAME],
    [MKTSEGMENT].[ID] as [SEGMENTID],
    [MKTSEGMENT].[NAME] as [SEGMENTNAME],
    [MKTPACKAGE].[ID] as [PACKAGEID],
    [MKTPACKAGE].[NAME] as [PACKAGENAME],
    [MKTSEGMENTATIONLISTACTIVE].[QUANTITY],
    [MKTSEGMENTATIONLISTACTIVE].[RESPONDERS],
    [MKTSEGMENTATIONLISTACTIVE].[RESPONSES],
    case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONLISTACTIVE].[ORGANIZATIONTOTALCOST] else [MKTSEGMENTATIONLISTACTIVE].[TOTALCOST] end as [TOTALCOST],
    case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONLISTACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT] else [MKTSEGMENTATIONLISTACTIVE].[TOTALGIFTAMOUNT] end as [TOTALGIFTAMOUNT],
    [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],
    [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE],
    [MKTSEGMENTATIONLISTACTIVE].[BASECURRENCYID]
  from
    [MKTSEGMENTATIONLISTACTIVE]
    left join [MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONLISTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
    left join [MKTSEGMENTATION] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    left join [MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
    left join [MKTPACKAGE] on [MKTSEGMENTATIONLISTACTIVE].[PACKAGEID] = [MKTPACKAGE].[ID]
    inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENTATIONLISTACTIVE].[BASECURRENCYID] end
  where
    [MKTSEGMENTATIONLISTACTIVE].[LISTID] = @LISTID;

  return 0;