co_individual_dup_check
This stored procedure is used in the Duplicate Check Child Form on the Individual Object.
It attempts to find possible duplicate records when a new Individual is added.
Where Used
All Individual Forms.
Logic
Any individuals who meet the following conditions will be flagged as a suspected duplicate:
- The SOUNDEX values of first name and last name are equal
- Any of the following conditions is true:
- the existing individual has an empty city and state
- the existing individual has a primary address with the exact same city and state of the individual being added
- the existing individual has a business address with the exact samecity and state of the individual being added
SQL Text
Here is the text of the SP as of 2006.02. The text may change from build to build. Check your database for the exact text.
CREATE PROCEDURE [dbo].[co_individual_dup_check]
@ind_first_name nvarchar(255),
@ind_last_name nvarchar(255),
@adr_state nvarchar(255),
@adr_city nvarchar(255),
@ad2__adr_state nvarchar(255),
@ad2__adr_city nvarchar(255),
@ind_cst_key av_key
AS
SET nocount ON
SELECT ind_cst_key,
[Individual] = isnull(cxa.cxa_mailing_label_html, ind_full_name_cp)
FROM co_individual (nolock)
JOIN co_customer (nolock) ON cst_key = ind_cst_key
LEFT JOIN co_customer_x_address cxa (nolock) ON cxa.cxa_key = cst_cxa_key
LEFT JOIN co_address adr (nolock) ON adr.adr_key = cxa.cxa_adr_key
WHERE soundex(ind_first_name)=soundex(@ind_first_name)
AND soundex(ind_last_name)=soundex( @ind_last_name)
AND ( (adr.adr_state IS NULL AND adr.adr_city IS NULL ) OR (adr.adr_state = @adr_state AND adr.adr_city = @adr_city )
OR (adr.adr_state = @ad2__adr_state AND adr.adr_city = @ad2__adr_city) )
AND (ind_cst_key IS NOT NULL OR ind_cst_key <> @ind_cst_key)
AND @ind_cst_key IS NULL
AND cst_delete_flag=0
Altering Logic
If you want to alter this logic, do not change the SP. Make a new version of the SP and name it client_xyz_co_individual_dup_check. Then edit the duplicate check child form and change the select sql to run your new SP instead. Be sure to lock the child form.