We are doing some clean-up for on our database for constituents duplicates.
Has anyone written an automated procedure to do a mass merge of constutents?
I am thinking of building a local table based on an agreed upon set of rules and call the internal Tessitura procedure with the from and to constituent id's
Bob
Hi Bob,
We have such process in place. The DB procedures are (see attached):
1. procedure lp_chc_const_dups_PREPgets customer duplications into custom table C_CHC_CONST_DUPS;
2. procedure lp_chc_const_dups_PREPpopulates Tess tbl T_POTENTIAL_DUPS;
3. procedure lp_chc_const_dups_TO_MERGEPrepares potential dups to merge:the smallest customer_no will be selected to Keep, the rest - to Delete;
4. two service functions:lfn_chc_get_norm_name lfn_chc_get_norm_street.
A few notes:
A. for the custom dups identification, we used the business rules which are quite different than Tessitura standard ones. When we run the custom de-dup first time back in 2008, about 20,000 accounts were de-duped;
B. for the custom dups merge, we still rely on the Tessitura's AP_MERGE_CUSTOMER2;
C. for day-to-day dedup, we still use the Tessitura's AP_IDENTIFY_DUPLICATES .
D. we have a customized way to treat the merged accounts interest and weight (tx_cust_tkw.Selected and tx_cust_tkw.Weight) and also marketing restrictions (t_customer.mail_ind, phone_ind and emarket_ind) through the customizable proc. LP_CONST_MERGE which is called from AP_MERGE_CUSTOMER.
Thanks,Simon Basyuk,DBA of Carnegie Hallsbasyuk@carnegiehall.org