USP_DATALIST_SELECTIONBRIEF

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

  select
    [MKTSEGMENTATION].[NAME],
    [MKTSEGMENTATION].[MAILINGTYPECODE],
    [MKTSEGMENTATION].[MAILINGTYPE],
    [MKTSEGMENTATION].[DESCRIPTION],
    [MKTSEGMENTATION].[CODE],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENTATION].[SITEID]) [SITE],
    [MKTSEGMENTATION].[IDINTEGER],
    [MKTSEGMENTATION].[ACTIVE],
    [MKTSEGMENTATION].[ACTIVATEDATE],
    isnull(stuff(
                  (
                    select ', ' + [APPEALDESCRIPTION]
                    from dbo.[MKTSEGMENTATIONACTIVATE]
                    where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
                    for xml path(''), type
                  ).value('.', 'varchar(max)')
                  , 1, 2, ''
                ), '') as [APPEAL],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [NUMSEGMENTS],
    (select count([MKTSEGMENTATIONSEGMENT].[ID]) from dbo.[MKTSEGMENTATIONSEGMENT] inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENT].[SEGMENTTYPECODE] = 2) as [NUMVENDORMANAGEDSEGMENTS],
    (select count([ID]) from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [NUMPACKAGES],
     case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONBUDGET].[ORGANIZATIONBUDGETAMOUNT] else [MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT] end as [BUDGETAMOUNT],
     case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONBUDGET].[ORGANIZATIONFIXEDCOST] else [MKTSEGMENTATIONBUDGET].[FIXEDCOST] end as [FIXEDCOST],
    isnull([MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED], [MKTSEGMENTATION].[ACTIVATEDATE]) as [DATEREFRESHED],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEED] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [NUMSEEDS],
    (select count([TEST].[ID]) from dbo.[MKTSEGMENTATION] as [TEST] where [TEST].[PARENTSEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [NUMTESTMAILINGS],
    [MKTSEGMENTATION].[MAILDATE],
    [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
    [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
    [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
    [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
    [CURRENCY].[ISO4217],
    [CURRENCY].[DECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE],
    cast(case when [MKTSEGMENTATION].[ACTIVE] = 0 then dbo.[UFN_MKTSEGMENTATION_ISRECORDCOUNTCACHECURRENT]([MKTSEGMENTATION].[ID], 1) else 1 end as bit) as [ISRECORDCOUNTCACHECURRENT],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [EXCLUDE] = 0) as [ACTIVESEGMENTS],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [EXCLUDE] = 1) as [EXCLUSIONSEGMENTS]
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENTATIONBUDGET].[BASECURRENCYID] end
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

  return 0;