We have a campaign with multiple communication nodes, ie Email, SMS, iVoice.
Then we have a stored procedure node attached to each communication that updates contact dates (per product offered) in an Oracle table (Customer Contact Summary Table).
The same customer can be in more than one communication in the campaign. When the campaign is executed (or run in a schedule) some of the updates on the Oracle table fails as multiple processes cannot update the same record on the Customer Contact Summary Oracle Table.
I need to know how we can resolve this as we must be able to date stamp the contact fields after each campaign is executed, otherwise we would incorrectly contact a customer who was already contacted with a marketing offer.
See attached document for screenshots of the layout of the campaign and the Contact Summary Oracle Table.
This is the code that updates the Oracle Table.
&UpdateContactField. This is a macro variable created from the metadata selected by the user in the communication definition.
For example, it could resolve to this variable name : LAST_SMS_DT
/* update mart table */
proc sql;
update CI_MART.MA_Customer_Contact_Summary
set LAST_TELEMARKETING_DT = datetime(),
&UpdateContactField. = datetime()
where Customer_RK in (select customer_rk from CI_MART.MA_ACCOUNT
where account_rk in (select account_rk from &InTable1))
;
quit;