Payment Plan Report

For users or potential users.
Post Reply
User avatar
B.Thomas
Posts: 160
Joined: Mon Jul 23, 2007 11:00 pm

Payment Plan Report

Post by B.Thomas » Wed Mar 04, 2009 12:36 pm

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,

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Payment Plan Report

Post by jordansparks » Thu Mar 05, 2009 10:49 am

We got by without it at first, but it's obviously becoming more important. Make sure you vote for that feature request.
Jordan Sparks, DMD
http://www.opendental.com

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Payment Plan Report

Post by Mifa » Thu Mar 05, 2009 1:18 pm

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...

User avatar
B.Thomas
Posts: 160
Joined: Mon Jul 23, 2007 11:00 pm

Re: Payment Plan Report

Post by B.Thomas » Fri Mar 13, 2009 6:48 pm

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
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Payment Plan Report

Post by Mifa » Sat Mar 14, 2009 4:19 am

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!

Post Reply