Open Dental Forum

Hi Team,

Can you give me a query that retrieves all procedure details from the Active Treatment Plan section of a patient’s account? Specifically, I need the Fee, Primary Insurance, Secondary Insurance, Discount, and Balance for each procedure.
I want to calculate the total amount of all scheduled procedures in the active treatment plan. After that, we will subtract this total from the current family due, so we can add a note in the appointment showing the remaining amount the patient needs to pay.

Thank You

beacondental wrote: Wed Nov 26, 2025 6:14 am Hi Team,

Can you give me a query that retrieves all procedure details from the Active Treatment Plan section of a patient’s account? Specifically, I need the Fee, Primary Insurance, Secondary Insurance, Discount, and Balance for each procedure.
I want to calculate the total amount of all scheduled procedures in the active treatment plan. After that, we will subtract this total from the current family due, so we can add a note in the appointment showing the remaining amount the patient needs to pay.

Thank You

Hello beacondental,

We generally don't write custom SQL queries on the forum, since each office's workflow and reporting needs are unique. Writing custom queries falls on the developer or IT professional implementing them. However, we do provide a large collection of sample queries that you can use as a starting point:

https://opendentalsoft.com:1943/ODQuery ... yList.aspx

There are several treatment plan related examples that may help you build what you need.

Thanks!

Okay. Thank You for the response.

Depending on your situation, I've found the APIs to be a bit easier/more efficient for this task. It also doesn't clutter up your audit table with short query calls.

Two things to be mindful of:

• Estimates are generated when a user adds the procedure from the chart or the user views the treatment plan module. This means estimates can be outdated or wrong depending on when it was last generated and given any limitations/exclusions setup for the patient's plan.
• Don't forget about estimates for Discount Plans (if your practices uses this feature).

We created this query to retrieve information about Active Treatment Plan Procedures. When we run it for all patients scheduled for appointments on a given day, most results are correct, but a few come back wrong. When we run the query again, those previously incorrect patients return correct results, but some other random patients then show incorrect data. Has this type of issue occurred before, and what could be a possible fix? The query itself returns accurate values and does not include any discount plan.


select PatNum,ProcNum,Priority,Tth,Surf,ProcCode as Code,Description,Fee,PrimaryInsEst,SecondaryInsEst,Discount1Discount2 as Discount,CAST(Fee - PrimaryInsEst - SecondaryInsEst - Discount1 - Discount2 AS DECIMAL(12,2)) AS PatPortion,ClinicNum from (SELECT
tp.PatNum,pl.ProcNum,
CASE WHEN d.ItemName IS NOT NULL THEN CAST(d.ItemName AS INT) ELSE 9999 END as Priority,
CAST(pl.ToothNum AS INT) as Tth,
pl.Surf,
pc.ProcCode,
pc.Descript as Description,
CASE WHEN pl.procFee > 0 THEN pl.procFee ELSE 9999999 END as Fee,

COALESCE(
(SELECT cpr1.InsPayEst
FROM claimproc cpr1
WHERE cpr1.ProcNum = pl.ProcNum
AND cpr1.PlanNum = is1.PlanNum
AND cpr1.Patnum=tp.PatNum
ORDER BY SecDateTEdit desc
LIMIT 1
), 0
) AS PrimaryInsEst,

COALESCE(
(SELECT cpr2.InsPayEst
FROM claimproc cpr2
WHERE cpr2.ProcNum = pl.ProcNum
AND cpr2.PlanNum = is2.PlanNum
AND cpr2.Patnum=tp.PatNum
ORDER BY SecDateTEdit desc
LIMIT 1
), 0
) AS SecondaryInsEst,

COALESCE((select SUM(CASE WHEN cpr3.WriteOffEst = -1 THEN 0.00 ELSE cpr3.WriteOffEst END) as WriteOffEst from claimproc cpr3 where cpr3.ProcNum=tpp.ProcNum order by cpr3.SecDateTEdit desc),0.00) as Discount1,

pl.Discount as Discount2,

0.00 as PatPortion,

pl.ClinicNum

FROM treatplan tp
INNER JOIN treatplanattach tpp ON tp.TreatPlanNum = tpp.TreatPlanNum
INNER JOIN procedurelog pl ON tpp.ProcNum = pl.ProcNum
INNER JOIN procedurecode pc ON pl.CodeNum = pc.CodeNum
LEFT JOIN definition d ON tpp.Priority=d.DefNum

LEFT JOIN patplan pp1 ON pp1.PatNum = tp.PatNum AND pp1.Ordinal = 1
LEFT JOIN inssub is1 ON pp1.InsSubNum=is1.InsSubNum

LEFT JOIN patplan pp2 ON pp2.PatNum = tp.PatNum AND pp2.Ordinal = 2
LEFT JOIN inssub is2 ON pp2.InsSubNum=is2.InsSubNum

WHERE
tp.PatNum = patNum
AND tpp.Priority NOT IN (148)
AND AptNum <> 0
AND pl.procFee > 0
AND tp.ResponsParty = 0
AND tp.TPStatus = 1 -- ACTIVE treatment plan
ORDER BY pl.ProcDate) as t order by Priority,Tth,Surf,Fee asc