UFN_CONSTITUENT_GETDUPLICATECANDIDATES_2

Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@KEYNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@ADDRESSES	xml	IN
@MAIDENNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@PHONES	xml	IN
@EMAILADDRESSES	xml	IN
@GENDERCODE	tinyint	IN
@EMPLOYER	nvarchar(100)	IN
@TRANSACTIONTYPE	nvarchar(100)	IN
Definition

Copy


      create function [dbo].[UFN_CONSTITUENT_GETDUPLICATECANDIDATES_2](
                @KEYNAME nvarchar(100),
                @FIRSTNAME nvarchar(50) = '',
                @ADDRESSES xml = null,
                @MAIDENNAME nvarchar(50) = '',
                @MIDDLENAME nvarchar(50) = '',
                @PHONES xml = null,
                @EMAILADDRESSES xml = null,
                @GENDERCODE tinyint = null,
                @EMPLOYER nvarchar(100) = '',
                @TRANSACTIONTYPE nvarchar(100) = null                
            ) 
            returns @DUPLICATECANDIDATES table(
                CONSTITUENTID uniqueidentifier,
                ADDRESSID uniqueidentifier,
                MATCHPERCENTAGE numeric(5, 2)
            ) as
            begin

                declare @DUPLICATESEARCHSETTINGID uniqueidentifier

                select top(1) 
                    @DUPLICATESEARCHSETTINGID = ID 
                from 
                    dbo.CONSTITUENTDUPLICATESEARCHSETTINGS
                where 
                    TRANSACTIONTYPE = @TRANSACTIONTYPE
                order by
                    DATECHANGED;

                declare @POSTCODE nvarchar(12)
                declare @ADDRESSBLOCK nvarchar(150)
                declare @CITY nvarchar(100)
                declare @STATEID uniqueidentifier
                declare @COUNTRYID uniqueidentifier

                select
                    @POSTCODE = POSTCODE,
                    @ADDRESSBLOCK = ADDRESSBLOCK,
                    @CITY = CITY,
                    @STATEID = @STATEID,
                    @COUNTRYID = @COUNTRYID
                from dbo.UFN_BATCHCONSTITUENTUPDATE_GETADDRESSES_FROMITEMLISTXML(@ADDRESSES)

                declare @PHONENUMBER nvarchar(50);

                select
                    @PHONENUMBER = NUMBER
                from dbo.UFN_BATCHCONSTITUENTUPDATE_GETPHONES_FROMITEMLISTXML(@PHONES)

                declare @EMAILADDRESS nvarchar(100);

                select
                    @EMAILADDRESS = EMAILADDRESS
                from dbo.UFN_BATCHCONSTITUENTUPDATE_GETEMAILADDRESSES_FROMITEMLISTXML(@EMAILADDRESSES)

                --Keep this check

                --keyname is used to filter on keyname soundex and postcode is used to 'like' match

                --on the table expression to find candidates to pass into the fuzzy engine.

                if @KEYNAME is null or len(@KEYNAME) = 0 or @POSTCODE is null or len(@POSTCODE) = 0
                    return        

                insert into @DUPLICATECANDIDATES (
                    CONSTITUENTID,
                    ADDRESSID,
                    MATCHPERCENTAGE
                )
                select 
                    CONSTITUENTID,
                    ADDRESSID,
                    MATCHPERCENTAGE
                from
                    dbo.UFN_CONSTITUENT_GETFUZZYDUPLICATES_2 (
                        @KEYNAME,
                        @FIRSTNAME,
                        @POSTCODE,
                        @ADDRESSBLOCK,
                        @MAIDENNAME,
                        @MIDDLENAME,
                        @PHONENUMBER,
                        @EMAILADDRESS,
                        @GENDERCODE,
                        @CITY,
                        @STATEID,
                        @COUNTRYID,
                        @EMPLOYER,
                        @DUPLICATESEARCHSETTINGID
                    )

                return;
            end