Hi all,
I've created a new delivery method (Special Delivery Postage) which I only want available online on one performance. At the moment, it's appearing on all productions with the Internet MOS.
For some of our productions, we've used LTR_TNEW_DYN_SHIPPING_METHOD to limit certain productions to e-ticketing only. But, as this is for merchandise, we need the options for standard posting, and collection at box office, to still appear in the drop-down, along with the Special Delivery option.
Is there a way to remove this new delivery method from all productions, and to then make it only available as an option on one?
Many thanks,
Hannah
Heath WilderMichaela Donnelly
I fixed the problems I had excessive churn on LTR_TNEW_DYN_SHIPPING_METHOD. I'm not sure what the best way is to share projects like this, let me know if you have any ideas. Here's the readme text:
TNEW Dynamic Shipping Fix
Warning: this is a custom procedure set developed for Cal Performances internal use. No warranty of any kind is attached: use at your own peril.
Manifest
Functions:
LF_CPSMA_TNEW_FIX_UPDATE_DYN_SHIP.sql
LFT_CPSMA_PROD_ELEM_PKG_SELECT.sql
Procedures:
LP_CPSMA_TNEW_FIX_DYN_SHIP.sql
LP_CPSMA_TNEW_FIX_DYN_SHIP_MAIN.sql
Tables
LTR_CPSMA_TNEW_FIX_SHIP_KW.sql
LTR_CPSMA_TNEW_FIX_SHIP_METHOD.sql
How It Works
First you set up a set of keywords for each shipping method that you use online. You can, if you choose separate out the keywords by organization so that you can control a performance separately based on the site it is being sold through. When these keywords are created, they are then connected with the appropriate shipping method with LTR_CPSMA_TNEW_FIX_SHIP_KW.
Now your performances (or other production elements) and packages can be flagged with keywords, and the keywords will then be translated into rows in LTR_TNEW_DYN_SHIPPING_METHOD. To simplify things for the procedure, whatever production elements are keyworded, only the associated performances are entered into LTR_TNEW_DYN_SHIPPING_METHOD. Since there are two other items that can feed into LTR_TNEW_DYN_SHIPPING_METHOD (price types and payment methods), and also to allow other “standard” production element configurations, there’s a second table, LTR_CPSMA_TNEW_FIX_SHIP_METHOD, which is an expanded and improved version of LTR_TNEW_DYN_SHIPPING_METHOD. Configurations in this table also feed into LTR_TNEW_DYN_SHIPPING_METHOD.
LFT_CPSMA_PROD_ELEM_PKG_SELECT is a function used as a selection table for LTR_CPSMA_TNEW_FIX_SHIP_METHOD, displaying text values for production elements or packages, and limiting them to current products (based on product dates or associated seasons’ end dates).
LP_CPSMA_TNEW_FIX_DYN_SHIP now should be configured to run as a SQL Server Agent Job (alternatively you could set it up in a utility if you only want manual updates. It takes two parameters: @dry_run (0 = run with output, -1 run silently, anything else is a dry run with output) and @force_update (1 = force update even if no tables have changed, anything else, only update on changes, see below).
Unless @force_update is set to 1, LP_CPSMA_TNEW_FIX_DYN_SHIP will first run LF_CPSMA_TNEW_FIX_UPDATE_DYN_SHIP. This looks at every table that might feed into dynamic shipping configuration, and if it detects a recent update (vs the last update on LTR_TNEW_DYN_SHIPPING_METHOD) it will trigger an update (this is actually LP_CPSMA_TNEW_FIX_DYN_SHIP_MAIN), otherwise it will simply end.
If it doesn’t, it review all keywords on current (as defined above) production elements and packages, as well as any current production elements and packages in LTR_CPSMA_TNEW_FIX_SHIP_METHOD. It will also pull in price types and payment methods from the table. Then if anything in LTR_TNEW_DYN_SHIPPING_METHOD isn’t in that list it is deleted, and if anything is missing from that table, it is now added.
One note about LP_CPSMA_TNEW_FIX_DYN_SHIP_MAIN: it uses a custom function, not supplied, to fill created_by and last_updated_by. This should just be replaced by dbo.fs_user(), or any custom function used to do the same (basically, you need a function to enter that value, as the triggers on LTR_TNEW_DYN_SHIPPING_METHOD will fail if the database user in question has a name longer than 8 letters.