Discrepancy between otb reports

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.

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.