USP_DATALIST_MKTVENDORCONTACT

Displays a list of all contacts for a vendor.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@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.
@RECIPROCALTYPECODEID	uniqueidentifier	IN	Relationship
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN	Sites selected

Definition

Copy



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

  select
    [RELATIONSHIP].[ID],
    [RELATIONSHIPTYPECODE].[DESCRIPTION] as [RELATIONSHIP],
    [NAME].[NAME],
    isnull([CONTACTTYPECODE].[DESCRIPTION], '') as [TYPE],
    [RELATIONSHIP].[ISPRIMARYCONTACT],
    dbo.[UFN_RELATIONSHIPJOBINFO_GETCURRENTJOBTITLE]([RELATIONSHIP].[ID]) as [POSITION],
    [RELATIONSHIP].[RECIPROCALCONSTITUENTID] as [CONTACTID],
    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]([CONSTITUENT].[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 [USERHASFULLRIGHTSTOCONTACT],
    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.[RELATIONSHIP]
  inner join dbo.[RELATIONSHIPTYPECODE] on [RELATIONSHIPTYPECODE].[ID] = [RELATIONSHIP].[RECIPROCALTYPECODEID]
  left outer join dbo.[CONTACTTYPECODE] on [CONTACTTYPECODE].[ID] = [RELATIONSHIP].[CONTACTTYPECODEID]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [RELATIONSHIP].[RECIPROCALCONSTITUENTID]
  outer apply dbo.[UFN_CONSTITUENT_DISPLAYNAME]([CONSTITUENT].[ID]) as [NAME]
  where [RELATIONSHIP].[RELATIONSHIPCONSTITUENTID] = @ID
  and (@RECIPROCALTYPECODEID is null or [RELATIONSHIP].[RECIPROCALTYPECODEID] = @RECIPROCALTYPECODEID)
  and [RELATIONSHIP].[ISCONTACT] = 1
  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], [CONSTITUENT].[FIRSTNAME], [CONSTITUENT].[MIDDLENAME];

  return 0;