(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 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
Bob
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!