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 can't advise in detail without access to your system and data, but if I were you, I would avoid trying to join T_ORDER_SCHEDULE and T_ORDER_BILL_DETAIL directly to T_ORDER. We use order schedules on subscription renewal orders, and when I look at those, I stick mostly to T_ORDER_SCHEDULE.
E.g. I might look for Sub MOS orders with order dates within our renewal period, and among those with due < paid, find the orders with a schedule, and only then look at T_ORDER_SCHEDULE and amounts due.
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.