Hello,
I am doing a report on constituent with ticket exchanges. My question is by looking at some of the SQL tables such as t_order_seat_hist, t_order, t_sli_status. How can I identify that constituent having some ticket exchange transaction in that constituent?
in fact, can I look at the order_no, sli_status for exchangeable??
Thanks,
-Harry
I'm pretty sure you need to dig into the T_ORDER_SEAT_HIST table. Check out the Tessitura Table Structures document; it has a legend for every possible value in the event_code column, including 3 (Returned), 4 (Released), and 16 (Returned for Resale), which you will likely want to look into. I would recommend creating some sequences of actions in your test system and then observing what is written to T_ORDER_SEAT_HIST for each of those scenarios.
Hey Tom, some time ago I had investigated a number of these with support and we identified some defects. To your knowledge, were any of the "invalid data" you describe the result of "as designed" behavior?
I'm pretty sure you need to dig into the T_ORDER_SEAT_HIST table. Check out the Tessitura Table Structures document; it has a legend for every possible value in the event_code column, including 3 (Returned), 4 (Released), and 16 (Returned for Resale), which you will likely want to look into. I would recommend creating some sequences of actions in your test system and then observing what is written to T_ORDER_SEAT_HIST for each of those scenarios. From: Harry Hoang <bounce-harryhoang8094@tessituranetwork.com> Sent: 4/3/2017 3:29:48 PM Hello, I am doing a report on constituent with ticket exchanges. My question is by looking at some of the SQL tables such as t_order_seat_hist, t_order, t_sli_status. How can I identify that constituent having some ticket exchange transaction in that constituent? in fact, can I look at the order_no, sli_status for exchangeable?? Thanks, -Harry This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Ticketing Forum. You may reply to this message to post to the Ticketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
From: Harry Hoang <bounce-harryhoang8094@tessituranetwork.com> Sent: 4/3/2017 3:29:48 PM
Hey Tom, some time ago I had investigated a number of these with support and we identified some defects. To your knowledge, were any of the "invalid data" you describe the result of "as designed" behavior? From: Tom Brown <bounce-tombrown3568@tessituranetwork.com> Sent: 4/4/2017 2:46:25 AM As you are looking at T_Order_Seat_History, be aware that there are combinations of actions that end users can do to create invalid data in this table. This impacts less than 1% of the data in our system. If you want more info, you can contact the Tessitura Network or reach out to me directly. On Mon, Apr 3, 2017 at 4:27 PM, Nick Reilingh <bounce-nicholasreilingh4883@tessituranetwork.com> wrote: I'm pretty sure you need to dig into the T_ORDER_SEAT_HIST table. Check out the Tessitura Table Structures document; it has a legend for every possible value in the event_code column, including 3 (Returned), 4 (Released), and 16 (Returned for Resale), which you will likely want to look into. I would recommend creating some sequences of actions in your test system and then observing what is written to T_ORDER_SEAT_HIST for each of those scenarios. From: Harry Hoang <bounce-harryhoang8094@tessituranetwork.com> Sent: 4/3/2017 3:29:48 PM Hello, I am doing a report on constituent with ticket exchanges. My question is by looking at some of the SQL tables such as t_order_seat_hist, t_order, t_sli_status. How can I identify that constituent having some ticket exchange transaction in that constituent? in fact, can I look at the order_no, sli_status for exchangeable?? Thanks, -Harry This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Ticketing Forum. You may reply to this message to post to the Ticketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you! This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Ticketing Forum. You may reply to this message to post to the Ticketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
From: Tom Brown <bounce-tombrown3568@tessituranetwork.com> Sent: 4/4/2017 2:46:25 AM
The tables you are looking at will only tell you that the seats were returned but not if that was part of an exchange or just a return+refund.
The table that may help is t_transaction as that will show a Ticket Change transaction type if the tickets werre exchanged for other tickets in the same transaction. Again this depends how exchanges are processed.
You should be able to link that to order seat history with order numebr and perf number but will also need to use event date to match around (guessing wont be an exact match but shoudl be within seconds/minutes) the transaction date
Mark
Thanks Everyone.
Mark,
Thanks for the table t_transaction. I believe the thing I will to check if they are under in one transaction.. then snail down on the t_order_seat_hist to find out any transactions with return, refund, add back with the new seat changes.
But some people here also mentioned there are some issue with t_order_seat_hist table. However, let’s find out.
Thanks again,
From: Harry Hoang <bounce-harryhoang8094@tessituranetwork.com>Sent: 4/3/2017 3:29:48 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Ticketing Forum. You may reply to this message to post to the Ticketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
hello Nick,
Thank you for your reply.
when i do a seat exchange, I still get the another ticket_no or just the same as the previous one? it seems i get NULL in the ticket_no on t_order_seat_hist table and the same order_no.
Harry
Remember that a ticket_no is only generated when the ticket is printed. When an SLI is unprinted, it can be moved to any seat easily, but once it has been printed and has a ticket_no, it can only be returned or donated, not moved. If a ticketed seat is being exchanged, it is returned, and then a new SLI is created for the new seat.
Hello Mark,
Thanks for the hint.
I believe I see some light on table t_transaction, since every transaction is having the transaction type which recorded on trn_type in this table. I ran some tests:
1.) ticket refund vs. ticket with seat exchange. "ticket change" vs "ticket refund"
2.) Donate ticket for resale vs. ticket with seat exchange. there is no transaction type for this "donate ticket for resale"
trn_type stores a unique transaction type which refers from table tr_transaction_type. The table t_order_seat_hist will be refered to pull out constituent ID, perf_no as well
I believe by this way, it can be easily identify any constituent with seat exchanges. Do you have any idea with this thought?
Cannt help too much with seats donated for reasale as we don't do that in the UK so is not something I am familiar with.
If the Ticket Change type is the only one you are interested in then you should be able too link t_transaction to t_order to get the constituent number, which would be a faster query.
If you need to know which seats were exchanged then use order seat history using order_no, perf_no and the datetime (+/- a few minutes) of the transaction to the event datetime.