USP_DATALIST_MKTSPONSORSHIPMAILINGACTIVATEPROCESS

A datalist of sponsorship efforts that are ready to be activated.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ACTIVESTATUS	bit	IN	Status
@MAILDATEFROM	datetime	IN	Mail date from
@MAILDATETO	datetime	IN	To
@SHOWUNSCHEDULED	bit	IN	Include sponsorship efforts with no mail date
@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.
@SEGMENTATIONID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTSPONSORSHIPMAILINGACTIVATEPROCESS]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @ACTIVESTATUS bit = 0,
  @MAILDATEFROM datetime = null,
  @MAILDATETO datetime = null,
  @SHOWUNSCHEDULED bit = 1,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @SEGMENTATIONID uniqueidentifier = null
)
as
  set nocount on

  declare @CURRENTDATE as datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  if @SHOWUNSCHEDULED is null
    set @SHOWUNSCHEDULED = 1;

  select
    [MKTSEGMENTATIONACTIVATEPROCESS].[ID],
    [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID],
    [MKTSPONSORSHIPMAILINGTEMPLATE].[NAME] [SPONSORSHIPPROCESSNAME],
    [MKTSEGMENTATION].[DATEADDED] as [PROCESSDATE],
    [MKTSEGMENTATION].[IDINTEGER],
    [MKTSEGMENTATION].[CODE],
    [MKTSEGMENTATION].[NAME] as [MAILING],
    [MKTSEGMENTATION].[MAILDATE],
    [MKTSEGMENTATION].[ACTIVE],
    [MKTSEGMENTATION].[DESCRIPTION],
    cast((case when exists(select top 1 1 from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) then 1 else 0 end) as bit) as [CRITERIADEFINED],
    case
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      when cast([MKTSEGMENTATION].[DATEADDED] as date) = dateadd(day, 0, @CURRENTDATE) then 'Today'
      when cast([MKTSEGMENTATION].[DATEADDED] as date) = dateadd(day, -1, @CURRENTDATE) then 'Yesterday'
      when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -6, @CURRENTDATE) then 'Last week'             
      when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -13, @CURRENTDATE) then 'Two weeks ago'
      when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -20, @CURRENTDATE) then 'Three weeks ago'
      when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -30, @CURRENTDATE) then 'Last month'
      else 'Older'
    end [PROCESSDATETEXT],
    case
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      when cast([MKTSEGMENTATION].[DATEADDED] as date) = dateadd(day, 0, @CURRENTDATE) then 0
      when cast([MKTSEGMENTATION].[DATEADDED] as date) = dateadd(day, -1, @CURRENTDATE) then 1
      when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -6, @CURRENTDATE) then 2             
      when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -13, @CURRENTDATE) then 3
      when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -20, @CURRENTDATE) then 4
      when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -30, @CURRENTDATE) then 5
      else 6
    end [PROCESSDATEORDER]
  from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID]
  inner join dbo.[MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION] on [MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left outer join dbo.[MKTSPONSORSHIPMAILINGPROCESSSTATUS] on [MKTSPONSORSHIPMAILINGPROCESSSTATUS].[ID] = [MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION].[SPONSORSHIPMAILINGPROCESSSTATUSID]
  left outer join dbo.[MKTSPONSORSHIPMAILINGPROCESS] on [MKTSPONSORSHIPMAILINGPROCESS].[ID] = [MKTSPONSORSHIPMAILINGPROCESSSTATUS].[PARAMETERSETID]
  left outer join dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] on [MKTSPONSORSHIPMAILINGPROCESS].[SPONSORSHIPMAILINGTEMPLATEID] = [MKTSPONSORSHIPMAILINGTEMPLATE].[ID]
  where exists (select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID])
  and (@ACTIVESTATUS is null or [MKTSEGMENTATION].[ACTIVE] = @ACTIVESTATUS)
  and (@MAILDATEFROM is null or ([MKTSEGMENTATION].[MAILDATE] is not null and [MKTSEGMENTATION].[MAILDATE] >= @MAILDATEFROM) or (@SHOWUNSCHEDULED = 1 and [MKTSEGMENTATION].[MAILDATE] is null))
  and (@MAILDATETO is null or ([MKTSEGMENTATION].[MAILDATE] is not null and [MKTSEGMENTATION].[MAILDATE] <= @MAILDATETO) or (@SHOWUNSCHEDULED = 1 and [MKTSEGMENTATION].[MAILDATE] is null))
  and ((@SHOWUNSCHEDULED = 1) or (@SHOWUNSCHEDULED = 0 and [MKTSEGMENTATION].[MAILDATE] is not null))          
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTSEGMENTATION].[SITEID] or (SITEID is null and [MKTSEGMENTATION].[SITEID] is null)))
  and (@SITEFILTERMODE = 0 or [MKTSEGMENTATION].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  order by [MKTSEGMENTATION].[NAME];

  return 0;