USP_SEARCHLIST_CONSTITUENTBATCHDUPLICATECONSTITUENT

Search for Duplicate Constituents in Constituent Batch.

Parameters

Parameter	Parameter Type	Mode	Description
@KEYNAME	nvarchar(100)	IN	Last name
@FIRSTNAME	nvarchar(50)	IN	First name
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Address
@ADDRESS_POSTCODE	nvarchar(12)	IN	ZIP
@ISORGANIZATION	tinyint	IN	Is organization
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@MIDDLENAME	nvarchar(50)	IN
@TITLECODEID	uniqueidentifier	IN
@SUFFIXCODEID	uniqueidentifier	IN
@ADDRESS_COUNTRYID	uniqueidentifier	IN
@CUSTOMIDENTIFIER	nvarchar(100)	IN
@PHONES	xml	IN
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN
@ALTERNATELOOKUPIDS	xml	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@CONSTITUENTDUPLICATEMATCHINGENABLED	bit	IN

Definition

Copy



CREATE procedure dbo.USP_SEARCHLIST_CONSTITUENTBATCHDUPLICATECONSTITUENT
(
    @KEYNAME nvarchar(100) = null,
    @FIRSTNAME nvarchar(50) = null,
    @ADDRESS_ADDRESSBLOCK nvarchar(150) = null,
    @ADDRESS_POSTCODE nvarchar(12) = null,
    @ISORGANIZATION tinyint = null,
    @MAXROWS smallint = 500,
    @MIDDLENAME nvarchar(50) = null,
    @TITLECODEID uniqueidentifier = null,
    @SUFFIXCODEID uniqueidentifier = null,
    @ADDRESS_COUNTRYID uniqueidentifier = null,
    @CUSTOMIDENTIFIER nvarchar(100) = null,
    @PHONES xml = null,
    @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null,
    @ALTERNATELOOKUPIDS xml = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CONSTITUENTDUPLICATEMATCHINGENABLED bit = null
)
as
    set nocount on

    declare @PHONENUMBER nvarchar(100);
    declare @KEYNAMEBODY nvarchar(100);
    declare @OVERALLMATCHTHRESHOLD decimal(20,4);

    select top 1 @PHONENUMBER = NUMBER
    from dbo.UFN_CONSTITUENT_GETPHONES_FORUPDATEBATCH_FROMITEMLISTXML(@PHONES)
    order by case when ISPRIMARY = 1 then 1 else 2 end, SEQUENCE

    if @ISORGANIZATION = 1 -- orgs

    begin

        begin try
            exec dbo.USP_PARSE_ORGANIZATION_NAME @KEYNAME, @KEYNAMEBODY output;
        end try
        begin catch
            set @KEYNAMEBODY = @KEYNAME;
        end catch
    end
    else
        set @KEYNAMEBODY = @KEYNAME;

    select top 1
        @OVERALLMATCHTHRESHOLD = OVERALLMATCHTHRESHOLDBATCH
    from dbo.CONSTITUENTDUPLICATESEARCHSETTINGS
    where TRANSACTIONTYPE = 'Default Criteria'
    order by DATECHANGED;

    select top(@MAXROWS)
        CONSTITUENT.ID,
        DUPLICATECANDIDATE.MATCHPERCENTAGE,
        CONSTITUENT.NAME,
        ADDRESS.ADDRESSBLOCK,
        ADDRESS.CITY,
        STATE.ABBREVIATION,
        ADDRESS.POSTCODE,
        CONSTITUENT.LOOKUPID
    from dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_3 (
        @TITLECODEID,
        @FIRSTNAME,
        @MIDDLENAME,
        @KEYNAMEBODY,
        @SUFFIXCODEID,
        @ADDRESS_ADDRESSBLOCK,
        @ADDRESS_POSTCODE,
        @ADDRESS_COUNTRYID,
        case @ISORGANIZATION when 1 then 1 else 0 end,
        case when @ISORGANIZATION in(2,3) then 1 else 0 end,
        @CUSTOMIDENTIFIER,
        @ALTERNATELOOKUPIDS,
        @EMAILADDRESS_EMAILADDRESS,
        @PHONENUMBER,
        @OVERALLMATCHTHRESHOLD,
        0,
        @CURRENTAPPUSERID,
        @CONSTITUENTDUPLICATEMATCHINGENABLED
        ) DUPLICATECANDIDATE
    inner join dbo.CONSTITUENT on DUPLICATECANDIDATE.CONSTITUENTID = CONSTITUENT.ID
    left join dbo.ADDRESS on DUPLICATECANDIDATE.ADDRESSID = ADDRESS.ID
    left join dbo.STATE on ADDRESS.STATEID = STATE.ID
    order by DUPLICATECANDIDATE.MATCHPERCENTAGE desc, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME