Page 1 of 1
Insurance payment not finalized error message
Posted: Tue Mar 21, 2017 10:37 am
by ki0ak
Hello,
I'm encountering an error message when trying to run reports ("Reports" -> "Standard") which states:
At least one insurance payment is not finalized. Reports will be inaccurate until all payments are finalized.
See query example in the online manual #958, "Claims with payments entered by no check, not finalized
So running query #958:
/*958 Claims with payments entered but no check, not finalized*/
/*Query code written/modified: 03/11/2015*/
SELECT claim.PatNum AS 'Pat#',
CONCAT(CONCAT(patient.LName,', '),patient.FName) AS 'PatientName',
claim.DateService,
carrier.CarrierName,
SUM(claimproc.InsPayAmt) AS '$InsPayAmt'
FROM claim
INNER JOIN claimproc ON claimproc.ClaimNum = claim.ClaimNum
INNER JOIN patient ON patient.PatNum = claim.PatNum
INNER JOIN insplan ON insplan.PlanNum = claim.PlanNum
INNER JOIN carrier ON insplan.CarrierNum = carrier.CarrierNum
WHERE (claimproc.Status = '1' OR claimproc.Status = '4' OR claimproc.Status=5)
AND (claimproc.InsPayAmt != 0 AND claimproc.ClaimPaymentNum = '0')
GROUP BY claim.PatNum,claim.ClaimNum,claimproc.ClaimPaymentNum,claim.PatNum
ORDER BY patient.LName, patient.FName, claim.DateService
Produces no output (column headings are present, but no data). Any ideas on how to fix this? Running Open Dental 16.3.45.0.
Thanks.
Brad
Re: Insurance payment not finalized error message
Posted: Tue Mar 21, 2017 12:32 pm
by cmcgehee
Query #958 get most offenders but not all. To also get partial payments that are not finalized, run #971:
Code: Select all
/*971 List of partial payments that need finalized.*/
/*The results of this query are the partial payments that need finalized for the message box when clicking the Reports menu in Open Dental.*/
/*Query code written/modified: 07/22/2015*/
SET @StartDate=CURDATE()-INTERVAL 3 MONTH, @EndDate=CURDATE();
SELECT *
FROM ((SELECT claimproc.PatNum,claimpayment.CheckDate,claimpayment.CheckAmt,claimpayment.ClaimPaymentNum
FROM claimpayment
INNER JOIN claimproc ON claimproc.ClaimPaymentNum=claimpayment.ClaimPaymentNum
WHERE claimpayment.IsPartial = 1
AND claimpayment.CheckDate BETWEEN @StartDate AND @EndDate)
UNION ALL
(SELECT claimproc.PatNum,claimproc.DateCP,claimproc.FeeBilled,claimproc.ClaimProcNum
FROM claimproc
WHERE claimproc.ClaimPaymentNum = 0
AND claimproc.InsPayAmt != 0
AND claimproc.Status IN(1,4,5)
AND claimproc.DateEntry BETWEEN @StartDate AND @EndDate
)
) partialpayments
Re: Insurance payment not finalized error message
Posted: Tue Mar 21, 2017 1:00 pm
by ki0ak
Thanks - that got it.
cmcgehee wrote:Query #958 get most offenders but not all. To also get partial payments that are not finalized, run #971:
Code: Select all
/*971 List of partial payments that need finalized.*/
/*The results of this query are the partial payments that need finalized for the message box when clicking the Reports menu in Open Dental.*/
/*Query code written/modified: 07/22/2015*/
SET @StartDate=CURDATE()-INTERVAL 3 MONTH, @EndDate=CURDATE();
SELECT *
FROM ((SELECT claimproc.PatNum,claimpayment.CheckDate,claimpayment.CheckAmt,claimpayment.ClaimPaymentNum
FROM claimpayment
INNER JOIN claimproc ON claimproc.ClaimPaymentNum=claimpayment.ClaimPaymentNum
WHERE claimpayment.IsPartial = 1
AND claimpayment.CheckDate BETWEEN @StartDate AND @EndDate)
UNION ALL
(SELECT claimproc.PatNum,claimproc.DateCP,claimproc.FeeBilled,claimproc.ClaimProcNum
FROM claimproc
WHERE claimproc.ClaimPaymentNum = 0
AND claimproc.InsPayAmt != 0
AND claimproc.Status IN(1,4,5)
AND claimproc.DateEntry BETWEEN @StartDate AND @EndDate
)
) partialpayments
Re: Insurance payment not finalized error message
Posted: Wed Jan 31, 2018 9:26 am
by LakesideFamily
Neither code worked for me?? I have version 16.1.39.0 if that makes a difference. Any other options? I've had this code up for over a week. I've gone through all of my payments over the last 2 weeks. I can't find anything and its driving me mad!
Re: Insurance payment not finalized error message
Posted: Wed Jan 31, 2018 9:53 am
by JoeMontano
The below text is from our documentation, I'm leaving it here in case someone else has this issue. If you were to update to 17.3 you would have access to our Unfinalized Insurance Payments report so if you can I would recommend updating. Otherwise it looks like you already ran 971, so calling support to troubleshoot might be a good option too.
http://opendental.com/manual/reportsstandard.html
Problem: When I click Standard Reports, I receive the following message: At least one insurance payment is not finalized. Reports will be inaccurate until all payments are finalized.
Solution: Prior to version 17.3, the Unfinalized Insurance Payments was not available under Standard Reports. One or more insurance payments were received but not finalized. To find these payments, follow the instructions below.
Run query 971 in Query Examples to identify the claims. Partial payments and unfinalized claims will list.
Re: Insurance payment not finalized error message
Posted: Mon Feb 05, 2018 3:32 pm
by tgriswold
If this isn't enough info you'll need to contact support. It is possible that there is a broken state on one of these entries and that is why they are not showing in another report, if this is the case no one here on the forum is going to be able to help you much because we don't know your current DB state. Running DBM may fix some of these issues if you can't figure out why something is showing, but the best bet is to contact support.
That being said, here is the query that is run in 16.3 to make that popup show. This will only tell you how many issues there are to be fixed.
Code: Select all
SELECT COUNT(*)
FROM (
SELECT claimpayment.ClaimPaymentNum
FROM claimpayment
WHERE claimpayment.IsPartial = 1
AND claimpayment.CheckDate <= DATE(NOW())
AND claimpayment.CheckDate >= DATE(NOW()-INTERVAL 1 MONTH)
UNION ALL
SELECT claimproc.ClaimProcNum
FROM claimproc
WHERE claimproc.ClaimPaymentNum = 0
AND claimproc.InsPayAmt != 0
AND claimproc.Status IN(1,4,5)
AND claimproc.DateEntry <= DATE(NOW())
AND claimproc.DateEntry >= DATE(NOW()-INTERVAL 1 MONTH)
) partialpayments
Here is the two parts of this query with a little more info to help you track down which payment or insurance estimate is causing this dialogue box to show.
Code: Select all
/*Insurance payment partial*/SELECT claimpayment.ClaimPaymentNum, claimpayment.CheckDate, claimpayment.CheckAmt, claimpayment.DepositNum
FROM claimpayment
WHERE claimpayment.IsPartial = 1
AND claimpayment.CheckDate <= DATE(NOW())
AND claimpayment.CheckDate >= DATE(NOW()-INTERVAL 1 MONTH)
Code: Select all
/*Insurance estimate not attached to a claim payment*/
SELECT claimproc.ClaimProcNum, claimproc.PatNum, claimproc.ProcDate, claimproc.InsPayAmt
FROM claimproc
WHERE claimproc.ClaimPaymentNum = 0
AND claimproc.InsPayAmt != 0
AND claimproc.Status IN(1,4,5)
AND claimproc.DateEntry <= DATE(NOW())
AND claimproc.DateEntry >= DATE(NOW()-INTERVAL 1 MONTH)
Re: Insurance payment not finalized error message
Posted: Sun Mar 18, 2018 9:28 pm
by rhaber123
Running OD V 15.3 - I had an insurance claim for a child patient that I thought the insurance will pay the parent, so we paid the claim zero, and closed it.
But surprisingly, we gat a check on Thursday, as my receptionist can not delete payment after a certain period of time, she asked me to delete the payment.
While I was at it, and after I deleted the payment , we were both working on recreating the claim and paying it at the same time.
So now I have the same error message you were talking about.
I ran Database Maintenance, but this did not help,
I ran the above queries, and nothing is showing.
Any help will be appreciated
Re: Insurance payment not finalized error message
Posted: Mon Mar 19, 2018 9:46 am
by tgriswold
On 15.3 it's hard to say, could have been a bug that we fixed a long time ago or we hadn't created/enhanced a DBM to fix it yet. You'd have to either update to a newer version, or go scan through the source code of 15.3 yourself and see what query is being run to show that message.
You should double check that an insurance check was created and finalized for the claim you added payment for, and that the duplicate claim that was created (since 2 people worked on the same) got deleted.
Re: Insurance payment not finalized error message
Posted: Mon Mar 19, 2018 3:13 pm
by rhaber123
On OD V17.3.70. fixed now. Thank you