• Replies 6 replies
  • Subscribers 336 subscribers
  • Views 49 views
  • Users 0 members are here
Related

Extracting Partial Payments for ticket packages

Former Member
Former Member $organization

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

 

 

Parents
  • $organization

    Hi Bob, are you using auto-billing schedules or just billing manually by hand?

  • Former Member
    Former Member $organization in reply to Ryan Rowell

    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

     

    Hi Bob, are you using auto-billing schedules or just billing manually by hand?

    From: Bob Thomas <bounce-bobthomas7245@tessituranetwork.com>
    Sent: 8/27/2009 1:47:27 PM

    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

     

     




    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!

  • $organization in reply to Former Member

    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.

    Cheers!

Reply
  • $organization in reply to Former Member

    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.

    Cheers!

Children
  • Former Member
    Former Member $organization in reply to Ryan Rowell

    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…..

     

    Best,

    Bob

    x3726

     

    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

     

    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.

    Cheers!

    From: Bob Thomas <bounce-bobthomas7245@tessituranetwork.com>
    Sent: 8/31/2009 11:37:06 AM

    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

     

    Hi Bob, are you using auto-billing schedules or just billing manually by hand?

    From: Bob Thomas <bounce-bobthomas7245@tessituranetwork.com>
    Sent: 8/27/2009 1:47:27 PM

    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

     

     




    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!




    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!

  • Former Member
    Former Member $organization in reply to Ryan Rowell

    Hi Ryan

     

    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

     

    Bob

     

     

     

     

    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

     

     

    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

     

    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.

    Cheers!

    From: Bob Thomas <bounce-bobthomas7245@tessituranetwork.com>
    Sent: 8/31/2009 11:37:06 AM

    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

     

    Hi Bob, are you using auto-billing schedules or just billing manually by hand?

    From: Bob Thomas <bounce-bobthomas7245@tessituranetwork.com>
    Sent: 8/27/2009 1:47:27 PM

    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

     

     




    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!




    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!

  • $organization in reply to Former Member

    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.

    Cheers!