USP_DATALIST_MKTLISTSEGMENTS

Returns all the segments based on a particular list.

Parameters

Parameter	Parameter Type	Mode	Description
@LISTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@SEGMENTCATEGORYCODEID	uniqueidentifier	IN	Category
@SEGMENTGROUPID	uniqueidentifier	IN	Group
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN	Sites selected
@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.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTLISTSEGMENTS]
(
  @LISTID uniqueidentifier,
  @SEGMENTCATEGORYCODEID uniqueidentifier = null,
  @SEGMENTGROUPID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  select
    [MKTSEGMENT].[ID],
    [MKTSEGMENT].[NAME],
    [MKTSEGMENT].[DESCRIPTION],
    [MKTSEGMENT].[CODE],
    [MKTSEGMENTCATEGORYCODE].[DESCRIPTION] as [CATEGORY],
    [MKTSEGMENTGROUP].[NAME] as [GROUP],
    dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]) as [ISINUSE],
    [MKTSEGMENTLIST].[STATUS],
    [MKTSEGMENTLIST].[TOTALRECORDCOUNT],
    [MKTSEGMENTLIST].[ORDERDATE],
    [MKTSEGMENTLIST].[EXPIRATIONDATE],
    [MKTSEGMENTLIST].[NUMBEROFCONTACTS],
    cast((case when [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID] is null then 0 else 1 end) as bit) as [ISCONSOLIDATED],
    [QUERYVIEWCATALOG].[ROOTOBJECT] as [SHOWINQUERYDESIGNER],
    cast((case when [MKTSEGMENTLIST].[PARENTSEGMENTID] is null then 0 else 1 end) as bit) as [ISCHILDLISTSEGMENT],
    [MKTSEGMENTREFRESHPROCESS].[ID] as [SEGMENTREFRESHPROCESSID],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENT].[SITEID]) as [SITE],
    [MKTSEGMENT].[ISHISTORICAL]
  from dbo.[MKTSEGMENTLIST]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[CURRENTSEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
  left join dbo.[MKTSEGMENTREFRESHPROCESS] on [MKTSEGMENTREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENT].[ID]
  left join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]
  left join dbo.[MKTGROUPSEGMENTS] on [MKTGROUPSEGMENTS].[SEGMENTID] = [MKTSEGMENT].[ID]
  left join dbo.[MKTSEGMENTGROUP] on [MKTSEGMENTGROUP].[ID] = [MKTGROUPSEGMENTS].[SEGMENTGROUPID]
  left join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
  where [MKTSEGMENTLIST].[LISTID] = @LISTID
  and (@SEGMENTCATEGORYCODEID is null or [MKTSEGMENT].[SEGMENTCATEGORYCODEID] = @SEGMENTCATEGORYCODEID)
  and (@SEGMENTGROUPID is null or [MKTSEGMENTGROUP].[ID] = @SEGMENTGROUPID)
  and (
        (
          ( -- check site security

            select count(1) 
            from
              (select [SITEID]
               from dbo.[MKTSEGMENT] as [SEGMENTSITE]
               where [SEGMENTSITE].[ID] = [MKTSEGMENT].[ID]) 
            as [SEGMENTSITE]
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SEGMENTSITE].[SITEID] or (SITEID is null and [SEGMENTSITE].[SITEID] is null)))
          ) > 0
        )
      and 
        ( -- apply site filter

          @SITEFILTERMODE = 0
          or [MKTSEGMENT].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
        )
    )
  order by [MKTSEGMENT].[NAME];

  return 0;