Unfinalized Insurance Payments Query

If you are struggling to reconcile your daily production and income reports because insurance payments seem to be missing, you likely have payments that were received but never finalized. This open dental unfinalized payments query identifies those specific claims so you can finalize them and ensure your financial data is accurate.

The Query

Copy and paste the following code into your User Query window. This query looks for insurance payments that have been entered but are not yet attached to a finalized insurance payment record.

/* Set your date range here */
SET @FromDate = '2026-01-01';
SET @ToDate = '2026-04-08';

SELECT 
    p.LName, 
    p.FName, 
    c.DateSent, 
    c.ClaimStatus, 
    cp.InsPayAmt AS 'UnfinalizedAmount',
    ca.CarrierName
FROM claimproc cp
INNER JOIN claim c ON cp.ClaimNum = c.ClaimNum
INNER JOIN patient p ON c.PatNum = p.PatNum
INNER JOIN insplan ip ON c.PlanNum = ip.PlanNum
INNER JOIN carrier ca ON ip.CarrierNum = ca.CarrierNum
WHERE cp.Status = 0 -- Not Received
AND c.DateSent BETWEEN @FromDate AND @ToDate
AND p.PatStatus = 0 -- Active Patients
ORDER BY c.DateSent;

How to Run This Query

1. In Open Dental, go to Reports in the Main Menu.
2. Click User Query.
3. Paste the query provided above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below.

Understanding the Results

• LName / FName: The last and first name of the patient associated with the claim.
• DateSent: The date the claim was originally sent to the insurance carrier.
• ClaimStatus: The current status of the claim in your system.
• UnfinalizedAmount: The amount of the insurance payment that has not been finalized.
• CarrierName: The name of the insurance company associated with the claim.

How to Customize

You can easily adjust the query to fit your needs by changing the values in the SET statements at the top of the query:

• Change the Date Range: Modify the @FromDate and @ToDate lines. For example, to look at all of March 2026, change the lines to:
  SET @FromDate = '2026-03-01';
SET @ToDate = '2026-03-31';
• Filter by Clinic: If you use the Clinics feature, you can add a line to the WHERE clause to filter by a specific clinic number:
  AND c.ClinicNum = 1 (Replace 1 with your specific ClinicNum).

Variations

If you want to focus only on primary insurance claims, you can add this line to the WHERE clause:
AND c.ClaimType = 'P'

If you want to see only claims with a high dollar amount (e.g., over $500), add this line to the WHERE clause:
AND cp.InsPayAmt > 500

Skip the Query — Use DentalCanvas Instead

Don't want to write SQL? DentalCanvas connects to your Open Dental database and shows you this data automatically in a visual dashboard — no queries required.

This article is provided by opendentalsupport.com, an independent community resource. We are not affiliated with Open Dental Software, Inc.