USP_DATALIST_MKTVENDORLIST

Displays a list of all lists for a vendor.

Parameters

Parameter	Parameter Type	Mode	Description
@VENDORID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@LISTCATEGORYCODEID	uniqueidentifier	IN	Category
@ISINACTIVE	int	IN	Status
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN	Sites selected

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTVENDORLIST]
(
  @VENDORID uniqueidentifier,
  @LISTCATEGORYCODEID uniqueidentifier = null, 
  @ISINACTIVE int = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null
)
as 
  set nocount on;

  select
    [MKTLIST].[ID],
    [MKTLIST].[NAME],
    [MKTLIST].[DESCRIPTION],
    [MKTLIST].[CODE],
    [MKTLIST].[LISTCATEGORYCODEID],
    [MKTLISTCATEGORYCODE].[DESCRIPTION],
    [QUERYVIEWCATALOG].[DISPLAYNAME],
    [MKTLIST].[ISINACTIVE],
    (case when [MKTLIST].[ISINACTIVE] = 1 then 'RES:x_16' else 'RES:check' end) as [IMAGEKEY],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTLIST].[SITEID]) as [SITE]
  from dbo.[MKTLIST]
  left outer join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTLIST].[VENDORID]
  left outer join dbo.[MKTLISTCATEGORYCODE] on [MKTLISTCATEGORYCODE].[ID] = [MKTLIST].[LISTCATEGORYCODEID]
  left outer join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTLIST].[RECORDSOURCEID]
  where [MKTLIST].[VENDORID] = @VENDORID
  and (@LISTCATEGORYCODEID is null or [MKTLIST].[LISTCATEGORYCODEID] = @LISTCATEGORYCODEID)
  and (@ISINACTIVE is null or [MKTLIST].[ISINACTIVE] = @ISINACTIVE)
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTLIST].[SITEID] or (SITEID is null and [MKTLIST].[SITEID] is null)))
  and (@SITEFILTERMODE = 0 or [MKTLIST].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  order by [MKTLIST].[NAME];

  return 0;