USP_DATALIST_MKTSEGMENTWHITEMAILSUMMARY

Returns summary information for white mail segments.

Parameters

Parameter	Parameter Type	Mode	Description
@ACTIVEASOF	date	IN	Active as of
@INCLUDEINACTIVE	bit	IN	Include inactive
@SOURCECODEID	uniqueidentifier	IN	Source code
@CURRENCYCODE	tinyint	IN	Currency
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTWHITEMAILSUMMARY]
(
  @ACTIVEASOF date = null,
  @INCLUDEINACTIVE bit = null,
  @SOURCECODEID uniqueidentifier = null,
  @CURRENCYCODE tinyint = 1,
  @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  if @ACTIVEASOF is null set @ACTIVEASOF = getdate();

  select
    [MKTSEGMENT].[ID],
    dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTWHITEMAIL].[ID], null, null) as [SOURCECODE],
    [MKTSEGMENT].[NAME],
    [MKTSEGMENTCATEGORYCODE].[DESCRIPTION] as [SEGMENTCATEGORYCODE],
    --[MKTSEGMENTATION].[NAME] as [SEGMENTATION],

    [MKTSEGMENTWHITEMAILCACHE].[RESPONDERS],
    [MKTSEGMENTWHITEMAILCACHE].[RESPONSES],
    case @CURRENCYCODE when 1 then [MKTSEGMENTWHITEMAILCACHE].[ORGANIZATIONTOTALGIFTAMOUNT] else [MKTSEGMENTWHITEMAILCACHE].[TOTALGIFTAMOUNT] end as [TOTALGIFTAMOUNT],
    case @CURRENCYCODE when 1 then [MKTSEGMENTWHITEMAILCACHE].[ORGANIZATIONAVERAGEGIFTAMOUNT] else [MKTSEGMENTWHITEMAILCACHE].[AVERAGEGIFTAMOUNT] end as [AVERAGEGIFTAMOUNT],
    [MKTSEGMENTWHITEMAILREFRESHPROCESS].[DATEREFRESHED],
    [MKTSEGMENT].[DESCRIPTION],
    [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],
    [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE]    
  from dbo.[MKTSEGMENTWHITEMAIL]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
  inner join dbo.[MKTSEGMENTWHITEMAILREFRESHPROCESS] on [MKTSEGMENTWHITEMAILREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENTWHITEMAIL].[ID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENT].[BASECURRENCYID] end
  left outer join dbo.[MKTSEGMENTWHITEMAILCACHE] on [MKTSEGMENTWHITEMAILCACHE].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
  --left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID]

  left outer join dbo.[MKTGROUPSEGMENTS] on [MKTSEGMENT].[ID] = [MKTGROUPSEGMENTS].[SEGMENTID]
  left outer join dbo.[MKTSEGMENTGROUP] on [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = [MKTSEGMENTGROUP].[ID]
  left outer join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]
  where (@SOURCECODEID is null or [MKTSEGMENTWHITEMAIL].[SOURCECODEID] = @SOURCECODEID)
  and (@INCLUDEINACTIVE = 1 or (([MKTSEGMENTWHITEMAIL].[STATUSCODE] = 0 or ([MKTSEGMENTWHITEMAIL].[STATUSCODE] = 1 and @ACTIVEASOF between isnull([MKTSEGMENTWHITEMAIL].[ACTIVEFROM], dateadd(d, -1, @ACTIVEASOF)) and isnull([MKTSEGMENTWHITEMAIL].[ACTIVETO], dateadd(d, 1, @ACTIVEASOF))))))
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTSEGMENT].[SITEID] or (SITEID is null and [MKTSEGMENT].[SITEID] is null)))
  order by [MKTSEGMENT].[NAME];

  return 0;