USP_DUPLICATESEARCH_ORGANIZATION

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

Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(100)	IN	Name
@ADDRESS_POSTCODE	nvarchar(12)	IN	ZIP/Postal Code
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Address
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@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_ORGANIZATION (
                    @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 @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 @KEYNAME nvarchar(100);
            declare @KEYNAMEPREFIX nvarchar(50);
            declare @NULLVALUE uniqueidentifier = (select cast(cast(0 as binary) as uniqueidentifier))

          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
                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 = @KEYNAME 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