USP_DUPLICATESEARCH_PROSPECTSEARCHADDINDIVIDUAL

Parameters

Parameter	Parameter Type	Mode
@LASTNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@ADDRESS_POSTCODE	nvarchar(12)	IN
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN
@MAXROWS	smallint	IN
@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
@CURRENTAPPUSERID	uniqueidentifier	IN
@CONSTITUENTDUPLICATEMATCHINGENABLED	bit	IN

Definition

Copy



                CREATE procedure dbo.USP_DUPLICATESEARCH_PROSPECTSEARCHADDINDIVIDUAL (
                    @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_ADDRESSTYPECODEID uniqueidentifier = null,
          @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
          @PHONE_PHONETYPECODEID uniqueidentifier = null,
          @CURRENTAPPUSERID uniqueidentifier = null,
          @CONSTITUENTDUPLICATEMATCHINGENABLED bit = 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);

                    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
          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

                end