Welcome, the below is a report for pre-authorizations that have been received and entered into Opendental but the patient does not have appointment with those procedure codes attached.
Note: I have enhanced this query to automatically send SMS/Email message to patients. If you want to share any feedback/modifications to this query, please reach me email@example.com
Collaboration: Feel free to join below fb groups to receive new updates/queries/reports etc.
Step1:Copy the below query and paste into your Report query. Save as favorite Report
/* Lists only active patients between the data range specified. PX Pre-Authorized Tx .Based on ClaimType = PreAuth and Status(R) and InsPayEst > 0*/
SET @StartDate = '2020-07-022';
SET @EndDate = '2021-07-22';
SET @ExclusionCodes = 'D2950,D2950'; -- Exclusion List: Comma delimited list of procedure code
SELECT distinct pc.AbbrDesc ,pc.ProcCode, cp.DateSent, cp.DateReceived,
case when c.CarrierName = 'DENTI-CAL' then cp.DateReceived + INTERVAL 6 MONTH else
cp.DateReceived + INTERVAL 24 MONTH end as PreAuthExpiryDate,
cp.InsPayEst, p.PatNum, DateTP,CONCAT(p.Lname, ',' , p.Fname)AS fullName, p.HmPhone, p.WirelessPhone, p.Email, c.CarrierName
,UPPER(p.Fname) as FName,
p.Lname as LName
FROM procedurelog pl join patient p on pl.PatNum = p.PatNum
join procedurecode pc on pc.codeNum = pl.codeNum
LEFT JOIN patplan pp ON p.PatNum = pp.PatNum
LEFT JOIN inssub ins ON pp.InsSubNum = ins.InsSubNum
LEFT JOIN insplan ip ON ins.PlanNum = ip.PlanNum
LEFT JOIN carrier c ON ip.CarrierNum = c.CarrierNum
LEFT JOIN appointment a on a.PatNum = p.PatNum and a.AptDateTime >= pl.DateTP
INNER JOIN (SELECT
c.ClaimNum, c.PatNum, c.InsPayEst, c.DateSent, c.DateReceived, cp.ProcNum
FROM claimProc cp inner join claim c on c.PatNum = cp.PatNum and c.claimNum = cp.ClaimNum
c.ClaimType = 'PreAuth'
AND c.ClaimStatus = 'R'
AND c.InsPayEst > 0
) cp on pl.ProcNum = cp.ProcNum and pl.PatNum= cp.PatNum
pl.ProcStatus not in (2, 6)
and cp.DateReceived between @StartDate and @EndDate
and pl.AptNum = 0 and p.PatStatus=0
and NOT (IF((LENGTH(@ExclusionCodes) = 0),TRUE,FIND_IN_SET(pc.ProcCode,@ExclusionCodes)));
Step2a: Modify the query dates you would like to run it for.
Step 2b. If there are any Code you would like to exclude from this report, put them in the @ExclusionCodes variables. REMEMBER THIS WILL EXLUDE THOSE CODES FROM THE REPORT.
Step3: Send Message to patients using PatientXpress.
I am using PatientXpress plugin to send emails/sms messages to patients, right inside OD. PatientXpress gives the power and control to manage all our Outbound campaigns/messaging to patients right from OpenDental. All the communication is stored in comm log, so I have complete trail of the patient and office communication in one place without, without the need to login to other browsers, or portal but everything in one place right inside Open Dental. I run PatientXpress scheduler to send emails to patients who received approvals, on a daily basis.
Other Reports: PatientXpress got cool out of the box RECALL and TX over due reports, where you can message all your patients in just couple clicks – happy to share more info, if needed.
If you have any reporting need or need help, feel free to contact .
- What is PatientXpress? How dental offices benefit using PatientXpress? Click here to learn more.
- Dr. Peter received 45 reviews on the first day after launching feedback campaign with PatientXpress – click here to learn more.
- Dr. Larry’s staff saves 15 mins on each patient everyday with use of quick insurance verification on their calendars to quickly spot on the patients failing insurance eligibility. Click here to learn more.
- Click here to learn customizable and paper-less patient forms with PatientXpress.
- Want to launch powerful patient reach outbound campaigns using PatientXpress. Click here to learn more.
- Looking for a HIPAA compliant website assistant or chatbot to perform concierge services, click here to learn more.
About PatientXpress: Our advance technology integration within Open Dental allows our clients to carry out their day-to-day activities in a hassle-free and paperless manner, with Smart Caller ID, Real-Time Insurance Verification, custom online forms, 2-way text, and much much more. Engaged your patients like never before and be the #1 Patient Choice.
Click Request Demo button on PatientXpress page, incase you would like to see one.
About Author: Amar Veeramalla is experienced dental professional providing productivity hacks to dentists, dental professionals and staff on Patient Engagement, Case Acceptance & Patient Retention using modern and cutting edge technologies like AI, ML, Advanced SQL languages and other tools. You can reach at firstname.lastname@example.org or on LK: https://www.linkedin.com/in/amarveeramalla/