USP_DATALIST_MKTMARKETINGPLANMAILINGS

Displays a list of the marketing efforts for all marketing plans.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ISAPPROVED	bit	IN	Status
@ISLINKED	bit	IN	Marketing effort created
@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.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTMARKETINGPLANMAILINGS]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @ISAPPROVED bit = 1,
  @ISLINKED bit = 0,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null  
)
as
  set nocount on;

  select
    [MPI].[ID],
    [MKTMARKETINGPLAN].[ISAPPROVED],
    case when [MKTMARKETINGPLAN].[ISAPPROVED] = 0 then 'Unapproved' else 'Approved' end as [STATUS],
    [MPI].[CODE],
    [MPI].[STARTDATE],
    [MPI].[NAME],
    [MPI].[GOALS],
    [MPI].[ENDDATE],
    dbo.[UFN_MKTMARKETINGPLANITEM_PATH]([MPI].[ID], 0) as [PATH],
    [MPITI].[CAPTION],
    [MPITI].[BACKCOLOR],
    [MPITI].[FORECOLOR],
    [MPITI].[CAPTIONCOLOR],
    [MPITI].[LEVEL],
    [S].[NAME],
    [MPI].[MAILDATE],
    [S].[ID],
    case when [S].[ID] is null then 0 else 1 end as [MAILINGCREATED],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTMARKETINGPLAN].[SITEID]) as [SITE]
  from dbo.[MKTMARKETINGPLAN]
  inner join dbo.[MKTMARKETINGPLANITEM] as [MPI] on [MPI].[MARKETINGPLANID] = [MKTMARKETINGPLAN].[ID]
  inner join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on ([MPITI].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID] and [MPITI].[LEVEL] = [MPI].[LEVEL])
  left join dbo.[MKTSEGMENTATION] as [S] on [MPI].[ID] = [S].[MARKETINGPLANITEMID]
  where [MPI].[LEVEL] = (select max([SUB].[LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) 
  and (@ISAPPROVED is null or [MKTMARKETINGPLAN].[ISAPPROVED] = @ISAPPROVED)
  and (@ISLINKED is null or ([S].[ID] is null and @ISLINKED = 0) or ([S].[ID] is not null and @ISLINKED = 1)) 
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTMARKETINGPLAN].[SITEID] or (SITEID is null and [MKTMARKETINGPLAN].[SITEID] is null)))
  and (@SITEFILTERMODE = 0 or [MKTMARKETINGPLAN].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  order by [MPI].[NAME];

  return 0;