How to retrieve Open Dental's calculated insurance and patient portions?

[rinse-dental]

Is it possible to retrieve Open Dental's calculations for the following:

• Estimated patient portion as found in the appointment screen in Open Dental
• Treatment plan cost break down including Insurance portion, Patient portion, writeoff, etc. as found in the Treatment Plan screen

If not, is it possible to include these in the API return for both GET Appointment and GET Procedurelog? Or, perhaps a GET Estimates api?

re: ShortQuery. I understand this is also an option - has anyone come up with a simple ShortQuery to return the Open Dental calculations/estimates for a specific appointment?

[justine]

Is it possible to retrieve Open Dental's calculations for the following:

• Estimated patient portion as found in the appointment screen in Open Dental
• Treatment plan cost break down including Insurance portion, Patient portion, writeoff, etc. as found in the Treatment Plan screen

If not, is it possible to include these in the API return for both GET Appointment and GET Procedurelog? Or, perhaps a GET Estimates api?

re: ShortQuery. I understand this is also an option - has anyone come up with a simple ShortQuery to return the Open Dental calculations/estimates for a specific appointment?

Good morning rinse-dental,

I'm going to research the exact API workflow for calculating estimated patient portion and discuss the Treatment Plan Module with the API team. In the meantime, these methods may be helpful:

ClaimProcs GET,
AccountModules GET Aging,
TreatPlans GET,
TreatPlanAttaches GET or ProcTPs GET.

[justine]

Is it possible to retrieve Open Dental's calculations for the following:

• Estimated patient portion as found in the appointment screen in Open Dental
• Treatment plan cost break down including Insurance portion, Patient portion, writeoff, etc. as found in the Treatment Plan screen

If not, is it possible to include these in the API return for both GET Appointment and GET Procedurelog? Or, perhaps a GET Estimates api?

re: ShortQuery. I understand this is also an option - has anyone come up with a simple ShortQuery to return the Open Dental calculations/estimates for a specific appointment?

Calculating patient portion on an appointment via the API.

1. GET ProcedureLogs by PatNum and/or AptNum.

• You now have a list of procedures for a patient's specific appointment.
• Add up every procedurelog.ProcFeeTotal.
• This is the patient's portion before insurance.

2. GET ClaimProcs by PatNum.

• You now have a list of claimprocs for a patient.
• Filter this list where claimproc.ProcNum==procedurelog.ProcNum and claimproc status != Preauth.

3. Calculate insurance estimate.

• Take filtered claimproc list and add every claimproc.InsPayEst, claimproc.WriteOffEstOverride and every claimproc.WriteOffest (where value is not -1) for claimprocs of status NotReceived, Estimate, CapEstimate, or InsHist.
• This is the insurance estimate.

4. Calculate insurance paid.

• Take filtered claimproc list and add every claimproc.InsPayAmt and every claimproc.WriteOff for claimprocs of status Received, Supplemental, CapComplete, and CapClaim.
• This is the the insurance paid.

5. Add results from 3. and 4.

• This is total insurance estimate/paid.

6. Take results from 1 and subtract the results from 5. (Total patient portion for procedures minus total insurance estimate/paid.)

• This is patient portion after insurance.

7. GET Adjustments by PatNum.

• You now have a list of adjustments for a patient.
• Filter this list where adjustment.ProcNum==procedurelog.ProcNum from step 1.
• This is a list of adjustments for procedures on this appointment.
• For every element in this list, add up the adjustment.AdjAmt.
• This is the total adjustment amount.

8. Take total from 7 and add to total from 6.

• You now have the estimated patient portion as seen on an appointment.

[rinse-dental]

Thank you. Will this estimate consider date - similar to the Treatment Plan? For example, if a patient is not eligible for a cleaning until [recall date] and the appointment is booked after [recall date] - will the estimate be based on today, the date the estimate was created, or the scheduled appointment datetime?

[justine]

Thank you. Will this estimate consider date - similar to the Treatment Plan? For example, if a patient is not eligible for a cleaning until [recall date] and the appointment is booked after [recall date] - will the estimate be based on today, the date the estimate was created, or the scheduled appointment datetime?

This estimate is what you'd see on an appointment. In Open Dental, it is calculated when the appointment is opened and procedures are selected.

[rinse-dental]

Hey Justin - I've been really struggling with estimates so I'm hoping you might help me devise a strategy to work through or around the problem.

PROBLEM: The estimate for a scheduled appointment (claimproc) is wrong if EITHER:

• The scheduled procedure has a deductible and is created/added to an appointment directly. (SOLUTION: physically click into the Treatment Plan Module - seems it's a 'feature' ? https://www.opendental.com/manual/appoi ... indow.html)
• The priority for a scheduled procedure is not set or lower than another unscheduled procedure on the treatment plan. This is made evident if deductible, limitations, or maximum applies. (SOLUTION: increase the priority of the scheduled procedure)

EXAMPLE USE CASES:

• #1: PERIO recalls. D4910 typically requires a deductible and if booked via 'Make Recall', select Appointment Type, Quick Adds, or the POST Appointment API, the estimate will be wrong until a user physically clicks into the Treatment Plan Module.
• #2: New Patients with FMX. Increasingly the FMX requires a deductible and, like the aforementioned PERIO use case... the estimate is wrong until a Open Dental user physically clicks into the Treatment Plan Module GUI.
• #3: Deferred or Unscheduled Treatment. A patient defers a prioritized treatment (e.g. does not schedule or later cancels a filling) but continues with their scheduled recall visit. If the prioritized treatment (e.g. filling) exceeds the patients max or limitations, then their recall will always be wrong.
• #4: Out of sync treatment completion. A patient prefers to schedule a priority 2 filling ahead of their priority 1 SRPs. The estimate for their scheduled filling is wrong and can be very wrong in the event the priority 1 procedure exceeds the patients max.

I understand that #3 and #4 can be avoided (or resolved) with a strong front desk and rigorous treatment plan workflow; however, we're starting to see how fragile the 1:1 relationship between appointments and treatment plans are and how easy it is to get out of sync - especially in a high-volume practice that allows patients to book/reschedule/manage their visits online.

Anyway... please let me know if I'm missing something here.

Suggested options:

• GLOBAL solution: It would seem the most straight-forward solution is for Open Dental to include Appointment in it's join for Treatment Plans, then sort (and calculate it's estimate) by Appointment.AptDateTime ASC, then Procedurelog.Priority. Anytime a procedure is scheduled or a new procedure is added to a scheduled appointment (via quick procs, POST API, etc.), Open Dental sorts and recalculates the estimates for that patients treatment plan. This would guarantee the Appointment's (and the claimproc) estimates to be accurate.
• GLOBAL solution: Bifurcate the Appointment Estimate from the Treatment Plan and generate an Appointment Estimate based on the aforementioned logic... perhaps storing it if performance is a concern, then return it in the API.
• Open Dental GUI: Create a visual warning and/or report for appointment estimates that fall into one of the 4 use cases.
• API Only solution: Perhaps this is an issue with digital first practices which are likely using the API... thus, leave Open Dental GUI alone and add a GET Appointment Estimate (or Get ProcedureLog Estimate) which returns the estimate for an appointment (or procedure) based on all available information.

[rinse-dental]

@justine - checking in.

[SLeon]

Good morning,

Because calculating estimates is expensive computationally, Open Dental very intentionally only performs this logic when needed. This is why, as you said, it is described in our manual as a 'feature':

"Estimated patient portion for attached procedures. (patient portion = gross production - estimated insurance write-offs - insurance estimates - discount plan discount). For estimates to calculate correctly you must chart the procedure from the Chart Module then attach them to the appointment. If procedures are added directly to the appointment, you must click into the Treatment Plan Module to update the estimates."

Justine's post on how to compute estimates how they appear in the Appointment Edit window is accurate, and your application can execute this before displaying information to patients in your application. The reason it is called an estimate, however, is because any number of things on the account can happen between the scheduled appointment and the completed appointment, as you noted.

Is it possible to retrieve Open Dental's calculations for the following:

• Estimated patient portion as found in the appointment screen in Open Dental
• Treatment plan cost break down including Insurance portion, Patient portion, writeoff, etc. as found in the Treatment Plan screen

I believe you are referring to the main Procedures grid found in the TP Module. For Active and Inactive treatment plans, you can do what Open Dental does to calculate the row(s) you need:

Code: Select all

 Procedure.ProcFee
 - Claimproc.InsEstTotalOverride or Claimproc.BaseEst for Primary Insurance
 - Claimproc.InsEstTotalOverride or Claimproc.BaseEst for Secondary Insurance
 - Claimproc.WriteOffEstOverride or Claimproc.WriteOffEst
 - Procedure.Discount 
 + Procedure.TaxAmt
=
Patient Portion

For Saved treatment plans, the grid rows are directly from the ProcTP table. You can use ProcTPs GET to obtain them and calculate the patient portion similar to above.

[rinse-dental]

Thanks. I understand that generating an estimate can be expensive - that's the entire purpose of this thread, but you didn't address the issue, at least not directly...that is, Open Dental gives several ways to add procedures to an appointment, including the API, but we should assume the estimate generated is wrong unless the procedure was added via the Chart or a user views the Treatment Plan... which, I think, we have no way of knowing.

I take your response to mean that 1) you're aware, and 2) you have no plans to address the bug via Open Dental or the API. Is that right? Any sense of the incremental computational cost to ensure estimates are accurate?

On my end, I can tell you we've already written off several hundred dollars this year due to wrong OD generated estimates. We've also had team members who make $30-$50/hour (in San Francisco) spend a few hours ensuring that upcoming appointment estimates are accurate. I know that's small potatoes but it matters to us.

By contrast, my cloud computing cost for Open Dental during the same period was less than $150. You might read this and wonder why I'd write off mistakes... if so, it's because they're mistakes, not new information. We automate text reminders to our patients with estimated costs - patients love the transparency. We also confirm costs from the appointment module when they walk in... so estimates are important both financially and from a patient trust POV. Patients understand if costs change when treatment changes but an 'oops sorry, our system left out your deductible' makes us look like an idiot. So, for us... we'll happily wait a few milliseconds when adding/removing procedures to ensure estimates are as accurate as possible.

Sorry if I'm coming off cheeky or overly dramatic - I just can't wrap my head around why this is even an issue.

[omar22]

Hi I am having a similar issue. Is there a way to get the Deductible Applied or Insurance remaining through either a GET ClaimProcs InsAdjust or through a query? I am trying to find the latest Deductible Remaining and Insurance Remaining which shows up when u navigate to Tx Plan (see photo).

Screenshot 2024-02-13 131300.png (26.4 KiB) Viewed 16016 times

Thanks in advance!

[SLeon]

Good afternoon,

I would recommend a query to obtain this information. One of the advantages of this would be the ability to grab the information easily for list of patients.

Calculations for populating the text boxes in your screenshot:

AnnualMax - This is directly from the Benefit table (benefit.MonataryAmt) for the patient's insurance plan (benefit.PlanNum) and of type "Limitations" (benefit.BenefitType=5)

Deductible - Same as the AnnualMax, except of type "Deductible" (benefit.BenefitType=2)
Deductible Remaining - Obtain the ClaimProcs for the patient and sum claimproc.DedApplied. Subtract this value from the Deductible.
Insurance Used - Sum the the ClaimProcs for the patient that are of types "Received", "Adjustment", and "Supplemental" (claimproc.Status=1,3,4)
Insurance Pending - Same as above, except of type "NotReceived" (claimproc.Status=0).

Remaining = AnnualMax - InsUsed - InsPending

You would need to ensure your query considers additional insurance plans, if necessary for your application, and appropriate date ranges for the plan coverage.

[omar22]

Good afternoon,

I would recommend a query to obtain this information. One of the advantages of this would be the ability to grab the information easily for list of patients.

Calculations for populating the text boxes in your screenshot:

AnnualMax - This is directly from the Benefit table (benefit.MonataryAmt) for the patient's insurance plan (benefit.PlanNum) and of type "Limitations" (benefit.BenefitType=5)

Deductible - Same as the AnnualMax, except of type "Deductible" (benefit.BenefitType=2)
Deductible Remaining - Obtain the ClaimProcs for the patient and sum claimproc.DedApplied. Subtract this value from the Deductible.
Insurance Used - Sum the the ClaimProcs for the patient that are of types "Received", "Adjustment", and "Supplemental" (claimproc.Status=1,3,4)
Insurance Pending - Same as above, except of type "NotReceived" (claimproc.Status=0).

Remaining = AnnualMax - InsUsed - InsPending

You would need to ensure your query considers additional insurance plans, if necessary for your application, and appropriate date ranges for the plan coverage.

Can you provide me for the query just for "Insurance Remaining" and "Deductible Remaining" please? Thank you so much

[SLeon]

I would recommend checking out our Query Examples page to see if an existing one will work for you. There is insurance Used, Pending, and Remaining data returned in #1243, for example.

You can also follow the instructions and link at the top of that page to request Open Dental's Query Team to write one for you.

[rinse-dental]

Thanks. I understand that generating an estimate can be expensive - that's the entire purpose of this thread, but you didn't address the issue, at least not directly...that is, Open Dental gives several ways to add procedures to an appointment, including the API, but we should assume the estimate generated is wrong unless the procedure was added via the Chart or a user views the Treatment Plan... which, I think, we have no way of knowing.

I take your response to mean that 1) you're aware, and 2) you have no plans to address the bug via Open Dental or the API. Is that right? Any sense of the incremental computational cost to ensure estimates are accurate?

On my end, I can tell you we've already written off several hundred dollars this year due to wrong OD generated estimates. We've also had team members who make $30-$50/hour (in San Francisco) spend a few hours ensuring that upcoming appointment estimates are accurate. I know that's small potatoes but it matters to us.

By contrast, my cloud computing cost for Open Dental during the same period was less than $150. You might read this and wonder why I'd write off mistakes... if so, it's because they're mistakes, not new information. We automate text reminders to our patients with estimated costs - patients love the transparency. We also confirm costs from the appointment module when they walk in... so estimates are important both financially and from a patient trust POV. Patients understand if costs change when treatment changes but an 'oops sorry, our system left out your deductible' makes us look like an idiot. So, for us... we'll happily wait a few milliseconds when adding/removing procedures to ensure estimates are as accurate as possible.

Sorry if I'm coming off cheeky or overly dramatic - I just can't wrap my head around why this is even an issue.

@SLeon et al, In addition to this bug occurring when procedures are added outside of the Chart Module, it appears it also occurs when either 1) insurance maximums are met/exceeded (via the account module) or 2) when a discount is added to a procedure. We wrote off $225 just today as a result of errors in Open Dental's calculated estimate... I'm using the API as described by you/your colleague to provide an estimate - this estimate matches the apt module estimate and is then texted to the patient prior to their visit. In effect, every estimate is incorrect until a member of our team clicks into the TP module. I am not using the API to process insurance/payments or add/remove discounts.

[beacondental]

Good afternoon,

I would recommend a query to obtain this information. One of the advantages of this would be the ability to grab the information easily for list of patients.

Calculations for populating the text boxes in your screenshot:

AnnualMax - This is directly from the Benefit table (benefit.MonataryAmt) for the patient's insurance plan (benefit.PlanNum) and of type "Limitations" (benefit.BenefitType=5)

Deductible - Same as the AnnualMax, except of type "Deductible" (benefit.BenefitType=2)
Deductible Remaining - Obtain the ClaimProcs for the patient and sum claimproc.DedApplied. Subtract this value from the Deductible.
Insurance Used - Sum the the ClaimProcs for the patient that are of types "Received", "Adjustment", and "Supplemental" (claimproc.Status=1,3,4)
Insurance Pending - Same as above, except of type "NotReceived" (claimproc.Status=0).

Remaining = AnnualMax - InsUsed - InsPending

You would need to ensure your query considers additional insurance plans, if necessary for your application, and appropriate date ranges for the plan coverage.

For Insurance Used, for which field of the ClaimProc table do I need to do the sum of?

[justine]

Good afternoon,

I would recommend a query to obtain this information. One of the advantages of this would be the ability to grab the information easily for list of patients.

Calculations for populating the text boxes in your screenshot:

AnnualMax - This is directly from the Benefit table (benefit.MonataryAmt) for the patient's insurance plan (benefit.PlanNum) and of type "Limitations" (benefit.BenefitType=5)

Deductible - Same as the AnnualMax, except of type "Deductible" (benefit.BenefitType=2)
Deductible Remaining - Obtain the ClaimProcs for the patient and sum claimproc.DedApplied. Subtract this value from the Deductible.
Insurance Used - Sum the the ClaimProcs for the patient that are of types "Received", "Adjustment", and "Supplemental" (claimproc.Status=1,3,4)
Insurance Pending - Same as above, except of type "NotReceived" (claimproc.Status=0).

Remaining = AnnualMax - InsUsed - InsPending

You would need to ensure your query considers additional insurance plans, if necessary for your application, and appropriate date ranges for the plan coverage.

For Insurance Used, for which field of the ClaimProc table do I need to do the sum of?

Good afternoon beacondental,

I believe the value(s) you are looking for is claimproc.InsEstTotal, unless claimproc.InsEstTotalOverride has a value, then you'd use that.

Thanks!

[rinse-dental]

Hi there - We've found that patient estimates for recall appointments booked online or via the Book Recall feature within Open Dental are wrong a majority of the time. This is a result of the issues I called out earlier in the thread or because their appointment in 6 months + 1 day and we're calculating/sharing costs prior to their visit using your suggested method.

At this point, I'm not sure we can trust the claimproc table/API endpoints to calculate estimates and need to resort to a raw calculation using the dozen or so API endpoint required to get there. Before I delve too far into the weeds, I thought I might check to ensure this is even possible?

As far as I can tell I'll need: ProcedureLog, InsuranceHistory, PatPlans, InsSubs, InsPlans, ProcedureCodes, Benefits, and Adjustments. I'll also need ClaimProcs for treatment history and perhaps as a shortcut to in-network write-offs, otherwise, I believe I'd also need FeeSched and Fees. Am I on the right track?

[justine]

Hi there - We've found that patient estimates for recall appointments booked online or via the Book Recall feature within Open Dental are wrong a majority of the time. This is a result of the issues I called out earlier in the thread or because their appointment in 6 months + 1 day and we're calculating/sharing costs prior to their visit using your suggested method.

At this point, I'm not sure we can trust the claimproc table/API endpoints to calculate estimates and need to resort to a raw calculation using the dozen or so API endpoint required to get there. Before I delve too far into the weeds, I thought I might check to ensure this is even possible?

As far as I can tell I'll need: ProcedureLog, InsuranceHistory, PatPlans, InsSubs, InsPlans, ProcedureCodes, Benefits, and Adjustments. I'll also need ClaimProcs for treatment history and perhaps as a shortcut to in-network write-offs, otherwise, I believe I'd also need FeeSched and Fees. Am I on the right track?

Good morning rinse-dental,

I believe you've reached a point where the complexity of your API related questions are beyond the scope of support offered via the forums. My suggestion is to log on to the developer portal and click 'Contact Us' in the upper right. There you will find two emails. Use the second one and send a detailed email explaining your goals and we will get back to you ASAP.

Thanks!

[rinse-dental]

Thanks. I sent an email. I still stand by my notion that this is a defect within Open Dental and could be resolved any number of ways without taxing the DB.