Hi.
Newbie here. Please bear w/me...
We allow patrons who purchase ticket packages to make periodic partial payments until paid in full. Now I am trying to extract, per each order, the payment amounts and the dates they were recorded. It is easy to find the cumulative amounts paid and what is left outstanding, but I am stumped for extracting the date and payment amount details. It seems that the transaction table would be the place to find them, but each order has a multitude of transactions associated with it, and nothing I've tried so far matches up to the cumulative amounts in the order table. So now I'm just confused. Is there a better table? A preexisting report? Some simple little thing I'm just missing? (Won't be the first time...) How can I pull the payments?
Many thanks in advance to any and all who can help me out!
Cheers!
Bob Thomas
Hi Bob, are you using auto-billing schedules or just billing manually by hand?
Hi Ryan
Thanks for responding. We use a two-payment schedule. The first payment was done manually, for the second we used auto-billing.
Best,
Bob
x3726
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ryan Rowell Sent: Friday, August 28, 2009 4:48 PM To: Bob Thomas Subject: Re: [Tessitura Technical Forum] Extracting Partial Payments for ticket packages
From: Bob Thomas <bounce-bobthomas7245@tessituranetwork.com> Sent: 8/27/2009 1:47:27 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!
Hi Bob,
I'm also a noobie when it comes to SQL and pressed for time at the moment so hopefully someone has some good ideas on how to get this information out.A couple thoughts though would be, maybe limit it by transaction type (trn_type) found in tr_transaction_type and I may be wrong but I think if you are using auto-billing the manual payment may also have the same billing type (which I think is 4) so maybe there is a way to join up t_order_schedule or something with the transaction or payment table.
Thanks for the ideas, Ryan – I’ll give them a shot. I appreciate you taking the time, and I’ll let you know how things turned out…..
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ryan Rowell Sent: Monday, August 31, 2009 12:12 PM To: Bob Thomas Subject: Re: [Tessitura Technical Forum] RE: Extracting Partial Payments for ticket packages
I'm also a noobie when it comes to SQL and pressed for time at the moment so hopefully someone has some good ideas on how to get this information out. A couple thoughts though would be, maybe limit it by transaction type (trn_type) found in tr_transaction_type and I may be wrong but I think if you are using auto-billing the manual payment may also have the same billing type (which I think is 4) so maybe there is a way to join up t_order_schedule or something with the transaction or payment table.
From: Bob Thomas <bounce-bobthomas7245@tessituranetwork.com> Sent: 8/31/2009 11:37:06 AM
I promised that I’d get back if I found anything.
Well, after a few weeks of distractions, I got back to this issue, took your advice and came up with the attached query, which is somewhat bare-bones but gets payment info out.
Thanks again for your help
SELECT Convert(varchar(12), o.order_dt, 1) AS [OrderDate],
t.order_no, o.tot_due_amt, o.tot_paid_amt,
Convert(varchar(12), t.trn_dt, 1) AS [TransDate],
t.trn_amt, t.trn_type, tt.[Description], t.Perf_no
FROM t_transaction t
JOIN tr_transaction_type tt ON tt.[ID] = t.trn_type
JOIN t_order o ON o.order_no = t.order_no
WHERE
order_dt >= '5/1/2009'
AND o.order_dt <= '5/10/2009'
AND o.tot_due_amt <> o.tot_paid_amt
ORDER BY
o.order_dt ASC, t.order_no ASC, t.trn_dt ASC
Thanks for sharing Bob!I'll forward this thread onto my ticket office manager and see if he would like to have this information available as well.