Discrepancy between otb reports

Forgot to add that I regularly use the Single Sale Order Listing report to identify and paid/unseated orders - and then for the concert, for good measure I have done an order search with every combination of paid/unpaid seated/unseated that I can.   The discrepancy is $64 which relates to a particular price type.  I have also tried to pull a list for this particular price type to at least identify the possible constituent orders but didn't find anything very useful.  Darrell

Have you run the Performance Sales Summary by PT category both including donated tickets and not including donated tickets to see if that makes a difference?

Thanks for the response Phil but we don't use the donated seats option so that doesn't impact on the report. 

I have a feeling I might just have to go out and do crash course in SQL.  Cheers Darrell

1, there are no reports can figure out what is the difference in Tessitura.

2, and a crash course cannot help you getting the result. (if it is so simple, the report will be already there.)

3, the difference actually is the sli_status (1,2,6). you can find them in T_sli_detail table.

the sales report only count sli_status (3,12).

payment report is checking t_transaction table.

comapre t_sli_detail table with t_transactiontable.

the difference is

money in sli_status 1,2,6 will not be counted in sales report

but it will be counted in t_transaction table.

my suggestion is living with it.

to create our daily sales reports took me two months to finish it.

it is very complicated.

there are two parts in the Tessitura,

1, T_order---> t_sub_lineitem-->t_sli_detail(tr_price_type,t_pmap)-->t_transaction

2, T_transaction--->T_GL_POSTING_HISTORY(payments) or t_payment

you cannot link them together!!!

you can compare them at certain point of time(we take a copy of sales records at 2:00 am)

t_transaction table is in the middle.

it is a bridge, and bridge only.

first part of talbes will give you a sales report.

second part of tables will give you a GL report.

they never agree each other!!!

Never.

Why?

1,time difference.

Only posted batches GL will appear on a GL report.

2, sli_status difference.

sli_status (1,2,6) you never find them in a sales report.

so what is missing?

this is very tricky question.

there are no transaction reports in the system.

the answer is a transaction report.

Our requirement for the enhancement should be a transaction report.

this report will compare t_transaction with t_sli_detail.

also will comapre t_transaction with GL records(T_GL_POSTING_HISTORY or t_payment).

In this way, everything is checked.

this is what we have as a daily sales report.

my suggestion still is living with it.

Thanks Ben, your response was very helpful. 

Sorry if I seemed to be suggested that learning SQL was a cure-all or anything less than complex.  I appreciate the explanation of which tables the reports read - had no idea.   I'm perfectly happy to live with this but our finance department is less so and expect us in the box office to come up with some answers.  

Cheers Darrell

Hi Darrell,

Have you tried running the two reports down to the performance level so you can at least isolate in which performance the discrepancy is occurring?  (Assuming that you have more than one performance per concert, that is.)  I'm thinking that if you can make your parameter universe smaller you can at least find the performance that it happened in and then investigate from there.  Which may mean pulling each order until you find it... depending on how persistent your finance folks are, of course!

Whenever this happens to me, the answer always has to do with sub exchanges.   Some days it seems I do nothing but chase down discrepancies, and there's usually a logical explanation for them.  Frustrating, but educational...

Good luck! 

We're fighting the same thing here.  (And it frequently feels like we spend way too much time on this project).  Unfortunately we can not "live with it" because we have to report back to our partners and balance with their finance departments, not just our own.

One tool we use is a spreadsheet crosstab that's (very loosely) based off of the Performance Sales Summary by PT Category and the Performance Payment Summary (new).  I've uploaded the SQL query I run to generate the data file that I cut and paste into Excel and then create a crosstab that the Box Office uses to try and find the issues. Once the crosstab is built, we use the drill down features to figure out what orders need more investigation.

It's called OperaCrosstab.sql in my files area.  If you have any questions about what this does or how to use it, feel free to ask and I'll do what I can to help.  (There are NO comments in the code, sorry).

I keep meaning to open up an enchancement request - I think there is a fairly easy solution to this problem.  (Warning, Geek alert!)  If the transaction_no (and possibly sequence_no) was added to t_order_seat_hist (from my quick perusal of the code, it is available when rows are added to t_order_seat_hist) we would be able to link better between the money and the seats (which is what our partners keep asking us for).

 -steve

I don't know if this will relate to your particular $64 dollars at the moment, but in writing one of our custom reports I found the most frequent cause of discrepancies for us related to partially paid orders in which the partial payment amount equaled a fraction of a ticket, which we see as the green number on season overview, but the whole value of the unpaid ticket remains in that line until it is entirely paid. At the same time the paid amount shows as paid because it's money received. So we spent huge amounts of time trying to figure out why we were matching on paid vs unpaid tickets but not paid vs unpaid amounts. So you may want to look specifically for sublineitems with partial payments.

Hey - I'm stunned how many replies and how helpful everyone is, BIG thank you to all.

Nancy - we are are dealing with individual concerts, that is 1 performance of 1 concert so it's clear where the discrepancy is.  Sometimes a discrepancy has corrected itself by the time we reconcile the concert - sometimes not.

Amanda - I'm aware of the partial payment issue and try to get our ticket sellers to avoid this, at all costs - and I have been relying on the discrepancy being identifiable in a sub-line item somewhere - this issue for me being that I have to look in every order and every sub-line item hunting for something out of the ordinary and this is not only tedious but wastes a huge amount of time.

Steve - thnks for understanding what I mean - although I am really growing into Tessitura and just what it can do - and (I'm just going to say the unsayable) I spent years using Tickmaster and it seems to me that "seeing" where the money went on a performance was much easier.

Phil - sounds like the report you mention would be very useful - when a discrepancy occurs it is generally a ticket price and in this particular case I know it relates to Compose Your Own Adult ticket in out Silver price zone.   Your custom report sound as if it details what I need - i.e. the price paid for individual seats.

Cheers, Darrell

Hi all,

I just modified one of my local procedure to share it.

this procedure will check the t_transaction with t_sli_detail table based on GL code and order_no

If something is wrong with ticketing, it will picked it up.

this stored procedure is targeting at ticketing only and paid_amt only.

It will return you a tmp table with error orders only.

have fun.

create procedure LRP_MTC_SLI_TRANS_ERROR_CHECK

as
begin

--get all paid_amt from sli_detail
                SELECT     T_SUB_LINEITEM.sli_no, T_SUB_LINEITEM.sli_status, T_SUB_LINEITEM.perf_no, T_SUB_LINEITEM.order_no, T_SLI_DETAIL.due_amt,
                                                            T_SLI_DETAIL.paid_amt, T_PERF.prod_season_no, T_PERF.season, T_PMAP.gl_hold_no, T_ORDER.customer_no
                into #LT_SLI_DETAIL_NEW
                FROM         T_SUB_LINEITEM INNER JOIN
                                                            T_SLI_DETAIL ON T_SUB_LINEITEM.sli_no = T_SLI_DETAIL.sli_no INNER JOIN
                                                            T_PERF ON T_SUB_LINEITEM.perf_no = T_PERF.perf_no INNER JOIN
                                                            T_PMAP ON T_SLI_DETAIL.pmap_no = T_PMAP.pmap_no INNER JOIN
                                                            T_ORDER ON T_SUB_LINEITEM.order_no = T_ORDER.order_no

--get all transaction record
             SELECT     T.sequence_no, T.transaction_no, T.trn_dt, T.trn_type, T.trn_amt, T.fund_no, ISNULL(F.fee_gl_no, '') AS T_FEE_fee_gl_no, ISNULL(P.gl_hold_no, '')
                                                         AS T_PMAP_gl_hold_no, ISNULL(P.gl_realize_no, '') AS T_PMAP_gl_realize_no, ISNULL(P.gl_benevolent_no, '') AS T_PMAP_gl_benevolent_no,
                                                         ISNULL(T_FUND.restricted_income_gl_no, '') AS T_FUND_restricted_income_gl_no, ISNULL(T_FUND.nonrestricted_income_gl_no, '')
                                                         AS T_FUND_nr_income_gl_no, ISNULL(T_FUND.future_rec_gl_no, '') AS T_FUND_future_rec_gl_no, ISNULL(T_FUND.current_rec_gl_no, '')
                                                         AS T_FUND_current_rec_gl_no, ISNULL(T_FUND.written_off_gl_no, '') AS T_FUND_written_off_gl_no, T.batch_no, ISNULL(C.fyear, 0) AS fyear,
                                                         ISNULL(Ty.batch_type_group, 0) AS batch_type_group, T.pmap_no, T.fee_no, T.posted_status, T.order_no,cast( null as varchar(50)) as T_act_gl_hold_no
             into #LT_TRANSACTION_NEW
             FROM         T_TRANSACTION AS T LEFT OUTER JOIN
                                                         T_FEE AS F ON T.fee_no = F.fee_no LEFT OUTER JOIN
                                                         T_FUND ON T.fund_no = T_FUND.fund_no LEFT OUTER JOIN
                                                         T_PMAP AS P ON T.pmap_no = P.pmap_no INNER JOIN
                                                         T_CAMPAIGN AS C ON T.campaign_no = C.campaign_no LEFT OUTER JOIN
                                                         T_BATCH AS B ON T.batch_no = B.batch_no LEFT OUTER JOIN
                                                         TR_BATCH_TYPE AS Ty ON B.batch_type = Ty.id


             UPDATE    #LT_TRANSACTION_NEW
             SET              T_act_gl_hold_no = CASE WHEN len(ltrim(T_FEE_fee_gl_no)) > 0 THEN T_FEE_fee_gl_no WHEN len(ltrim(T_PMAP_gl_hold_no))
                                                         > 0 THEN T_PMAP_gl_hold_no WHEN len(ltrim(T_PMAP_gl_realize_no)) > 0 THEN T_PMAP_gl_realize_no WHEN len(ltrim(T_PMAP_gl_benevolent_no))
                                                         > 0 THEN T_PMAP_gl_benevolent_no WHEN len(ltrim(T_FUND_restricted_income_gl_no))
                                                         > 0 THEN T_FUND_restricted_income_gl_no WHEN len(ltrim(T_FUND_nr_income_gl_no))
                                                         > 0 THEN T_FUND_nr_income_gl_no WHEN len(ltrim(T_FUND_future_rec_gl_no))
                                                         > 0 THEN T_FUND_future_rec_gl_no WHEN len(ltrim(T_FUND_current_rec_gl_no))
                                                         > 0 THEN T_FUND_current_rec_gl_no WHEN len(ltrim(T_FUND_written_off_gl_no)) > 0 THEN T_FUND_written_off_gl_no END
             where T_act_gl_hold_no is null

             --group trn_amt by order_no
             SELECT     SUM(trn_amt) AS trn_amt, T_act_gl_hold_no as gl_hold_no, order_no
             into #tran1
             FROM         #LT_TRANSACTION_NEW

             where not order_no is null
             GROUP BY T_act_gl_hold_no, order_no
             HAVING      (SUM(trn_amt) <> 0)
             ORDER BY order_no

             --group paid_amt by order_no
             SELECT     order_no, SUM(paid_amt) AS paid_amt, gl_hold_no, customer_no
             into #sl1
             FROM         #LT_SLI_DETAIL_NEW
             GROUP BY order_no, gl_hold_no, customer_no
             HAVING      (SUM(paid_amt) <> 0)
             ORDER BY order_no



             ---Trans exclude gl_hold_no from transaction And group by gl and order_no
             SELECT     trn_amt, gl_hold_no, order_no
             into #tran2
             FROM         #tran1
             where gl_hold_no in (select distinct gl_hold_no from #sl1)
             order by gl_hold_no, order_no


             ---Sli group by gl and order_no
             SELECT     order_no, paid_amt, gl_hold_no, customer_no
             into #sl2
             FROM         #sl1
             order by gl_hold_no, order_no

             --final difference table
             CREATE TABLE #finaltrans(
                 [order_no] [int] NULL,
                 [paid_amt] [money] NULL,
                 [gl_hold_no] [char](30) NULL,
                 [customer_no] [int] NULL,
                 [trn_amt] [money] NULL,
                 [diff] [money] NULL
             )

             --get value for difference table
                INSERT INTO [#finaltrans]
                                                            (order_no, paid_amt, gl_hold_no, customer_no, trn_amt, diff)
                SELECT     [#sl2].order_no, [#sl2].paid_amt, [#sl2].gl_hold_no, [#sl2].customer_no, [#tran2].trn_amt, [#tran2].trn_amt - [#sl2].paid_amt AS diff
                FROM         [#sl2] INNER JOIN
                                                            [#tran2] ON [#sl2].gl_hold_no = [#tran2].gl_hold_no AND [#sl2].order_no = [#tran2].order_no
                WHERE     ([#tran2].trn_amt - [#sl2].paid_amt <> 0)

             --clean tmp table
             drop table #tran1
             drop table #tran2
             drop table #sl1
             drop table #sl2
             drop table #LT_SLI_DETAIL_NEW
             drop table #LT_TRANSACTION_NEW

             --    return all records
             SELECT     *
             FROM         #finaltrans
             ORDER BY  customer_no, gl_hold_no,order_no

end

Just to add a little bit of extra to this (since I get this question a lot), the two biggest items that cause differences between all of the standard reports are Partially Paid orders and Unseated orders. These situations are treated differently by the 7 Reports most people use (General Ledger Summary, Daily Sales Report, Perf Sales Summary, Perf Sales Summary by PT Category, Box Office Statement, and Payments by Performance (new)). If you go to my Profile page here on Tessituranetwork.com, I have a PowerPoint slide that does the best job of explaining the differences between the reports.

Hi all

 This is what i normally do when im looking for a partial paid order or a paid order not seated , that i cant find thru reports . I usually search thru Order Search  and key in information as Perf. Date , MOS , Delivery, Date of shows, paid/unpaid  , Seated/not Seated  , printed/not printed  and then search this usually narrows  it down . You will in most cases find a reservation/ order that wasn't paid in full or that wasn't seated but paid.