(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
Hi Bob,
The details of the billing schedule are in T_ORDER_SCHEDULE.
The due dates and the amounts due are all there. The order_no field in this table is populated with either the order number or the invoice number depending on whether the billing scheduled is associated to either a ticket order or an invoice.
Jared
Jared!
AhHa - I see! It was that either/or thing in the order_no field that had me flummoxed. A JOIN on T_Order_Schedule.order_no = T_Payment.payment_no gave me what I was looking for.
Thank you so much for pointing me in the right direction. You've saved me hours of head-scratching!
Bob
Can I see the code that you wrote for this? I am trying to create a report that shows all invoice receivables that are due in a particular month.
Hi Terry
Here’s what I came up with. I hope that it works in your environment – as always, your mileage may vary. Good luck!
------------ Begin Listing
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
DECLARE @start_dt datetime, @end_dt datetime
SELECT @start_dt = '2/1/2012', @end_dt = '7/1/2012' -- Example dates
-- get invoices within period
SELECT
max(e.customer_no) AS [customer_no],
max(c.fname) AS [fname],
max(c.lname) AS [lname],
e.payment_no,
min(e.pmt_dt) AS [pmt_dt],
sum(e.pmt_amt) AS [payment],
min(e.transaction_no) AS [transaction_no],
min(k.description) AS [pmt_method]
INTO #Temp1
FROM dbo.t_payment e
JOIN dbo.T_CUSTOMER c ON c.customer_no = e.customer_no
JOIN dbo.VRS_PAYMENT_METHOD k on k.id = e.pmt_method and k.pmt_type = 4
WHERE e.pmt_dt >= @start_dt AND e.pmt_dt <= @end_dt
GROUP BY e.payment_no
SET NOCOUNT OFF
-- List Payments and Due Dates within period:
max(t.customer_no) AS [customer_no],
max(t.fname) AS [fname],
max(t.lname) AS [lname],
os.due_dt, os.amt_due AS [pmt_due],
max(o.order_no) AS [order_no],
t.payment_no AS [invoice_no],
max(t.pmt_dt) AS [inv_dt],
(SELECT sum(y.pmt_amt) FROM dbo.t_payment y WHERE t.payment_no = y.payment_no and y.transaction_no = t.transaction_no) AS [invoice_amt],
max(t.payment) AS [inv_due],
min(pmt_method) AS [pmt_method]
FROM #Temp1 t
JOIN dbo.T_TRANSACTION o ON o.transaction_no = t.transaction_no
JOIN dbo.T_ORDER_SCHEDULE os ON os.order_no = t.payment_no
WHERE t.payment <> 0
GROUP BY t.payment_no, t.transaction_no, os.due_dt, os.amt_due
ORDER BY customer_no, payment_no, due_dt
DROP TABLE #Temp1
------ End of Listing
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
Bob,
Thank you so much you saved me hours of time and it works beautifully!
Good to hear! You’re very welcome.
P: 503-445-3726
C: 530-401-4518
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Terry StevensSent: Friday, March 02, 2012 11:30 AMTo: Bob ThomasSubject: RE: [Tessitura Technical Forum] Invoice Receivables - Future Due Dates
From: Bob Thomas <bounce-bobthomas7245@tessituranetwork.com>Sent: 3/2/2012 12:02:42 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!
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
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
From: Bob Thomas <bounce-bobthomas7245@tessituranetwork.com> Sent: 3/2/2012 12:02:42 PM
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.