USP_DATALIST_PLANITEMHIERARCHYCALENDARITEM

Displays all child marketing plan items and tasks for a given marketing plan item on the organization's calendar.
Parameters

Parameter	Parameter Type	Mode	Description
@PLANITEMS	xml	IN	Plans
@STARTDATE	datetime	IN	Start date
@ENDDATE	datetime	IN	End date
@INCLUDETASKS	bit	IN	Include tasks
@DATEFILTER	tinyint	IN	Date
Definition

Copy


CREATE procedure dbo.[USP_DATALIST_PLANITEMHIERARCHYCALENDARITEM]
(
  @PLANITEMS xml = null,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @INCLUDETASKS bit = null,
  @DATEFILTER tinyint = null
)
as
  set nocount on;
  select
    [MKTMARKETINGPLANITEM].[ID],
    [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID] as [PARENTID],
    [MKTMARKETINGPLANITEM].[NAME],
    dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MKTMARKETINGPLANITEM].[STARTDATE]) as [STARTDATE],
    case [MKTMARKETINGPLANITEM].[ENDDATE] 
      when '00000000' then dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MKTMARKETINGPLANITEM].[STARTDATE])
      else dbo.[UFN_DATE_LATESTFROMFUZZYDATE]([MKTMARKETINGPLANITEM].[ENDDATE]) 
    end as [ENDDATE],
    [MKTMARKETINGPLANITEMTEMPLATEITEM].[BACKCOLOR],
    0 as [TYPECODE]
  from dbo.[MKTMARKETINGPLANITEM]
  left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] on ([MKTMARKETINGPLANITEM].[MARKETINGPLANID] = [MKTMARKETINGPLANITEMTEMPLATEITEM].[MARKETINGPLANID] and [MKTMARKETINGPLANITEM].[LEVEL] = [MKTMARKETINGPLANITEMTEMPLATEITEM].[LEVEL])
  inner join @PLANITEMS.nodes('/PLANITEMS/ITEM') T(c) on ([MKTMARKETINGPLANITEM].[ID] = T.c.value('(ID)[1]', 'uniqueidentifier'))
  where
    (
      (@STARTDATE is null and @ENDDATE is null)
      or (@STARTDATE is not null and @ENDDATE is null and dbo.[UFN_DATE_LATESTFROMFUZZYDATE]([MKTMARKETINGPLANITEM].[STARTDATE]) >= @STARTDATE)
      or (@STARTDATE is null and @ENDDATE is not null and dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MKTMARKETINGPLANITEM].[STARTDATE]) <= @ENDDATE)
      or (@STARTDATE is not null and @ENDDATE is not null and dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MKTMARKETINGPLANITEM].[STARTDATE]) <= @ENDDATE and (@STARTDATE <= dbo.[UFN_DATE_LATESTFROMFUZZYDATE]([MKTMARKETINGPLANITEM].[ENDDATE]) or [MKTMARKETINGPLANITEM].[ENDDATE] = '00000000'))
    )
    and T.c.value('(CHECKED)[1]', 'bit') = 1
  union
  select
    [MKTMARKETINGPLANITEMTASK].[ID],
    [MKTMARKETINGPLANITEMTASK].[MARKETINGPLANITEMID] as [PARENTID],
    [MKTMARKETINGPLANITEMTASK].[SUBJECT] as [NAME],
    [MKTMARKETINGPLANITEMTASK].[DUEDATE] as [STARTDATE],
    dbo.[UFN_DATE_LATESTFROMFUZZYDATE]([MKTMARKETINGPLANITEMTASK].[DUEDATE]) as [ENDDATE],
    -16777216 as [BACKCOLOR], -- black

    1 as [TYPECODE]
  from dbo.[MKTMARKETINGPLANITEMTASK]
  inner join dbo.[MKTMARKETINGPLANITEM] on [MKTMARKETINGPLANITEM].[ID] = [MKTMARKETINGPLANITEMTASK].[MARKETINGPLANITEMID]
  inner join @PLANITEMS.nodes('/PLANITEMS/ITEM') T(c) on [MKTMARKETINGPLANITEM].[ID] = T.c.value('(ID)[1]', 'uniqueidentifier')
  where
    (
      (@STARTDATE is null and @ENDDATE is null)
      or (@STARTDATE is not null and @ENDDATE is null and dbo.[UFN_DATE_LATESTFROMFUZZYDATE]([MKTMARKETINGPLANITEMTASK].[DUEDATE]) >= @STARTDATE)
      or (@STARTDATE is null and @ENDDATE is not null and dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MKTMARKETINGPLANITEMTASK].[DUEDATE]) <= @ENDDATE)
      or (@STARTDATE is not null and @ENDDATE is not null and dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MKTMARKETINGPLANITEMTASK].[DUEDATE]) between @STARTDATE and @ENDDATE)
    )
    and @INCLUDETASKS = 1
    and T.c.value('(CHECKED)[1]', 'bit') = 1
  union
  select 
    [MKTMARKETINGPLANITEM].[ID],
    [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID] as [PARENTID],
    [MKTMARKETINGPLANITEM].[NAME] + ' - Mail date' as [NAME],
    [MKTMARKETINGPLANITEM].[MAILDATE] as [STARTDATE],
    [MKTMARKETINGPLANITEM].[MAILDATE] as [ENDDATE],
    [MKTMARKETINGPLANITEMTEMPLATEITEM].[BACKCOLOR],
    0 as [TYPECODE]
  from dbo.[MKTMARKETINGPLANITEM]
  left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] on ([MKTMARKETINGPLANITEM].[MARKETINGPLANID] = [MKTMARKETINGPLANITEMTEMPLATEITEM].[MARKETINGPLANID] and [MKTMARKETINGPLANITEM].[LEVEL] = [MKTMARKETINGPLANITEMTEMPLATEITEM].[LEVEL])
  inner join @PLANITEMS.nodes('/PLANITEMS/ITEM') T(c) on ([MKTMARKETINGPLANITEM].[ID] = T.c.value('(ID)[1]', 'uniqueidentifier'))
  where
    (
      (
       (@STARTDATE is null and @ENDDATE is null)
        or (@STARTDATE is not null and @ENDDATE is null and [MAILDATE] >= @STARTDATE)
        or (@STARTDATE is null and @ENDDATE is not null and [MAILDATE] <= @ENDDATE)
        or (@STARTDATE is not null and @ENDDATE is not null and [MAILDATE] between @STARTDATE and @ENDDATE)
      )
      and [MKTMARKETINGPLANITEM].[MAILDATE] is not null
    )
    and T.c.value('(CHECKED)[1]', 'bit') = 1