USP_CLIENT_CONSTITUENT_FUZZY_LOOKUP

This data list returns a list of criteria for constituent auto match.

Parameters

Parameter	Parameter Type	Mode	Description
@TRANSACTIONTYPE	nvarchar(512)	IN	Transaction Type
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN	Email Address
@LASTNAME	nvarchar(100)	IN	Last Name
@FIRSTNAME	nvarchar(50)	IN	First Name
@MAIDENNAME	nvarchar(100)	IN	Maiden Name
@MIDDLENAME	nvarchar(50)	IN	Middle Name
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Address Block
@ADDRESS_CITY	nvarchar(100)	IN	Address City
@ADDRESS_COUNTRYID	uniqueidentifier	IN	Country
@ADDRESS_STATEID	uniqueidentifier	IN	State
@ADDRESS_POSTCODE	nvarchar(12)	IN	Address Zip Code
@PHONE_NUMBER	nvarchar(100)	IN	Phone Number
@BIRTHDATE	UDT_FUZZYDATE	IN	Birth Date
@GENDERCODE	tinyint	IN	Gender
@LOOKUPID	nvarchar(20)	IN	LookupID
@INACTIVE	bit	IN	Include Inactive
@DECEASED	bit	IN	Include Deceased
@MAXROWS	int	IN	Input parameter indicating the maximum number of rows to return.
@EVENTID	uniqueidentifier	IN

Definition

Copy



CREATE PROCEDURE [dbo].[USP_CLIENT_CONSTITUENT_FUZZY_LOOKUP]
(
      @TRANSACTIONTYPE                nvarchar(512)    = 'FAF Registration'
     ,@EMAILADDRESS_EMAILADDRESS    dbo.UDT_EMAILADDRESS = ''                
     ,@LASTNAME                        nvarchar(100)    = ''
     ,@FIRSTNAME                    nvarchar(50)    = ''
     ,@MAIDENNAME                    nvarchar(100)    = ''
     ,@MIDDLENAME                    nvarchar(50)    = ''
     ,@ADDRESS_ADDRESSBLOCK            nvarchar(150)    = ''
     ,@ADDRESS_CITY                    nvarchar(100)    = ''
     ,@ADDRESS_COUNTRYID            uniqueidentifier= null
     ,@ADDRESS_STATEID                uniqueidentifier= null
     ,@ADDRESS_POSTCODE                nvarchar(12)    = ''    
     ,@PHONE_NUMBER                    nvarchar(100)    = '' -- Any phone

     ,@BIRTHDATE                    AS  dbo.UDT_FUZZYDATE = ''
     ,@GENDERCODE                    tinyint            = null
     ,@LOOKUPID                        nvarchar(20)    = ''     
     ,@INACTIVE                        bit                = 0
     ,@DECEASED                        bit                = 0
     ,@MAXROWS                        integer            = 1           
     ,@EVENTID            uniqueidentifier= null

)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @MAXROWS_I AS INTEGER;
    SELECT @MAXROWS_I = @MAXROWS+1;

    DECLARE @CONSTITUENTS AS TABLE(
         [CONSTITUENTID]    uniqueidentifier
        ,[FIRSTNAME]        nvarchar(100)    
        ,[LASTNAME]            nvarchar(100)
        ,[MATCHPERCENTAGE]    decimal(20,2)
    ) 

    INSERT @CONSTITUENTS
    (
         [CONSTITUENTID]            
        ,[FIRSTNAME]    
        ,[LASTNAME]            
        ,[MATCHPERCENTAGE]    
    )
    EXEC [dbo].[USP_CONSTITUENT_FUZZY_LOOKUP]
      @TRANSACTIONTYPE                =@TRANSACTIONTYPE
     ,@EMAILADDRESS_EMAILADDRESS    =@EMAILADDRESS_EMAILADDRESS            
     ,@LASTNAME                        =@LASTNAME
     ,@FIRSTNAME                    =@FIRSTNAME
     ,@MAIDENNAME                    =@MAIDENNAME
     ,@MIDDLENAME                    =@MIDDLENAME
     ,@ADDRESS_ADDRESSBLOCK            =@ADDRESS_ADDRESSBLOCK
     ,@ADDRESS_CITY                    =@ADDRESS_CITY
     ,@ADDRESS_COUNTRYID                =@ADDRESS_COUNTRYID          
     ,@ADDRESS_STATEID                =@ADDRESS_STATEID
     ,@ADDRESS_POSTCODE                =@ADDRESS_POSTCODE    
     ,@PHONE_NUMBER                    =@PHONE_NUMBER
     ,@BIRTHDATE                    =@BIRTHDATE
     ,@GENDERCODE                        =@GENDERCODE
     ,@LOOKUPID                        =@LOOKUPID 
     ,@INACTIVE                        =@INACTIVE    
     ,@DECEASED                        =@DECEASED
     ,@MAXROWS                        =@MAXROWS_I
   ,@EVENTID            =@EVENTID;                

    -- CMS Clients linked to Constituents (ECRM records)

    WITH [CTE_CLIENTCONSTITUENTS]
    AS
    (    
        SELECT    
             C.ID            AS [CONSTITUENTID]
            ,cu.ID            AS [CLIENTUSERID]
            ,cu.UserName    AS [CLIENTUSERNAME]
            ,cu.[Password]    AS [CLIENTPASSWORD]
        FROM    dbo.ClientUsers cu  (NOLOCK)
        INNER    JOIN dbo.BackOfficeSystemUsers bosu (NOLOCK)
        ON        cu.ID = bosu.ClientUsersID AND bosu.[current] = 1 AND cu.Deleted = 0  
        INNER    JOIN dbo.BackOfficeSystemPeople bosp (NOLOCK)
        ON        bosp.ID = bosu.BackofficePeopleID AND bosp.BackOfficeSystemID = 0  
        INNER    JOIN dbo.CONSTITUENT C (NOLOCK)
        ON        bosp.BackofficeRecordID = C.SEQUENCEID    
    ) 
    -- Constituents with CMS accounts

    ,[CTE_AUTHENTICATED_CLIENTS]
    AS (
        SELECT    TOP(@MAXROWS)        
             V1.CONSTITUENTID
            ,V1.FIRSTNAME
            ,V1.LASTNAME
            ,V1.CLIENTUSERID
            ,V1.CLIENTUSERNAME
            ,V1.[CLIENTPASSWORD]
      ,V1.DISPLAYNAME
      ,V1.PROVIDERNAME
      ,V1.IDENTIFIER
    FROM (
          SELECT
               FL.CONSTITUENTID
              ,FL.FIRSTNAME
              ,FL.LASTNAME
              ,C.CLIENTUSERID
              ,C.CLIENTUSERNAME
              ,C.[CLIENTPASSWORD]
        ,FL.[MATCHPERCENTAGE]
        ,NULL AS DISPLAYNAME
        ,NULL AS PROVIDERNAME
        ,NULL AS IDENTIFIER
          FROM    @CONSTITUENTS FL
          INNER    JOIN [CTE_CLIENTCONSTITUENTS] C ON FL.CONSTITUENTID = C.CONSTITUENTID    
      WHERE C.CLIENTUSERNAME NOT LIKE 'SocialWeb_%'     -- system auto-generate SocialWeb_<PKID> for username


      UNION

           SELECT
               FL.CONSTITUENTID
              ,FL.FIRSTNAME
              ,FL.LASTNAME
              ,C.CLIENTUSERID
              ,C.CLIENTUSERNAME
              ,C.[CLIENTPASSWORD]
        ,FL.[MATCHPERCENTAGE]
        ,Ext.DISPLAYNAME
        ,Ext.PROVIDERNAME
        ,Ext.IDENTIFIER
          FROM    @CONSTITUENTS FL
          INNER    JOIN [CTE_CLIENTCONSTITUENTS] C ON FL.CONSTITUENTID = C.CONSTITUENTID    
      INNER JOIN ClientUsersExtension Ext ON Ext.ClientUserID = C.CLIENTUSERID
    ) V1
        ORDER    BY [MATCHPERCENTAGE] DESC
    )
    -- Constituents without CMS accounts

    ,[CTE_NONAUTHENTICATED_CLIENTS]
    AS
    (
        SELECT    TOP 1    
             FL.CONSTITUENTID
            ,FL.FIRSTNAME
            ,FL.LASTNAME
            ,C.CLIENTUSERID
            ,C.CLIENTUSERNAME
            ,C.[CLIENTPASSWORD]
      ,NULL AS DISPLAYNAME
      ,NULL AS PROVIDERNAME
      ,NULL AS IDENTIFIER
        FROM    @CONSTITUENTS FL
        LEFT    JOIN [CTE_CLIENTCONSTITUENTS] C
        ON        FL.CONSTITUENTID = C.CONSTITUENTID
        WHERE    C.CONSTITUENTID IS NULL
        ORDER    BY FL.[MATCHPERCENTAGE] DESC
    )
    SELECT * FROM [CTE_NONAUTHENTICATED_CLIENTS]
    UNION ALL
    SELECT * FROM [CTE_AUTHENTICATED_CLIENTS];

    SET NOCOUNT OFF;
END