USP_DATALIST_MKTSEGMENTATIONPASSIVE

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

  if @SHOWUNSCHEDULED is null
    set @SHOWUNSCHEDULED = 1;

  select
    [MKTSEGMENTATION].[ID],
    [MKTSEGMENTATION].[IDINTEGER],
    [MKTSEGMENTATION].[CODE],
    [MKTSEGMENTATION].[NAME],
    [MKTSEGMENTATION].[DESCRIPTION],
    [MKTSEGMENTATION].[MAILDATE],
    [SITE].[NAME] as [SITE]
  from dbo.[MKTSEGMENTATION]
  left outer join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENTATION].[SITEID]
  where (@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 [MKTSEGMENTATION].[PARENTSEGMENTATIONID] is null
  and [MKTSEGMENTATION].[MAILINGTYPECODE] = 4       -- public media mailings only

  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;