Oh mighty DBAs hear my plea!
This can't be as complicated as it seems.
If I want to determine the sales of a particular performance as part of a particular package, I write something like this:
select a.perf_no,sum(a.due_amt),sum(a.paid_amt) from t_sub_lineitem a (nolock) join t_perf b (nolock) on a.perf_no = b.perf_nowhere a.pkg_no = ###group by a.perf_no
Since any returns or exchanges generate an additional record in t_sub_lineitem with a negative cash amount, I'm assuming the above query is telling me the total gross sales less all exchanges and returns.
What pieces am I missing? The numbers I get are close to, but not equal to, the numbers on several box office reports. None of the box office reports agree with each other.
Now, I know the box office reports have specifically designed purposes but the folks trying to reconcile with finance are quite perturbed. So, wouldn't the above query be the number that the box office should be handing to finance?
Any thoughts, strategies, additional head-banging-desk comments are appreciated.
Hi Levi,
Exchange sub line items do not have a package number on them (technically speaking they are single ticket sub line items, both the return and the sale) so they won’t be included in your query. Because sub line item status is not a part of your query you should be getting all the original package performance sub line items, before exchanges. The standard package sales reports filter out package performance sub line items that have a void status, which is why the numbers go down when exchanges start. Your query does not exclude those voided (i.e. returned) sub line items, so it is not accounting for exchanges or full package refunds (which are probably rare but should be considered).
The Performance Sales Summary by PT Category report might give you what you are looking for. It groups ticket sales into single tickets and subscriptions based on the price type category of your price types. As long as all the price types used for exchanges have a price type category of Subscription the report will accurately divide performance revenue between single tickets and subscriptions. This is the ticket sales report we suggest to match against finance reports. But this report isn’t going to tell you which packages led to the sales. Because exchange sub line items do not have a package number I don’t think you really can sort package performance seats by the package they came from. Probably the best you could do is sort unexchanged tickets by package and then have a bucket for exchanged package tickets.
One other thing that could cause a discrepancy between GLs and ticket sales report numbers. When there is an exchange downgrade (more expensive ticket to less expensive ticket) what do you do with the left over money? If you use a fee or a payment method to take care of that money, it will no longer be counted by sales reports. But if you use the same GL account for the fee or payment method as the GL map for the performance price maps, the GL reports will still count the money. You would have to run a second report on the fee or payment method you use for downgrades and add that amount to your sales report to match the GL report.
Kevin Sheehan
Documentation & Learning Resources Specialist
Tessitura Network
1 888 643 5778 ext 329 Office
ksheehan@tessituranetwork.com
Thanks Kevin. That does help a lot.
I feel like I'm getting closer to grasping this. Another 12-18 months and I think I will have it!
Good day.
This topic is a bit complicated. Because the link between price type and pkg type is missing. It is DIY time.
I think package counting should be based on the price type level. and it should go to the t_sli_detail level.
because there are no link between price_type and package_type, so you need to setup a lookup table to do this.
it means every price_type should have a package_type. then the count can start from here.
for example, someone buy 1 ticket in 12 pkg price_type, it should regard as 1/12 package sale.
so we can have a custom table (Detail Table) included all sales records, every record should include some new fields:
field 1, tells you (it is package ticket,or single ticket or other ticket, or it is a fee)
field 2, tells you what package type (12, or 10 or 9 .....)
you can schedule this as a overnight job.
also you can create a summary table for a season level or monthly level results.
When you create a report, you can only point to the summary table.
so the report will be very fast.
So I think we should have three custom tables.
1, price type<==> package type lookup table. (we have a LTR_PRICE_TYPE having a field called package_size)
2, sales record detail table.--- run a loop and update every sales records with package info. this is based on your business logic.
3, sales record summary table.
I use these steps to create our daily sales report.
our SQL server keeps a weekly sales detail records, if I use today's record subtract yesterday's sales record, it will be today's sales.
thank you for your time
have fun.
Ben
Ben,
That is an interesting approach and one that certainly gives me some ideas. One thing I'm not clear on though. Is the price_type to package_type relationship 1 to 1? If not, what does that do to the lookup table?
I don't think price_type to package_type will be 1 to 1 relationship. because it should be based on business logic.
I think it should also include the season, we should have a lookup table including pkg_no, price_type, season, and also we add a one more custom field pkg_size.
let's do something interesting.
we can create a LT_SLI_DETAIL_NEW table. we link all sales info and dump them in this table.
in this table detail_sli_no will still be unique.
we can inclue pkg_no, price_type, season, gl_hold_no, season, perf_no, prod_season_no, due_amt, paid_amt, order_no, order_dt, customer_no, sli_status, seat_no, pmap_no, campaign_no... , also can have some identity fields: id, create_date. (basically it just = t_sub_lineitem + t_sli_detail)
it will be an interesting table, because it has everything about sales, except payment.
next step
SELECT a.pkg_no, a.price_type, a.SEASON, b.description FROM LT_SLI_DETAIL_NEW AS a INNER JOIN T_PKG AS b ON a.pkg_no = b.pkg_no GROUP BY a.pkg_no, a.price_type, a.SEASON, b.description ORDER BY a.SEASON, a.pkg_no
OK, now we have a real lookup table need to be finished.
This is reverse engineering.
We can schedule a sql job if any item is not in this table, send out a notice.
So here is my answer for this lookup table:reverse engineering.
We can do this lookup table in an Excel file first, as in our system it has more than 500 records.
and also I should mention, we need put some index on LT_SLI_DETAIL_NEW.
I think this part of work is worth to do it. because if you finish this, you will have a real handy sales table. I have a few custom reports all based on this LT table. (like I have a Sli / Transaction Error Check report, it compares the paid_amt in order with Trn_amt in payment). I cannot share them, because I have a different sales table.