WordFly LP update preferences - anyone customized that successfully yet?

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LP_UPDATE_EMAIL_PROMOTION]    Script Date: 8/6/2020 2:29:21 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO




ALTER   Procedure [dbo].[LP_UPDATE_EMAIL_PROMOTION](
	@source_no int =  null,
	@customer_no int = null,
	@response_code int = null,
	@response_dt datetime  = null,
	@eaddress varchar(80) = null,
	@url varchar(1024) = null
)

AS

SET NOCOUNT ON

/*************************************************************************************************
New Localized Procedure CWR 10/31/2003 which is called from WP_UPDATE_EMAIL_PROMOTION to optionally
take additional actions upon receiving email response notifications

Updated 4/10/17 - CMP, Removed error causing comments, commented out CSI creations

Sample code is provided below to illustrate some possible actions, especially dealing with Unsubscribe
events (response_code 5)

Updated 10/2/18 - BLS, updated to add unsubscribe for ahha Tulsa & generic 

Updated 7/24/19 - BLS, updated to add Gilcrease
*************************************************************************************************/


/* start WordFly sample code  ========================================================================*/


-- here is code that will set an attribute (hard coded values)



If @response_code = 5 -- Philbrook Unsubscribe
	Begin

	If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 215)
		Update	tx_cust_keyword
		Set	key_value = 'Unsubscribe'
		Where	customer_no = @customer_no 
			and keyword_no = 215
	Else
		Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
		Select	@customer_no, 215, 'Unsubscribe'
	End


  If @response_code = 2 -- Philbrook Hardbounce
	  Begin
		If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 215)
			Update	tx_cust_keyword
			Set	key_value = 'Hard Bounce'
			Where	customer_no = @customer_no 
				and keyword_no = 215
		Else
			Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
			Select	@customer_no, 215, 'Hard Bounce'
	  End


If @response_code = 10 -- ahha Tulsa Unsubscribe
	Begin
		If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 533)
			Update	tx_cust_keyword
			Set	key_value = 'Unsubscribe'
			Where	customer_no = @customer_no 
			and keyword_no = 533
		Else
			Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
			Select	@customer_no, 533, 'Unsubscribe'
			End


  If @response_code = 7 -- ahha Tulsa Hardbounce
	  Begin
		If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 533)
			Update	tx_cust_keyword
			Set	key_value = 'Hard Bounce'
			Where	customer_no = @customer_no 
				and keyword_no = 533
		Else
			Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
			Select	@customer_no, 533, 'Hard Bounce'
	  End



  If @response_code = 15 -- Gilcrease Unsubscribe
	Begin
		If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 590)
			Update	tx_cust_keyword
			Set	key_value = 'Unsubscribe'
			Where	customer_no = @customer_no 
			and keyword_no = 590
		Else
			Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
			Select	@customer_no, 590, 'Unsubscribe'
			End


  If @response_code = 12 -- Gilcrease Hardbounce
	  Begin
		If Exists (Select * from tx_cust_keyword where customer_no = @customer_no and keyword_no = 590)
			Update	tx_cust_keyword
			Set	key_value = 'Hard Bounce'
			Where	customer_no = @customer_no 
				and keyword_no = 590
		Else
			Insert	tx_cust_keyword(customer_no, keyword_no, key_value)
			Select	@customer_no, 590, 'Hard Bounce'
	  End
 


  
  

	
/* == end WordFly sample code  ========================================================================*/




/*== start Tessitura sample code =======================================================================*/


--Declare	@errmsg varchar(255), @rowcount int

------ here is code that will set the t_customer emarket_ind to a value dependant on response codes
--If @response_code = 5 --  UNSUBSCRIBE: set the marketing indicator on the eaddress record for the customer
--	Begin
--		Update	t_customer
--		Set	emarket_ind = 5	-- no emarketing to philbrook
--		Where	customer_no = @customer_no
--	End

--If @response_code = 2  --  HARDBOUNCE:  set the marketing indicator on the eaddress record for the customer
--	Begin
--		Update	t_customer
--		Set	emarket_ind = 4	-- no emarketing to all organizations
--		Where	customer_no = @customer_no	
--	End

/* -- E-Marketing Codes
1 - n/a does not exist
2 - "No Emarketing (depriciated)
3 - (none) i.e. No preference
4 - "Do Not Email"
5 - "P Do Not Email" 
*/

/*  -- Commented out by Barbara S --
-- here is code that will write a customer service issue (hard coded values)
Declare @activity_no int

--Philbrook Unsub
If @response_code = 5
  Begin

	-- get an issue number
	EXEC @activity_no = AP_GET_NEXTID_Function 'AC'
	
	-- Put in the Customer Service Issue
	Insert into t_cust_activity(
		activity_no,
		category,
		activity_type,
		customer_no,
		contact_type,
		inout_ind,
		notes,
		issue_dt,
		origin)
	Select 	@activity_no,
		null,
		97,
		@customer_no,
		22,
		'I',
		'WordFly Unsubscribe',
		getdate(),
		null

  End

  -- Philbrook Hardbounce
  If @response_code = 2
  Begin

	-- get an issue number
	EXEC @activity_no = AP_GET_NEXTID_Function 'AC'
	
	-- Put in the Customer Service Issue
	Insert into t_cust_activity(
		activity_no,
		category,
		activity_type,
		customer_no,
		contact_type,
		inout_ind,
		notes,
		issue_dt,
		origin)
	Select 	@activity_no,
		null,
		98,
		@customer_no,
		22,
		'I',
		'WordFly Hard Bounce',
		getdate(),
		null

  End
*/
--RETURN

/*== end Tessitura sample code =======================================================================*/