USP_DUPLICATESEARCH_PROSPECTSEARCHPOPUPORGANIZATION

Parameters

Parameter	Parameter Type	Mode
@NAME	nvarchar(100)	IN
@ADDRESS_POSTCODE	nvarchar(12)	IN
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN
@MAXROWS	smallint	IN
@ADDRESS_COUNTRYID	uniqueidentifier	IN
@ADDRESS_STATEID	uniqueidentifier	IN
@ADDRESS_CITY	nvarchar(50)	IN
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN
@PHONE_NUMBER	nvarchar(100)	IN
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@PHONE_PHONETYPECODEID	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
@DPC	nvarchar(max)	INOUT
@CART	nvarchar(max)	INOUT
@LOT	nvarchar(5)	INOUT
@INFOSOURCECODEID	uniqueidentifier	INOUT
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT
@COUNTYCODEID	uniqueidentifier	INOUT
@REGIONCODEID	uniqueidentifier	INOUT
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	INOUT
@STATESENATEDISTRICTCODEID	uniqueidentifier	INOUT
@LOCALPRECINCTCODEID	uniqueidentifier	INOUT
@CERTIFICATIONDATA	int	INOUT
@LASTVALIDATIONATTEMPTDATE	date	INOUT
@OMITFROMVALIDATION	bit	INOUT
@VALIDATIONMESSAGE	nvarchar(200)	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@CONSTITUENTDUPLICATEMATCHINGENABLED	bit	IN

Definition

Copy



                CREATE procedure dbo.USP_DUPLICATESEARCH_PROSPECTSEARCHPOPUPORGANIZATION (
                    @NAME nvarchar(100) = '',
                    @ADDRESS_POSTCODE nvarchar(12) = '',
          @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
          @MAXROWS smallint = 100,
          @ADDRESS_COUNTRYID uniqueidentifier  = null,
          @ADDRESS_STATEID uniqueidentifier  = null,
          @ADDRESS_CITY nvarchar(50)  = null,
          @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
          @PHONE_NUMBER nvarchar(100) = '',
          @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
          @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
          @PHONE_PHONETYPECODEID 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,
          @DPC nvarchar(max) = null output,
          @CART nvarchar(max) = null output,
          @LOT nvarchar(5) = null output,
          @INFOSOURCECODEID uniqueidentifier = null output,
          @INFOSOURCECOMMENTS nvarchar(256) = null output,
          @COUNTYCODEID uniqueidentifier = null output,
          @REGIONCODEID uniqueidentifier = null output,
          @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
          @STATEHOUSEDISTRICTCODEID uniqueidentifier = null output,
          @STATESENATEDISTRICTCODEID uniqueidentifier = null output,
          @LOCALPRECINCTCODEID uniqueidentifier = null output,
          @CERTIFICATIONDATA int = null output,
          @LASTVALIDATIONATTEMPTDATE date = null output,
          @OMITFROMVALIDATION bit = null output,
          @VALIDATIONMESSAGE nvarchar(200) = null output,
          @CURRENTAPPUSERID uniqueidentifier = null,
          @CONSTITUENTDUPLICATEMATCHINGENABLED bit = null
                ) as begin

                    declare @KEYNAME nvarchar(100);
                    declare @KEYNAMEPREFIX nvarchar(50);

          begin try
                      exec dbo.USP_PARSE_ORGANIZATION_NAME @NAME, @KEYNAME output, @KEYNAMEPREFIX output;
          end try
          begin catch
            set @KEYNAME = @NAME;
          end catch

                    ;with DUPLICATECANDIDATES_CTE as(
              select 
                  CONSTITUENTID,
              ADDRESSID,
              EMAILADDRESSID,
              PHONEID,
              MATCHPERCENTAGE,
              STREETNUMBERRESULT,
              STREETNAMERESULT,
              POSTCODERESULT               
            from dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_3(
              null,
              null,
              null,
              @KEYNAME,
              null,
              @ADDRESS_ADDRESSBLOCK,
              @ADDRESS_POSTCODE,
              @ADDRESS_COUNTRYID,
              1,
              0,
              null,
              null,
              @EMAILADDRESS_EMAILADDRESS,
              @PHONE_NUMBER,
              null,
              null,
              @CURRENTAPPUSERID,
              @CONSTITUENTDUPLICATEMATCHINGENABLED)
                    )
                    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
                        DUPLICATECANDIDATES_CTE 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
                    order by
                        DC.MATCHPERCENTAGE desc

                end