USP_DATALIST_MKTGROUPSEGMENTS

Displays a list of all segments and indicates if they are in a specified group.

Parameters

Parameter	Parameter Type	Mode	Description
@SEGMENTFAMILYTYPECODE	tinyint	IN	Segment family type code
@SEGMENTGROUPID	uniqueidentifier	IN	Segment group ID
@ALLSEGMENTS	tinyint	IN	Show all segments
@BASECURRENCYID	uniqueidentifier	IN	Base currency ID
@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_MKTGROUPSEGMENTS]
(
  @SEGMENTFAMILYTYPECODE tinyint,
  @SEGMENTGROUPID uniqueidentifier = null,
  @ALLSEGMENTS tinyint = 1,
  @BASECURRENCYID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  if @BASECURRENCYID is null
    select 
      [MKTSEGMENT].[ID],
      [MKTSEGMENT].[NAME],
      [MKTSEGMENT].[CODE],
      [MKTSEGMENT].[DESCRIPTION],
      [MKTSEGMENT].[SEGMENTTYPE],
      (case when dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]) = 0 then 'No' else 'Yes' end) as [INUSE],
      (case when exists(select * from dbo.[MKTGROUPSEGMENTS] where [MKTSEGMENT].[ID] = [SEGMENTID] and [SEGMENTGROUPID] = @SEGMENTGROUPID) then 1 else 0 end) as [INGROUP],
      isnull((select [SEQUENCE] from dbo.[MKTGROUPSEGMENTS] where [MKTSEGMENT].[ID] = [SEGMENTID] and [SEGMENTGROUPID] = @SEGMENTGROUPID), 9999999) as [SEQUENCE]
    from dbo.[MKTSEGMENT]
    left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    left outer join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
    where [MKTSEGMENT].[SEGMENTFAMILYTYPECODE] = @SEGMENTFAMILYTYPECODE
    and (@ALLSEGMENTS = 0 or exists (select * from dbo.[MKTGROUPSEGMENTS] where [MKTSEGMENT].[ID] = [SEGMENTID] and [SEGMENTGROUPID] = @SEGMENTGROUPID))
    /* exclude segments from inactive lists */
    and ([MKTLIST].[ISINACTIVE] is null or [MKTLIST].[ISINACTIVE] = 0)
    /* Exclude appeal mailing segments */
    and not exists(select 1
                   from dbo.[APPEALMAILING]
                   inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID]
                   where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID])
    /* Exclude system records */
    and [MKTSEGMENT].[ISSYSTEM] = 0
    /* Exclude historical segments */
    and [MKTSEGMENT].[ISHISTORICAL] = 0
    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 isnull((select [SEQUENCE] from dbo.[MKTGROUPSEGMENTS] where [MKTSEGMENT].[ID] = [SEGMENTID] and [SEGMENTGROUPID] = @SEGMENTGROUPID), 9999999), [MKTSEGMENT].[NAME];    

  else
    select 
      [MKTSEGMENT].[ID],
      [MKTSEGMENT].[NAME],
      [MKTSEGMENT].[CODE],
      [MKTSEGMENT].[DESCRIPTION],
      [MKTSEGMENT].[SEGMENTTYPE],
      (case when dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]) = 0 then 'No' else 'Yes' end) as [INUSE],
      (case when exists(select * from dbo.[MKTGROUPSEGMENTS] where [MKTSEGMENT].[ID] = [SEGMENTID] and [SEGMENTGROUPID] = @SEGMENTGROUPID) then 1 else 0 end) as [INGROUP],
      isnull((select [SEQUENCE] from dbo.[MKTGROUPSEGMENTS] where [MKTSEGMENT].[ID] = [SEGMENTID] and [SEGMENTGROUPID] = @SEGMENTGROUPID), 9999999) as [SEQUENCE]
    from dbo.[MKTSEGMENT]
    left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    left outer join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
    where [MKTSEGMENT].[SEGMENTFAMILYTYPECODE] = @SEGMENTFAMILYTYPECODE
    and ([MKTSEGMENT].[BASECURRENCYID] is null or [MKTSEGMENT].[BASECURRENCYID] = @BASECURRENCYID)
    and (@ALLSEGMENTS = 0 or exists (select * from dbo.[MKTGROUPSEGMENTS] where [MKTSEGMENT].[ID] = [SEGMENTID] and [SEGMENTGROUPID] = @SEGMENTGROUPID))
    /* exclude segments from inactive lists */
    and ([MKTLIST].[ISINACTIVE] is null or [MKTLIST].[ISINACTIVE] = 0)
    /* Exclude appeal mailing segments */
    and not exists(select 1
                   from dbo.[APPEALMAILING]
                   inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID]
                   where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID])
    /* Exclude system records */
    and [MKTSEGMENT].[ISSYSTEM] = 0
    /* Exclude historical segments */
    and [MKTSEGMENT].[ISHISTORICAL] = 0
    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 isnull((select [SEQUENCE] from dbo.[MKTGROUPSEGMENTS] where [MKTSEGMENT].[ID] = [SEGMENTID] and [SEGMENTGROUPID] = @SEGMENTGROUPID), 9999999), [MKTSEGMENT].[NAME];  

  return 0;