USP_DATALIST_MKTMARKETINGPLANS

Displays a list of the marketing plans.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ISAPPROVED	bit	IN	Status
@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_MKTMARKETINGPLANS]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @ISAPPROVED bit = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  select
    [MPI].[ID],
    [MPI].[MARKETINGPLANID],
    [MKTMARKETINGPLAN].[ISACTIVE],
    [MPI].[NAME],
    [MKTMARKETINGPLAN].[ISAPPROVED],
    [MPI].[STARTDATE],
    [MPI].[GOALS],
    [MPI].[ENDDATE],
    [MPITI].[CAPTION],
    [MPITI].[BACKCOLOR],
    [MPITI].[FORECOLOR],
    [MPITI].[CAPTIONCOLOR],
    [MPITI].[LEVEL],
    case when (select max([SUB].[LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [SUB].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID]) = [MPITI].[LEVEL] then 1 else 0 end as [ISAPPEALLEVEL],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTMARKETINGPLAN].[SITEID]) as [SITE]
  from dbo.[MKTMARKETINGPLAN]
  inner join dbo.[MKTMARKETINGPLANITEM] as [MPI] on [MKTMARKETINGPLAN].[ID] = [MPI].[MARKETINGPLANID]
  inner join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on ([MPI].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID] and [MPI].[LEVEL] = [MPITI].[LEVEL])
  where [MPI].[LEVEL] = 0
  and (@ISAPPROVED is null or [MKTMARKETINGPLAN].[ISAPPROVED] = @ISAPPROVED)
  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;