UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_3

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@TITLECODEID	uniqueidentifier	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@KEYNAME	nvarchar(100)	IN
@SUFFIXCODEID	uniqueidentifier	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@POSTCODE	nvarchar(12)	IN
@COUNTRYID	uniqueidentifier	IN
@ISORGANIZATION	bit	IN
@ISGROUP	bit	IN
@LOOKUPID	nvarchar(100)	IN
@ALTERNATELOOKUPIDS	xml	IN
@EMAILADDRESS	nvarchar(100)	IN
@PHONENUMBER	nvarchar(100)	IN
@OVERALLMATCHTHRESHOLD	tinyint	IN
@AUTOMATCHTHRESHOLD	tinyint	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@CONSTITUENTDUPLICATEMATCHINGENABLED	bit	IN

Definition

Copy



CREATE function dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_3 (
  @TITLECODEID uniqueidentifier,
  @FIRSTNAME nvarchar(50),
  @MIDDLENAME nvarchar(50),
  @KEYNAME nvarchar(100),
  @SUFFIXCODEID uniqueidentifier,
  @ADDRESSBLOCK nvarchar(150),
  @POSTCODE nvarchar(12),
  @COUNTRYID uniqueidentifier,
  @ISORGANIZATION bit,
  @ISGROUP bit,
  @LOOKUPID nvarchar(100),
  @ALTERNATELOOKUPIDS xml,
  @EMAILADDRESS nvarchar(100),
  @PHONENUMBER nvarchar(100),
  @OVERALLMATCHTHRESHOLD tinyint,
  @AUTOMATCHTHRESHOLD tinyint,
  @CURRENTAPPUSERID uniqueidentifier,
  @CONSTITUENTDUPLICATEMATCHINGENABLED bit = 0
)
returns @MATCHES table (
  CONSTITUENTID uniqueidentifier,
  ADDRESSID uniqueidentifier,
  EMAILADDRESSID uniqueidentifier,
  PHONEID uniqueidentifier,
  MATCHPERCENTAGE tinyint,
  STREETNUMBERRESULT tinyint,
  STREETNAMERESULT tinyint,
  POSTCODERESULT tinyint
)
as
begin
    -- use the given threshold if provided, otherwise fetch it from the table

  if @OVERALLMATCHTHRESHOLD is null or @AUTOMATCHTHRESHOLD is null
      select top(1)
          @OVERALLMATCHTHRESHOLD = isnull(@OVERALLMATCHTHRESHOLD,OVERALLMATCHTHRESHOLD),
      @AUTOMATCHTHRESHOLD = isnull(@AUTOMATCHTHRESHOLD,AUTOMATCHTHRESHOLD)
      from dbo.CONSTITUENTDUPLICATESEARCHSETTINGS
    where TRANSACTIONTYPE = 'Default Criteria'
      order by DATECHANGED;

  if @AUTOMATCHTHRESHOLD > 0 or @OVERALLMATCHTHRESHOLD > 0
  begin
      insert into @MATCHES
      select CONSTITUENTID, ADDRESSID, EMAILADDRESSID, PHONEID, COMPOSITESCORE, STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT
      from dbo.UFN_FINDCONSTITUENTMATCHES_3(@TITLECODEID, @FIRSTNAME, @MIDDLENAME, @KEYNAME, @SUFFIXCODEID, @ADDRESSBLOCK, @POSTCODE, @COUNTRYID, @ISORGANIZATION, @ISGROUP, @LOOKUPID, @ALTERNATELOOKUPIDS, @EMAILADDRESS, @PHONENUMBER, @OVERALLMATCHTHRESHOLD, @AUTOMATCHTHRESHOLD) as MATCHES
      where COMPOSITESCORE >= isnull(nullif(@OVERALLMATCHTHRESHOLD, 0), @AUTOMATCHTHRESHOLD)
        and (    @CONSTITUENTDUPLICATEMATCHINGENABLED = 0 
            or exists(select SITEID from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(MATCHES.CONSTITUENTID) as CONSTITUENTSITES where dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, CONSTITUENTSITES.SITEID) = 1)            
            or @CURRENTAPPUSERID is null);
  end
  return
end