USP_DATALIST_APPEALDIRECTMARKETINGMAILING

Displays a list of direct marketing efforts associated with an appeal.

Parameters

Parameter	Parameter Type	Mode	Description
@APPEALID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@ACTIVESTATUS	bit	IN	Status
@MAILDATEFROM	datetime	IN	Mail date from
@MAILDATETO	datetime	IN	To
@SHOWUNSCHEDULED	bit	IN	Include mailings with no mail date
@DATERANGE	tinyint	IN
@FROM	datetime	IN
@TO	datetime	IN

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_APPEALDIRECTMARKETINGMAILING]
(
  @APPEALID uniqueidentifier,
  @ACTIVESTATUS bit = null,
  @MAILDATEFROM datetime = null,
  @MAILDATETO datetime = null,
  @SHOWUNSCHEDULED bit = 1,
  @DATERANGE tinyint = null,
  @FROM datetime = null,
  @TO datetime = null
)
as
  set nocount on;

  select
    [MKTSEGMENTATION].[ID],
    [MKTSEGMENTATION].[CODE],
    [MKTSEGMENTATION].[NAME],
    [MKTSEGMENTATION].[DESCRIPTION],
    [MKTSEGMENTATION].[MAILDATE],
    [MKTSEGMENTATION].[ACTIVE],
    [MKTSEGMENTATION].[MAILINGTYPECODE]
  from dbo.[MKTSEGMENTATIONACTIVATE]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[APPEALMAILING] on [APPEALMAILING].[ID] = [MKTSEGMENTATION].[ID]
  where [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] = cast(@APPEALID as nvarchar(36))
  and [APPEALMAILING].[ID] is null
  and ([MKTSEGMENTATION].[PARENTSEGMENTATIONID] is null)
  and (@ACTIVESTATUS is null or @ACTIVESTATUS = [MKTSEGMENTATION].[ACTIVE])
  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))
  order by [MKTSEGMENTATION].[MAILDATE] desc, [MKTSEGMENTATION].[ACTIVATEDATE] desc, [MKTSEGMENTATION].[DATEADDED] desc, [MKTSEGMENTATION].[NAME] asc;

  return 0;