USP_DUPLICATESEARCH_INDIVIDUALSPOUSEBUSINESS

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

Parameters

Parameter	Parameter Type	Mode	Description
@LASTNAME	nvarchar(100)	IN	Last name
@FIRSTNAME	nvarchar(50)	IN	First name
@ADDRESS_POSTCODE	nvarchar(12)	IN	ZIP
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Address
@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
@ADDRESS_STATEID	uniqueidentifier	IN
@ADDRESS_CITY	nvarchar(50)	IN
@BIRTHDATE	UDT_FUZZYDATE	IN
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN
@PHONE_NUMBER	nvarchar(100)	IN
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@PHONE_PHONETYPECODEID	uniqueidentifier	IN
@MAIDENNAME	nvarchar(100)	IN
@NICKNAME	nvarchar(50)	IN
@GENDERCODE	tinyint	IN
@DECEASED	bit	IN
@DECEASEDDATE	UDT_FUZZYDATE	IN
@GIVESANONYMOUSLY	bit	IN
@MARITALSTATUSCODEID	uniqueidentifier	IN
@WEBADDRESS	UDT_WEBADDRESS	IN
@ADDRESS_HISTORICALSTARTDATE	date	INOUT
@ADDRESS_HISTORICALENDDATE	date	INOUT
@ADDRESS_DONOTMAIL	bit	INOUT
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	INOUT
@ADDRESS_STARTDATE	UDT_MONTHDAY	INOUT
@ADDRESS_ENDDATE	UDT_MONTHDAY	INOUT
@ADDRESS_DPC	nvarchar(max)	INOUT
@ADDRESS_CART	nvarchar(max)	INOUT
@ADDRESS_LOT	nvarchar(5)	INOUT
@ADDRESS_INFOSOURCECODEID	uniqueidentifier	INOUT
@ADDRESS_INFOSOURCECOMMENTS	nvarchar(256)	INOUT
@ADDRESS_COUNTYCODEID	uniqueidentifier	INOUT
@ADDRESS_REGIONCODEID	uniqueidentifier	INOUT
@ADDRESS_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT
@ADDRESS_STATEHOUSEDISTRICTCODEID	uniqueidentifier	INOUT
@ADDRESS_STATESENATEDISTRICTCODEID	uniqueidentifier	INOUT
@ADDRESS_LOCALPRECINCTCODEID	uniqueidentifier	INOUT
@ADDRESS_CERTIFICATIONDATA	int	INOUT
@ADDRESS_LASTVALIDATIONATTEMPTDATE	date	INOUT
@ADDRESS_OMITFROMVALIDATION	bit	INOUT
@ADDRESS_VALIDATIONMESSAGE	nvarchar(200)	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@CONSTITUENTDUPLICATEMATCHINGENABLED	bit	IN
@GENDERCODEID	uniqueidentifier	IN

Definition

Copy



    CREATE procedure dbo.USP_DUPLICATESEARCH_INDIVIDUALSPOUSEBUSINESS (
        @LASTNAME nvarchar(100) = '',
        @FIRSTNAME nvarchar(50) = '',
        @ADDRESS_POSTCODE nvarchar(12) = '',
        @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
        @MAXROWS smallint = 100,
        @MIDDLENAME nvarchar(50) = null,
        @TITLECODEID uniqueidentifier = null,
        @SUFFIXCODEID uniqueidentifier = null,
        @ADDRESS_COUNTRYID uniqueidentifier  = null,
        @ADDRESS_STATEID uniqueidentifier  = null,
        @ADDRESS_CITY nvarchar(50)  = null,
        @BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
        @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
        @PHONE_NUMBER nvarchar(100) = '',
        --Address type code is not used in the search, but must be included

        --here to give it a value in the UI code.

        @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
        @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
        @PHONE_PHONETYPECODEID uniqueidentifier = null,
        @MAIDENNAME nvarchar(100) = '',
        @NICKNAME nvarchar(50) = '',
        @GENDERCODE tinyint = 0,
        @DECEASED bit = 0,
        @DECEASEDDATE dbo.UDT_FUZZYDATE = '00000000',
        @GIVESANONYMOUSLY bit = 0,
        @MARITALSTATUSCODEID uniqueidentifier = null,
        @WEBADDRESS dbo.UDT_WEBADDRESS = '',
        @ADDRESS_HISTORICALSTARTDATE date = null output,
        @ADDRESS_HISTORICALENDDATE date = null output,
        @ADDRESS_DONOTMAIL bit = null output,
        @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null output,
        @ADDRESS_STARTDATE dbo.UDT_MONTHDAY = null output,
        @ADDRESS_ENDDATE dbo.UDT_MONTHDAY = null output,
        @ADDRESS_DPC nvarchar(max) = null output,
        @ADDRESS_CART nvarchar(max) = null output,
        @ADDRESS_LOT nvarchar(5) = null output,
        @ADDRESS_INFOSOURCECODEID uniqueidentifier = null output,
        @ADDRESS_INFOSOURCECOMMENTS nvarchar(256) = null output,
        @ADDRESS_COUNTYCODEID uniqueidentifier = null output,
        @ADDRESS_REGIONCODEID uniqueidentifier = null output,
        @ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
        @ADDRESS_STATEHOUSEDISTRICTCODEID uniqueidentifier = null output,
        @ADDRESS_STATESENATEDISTRICTCODEID uniqueidentifier = null output,
        @ADDRESS_LOCALPRECINCTCODEID uniqueidentifier = null output,
        @ADDRESS_CERTIFICATIONDATA int = null output,
        @ADDRESS_LASTVALIDATIONATTEMPTDATE date = null output,
        @ADDRESS_OMITFROMVALIDATION bit = null output,
        @ADDRESS_VALIDATIONMESSAGE nvarchar(200) = null output,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @CONSTITUENTDUPLICATEMATCHINGENABLED bit = null,
        @GENDERCODEID uniqueidentifier = null
    ) as begin

        declare @ISADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
        declare @APPUSER_IN_NONRACROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
        declare @APPUSER_IN_NOSECGROUPROLE bit = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
        declare @NULLVALUE uniqueidentifier = (select cast(cast(0 as binary) as uniqueidentifier))

        select top(@MAXROWS)
            C.ID,
            DC.MATCHPERCENTAGE,
            C.NAME,
            A.ADDRESSBLOCK,
            A.CITY,
            S.DESCRIPTION,
            A.POSTCODE,
            C.LOOKUPID,
            A.ID ADDRESSID,
            DC.EMAILADDRESSID,
            DC.PHONEID,
            DC.STREETNUMBERRESULT,
            DC.STREETNAMERESULT,
            DC.POSTCODERESULT,
            case when ADDRESSID is not null then dbo.UFN_BUILDFULLADDRESS(ADDRESSID,a.ADDRESSBLOCK,a.CITY,a.STATEID,a.POSTCODE,a.COUNTRYID) else dbo.UFN_CONSTITUENTDUPLICATESEARCH_DEFAULTADDRESS(C.ID,@ADDRESS_ADDRESSTYPECODEID) end as FULLADDRESS     
            from
            dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_3 (
              @TITLECODEID,
              @FIRSTNAME,
              @MIDDLENAME,
              @LASTNAME,
              @SUFFIXCODEID,
              @ADDRESS_ADDRESSBLOCK,
              @ADDRESS_POSTCODE,
              @ADDRESS_COUNTRYID,
              0,
              0,
              null,
              null,
              @EMAILADDRESS_EMAILADDRESS,
              @PHONE_NUMBER,
              null,
              null,
              @CURRENTAPPUSERID,
              @CONSTITUENTDUPLICATEMATCHINGENABLED 
                ) DC
            left outer join dbo.CONSTITUENT C on C.ID = DC.CONSTITUENTID
            left outer join dbo.ADDRESS A on A.ID = DC.ADDRESSID
            left outer join dbo.STATE S on S.ID = A.STATEID
            left join dbo.PHONE P on DC.PHONEID = P.ID
            left join dbo.EMAILADDRESS E on DC.EMAILADDRESSID = E.ID
            where (@ISADMIN = 1 or @CURRENTAPPUSERID is null or @APPUSER_IN_NONRACROLE = 1 or
                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, DC.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)                           
            order by
                DC.MATCHPERCENTAGE desc,
                case when C.KEYNAME = @LASTNAME then 1 else 2 end, 
                case when isnull(C.FIRSTNAME,'') = isnull(@FIRSTNAME,'') then 1 else 2 end,
                case when isnull(A.ADDRESSTYPECODEID,@NULLVALUE) = isnull(@ADDRESS_ADDRESSTYPECODEID,@NULLVALUE) then 1 else 2 end,
                case when isnull(P.PHONETYPECODEID,@NULLVALUE) = isnull(@PHONE_PHONETYPECODEID,@NULLVALUE) then 1 else 2 end, 
                case when isnull(E.EMAILADDRESSTYPECODEID,@NULLVALUE) = isnull(@EMAILADDRESS_EMAILADDRESSTYPECODEID,@NULLVALUE) then 1 else 2 end,
                case when isnull(A.ADDRESSBLOCK,'') = isnull(@ADDRESS_ADDRESSBLOCK,'') then 1 else 2 end,
                A.ISPRIMARY desc,
                P.ISPRIMARY desc,
                E.ISPRIMARY desc

        end