Insurance Aging Report

For users or potential users.
Post Reply
colellac
Posts: 10
Joined: Thu Dec 03, 2015 6:59 pm

Insurance Aging Report

Post by colellac » Thu Dec 03, 2015 7:02 pm

Is there a way to print an insurance aging report to include only PPO insurance or if I wanted to exclude specific insurances?

Thanks in advance.

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Insurance Aging Report

Post by cmcgehee » Fri Dec 04, 2015 11:38 am

This report shows outstanding claims from PPO insurances where you can excluded specific carriers. Let us know if this works for you.

/*Modified 1071 List of outstanding PPO claims excluding specific carriers*/
/*Query code written/modified: 12/04/2015*/
SET @CarriersToExclude="Alliance Medical|Moda Insurance";/*Enter carriers separated by a pipe ('|' without the quotes). To see all leave blank as ""*/

SET @CarriersToExclude=(CASE WHEN @CarriersToExclude="" THEN "^" ELSE CONCAT('^',REPLACE(@CarriersToExclude,"|","$|^"),"$") END);
SELECT ca.CarrierName,CONCAT(pa.FName,' ',pa.LName) AS 'Patient Name',DATE_FORMAT(pa.Birthdate,"%m/%d/%Y") AS DOB ,
CONCAT(pat.FName,' ',pat.LName) AS 'Subscriber Name',ins.SubscriberID, DATE_FORMAT(pat.Birthdate,"%m/%d/%Y") AS 'Subscriber DOB'
FROM patient pa
INNER JOIN claim cla ON pa.PatNum=cla.PatNum
AND cla.ClaimStatus='S'
INNER JOIN inssub ins ON cla.InsSubNum=ins.InsSubNum
INNER JOIN insplan ip ON ins.PlanNum=ip.PlanNum
AND ip.PlanType='p'
INNER JOIN carrier ca ON ip.CarrierNum=ca.CarrierNum
AND ca.CarrierName NOT REGEXP @CarriersToExclude
INNER JOIN patient pat ON ins.Subscriber=pat.PatNum
Last edited by cmcgehee on Fri Jan 08, 2016 9:15 am, edited 1 time in total.
Chris McGehee
Open Dental Software
http://www.opendental.com

colellac
Posts: 10
Joined: Thu Dec 03, 2015 6:59 pm

Re: Insurance Aging Report

Post by colellac » Tue Dec 08, 2015 8:16 pm

Hey Chris. Thank you for your reply. Unfortunately what you gave me did not make much sense. Is this coding for the software to have that option? At least that is what it appears to me. If so where do I go to modify this new coding?

Thanks again.

allends
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Insurance Aging Report

Post by allends » Wed Dec 09, 2015 7:19 am

That is an SQL query. It runs in the User Query section of OpenDental and outputs information from the database. If you go to Reports>User Query, you will see a box at the top you can paste this in. After that, hit the "Submit Query" button and you will see you information displayed in the bottom half of the form.

At the top of that query is a piped '|' list of carriers.
This line:
SET @CarriersToExclude="Alliance Medical|Moda Insurance";/*Enter carriers separated by a pipe ('|' without the quotes). To see all leave blank as ""*/

If you want to exclude certain carriers simple type the name between the quotes, separating each name by a '|'
Allen
Open Dental Software
http://www.opendental.com

colellac
Posts: 10
Joined: Thu Dec 03, 2015 6:59 pm

Re: Insurance Aging Report

Post by colellac » Wed Dec 09, 2015 11:37 am

Thanks again for the reply. I think I got it but before I go forward because I do not want to screw anything up,

Does this SQL Query start at this

/*Modified 1071 List of outstanding PPO claims excluding specific carriers*/
/*Query code written/modified: 12/04/2015*/
SET @CarriersToExclude="Alliance Medical|Moda Insurance";/*Enter carriers separated by a pipe ('|' without the quotes). To see all leave blank as ""*/

or the information below it.

This shows two medical insurances examples, "Alliance Medical" and "Moda Insurance." Is this were I would be including the insurances I want to exclude obviously separated by |

apologize for the constant questions, just want to make sure I do it right. Is this also a query I can remove if need be?

allends
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Insurance Aging Report

Post by allends » Wed Dec 09, 2015 12:02 pm

Does this SQL Query start at this

/*Modified 1071 List of outstanding PPO claims excluding specific carriers*/
/*Query code written/modified: 12/04/2015*/
SET @CarriersToExclude="Alliance Medical|Moda Insurance";/*Enter carriers separated by a pipe ('|' without the quotes). To see all leave blank as ""*/
Yes you need to run that to set the variable "@CarriersToExclude" for the query.
Is this were I would be including the insurances I want to exclude obviously separated by |
Yes, this is what you would change to exclude the specific carriers.
Is this also a query I can remove if need be?
This query shouldn't save to favorites unless you choose to, but if you do, you can always remove it afterwards. Also this query will not change any information in your database. It simply gives you access to see it.
apologize for the constant questions
No problem, happy to help. :D
Allen
Open Dental Software
http://www.opendental.com

colellac
Posts: 10
Joined: Thu Dec 03, 2015 6:59 pm

Re: Insurance Aging Report

Post by colellac » Wed Dec 09, 2015 3:14 pm

I am sorry. I may be doing something wrong.

first I copied and pasted this


/*Modified 1071 List of outstanding PPO claims excluding specific carriers*/
/*Query code written/modified: 12/04/2015*/
SET @CarriersToExclude="Alliance Medical|Moda Insurance";/*Enter carriers separated by a pipe ('|' without the quotes). To see all leave blank as ""*/

SET @CarriersToExclude=CONCAT('^',REPLACE(@CarriersToExclude,"|","$|^"),"$");
SELECT ca.CarrierName,CONCAT(pa.FName,' ',pa.LName) AS 'Patient Name',DATE_FORMAT(pa.Birthdate,"%m/%d/%Y") AS DOB ,
CONCAT(pat.FName,' ',pat.LName) AS 'Subscriber Name',ins.SubscriberID, DATE_FORMAT(pat.Birthdate,"%m/%d/%Y") AS 'Subscriber DOB'
FROM patient pa
INNER JOIN claim cla ON pa.PatNum=cla.PatNum
AND cla.ClaimStatus='S'
INNER JOIN inssub ins ON cla.InsSubNum=ins.InsSubNum
INNER JOIN insplan ip ON ins.PlanNum=ip.PlanNum
AND ip.PlanType='p'
INNER JOIN carrier ca ON ip.CarrierNum=ca.CarrierNum
AND ca.CarrierName NOT REGEXP @CarriersToExclude
INNER JOIN patient pat ON ins.Subscriber=pat.PatNum

When I submit it seems to work but does not give any information. It shows at the top name, DOB etc.

So I tried following the sentence where it says, "To see all leave blank as ""*/" So I do this,


/*Modified 1071 List of outstanding PPO claims excluding specific carriers*/
/*Query code written/modified: 12/04/2015*/
SET @CarriersToExclude=""*/

SET @CarriersToExclude=CONCAT('^',REPLACE(@CarriersToExclude,"|","$|^"),"$");
SELECT ca.CarrierName,CONCAT(pa.FName,' ',pa.LName) AS 'Patient Name',DATE_FORMAT(pa.Birthdate,"%m/%d/%Y") AS DOB ,
CONCAT(pat.FName,' ',pat.LName) AS 'Subscriber Name',ins.SubscriberID, DATE_FORMAT(pat.Birthdate,"%m/%d/%Y") AS 'Subscriber DOB'
FROM patient pa
INNER JOIN claim cla ON pa.PatNum=cla.PatNum
AND cla.ClaimStatus='S'
INNER JOIN inssub ins ON cla.InsSubNum=ins.InsSubNum
INNER JOIN insplan ip ON ins.PlanNum=ip.PlanNum
AND ip.PlanType='p'
INNER JOIN carrier ca ON ip.CarrierNum=ca.CarrierNum
AND ca.CarrierName NOT REGEXP @CarriersToExclude
INNER JOIN patient pat ON ins.Subscriber=pat.PatNum

And it gives me and error that my SQL syntax is wrong.

Basically I just want the PPO insurance and do not want it to show Medicaid.

User avatar
dgraffeo
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: Insurance Aging Report

Post by dgraffeo » Wed Dec 09, 2015 3:44 pm

You were close. What it means is that the line should literally be:

SET @CarriersToExclude=""; (a blank set of quotation marks)
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Insurance Aging Report

Post by cmcgehee » Wed Dec 09, 2015 5:41 pm

SET @CarriersToExclude=""*/
The reason why this gave you an error is because you are missing the semicolon at the end of the line and because you have an extra "*/".

I realize now that the query that I originally gave you will exclude all carriers if you have SET @CarriersToExclude="";. So I rewrote it a little so that if you leave that blank, it will include all carriers.

/*Modified 1071 List of outstanding PPO claims excluding specific carriers*/
/*Query code written/modified: 12/09/2015*/
SET @CarriersToExclude=""; /*Enter carriers separated by a pipe ('|' without the quotes). To see all leave blank as ""
---------------------------------------------------------------------------------------------*/
SET @CarriersToExclude=(CASE WHEN @CarriersToExclude="" THEN "a{10}"/*A string designed to not match anything*/ ELSE CONCAT('^',REPLACE(@CarriersToExclude,"|","$|^"),"$") END);
SELECT ca.CarrierName,CONCAT(pa.FName,' ',pa.LName) AS 'Patient Name',DATE_FORMAT(pa.Birthdate,"%m/%d/%Y") AS DOB ,
CONCAT(pat.FName,' ',pat.LName) AS 'Subscriber Name',ins.SubscriberID, DATE_FORMAT(pat.Birthdate,"%m/%d/%Y") AS 'Subscriber DOB'
FROM patient pa
INNER JOIN claim cla ON pa.PatNum=cla.PatNum
AND cla.ClaimStatus='S'
INNER JOIN inssub ins ON cla.InsSubNum=ins.InsSubNum
INNER JOIN insplan ip ON ins.PlanNum=ip.PlanNum
AND ip.PlanType='p'
INNER JOIN carrier ca ON ip.CarrierNum=ca.CarrierNum
AND ca.CarrierName NOT REGEXP @CarriersToExclude
INNER JOIN patient pat ON ins.Subscriber=pat.PatNum
Chris McGehee
Open Dental Software
http://www.opendental.com

colellac
Posts: 10
Joined: Thu Dec 03, 2015 6:59 pm

Re: Insurance Aging Report

Post by colellac » Thu Dec 10, 2015 8:27 pm

cmcgehee wrote:
SET @CarriersToExclude=""*/
The reason why this gave you an error is because you are missing the semicolon at the end of the line and because you have an extra "*/".

I realize now that the query that I originally gave you will exclude all carriers if you have SET @CarriersToExclude="";. So I rewrote it a little so that if you leave that blank, it will include all carriers.

/*Modified 1071 List of outstanding PPO claims excluding specific carriers*/
/*Query code written/modified: 12/09/2015*/
SET @CarriersToExclude=""; /*Enter carriers separated by a pipe ('|' without the quotes). To see all leave blank as ""
---------------------------------------------------------------------------------------------*/
SET @CarriersToExclude=(CASE WHEN @CarriersToExclude="" THEN "a{10}"/*A string designed to not match anything*/ ELSE CONCAT('^',REPLACE(@CarriersToExclude,"|","$|^"),"$") END);
SELECT ca.CarrierName,CONCAT(pa.FName,' ',pa.LName) AS 'Patient Name',DATE_FORMAT(pa.Birthdate,"%m/%d/%Y") AS DOB ,
CONCAT(pat.FName,' ',pat.LName) AS 'Subscriber Name',ins.SubscriberID, DATE_FORMAT(pat.Birthdate,"%m/%d/%Y") AS 'Subscriber DOB'
FROM patient pa
INNER JOIN claim cla ON pa.PatNum=cla.PatNum
AND cla.ClaimStatus='S'
INNER JOIN inssub ins ON cla.InsSubNum=ins.InsSubNum
INNER JOIN insplan ip ON ins.PlanNum=ip.PlanNum
AND ip.PlanType='p'
INNER JOIN carrier ca ON ip.CarrierNum=ca.CarrierNum
AND ca.CarrierName NOT REGEXP @CarriersToExclude
INNER JOIN patient pat ON ins.Subscriber=pat.PatNum
Thanks again for the reply. I do realize now what I did. I took an html course and remembered that /****/ was used to make notes that do not interfere with code along with always closing out with semi-colon.

I went ahead and pasted this code leaving it blank as well. It works but it does not provide any insurance information. I get the name, DOB, Carrier name tabs at the top but not information needed to print out below it. I attempt putting in "PPO" or "medicaid" and still got the same result.

I even went ahead and tried to modify it a different way. Since I just want PPO I did,

SET @CarriersToInclude="PPO";

Any ideas?

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Insurance Aging Report

Post by tgriswold » Fri Dec 11, 2015 2:29 pm

If you continue having trouble getting what you are wanting from this query I recommend checking out http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx. There an OD query engineer can assist you in getting the information you need.

To answer your immediate question, you would have to change

Code: Select all

AND ca.CarrierName NOT REGEXP @CarriersToExclude
to

Code: Select all

AND ca.CarrierName REGEXP @CarriersToInclude


But then you'd also need to change the two set statements at the top to reference @CarriersToInclude as well.
Travis Griswold
Open Dental Software
http://www.opendental.com

colellac
Posts: 10
Joined: Thu Dec 03, 2015 6:59 pm

Re: Insurance Aging Report

Post by colellac » Sat Jan 02, 2016 10:38 am

Good morning all. Looking to hopefully get this resolved. I am in need to get this type of report printed but have not been able to accomplish it.

I have entered this query and do not get any errors but I also do not get any information.

/*Modified 1071 List of outstanding PPO claims excluding specific carriers*/
/*Query code written/modified: 12/09/2015*/
SET @CarriersToExclude=""; /*Enter carriers separated by a pipe ('|' without the quotes). To see all leave blank as ""
---------------------------------------------------------------------------------------------*/
SET @CarriersToExclude=(CASE WHEN @CarriersToExclude="" THEN "a{10}"/*A string designed to not match anything*/ ELSE CONCAT('^',REPLACE(@CarriersToExclude,"|","$|^"),"$") END);
SELECT ca.CarrierName,CONCAT(pa.FName,' ',pa.LName) AS 'Patient Name',DATE_FORMAT(pa.Birthdate,"%m/%d/%Y") AS DOB ,
CONCAT(pat.FName,' ',pat.LName) AS 'Subscriber Name',ins.SubscriberID, DATE_FORMAT(pat.Birthdate,"%m/%d/%Y") AS 'Subscriber DOB'
FROM patient pa
INNER JOIN claim cla ON pa.PatNum=cla.PatNum
AND cla.ClaimStatus='S'
INNER JOIN inssub ins ON cla.InsSubNum=ins.InsSubNum
INNER JOIN insplan ip ON ins.PlanNum=ip.PlanNum
AND ip.PlanType='p'
INNER JOIN carrier ca ON ip.CarrierNum=ca.CarrierNum
AND ca.CarrierName NOT REGEXP @CarriersToExclude
INNER JOIN patient pat ON ins.Subscriber=pat.PatNum

The query generates but does not provide the info below it. Could there be something missing? Thank you in a advance.

User avatar
Jandrew
Posts: 17
Joined: Thu Jun 11, 2015 2:45 pm

Re: Insurance Aging Report

Post by Jandrew » Mon Jan 04, 2016 3:05 pm

While the report that you've been using is not an aging report, it should still be pulling up all open (sent but not received) claims. If it comes up blank, then perhaps you do not have any outstanding claims as defined by the report. What exactly are you looking for when you say you want an insurance aging report?

The report looks correct, and if it does not throw an error then that means that all of the syntax is fine.
However, there are still many reasons that you may not be getting any results when running it, but it's a little difficult to troubleshoot when we can't really see what is going on.
If this report is a pressing issue for you, I would highly recommend you give us a call so that one of our technicians can take a look at why the report may not be working on your end. Our number is 503.363.5432 so please don't hesitate!
Programmer (n.): A machine that turns coffee into code.

Andrew Jansen
Open Dental Software
Software Engineer
andrew@opendental.com
503-363-5432

colellac
Posts: 10
Joined: Thu Dec 03, 2015 6:59 pm

Re: Insurance Aging Report

Post by colellac » Tue Jan 05, 2016 6:21 am

Jandrew wrote:While the report that you've been using is not an aging report, it should still be pulling up all open (sent but not received) claims. If it comes up blank, then perhaps you do not have any outstanding claims as defined by the report. What exactly are you looking for when you say you want an insurance aging report?

The report looks correct, and if it does not throw an error then that means that all of the syntax is fine.
However, there are still many reasons that you may not be getting any results when running it, but it's a little difficult to troubleshoot when we can't really see what is going on.
If this report is a pressing issue for you, I would highly recommend you give us a call so that one of our technicians can take a look at why the report may not be working on your end. Our number is 503.363.5432 so please don't hesitate!

I want to generate a report of claims that have been sent out but have not been paid yet. I want this report to be able to exclude certain insurance. If I go to reports, monthly, aging of A/R it generates a report for me but includes everything. I am just interested in PPO insurance.

JoeMontano
Posts: 64
Joined: Thu Aug 20, 2015 11:08 am

Re: Insurance Aging Report

Post by JoeMontano » Tue Jan 05, 2016 7:47 am

Have you taken a look at our custom querys page? http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
Here we have over 1000 query reports we provide for customers and you may be able to find one that will work for you. Otherwise you may want to look into submitting a query request since that is how we typically handle custom reports.
Here is the link for the query request form http://opendentalsoft.com:1942/ODQueryR ... tForm.aspx
Joe Montaño
Software Engineer
Open Dental Software
Joe@OpenDental.com

Post Reply