Hi All,
We are attempting to write a query that will tell us who has payment plan payments coming up by due date and who has money outstanding on said payment plans for our school.
Here is the code I came up with (which I'm pretty proud of, newbie here....) that has got us almost there:
select C.customer_no, C.fname, C.lname, E.address, O.order_no, O.MOS, O.tot_paid_amt as 'Total Paid To Date', O.tot_due_amt as 'Total Order Amount', S.due_dt, S.amt_due as 'Due on next run',B.order_amt, B.order_paid, B.bill_amt, B.past_due from T_Customer as C join T_EADDRESS as E on C.customer_no = E.customer_no join T_Order as O on C.customer_no = O.customer_no join T_ORDER_SCHEDULE as S on O.order_no = S.order_no join T_ORDER_BILL_DETAIL as B on S.order_no = B.order_no where S.due_dt between '2023-06-14' and '2023-07-16' and O.MOS = '11' and E.primary_ind = 'Y' order by C.customer_no asc
But we are stuck on the past due line. This constituent missed their July payment, so theoretically they should have a past due amount associated with their August payment, but it's not pulling anything in:
Any thoughts on what we are doing wrong? Or how I can amend the code so that it shows us that information?
Thanks!
Nicki LeGrand
I think it might have to do with how the past_due column in T_ORDER_BILL_DETAIL is calculated. I checked an order in my database that had missed a payment from yesterday and it also shows past_due as 0. I wonder if there's a certain timeframe where it's still considered current due and when it becomes past due?
Maybe have a discussion with your finance department about the Ticket Order Billing Utility. https://www.tessituranetwork.com/Help_System/Content/Ticket%20Order%20Billing/Ticket%20Order%20Billing.htm
I'm more familiar with pledge billing personally, but I believe this is one of the utilities that's normally scheduled to run periodically to charge payments and udpate data like past due amounts.