USP_DUPLICATESEARCH_MKTVENDORCONTACT

This provides the ability to search for duplicates on vendor contact add forms.

Parameters

Parameter	Parameter Type	Mode	Description
@CONTACT_LASTNAME	nvarchar(100)	IN	Last name
@CONTACT_FIRSTNAME	nvarchar(50)	IN	First name
@CONTACT_ADDRESS_POSTCODE	nvarchar(12)	IN	ZIP
@CONTACT_ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Address
@CONTACTID	uniqueidentifier	IN	Contact
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



CREATE procedure dbo.[USP_DUPLICATESEARCH_MKTVENDORCONTACT] (
  @CONTACT_LASTNAME nvarchar(100) = '',
  @CONTACT_FIRSTNAME nvarchar(50) = '',
  @CONTACT_ADDRESS_POSTCODE nvarchar(12) = '',
  @CONTACT_ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
  @CONTACTID uniqueidentifier = null,
  @MAXROWS smallint = 100
) 
as 
  set nocount on;

  select top(@MAXROWS)
    [C].[ID],
    [DC].[MATCHPERCENTAGE],
    [C].[NAME],
    [A].[ADDRESSBLOCK],
    [A].[CITY],
    [S].[DESCRIPTION],
    [A].[POSTCODE],
    [C].[LOOKUPID]
  from
    dbo.[UFN_CONSTITUENT_GETDUPLICATECANDIDATES] (
      @CONTACT_LASTNAME,
      @CONTACT_FIRSTNAME,
      @CONTACT_ADDRESS_POSTCODE,
      @CONTACT_ADDRESS_ADDRESSBLOCK
    ) as [DC]
  left outer join
    dbo.[CONSTITUENT] as [C] on [C].[ID] = [DC].[CONSTITUENTID]
  left outer join
    dbo.[ADDRESS] as [A] on [A].[ID] = [DC].[ADDRESSID]
  left outer join
    dbo.[STATE] as [S] on [S].[ID] = [A].[STATEID]
  left outer join
    dbo.[VENDOR] on [VENDOR].[ID] = [C].[ID]
  where
    -- contact ID indicates that they've selected an existing contact, so no duplicate search is required

    @CONTACTID is null
  and
    [C].[ISORGANIZATION] = 0
  and
    [VENDOR].[ID] is null
  order by
    [DC].[MATCHPERCENTAGE] desc

  return 0;