USP_SIMPLEDATALIST_MKTVENDORCONTACTRELATIONSHIPS

Returns a list of all of the different contact relationship types in use by vendors.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN
@SECURITYFEATURETYPE	tinyint	IN

Definition

Copy



create procedure dbo.[USP_SIMPLEDATALIST_MKTVENDORCONTACTRELATIONSHIPS]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SECURITYFEATUREID uniqueidentifier,
  @SECURITYFEATURETYPE tinyint
)
as
  set nocount on;

  select distinct
    [RELATIONSHIPTYPECODE].[ID] as [VALUE], 
    [RELATIONSHIPTYPECODE].[DESCRIPTION] as [LABEL] 
  from dbo.[VENDOR]
  inner join dbo.[RELATIONSHIP] on [RELATIONSHIP].[RELATIONSHIPCONSTITUENTID] = [VENDOR].[ID]
  inner join dbo.[RELATIONSHIPTYPECODE] on [RELATIONSHIPTYPECODE].[ID] = [RELATIONSHIP].[RECIPROCALTYPECODEID]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [RELATIONSHIP].[RECIPROCALCONSTITUENTID]
  where [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] = [VENDOR].[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 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))))  
  order by [RELATIONSHIPTYPECODE].[DESCRIPTION];

  return 0;