Payment Plan Report

[B.Thomas]

For the payment plan report under monthly reports, is there any way to eliminate negative and 0 balances like an aging report? This would minimize our 29 page report to 1-2 pages.

Maybe a user Query?

Thanks,

[jordansparks]

We got by without it at first, but it's obviously becoming more important. Make sure you vote for that feature request.

[Mifa]

Until it's fixed, you can try to use query #44 (Payment plan information) to extract only those plans with past due payments. I've tailored it to my own needs and it's working just fine, shortening the payment plans report from 60 pages to 3 in our case...

[B.Thomas]

Thanks Mifa. I tiried that but unfortunately, I'm not very good at modifying user queries and keep getting errors. Do you mind posting your modified query?

[Mifa]

I'm no SQL expert either, there may be a more elegant way to do that.

In our case, we use payment plans to track insurance payments. In Canada, when we submit our claim electronically (through another software as OD doesn't work yet for eclaims for us), we know within seconds how much the insurance is gonna pay (not sure how it works in the States). This is the amount we enter in a payment plan's first and only term with a due date of one month from now (usually we receive the check within a week or two).

This query help us track those we have not received yet. It takes about a minute to generate this report that excludes any plans with amount due < 2$. You also may want to add a couple of Drop statement at the end to get rid of the tmp tables, I kept them only for debugging.

In any case, here it is:

DROP TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS tmp2;
CREATE TABLE tmp SELECT DISTINCT
pp.Guarantor,
pp.PayPlanNum,
(SELECT MIN(ChargeDate) FROM payplancharge WHERE pp.PayPlanNum=PayPlanNum
AND ChargeDate <= CurDate()) as 'NextPayDate',
(SELECT SUM(Principal) FROM payplancharge WHERE pp.PayPlanNum=PayPlanNum)
as 'OrigLoanAmount',
(SELECT SUM(SplitAmt) FROM paysplit WHERE pp.PayPlanNum=PayPlanNum) as
'SumPayMade' ,
pp.Note as 'PlanNote'
FROM payplan pp, patient, payplancharge ppc
WHERE pp.Guarantor=patient.PatNum
AND pp.PayPlanNum=ppc.PayPlanNum;

UPDATE tmp SET SumPayMade=0 WHERE IsNull(SumPayMade);

CREATE TABLE tmp2 SELECT
tmp.Guarantor,
tmp.PayPlanNum,
OrigLoanAmount,
SumPayMade,
(OrigLoanAmount-SumPayMade) as 'Remaining',
NextPayDate,
ppc.principal as 'NextPayPrinc',
ppc.interest as 'NextPayInt',
p.EstBalance as 'PastDue',
tmp.PlanNote
FROM tmp, patient p, payplancharge ppc
WHERE p.PatNum=tmp.Guarantor
AND ppc.PayPlanNum = tmp.PayPlanNum
AND tmp.NextPayDate=ppc.ChargeDate
AND OrigLoanAmount <> SumPayMade;

SELECT
tmp2.Guarantor as 'PID',
CONCAT(p.lname, ', ',p.fname) as 'Guarantor',
tmp2.Remaining as '$ Balance',
tmp2.NextPayDate as 'Duedate',
tmp2.OrigLoanAmount as '$ Orig Amount',
tmp2.SumPayMade as '$ paid to date',
tmp2.PlanNote as 'Note'
FROM tmp2, patient p
WHERE p.PatNum=tmp2.Guarantor
AND Remaining > 2
ORDER BY
tmp2.Guarantor ASC


Good luck!