Hi guys,
We are trying to query some data from Tessitura by SQL code and it's been a challenge to find the table that has the ticket price info.
We need to know only the Ticket Price without any fees, charges, discounts or taxes.
Example:
Order1 bought 1 seat and paid the total of: $50,00 (Ticket Price: $45.00 / Facility Fee: $2.50 / Service Charge: $2.50)
Order2 bought 1 seat and paid the total of: $100 (Ticket Price: $80.00 / Facility Fee: $3.00 / Service Charge: $2.50 / discount: $14.50)
We are looking for just only the Ticket Price amount paid.
Thanks
Hi Amanda,
I suspect what you're looking for is T_SUB_LINEITEM. However, that may be complicated by how you are implementing fees and charges. Normal fees will be split out of this, but if you are adding charges via Price Layers then subtracting them back out will be more difficult.
--Gawain
I believe t_sli_detail.non_discount_price will give you the ticket price minus any discount. Note that this amount appears to be always positive, so if there is a return, it isn't going to show you a negative number. Anyway, if you look at some of your orders that are in scope of your report, you can probably confirm whether it is what you are looking for.
It should also not include normal fees, but as Gawain pointed out, whether that fully excludes fees may depend on your pricing setup.
Good luck!David
I'd suggest you search for the "Tessitura Table Structures" document on the website. Specifically, the Ticketing and Subscription Related Tables, and Pricing Related Tables sections.
Another good trick to see the components of an order is to use PORD or PCONT. They should help with doing the JOINs.
In SQL, EXEC PORD ####, @include_web_tables = 'N' --Replace #### with order number. For WEB orders: EXEC PORD @order_no = ####, @include_web_tables = 'Y' --Replace #### with order number.
For Contribution batches: EXEC PCONT #### --Replace #### with contribution number [ref_no]
Sorry, it should be:
EXEC PORD @order_no = ####, @include_web_tables = 'N'--Replace #### with order number.
(I forgot the @order_no = part)
I was really excited when I saw this: I thought it might be a record of base price at the moment of sale, but this is just for the manual discounting system, isn't it? I see that it is always the same as our sale price at whatever price type.
When you say "moment of sale" are you specifically looking for that moment, regardless of future returns, exchanges, etc... that occur?
Because, unless I am mistaken, the SLI_DETAIL table SHOULD give you the price of each layer for each ticket sold, regardless of the home price for that layer.
Yes. So if on May 1st a person buys a Senior Discount Price Type ticket for $95, I want to know that the base price (Full Price Price Type) for that ticket, on May 1st, was $100, not that the original Full Price was $90, or that another pricing event on May 5th changed the Full Price to $110.
From what I see in T_SLI_DETAIL.non_discount_price is exactly what the vended price was, regardless of price type. I'm guessing it's related to this instead:
https://www.tessituranetwork.com/Help_System_v14/Content/Ticket%20Pricing/Discount%20Pricing.htm
Which we don't use.
Pretty much all of our discounts are done using price type changes, so this sounds a little new for us.
That said, you should be able to use a combination T_PERF_PRICE and T_PRICE_EVENT to figure out what the standard price for each layer is for any given price type at any given moment in time before any pricing rules or discounts are applied.
If you're going to Orlando in a few weeks, I would be more than happy to chat.
Yep. See you there!