Open Dental Forum

I know this is listed in the prev version page..."A report is needed to warn of procedures where the payment+writeoff is greater than the fee."

just want to add that I have come across this problem quite a bit lately where my staff put in double the writeoffs on primary and secondary ins plans and now it looks like I owe the pt double the money they ever paid us! I have a feelilng we have many cases like this and would greatly appreciate this report.

Maybe this should be integrate into the ins payment window? (i.e. not let a write off be entered if it makes the payment+writeoff greater than the fee)

This is the query that will likely become a report. This query might actually uncover quite a bit of cash. That's why I'm leaving it on the buglist rather than a feature request where it would normally go.

SELECT procedurelog.PatNum,procedurecode.ProcCode,procedurelog.ProcDate,procedurelog.ProcFee,SUM(claimproc.InsPayAmt + claimproc.Writeoff) AS
$PaidAndWriteoff
FROM procedurelog
LEFT JOIN claimproc ON procedurelog.ProcNum=claimproc.ProcNum
LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
WHERE procedurelog.ProcStatus=2/*complete*/
GROUP BY procedurelog.ProcNum
HAVING procedurelog.ProcFee < SUM(claimproc.InsPayAmt + claimproc.Writeoff)

Could the query be modified to compare the totals for the claim rather than looking at individual procedures? When I ran it I found several false positives - where the writeoffs on individual lines may have been too much, but then a writeoff on another line on the claim was less, so in the end it all balanced out to the correct amount.

There were also a few where the amounts showed equal in the query, even though the query uses "less than". Not sure why those came up.

thanks...our report shows about $22,000 difference over the past 3 years!!!!!...sooo......I have to do some investigating now...prob too late to fix past years, but this year's mistakes I need to get on!!!!

I think you are going to find your overpayments to be much less. The query was enhanced, but I forgot to post the change:

SELECT procedurelog.PatNum,CONCAT(patient.LName,', ',patient.FName) patname,
procedurelog.ProcDate,
SUM(procedurelog.ProcFee) $sumfee,
SUM((SELECT SUM(claimproc.InsPayAmt + claimproc.Writeoff) FROM claimproc WHERE claimproc.ProcNum=procedurelog.ProcNum)) AS
$PaidAndWriteoff
FROM procedurelog
LEFT JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
LEFT JOIN patient ON patient.PatNum=procedurelog.PatNum
WHERE procedurelog.ProcStatus=2/*complete*/
AND procedurelog.ProcFee > 0
GROUP BY procedurelog.PatNum,procedurelog.ProcDate
HAVING $sumfee < $PaidAndWriteoff
ORDER BY patname,ProcDate

whew...I thought it was going to be better, but looks like right at $20,000 now, so yes, it is probably more accurate and lower, but looks like we have some issues to take care of....

thanks....

I'm guessing there are a lot of false positives. Let me know.

I also found cases where writeoffs on preauths were included. They were from 2008 when preauths were handled differently.
I just went in to each one and cleared the writeoff amount from the preauth and that took care of it without affecting the account balance.

so far I have gone through 4 and all our accurate. I reduced the results to only 2009 and my errors are about $4000, looks like over the last couple of years I might have been out $16,000 that I can't really go back and try to collect now...


can we add a check in the program on ins entry to not allow a write off to be entered that will make the payment plus the writeoff greater than the fee? Seems like this would be easy to fix and eliminate this problem.

I'd be in favor of a warning message, but still want to be able to have the writeoff + payment be greater than the billed fee when needed. One situation I'm thinking of is when an insurance company overapays on a claim (i.e. paid as primary instead of secondary). In order to have the account show the correct amount, I increase the writeoff to what it should have been (which results in a credit on the account) then I enter a negative payment for the insurance refund. Is there a better way to handle that?

you should be entering a supplemental payment I think and then issuing a negative payment when you write a check back.

The negative payment should be a negative insurance payment, not a negative patient payment. In other words, the negative payment gets entered inside the claim. This is covered in this new page:
http://www.opendental.com/manual/reportinsoverpaid.html

Thanks for the instructions - that's a much better solution than what I was doing. I did have a "Insurance Refund" payment type that I was using for this so we could differentiate between patient refunds and insurance refunds - but we'll start doing it this other way instead.