Updating the General Tab for Email Subscribes/Unsubscribes

Knowledge Level:
Time: 10 minutes
Suggested Skills: Tessitura, SQL
Objective: Some organizations may wish to update the constituent record general tab Emarket Restriction when some one globally unsubscribes an address or reactivates one. This should only be done when the address that action was taken for is the primary address on a constituent record. Just because someone globally unsubscribes a secondary address doesn't mean their entire constituent record should be globally unsubscribed, so it is important that this action is only done when the primary address has a reactivation or global opt-out action taken for it. Here is what you'll do to make this customization:

1

Create a copy of the procedure:LWP_M2_HANDLE_SUBSCRIPTION.

Call it: LWP_M2_HANDLE_SUBSCRIPTION_LOCAL

2

Within that procedure, on line 62 put a return and start a new line.

Insert your new logic for handling the update here where someone globally unsubscribes an address.

58: IF @parent_key = 'Tessitura Web'
59: BEGIN
60: UPDATE VS_EADDRESS
61: SET market_ind = 'N'
62: WHERE address = @address 
63: 
64:UPDATE T_CUSTOMER
	65: SET mail_ind = (#whatever ind ID needs to be set#)
66: WHERE customer_no = vs_eaddress.customer_no
67: AND vs_eaddress.address = @address
68: AND vs_eaddress.primary_ind = 'Y' 
69:
70: END
3

Make a similar update to the block that handles reactivation code, after existing line 91.

This block should update the mail_ind to whatever number is necessary based on the reactivation.

4

Make sure to update the name of the procedure that is called in LWP_M2_HANDLE_WEB_HOOKS on line 86 from:

EXEC LWP_M2_HANDLE_SUBSCRIPTION
to
EXEC LWP_M2_HANDLE_SUBSCRIPTION_LOCAL

Still need help? Contact Us Contact Us