Has anyone customized the merge procedure so that it does not inactivate addresses from the deleted record? Can you please share? We are merging consortium records, but each organization has its own control-group address types that we need to keep active and it is time consuming to manually update each record after the scheduled merge runs. Thanks!!
Hi Stephanie,
We've got the following code in our LP_CONST_MERGE procedure in order to retain addresses (similar code also exists for email addresses) :
UPDATE a
Set Inactive = 'N'
from T_Address a
where a.customer_no = @kept_id
and a.last_update_dt > dateadd(Hour,-1,getdate())
--and last_updated_by = 'dbo' -- not sure if dbo would be used by the merge proc for this field.
and a.inactive = 'Y'
and not exists (select 1 from t_address b
where b.customer_no = a.customer_no
and b.inactive = 'N'
and b.address_type = a.address_type
and b.street1 = a.street1
and b.city = a.city
and ISNULL(b.state, '') = ISNULL(a.state, '')
)
Stacey Voigt Consortium Services Manager Santa Barbara Center for the Performing Arts svoigt@granadasb.org
Thank you, Katie and Stacey! This is a tremendous help!
I added this customization to our own merging script about 10 years ago, so there’s quite possibly a better way to do this, but basically I just save the addresses from the “deleted” records into a temporary table, run the canned merge procedure, and then use the temporary table to reactivate any addresses in T_ADDRESS that were just deactivated.
So the pseudocode would be something like:
CREATE TABLE #t_old_addrs (address_no)
INSERT INTO #t_old_addrs
SELECT active addresses from soon-to-be deleted records
EXEC TN merge proc
Set a.inactive=’n’
From T_ADDRESS A
JOIN #T_OLD_ADDRS on address_no
Where a.inactive=’y’
Good luck,
~Katie
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Stephanie Watson Sent: Monday, August 15, 2016 2:07 PM To: Catherine Lachance-Duffy Subject: [Tessitura Technical Forum] Merge Customization - Addresses
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Hi Steph
We take a slightly different approach. Instead of re-activating addresses after the main merge process has inactivated them, we move control-grouped addresses across to the Keep id as a Before action, so that they already belong to the Keep-id when the standard merge script runs, so it doesn't do anything with them.
Looks like this snippet below.
Ken:
-------------------------------------------------------------------------------------------------------
/********* ### STEP B5 [Control-grouped addresses] ### *************
-- move control-grouped addresses from D to K
-- so the main merge process doesn't inactivate them
**********************/
IF @merge_stage = 'B'
--
BEGIN
UPDATE [dbo].t_address
SET primary_ind = 'N' ,
customer_no = @kept_id
FROM t_address a
JOIN tr_address_type t ON t.id = a.address_type
WHERE customer_no = @deleted_id
--And t.control_group is not null --to get all the control grouped addresses
AND ISNULL(t.control_group, -1) <> -1 -- KenM 2012-10-06 New v11 default cg
IF @@error <> 0
SELECT @err_table = 'control-grped address handling - pre-merge'
GOTO GiveErrMsg
END
-----------------------------------------------------------------------
Thank you for all the input. We implemented Ken's code which keeps the control-grouped addresses as active when merged. This is fantastic!