(Note: This was re-posted from the Next Generation Forum)
I've been asked to create a report that shows the upcoming due dates for unpaid ticket invoices. After spending most of today trying to extract that info from Tessitura, I've now come here, hat in hand. While it is fairly easy to extract invoice payment information (via queries like AP_AGED_RECEIVABLE_REPORT (aka the Invoice Detail report), as well as finding which invoices are not fully paid, extracting future Due Dates and payment amounts (which appear in Constituents->View Invoices->Invoice List->Invoice Payment Schedule) seems to be a more arcane matter. Or so it seems to me. The other possibility is that I'm just blind. Anybody here have experience doing this?
Many thanks in advance for any suggestions and help!
Bob Thomas
Hey Bob:
Your code works good for pulling all scheduled invoices within a specified date range. However, this pulls all scheduled invoices, even if they have already been paid. What we really need to know is invoices that have not yet been paid, as we would not want to send an invoice to a constituent that has already made the monthly payment due. Do you know whether than can be done or not?
Penny Tabor
IT Manager
Midland Center for the Arts
Midland, MI 48640
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Bob Thomas Sent: Sunday, March 04, 2012 2:05 PM To: Tabor, Penny Subject: RE: [Tessitura Technical Forum] Invoice Receivables - Future Due Dates
Good to hear! You’re very welcome.
Bob
P: 503-445-3726
C: 530-401-4518
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Terry Stevens Sent: Friday, March 02, 2012 11:30 AM To: Bob Thomas Subject: RE: [Tessitura Technical Forum] Invoice Receivables - Future Due Dates
Bob,
Thank you so much you saved me hours of time and it works beautifully!
From: Bob Thomas <bounce-bobthomas7245@tessituranetwork.com> Sent: 3/2/2012 12:02:42 PM
One more thing:
If you want to narrow the final results to just the invoices due within the period in question, you can use this as the second WHERE clause:
WHERE
t.payment <> 0 AND os.due_dt between @start_dt and @end_dt
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!
There are probably several ways to approach this. I'll try to point you in the right direction, at least. Please test all code thoroughly in your test database and do not take my word for anything - your mileage, as they say, could differ, so test, test, test.
With that warning out of the way, I believe that what you are looking for are records in the T_PAYMENT table that have the same invoice number as in the original query, but not the same transaction number. These records should be your actual payments made against the invoice. If you don't include the date parameters, you'll get all the payments made. Alternatively, you can include the date parameters and get only the payments made within those dates. It's possible that some additional filtering might be necessary for your system, but I'd start with the example code here and see how it turns out.
Here is some code you can paste to the end of the columnset in the final SELECT statement. This should add a column of payment information, if any. It's just a subquery to the T_PAYMENT table:
, (SELECT (0-IsNull(SUM(pmts.pmt_amt),0)) FROM T_PAYMENT pmts WHERE pmts.payment_no = t.payment_no AND pmts.transaction_no<>t.transaction_no AND pmts.pmt_dt BETWEEN @start_dt AND @end_dt) AS [Paid]
Note the comma at the beginning (to extend the columnset), as well as the date variables and alias (t.), which are found in the original code. Also note that in the Tessitura database, the payments are stored as credits (a negative number). I have reversed the sign in the example, so the payment information appears without the minus sign in front.
Try working with this in your test database, and hopefully you can get to where your really want to go. Good luck. I hope I've been clear, and that this helps.