USP_DATALIST_MKTMARKETINGPLANALLPLANITEMS

Displays a list of all child marketing plan items and tasks for a given marketing plan item.

Parameters

Parameter	Parameter Type	Mode	Description
@PARENTMARKETINGPLANITEMID	uniqueidentifier	IN	ParentID
@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_MKTMARKETINGPLANALLPLANITEMS]
(
  @PARENTMARKETINGPLANITEMID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  select
    [MPI].[ID],
    [MPI].[PARENTMARKETINGPLANITEMID] as [PARENTID],
    [MPI].[MARKETINGPLANID] as [MARKETINGPLANID],
    0 as [APPOINTMENTINFOTYPE],
    '<<status>>' as [STATUS],
    [MPI].[NAME] as [SUBJECT],
    dbo.[UFN_MKTMARKETINGPLANITEM_PATH]([MPI].[ID], 0) as [PATH],
    [MPI].[STARTDATE],
    [MPI].[ENDDATE],
    [MPITI].[CAPTION],
    [MPITI].[BACKCOLOR],
    [MPITI].[CAPTIONCOLOR],
    [MPITI].[LEVEL]
  from dbo.[MKTMARKETINGPLANITEM] as [MPI]
  inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MPI].[MARKETINGPLANID]
  left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on ([MPITI].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID] and [MPITI].[LEVEL] = [MPI].[LEVEL])
  inner join dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID](@PARENTMARKETINGPLANITEMID) as [HIER] on [HIER].[ID] = [MPI].[ID]
  where (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)))

  union

  select
    [MPITI].[ID],
    [MPITI].[MARKETINGPLANITEMID] as [PARENTID],
    [MPI].[MARKETINGPLANID] as [MARKETINGPLANID],
    1 as [APPOINTMENTINFOTYPE],
    [MPITI].[MARKETINGPLANTASKSTATUS] as [STATUS],
    [MPITI].[SUBJECT],
    dbo.[UFN_MKTMARKETINGPLANITEM_PATH]([MPITI].[MARKETINGPLANITEMID], 1) as [PATH],
    [MPITI].[DUEDATE] as [STARTDATE],
    null as [ENDDATE],
    'Task' as [CAPTION],
    -1 as [BACKCOLOR],
    -16777216 as [CAPTIONCOLOR], -- black

    0 as [LEVEL]
  from dbo.[MKTMARKETINGPLANITEMTASK] as [MPITI]
  inner join dbo.[MKTMARKETINGPLANITEM] as [MPI] on [MPI].[ID] = [MPITI].[MARKETINGPLANITEMID]
  inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MPI].[MARKETINGPLANID]
  inner join dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID](@PARENTMARKETINGPLANITEMID) as [HIER] on [MPI].[ID] = [HIER].[ID]
  where (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)))

  order by 8, 6;

  return 0;