USP_DATALIST_MKTMARKETINGPLANNAVIGATION

Displays a list of the marketing plan items for building a treeview hierarchy.

Parameters

Parameter	Parameter Type	Mode	Description
@MARKETINGPLANITEMID	uniqueidentifier	IN	Marketing plan item ID
@STARTDATE	nvarchar(8)	IN	Start date
@ENDDATE	nvarchar(8)	IN	End date
@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_MKTMARKETINGPLANNAVIGATION]
(
  @MARKETINGPLANITEMID uniqueidentifier = null,
  @STARTDATE as nvarchar(8) = null,
  @ENDDATE as nvarchar(8) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  set @STARTDATE = isnull(cast(@STARTDATE + '00000000' as varchar(8)), '00000000');
  set @ENDDATE = isnull(cast(@ENDDATE + '00000000' as varchar(8)), '99999999');

  select
    [HIER].[ID],
    [HIER].[PARENTMARKETINGPLANITEMID],
    [HIER].[NAME],
    [HIER].[CAPTION],
    [HIER].[BACKCOLOR],
    [MPI].[STARTDATE],
    [MPI].[ENDDATE],
    [MPI].[LEVEL]
  from dbo.[UFN_MKTMARKETINGPLANITEMNAVIGATIONHIERARCHY](@MARKETINGPLANITEMID) as [HIER]
  inner join dbo.[MKTMARKETINGPLANITEM] as [MPI] on [MPI].[ID] = [HIER].[ID]
  inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MPI].[MARKETINGPLANID]
  where
    (([MPI].[STARTDATE] between @STARTDATE and @ENDDATE)
    or
    (([MPI].[STARTDATE] <= @ENDDATE) and (([MPI].[ENDDATE] = '00000000') or ([MPI].[ENDDATE] between @STARTDATE and @ENDDATE))))
    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)));

  return 0;