Purpose
Generate a weekly report of patients with outstanding balance in Open Dental, separating what insurance owes vs. what patient owes, so Billing Jr can prioritize follow-up.
Time: ~3-10 min runtime depending on client size ·
Audience: Billing Junior + Manager
Pre-checks
| Check | How to verify |
|---|---|
| Client has OD API enabled | "has_od_api": true in clients/<id>.json |
| venv activated | Prompt starts with (.venv) |
| Access to client Google Sheet | Configured in clients/<id>.json under google_sheet |
How to run
1Basic mode (all patients with balance)
python patient_ar_audit.py <client-id>
Default: scans procedures from last 365 days, groups by patient, calculates balance, writes to client Google Sheet.
2With min-days outstanding filter
python patient_ar_audit.py <client-id> --min-days 30
Skips claims newer than X days (default 30). Useful to not call insurance for a 5-day-old claim.
3"All balances" mode (no Stedi check)
python patient_ar_audit.py <client-id> --all-with-balance --no-stedi-check
For clients migrated from other PMS (Hallandale type). Lists ALL patients with balance without checking Stedi status.
What the report contains
| Column | Meaning |
|---|---|
| PatNum | Patient Number in OD |
| Chart # | Chart Number (if any) |
| Patient Name | Last, First |
| Last Procedure DOS | Date of last complete procedure |
| Days Outstanding | Days since last procedure |
| BalTotal | Total patient owes |
| InsBalance | Of that total, how much insurance owes |
| PatBalance | Of that total, how much patient owes |
| Insurance Carrier | Patient's primary carrier |
| Subscriber ID | Subscriber ID |
| Group # | Group number |
| Phone | Patient phone for contact |
| Action | System-suggested action |
Workflow to work the report
1Open client Google Sheet
Tab with client prefix + "Patient AR Audit" + timestamp.
2Sort by Days Outstanding (descending)
Attack the oldest first (filing limit risk).
3For each patient with InsBalance > 0:
- Was claim sent? → if not, send it
- Was claim denied? → see SOP-CL-005 (Common Denials)
- More than 60 days? → Stedi 276 status check + escalate if needed
- More than 90 days? → consider appeal
4For each patient with PatBalance > 0:
- Call patient (script in dashboard)
- Offer payment plan if balance >$500
- Note contact in OD
5Mark resolved in sheet
Each worked row → mark status. Next run won't show it again if balance dropped to 0.
Special cases system detects automatically
| Detection | Why it matters |
|---|---|
| Insurance payment received but claim still marked pending | Need to post EOB in OD |
| InsPayAmt > 0 but claim not linked | Orphan data — investigate and fix |
| BalTotal <= 0 | Balance already zero — skip |
| WriteOff/DedApplied > 0 without payment | Adjustment already applied — verify if expected |
| Non-ADA procedures (DEDUC, ADJUST) | Filter out — not real procedures |
Historical note: System was refined after "Wendy Jackson Cabrera" case in April 2026 — had $650 Ins Payment that wasn't detected because only ClaimNum was checked. Now detects InsPayAmt, WriteOff, DedApplied and BalTotal.
Troubleshooting
| Error | Solution |
|---|---|
Intermittent ReadTimeout | Re-run — has automatic retry with exponential backoff |
| OD returns 0 patients | Endpoint /patients doesn't expose BalTotal. Script already pivoted to use /procedurelogs |
DateTStamp filter rejected | OD doesn't accept that filter. Script paginates all and filters client-side |
| Hits Sheet row limit | Script already calls _ensure_capacity() before each write |
Reference commands
# Basic mode (recommended for OD API clients) python patient_ar_audit.py benitez-dental # With min threshold python patient_ar_audit.py casas-family-dentistry --min-days 30 # Legacy mode (migrated clients) python patient_ar_audit.py hallandale-dental --all-with-balance --no-stedi-check # Single patient only (debug) python patient_ar_audit.py benitez-dental --single-patient 12345