Insurance Aging Report

[colellac]

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.

[cmcgehee]

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

[colellac]

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]

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 '|'

[colellac]

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]

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.

[colellac]

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.

[dgraffeo]

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

SET @CarriersToExclude=""; (a blank set of quotation marks)

[cmcgehee]

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

[colellac]

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]

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.

[colellac]

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.

[Jandrew]

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!

[colellac]

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]

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