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,
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
UPDATE a
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!
Thank you, Katie and Stacey! This is a tremendous help!
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!