Has anyone come across any code that simply shows a list of customer's subscription series for a given season including seat locations? Sounds simple in English, but I have such a devil of a time figuring out which tables hook up to what when it comes to ticketing...me am ignorant.
Thanks, Tessiturians for this and all previous help.
BONUS TRIVIA: Nine out of every ten living things live in the ocean.
Can you get that from your sub history table? (probably lt_sub_hist)I'm not entirely sure what you want for "series" but if you want the package description and like us you have the package code in your sub history you can link back with the pkg_code to t_pkg to get the description.
Does that help?
No, see, that's the problem. The subscription history table (T_SUBSCRIPTION_HIST) doesn't contain any information for the upcoming season (43).
This always drives me crazy, being that I'm a novice SQL programmer on my best day, and given Tessitura's Byzantine table structure. I simply need the following information for my 2010/2011 Opera Season Subscribers:
EXAMPLE:
customer_no lname series seat101675 Johnson A OPEN 11 Orchestra Prime A1225945 Smith A OPEN 11 Orchestra Prime A
Everyone has been helpful in suggesting that I look in the subscription history table which seems to have most of the information I need - for past (okay, and current) seasons. Perhaps the issue as yet unnoticed is that some stored procedure is not updating that table with future subscriptions that have entered the system, indicative of some end of season procedure maybe?
In either case, it shouldn't be a Herculean task to pull this information from its constituent tables into an ostensibly simple report. Or so I believe.
I've certainly been wrong about this sort of thing before...
If you just modify the job that updates that local table to include that season it'll solve your problem. If you want exact syntax for how to grab the seats without referencing lt_sub_hist you could also probably go into your own LP_UPDATE_LT_SUB_HIST and borrow the sql it uses for that purpose. Plus then you know you're taking into account any weird custom things your organization might have.
What you're telling me sounds good. Unfortunately, I have no table called lt_sub_hist.
Or t_subscription_hist if that's what you're using, but I thought that table was essentially vestigial. Either way there should be a job that's updating whatever table you're using to store sub hist, and it's a matter of changing the job that does that updating.
I was under the impression that you weren't supposed to add the new season numbers into the job until the end of the year for some reason. I added it to the Subscription History step of our Update Ticket & Subs History job and everything magically appeared. Imagine that.
I don't know why I have such a mental block when it comes to this Ticketing stuff. It's so layered and involved compared to some of the other modules.
Having other people to bounce ideas off is quite helpful, and you all have my thanks.
Have a good weekend, guys.