Plan Notes Not Importing Correctly for Some Patients (Missing Text in Insurance Plan Notes)

[ironhawk1307]

Hi Team,

We’re facing an issue with one of our clinics where insurance plan notes are not consistently reflecting after import/integration.

Any guidance would be appreciated.

Thanks!

[ishubham2101]

"Not consistently, and only for some patients" is the tell. That pattern almost always means the notes imported fine but are attached to a duplicate plan record. An import typically creates a second insplan row for the same carrier + group. The note lands on one copy, and some patients' inssub still points at the other copy, which has an empty note. In the UI the note just looks missing for those patients.

Two read-only queries to confirm it before you change anything.

1. Carrier/group combinations that have both a note-bearing and a note-less copy:

Code: Select all

SELECT
    c.CarrierName,
    ip.GroupNum,
    ip.GroupName,
    COUNT(DISTINCT ip.PlanNum) AS plan_copies,
    SUM(CASE WHEN TRIM(COALESCE(ip.PlanNote,'')) =  '' THEN 1 ELSE 0 END) AS copies_without_note,
    SUM(CASE WHEN TRIM(COALESCE(ip.PlanNote,'')) <> '' THEN 1 ELSE 0 END) AS copies_with_note,
    COUNT(DISTINCT pp.PatNum) AS patients_attached
FROM insplan ip
    INNER JOIN carrier c  ON c.CarrierNum = ip.CarrierNum
    LEFT  JOIN inssub  s  ON s.PlanNum    = ip.PlanNum
    LEFT  JOIN patplan pp ON pp.InsSubNum = s.InsSubNum
GROUP BY c.CarrierName, ip.GroupNum, ip.GroupName
HAVING copies_without_note > 0 AND copies_with_note > 0
ORDER BY patients_attached DESC, plan_copies DESC;

Any row returned is a group where the note exists on one copy and not another.

2. The specific patients sitting on the note-less copy, with their clinic:

Code: Select all

SELECT
    c.CarrierName,
    ip.GroupNum,
    ip.GroupName,
    ip.PlanNum   AS plan_without_note,
    twin.PlanNum AS plan_with_note,
    pat.ClinicNum,
    pat.PatNum, pat.LName, pat.FName
FROM insplan ip
    INNER JOIN carrier c ON c.CarrierNum = ip.CarrierNum
    INNER JOIN insplan twin
            ON  twin.CarrierNum = ip.CarrierNum
            AND twin.GroupNum   = ip.GroupNum
            AND twin.PlanNum   <> ip.PlanNum
            AND TRIM(COALESCE(twin.PlanNote,'')) <> ''
    INNER JOIN inssub  s   ON s.PlanNum    = ip.PlanNum
    INNER JOIN patplan pp  ON pp.InsSubNum = s.InsSubNum
    INNER JOIN patient pat ON pat.PatNum   = pp.PatNum
WHERE TRIM(COALESCE(ip.PlanNote,'')) = ''
ORDER BY c.CarrierName, ip.GroupNum, pat.LName;

plan_with_note is where the text you are missing actually lives. If the affected patients cluster in one ClinicNum, that confirms it came in with that clinic's import.

Two things worth checking before you conclude anything:

• You may be looking at the wrong note field. insplan.PlanNote is the plan-level note. There is also a subscriber-level note on inssub — if what's missing is per-subscriber rather than per-plan, swap ip.PlanNote for the inssub note column and re-run.
• Run both read-only first and confirm against two or three patients in the UI before merging or repointing anything. Duplicate-plan cleanup is easy to get wrong and hard to undo. Column names shift slightly between versions, so if either query errors, post your version and the error and I'll adjust it.

If the queries come back empty then it isn't duplicates, and I'd look at the import path instead — say so and I'll suggest what to check next.

I write custom Open Dental queries to order if that's easier than doing it yourself: $150 for one query in 48 hours, $450 for three plus a Google Sheet that refreshes. Delivered as a .sql file you run on your own server, so no patient data leaves your building and there's nothing to sign.