Payment + Writeoff report - Waiting for it!

[drtech]

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)

[jordansparks]

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)

[atd]

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.

[drtech]

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

[jordansparks]

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

[drtech]

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

[jordansparks]

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

[atd]

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.

[drtech]

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.

[atd]

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?

[drtech]

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

[jordansparks]

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

[atd]

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.