USP_DATALIST_MKTVENDOR2

Displays a list of all vendors

Parameters

Parameter	Parameter Type	Mode	Description
@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.
@SERVICETYPECODE	tinyint	IN	Vendors of
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN	Sites selected

Definition

Copy



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

  select 
    [VENDOR].[ID],
    [CONSTITUENT].[NAME],
    [VENDOR].[DESCRIPTION],
    (case when [CONSTITUENT].[ISINACTIVE] = 1 then 'Inactive' else 'Active' end) as [STATUS],
    convert(bit, case when exists (select top 1 1 from dbo.[MKTVENDORSERVICETYPE] where [VENDORID] = [VENDOR].[ID] and [SERVICETYPECODE] = 1) then 1 else 0 end) as [PROVIDESCREATIVES],
    convert(bit, case when exists (select top 1 1 from dbo.[MKTVENDORSERVICETYPE] where [VENDORID] = [VENDOR].[ID] and [SERVICETYPECODE] = 2) then 1 else 0 end) as [PROVIDESLISTS],
    convert(bit, case when exists (select top 1 1 from dbo.[MKTVENDORSERVICETYPE] where [VENDORID] = [VENDOR].[ID] and [SERVICETYPECODE] = 3) then 1 else 0 end) as [PROVIDESPUBLICMEDIA],
    cast(0 as bit) as [USERWILLHAVEERRORONVENDORPAGE], -- unused field, leave in for backwards compatibility

    case when dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1
         then 1
         else
           case when dbo.[UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE](@CURRENTAPPUSERID, '5DF92861-001A-4B67-800F-D598F9CF334B') = 1 -- ContactInformation.DataList.xml

                then
                  case when exists (select top 1 1 
                                    from dbo.[UFN_SITEID_MAPFROM_CONSTITUENTID]([VENDOR].[ID])
                                    where dbo.[UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORSITE](@CURRENTAPPUSERID, '5DF92861-001A-4B67-800F-D598F9CF334B', [SITEID]) = 1) -- ContactInformation.DataList.xml

                       then 1
                       else 0
                  end
                else 0
           end
    end as [USERHASFULLRIGHTSTOVENDOR],
    stuff((select ', ' + [SITE].[NAME]
        from dbo.[SITE]
        inner join dbo.[CONSTITUENTSITE] on [CONSTITUENTSITE].[SITEID] = [SITE].[ID]
        where [CONSTITUENTSITE].[CONSTITUENTID] = [CONSTITUENT].[ID]
        order by [SITE].[NAME]
        for xml path(''), type
        ).value('.', 'varchar(max)') , 1, 2, '') as [SITES]
  from dbo.[VENDOR]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [VENDOR].[ID]
  where (@SERVICETYPECODE is null or exists (select top 1 1 from dbo.[MKTVENDORSERVICETYPE] where [VENDORID] = [VENDOR].[ID] and [SERVICETYPECODE] = @SERVICETYPECODE))
  and exists (select top 1 1
                from dbo.[CONSTITUENT] as [C]
                left join dbo.[CONSTITUENTSITE] on [CONSTITUENTSITE].[CONSTITUENTID] = [C].[ID]
                where [C].[ID] = [CONSTITUENT].[ID]
                and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[CONSTITUENTSITE].[SITEID] or (SITEID is null and [CONSTITUENTSITE].[SITEID] is null))))
  and (@SITEFILTERMODE = 0
       or exists (select top 1 1 from dbo.[CONSTITUENTSITE] with (nolock)
                  inner join dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) as [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID]
                  where [CONSTITUENTSITE].[CONSTITUENTID] = [CONSTITUENT].[ID]))
  order by [CONSTITUENT].[KEYNAME];

  return 0;