We would like to update the source number on a large number of web orders. Looking through the forums and the impresario tables, it seems like I can run an update statement on T_ORDER, T_TRANSACTION, and perhaps T_WEB_ORDER.
Is it as simple as this? Or am I missing an important update. Even better, is there an SP I can tap into to take care of all this for me?
Thanks!
Hi Levi -
This is *cough* not supported at all *cough* but something that I'm sure all of us have done one time or another. A couple of things to keep in mind -
There's a trigger on the t_order table that runs when you update source numbers. Because of this, it will error if you are trying to set the source number to null (which doesn't sound like you are).
You'll also probably want to make sure your campaigns/promos behavior is updated correctly. In looking at an older piece of code I wrote, you'll want to think of the following steps (at least - I'm probably missing something in here).
Part I: Remove old source from campaign/promotion/appeal numbers
1. Remove old counts from t_campaign
2. Remove promotions (t_promotion) to the old source
3. Update the old t_appeal with the correct number of customer successes
Part II: Change Source on Transactions
4. Update the source on t_transaction.
5. Update appeal, campaign and media type on t_transaction to match the new source
Part III:
6. Update source on order
7. Update appeal on order to match new source
8. Update the t_promotion table
Part IV:
9. Update all the campaign/appeal/source info with new numbers by running TP_UPDATE_CAMP_APPEAL_PROMO
That's the jist, but I haven't used this particular piece of code in quite a while, so I'd definitely advise you to run anything on test first and really look at reports etc...
HTH,
Heather
Thanks Heather. It sounds like I might want to ask the user how badly they actually want this done. I was hoping to get away with a quick and dirty update statement or two, but it sounds like a bit of effort and testing involved.
Good to know!
It's definitely dirty, not sure about the quick part (it actually isn't as bad as it sounds, but I always like to be a little over alarmist when it comes to changing data through the backend). And as always, it never hurts to put in a support request and have the faboo Tessi support take a gander at what you are doing. They've saved my bacon more times than I can count. In fact, I'm pretty sure they were the ones that first pointed me at the campaign/promotion/appeal data and how that would change.
- Heather
I think I made a stored procedure for this once... but I can't find it, so maybe I just thought about doing that. Anyway, here's my list of tables that potentially need updating:
t_promotion
t_transaction
t_order
t_contribution
t_web_order
t_cust_subscription_summary
tx_appeal_media_type (for the counts)
once those are updated, I would always run ap_update_appeal and ap_update_campaign
Also, before starting updating I always check to see if there are customer_no's in t_promotion that already have the new source_no, so as not to create duplicate records in t_promotion.
If anyone happens to find themselves in the offices of the Shakespeare Theatre Company and finds my stored procedure, or the file I was working on, or the notes on my old desk, let me know. :)