USP_DATALIST_MKTSEED

Returns a list of all seeds.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@INACTIVE	bit	IN	Show inactive seeds
@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.
@INACTIVEFILTER	bit	IN

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTSEED]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @INACTIVE bit = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @INACTIVEFILTER bit = 0
)
as
  set nocount on;

  if @INACTIVE is null set @INACTIVE = 0;

  -- To maintain backwards compatibility, override the inactivefilter filter if INACTIVE is set

  if @INACTIVE = 1
    set @INACTIVEFILTER = null;

  select
    [MKTSEED].[ID],
    case when [MKTSEED].[INACTIVE] = 1 then 0 else 1 end as [ACTIVE],
    [TITLECODE].[DESCRIPTION] as [TITLE],
    [MKTSEED].[FIRSTNAME],
    [MKTSEED].[MIDDLENAME],
    [MKTSEED].[LASTNAME],
    [SUFFIXCODE].[DESCRIPTION] as [SUFFIX],
    [COUNTRY].[ID] as [COUNTRYID],
    [COUNTRY].[ABBREVIATION] as [COUNTRY],
    [MKTSEED].[ADDRESSBLOCK],
    [MKTSEED].[CITY],
    [STATE].[ID] as [STATEID],
    [STATE].[ABBREVIATION] as [STATE],
    [MKTSEED].[POSTCODE],
    [MKTSEED].[CART],
    [MKTSEED].[DPC],
    [MKTSEED].[LOT],
    [MKTSEED].[PHONENUMBER],
    [MKTSEED].[EMAILADDRESS],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEED].[SITEID]) as [SITE]
  from dbo.[MKTSEED]
  left outer join dbo.[TITLECODE] on [MKTSEED].[TITLECODEID] = [TITLECODE].[ID]
  left outer join dbo.[SUFFIXCODE] on [MKTSEED].[SUFFIXCODEID] = [SUFFIXCODE].[ID]
  left outer join dbo.[COUNTRY] on [MKTSEED].[COUNTRYID] = [COUNTRY].[ID]
  left outer join dbo.[STATE] on [MKTSEED].[STATEID] = [STATE].[ID]
  where 
    (@INACTIVEFILTER is null 
      or (@INACTIVEFILTER = 0 and [MKTSEED].[INACTIVE] = 0)
      or (@INACTIVEFILTER = 1 and [MKTSEED].[INACTIVE] = 1))
    and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTSEED].[SITEID] or (SITEID is null and [MKTSEED].[SITEID] is null)))
    and (@SITEFILTERMODE = 0 or [MKTSEED].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  order by [MKTSEED].[LASTNAME], [MKTSEED].[FIRSTNAME], [MKTSEED].[MIDDLENAME];

  return 0;