Billing Manager Whether Insured not working properly V5.0.1(6)

Day-to-Day Users
,
#1

These are the steps taken.

Chosen criteria: Whether insured = Insured and Billing Status = Unbilled; This choice pulls up insured (correct) and all billed encounters (incorrect).

Chosen criteria: Whether Insured = Non Insured; When whether Insured is selected and Non- Insured is set, the BM pulls up insurance encounters along with the non-insured encounters.

Chosen criteria: Whether Insured = Non-insured and Billing Status = Unbilled; This choice returns a blank screen.

Chosen criteria: Whether Insured = Non-insured and Last Level Billed = None; This choice returns a blank screen.

@stephenwaite Has any of this been addressed in 5.0.2?

#2

hi @juggernautsei, not experiencing any of these issues.

#3

Thanks!
Is non insured just those people who don’t have any insurance associated in the program?

#4

yes, it’s an interesting part of the query

#5

Yes, I drilled down to that and I was looking for the hook that called that portion of the query. What I was really trying to do was export the query to file so I could like at it. But I have yet to find a place I could make that happen.

#6

it’s a little further down in billrep where the query is executed

#7

I found the query and it is this:

SELECT form_encounter.pid AS enc_pid, form_encounter.encounter AS enc_encounter, form_encounter.provider_id AS enc_provider_id, billing.* FROM form_encounter LEFT OUTER JOIN billing ON billing.encounter = form_encounter.encounter AND billing.pid = form_encounter.pid AND billing.code_type LIKE '%' AND billing.activity = 1 LEFT OUTER JOIN patient_data on patient_data.pid = form_encounter.pid LEFT OUTER JOIN claims on claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter LEFT OUTER JOIN insurance_data on insurance_data.pid = form_encounter.pid and insurance_data.type = 'primary' WHERE 1=1 AND (insurance_data.provider = '0' or insurance_data.date > form_encounter.date) AND (form_encounter.date between '2019-06-02 00:00:00' and '2019-06-08 23:59:59') AND billing.billed = '1' ORDER BY form_encounter.encounter, form_encounter.pid, billing.code_type, billing.code ASC;

I manually ran the query and it comes back empty even though there are patients that have insurance_data provider 0 in the table. I ran that query to make sure there are. So, now, need to figure out the correct query to bring back results.

@visolve can you shed some light on this query?

#8

Ok, I have figured out what is not working.
Below is the current code.

  48      } //---------------------------------------------------------
  49     elseif (strpos($criteria_value, "insurance_data.provider = '1'")!== false) {
  50          $query_part .= ' AND '."insurance_data.provider > '0' and insurance_data.date <= form_encounter.date";
  51      } elseif (strpos($criteria_value, "insurance_data.provider = '0'")!== false) {
  52          $query_part .= ' AND '."(insurance_data.provider = '0' or insurance_data.date > form_encounter.date)";
  53      } //---------------------------------------------------------

First thing is line 50 needs to be changed to

  50          $query_part .= ' AND '."insurance_data.provider > '1' and insurance_data.date <= form_encounter.date";

That will cause the with insurance query to work correctly.

Line 51 - 53 is the zero issue. If I take this query and run it from the command line it will return results.

SELECT form_encounter.pid AS enc_pid, form_encounter.encounter AS enc_encounter, form_encounter.provider_id AS enc_provider_id, billing.* FROM form_encounter LEFT OUTER JOIN billing ON billing.encounter = form_encounter.encounter AND billing.pid = form_encounter.pid AND billing.code_type LIKE '%' AND billing.activity = 1 LEFT OUTER JOIN patient_data on patient_data.pid = form_encounter.pid LEFT OUTER JOIN claims on claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter LEFT OUTER JOIN insurance_data on insurance_data.pid = form_encounter.pid and insurance_data.type = 'primary' WHERE 1=1 AND insurance_data.provider = '0' AND (form_encounter.date between '2019-06-02 00:00:00' and '2019-06-08 23:59:59') ORDER BY form_encounter.encounter, form_encounter.pid, billing.code_type, billing.code ASC;

This query will only return results if the AND insurance_data.provider = ‘0’ is changed to AND insurance_data.provider = ‘’ OR AND insurance_data.provider = 0. But with the quotes around it. It returns no results.

I have tried changing lines 52 to

 $query_part .= ' AND '."(insurance_data.provider = '' or insurance_data.date > form_encounter.date)";

And I have tried

$query_part .= ’ AND '."(insurance_data.provider = 0 or insurance_data.date > form_encounter.date)";

No matter how which way, PHP returns ‘0’.

The next thing I will try is updating the database to actually put a 0 in the table instead of it being NULL which is the current default.

@robert.down @sunsetsystems What would be your suggestion on this?

#9

Sure - We will look into the problem.
Do you have a demo machine that we can access this?
Otherwise, you can load your test data into ViSolve’s demo.

Meanwhile, we will play with our own data and get back to you

#10

@visolveemr
Thanks for taking a look. I don’t have demo data to share, apologies.

#11

@juggernautsei:
Here is the answer to your specific question and by looking at the query, we wish it could have been done better.

Problem:
SELECT form_encounter.pid AS enc_pid, form_encounter.encounter AS enc_encounter, form_encounter.provider_id AS enc_provider_id, billing.* FROM form_encounter LEFT OUTER JOIN billing ON billing.encounter = form_encounter.encounter AND billing.pid = form_encounter.pid AND billing.code_type LIKE ‘%’ AND billing.activity = 1 LEFT OUTER JOIN patient_data on patient_data.pid = form_encounter.pid LEFT OUTER JOIN claims on claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter LEFT OUTER JOIN insurance_data on insurance_data.pid = form_encounter.pid and insurance_data.type = ‘primary’ WHERE 1=1 AND (insurance_data.provider = ‘0’ or insurance_data.date > form_encounter.date) AND (form_encounter.date between ‘2019-06-02 00:00:00’ and ‘2019-06-08 23:59:59’) AND billing.billed = ‘1’ ORDER BY form_encounter.encounter, form_encounter.pid, billing.code_type, billing.code ASC;

Returns “0” rows, even though there are rows that qualifies the above criteria!!

Cause:

Though, one expects there are rows that qualifies the above criteria, technically speaking there is NO row that qualifies the above criteria.

This is simply due to the well-known confusion on the user side in understanding the difference between the values of NULL, EMPTY, ‘0’ and 0.

One of the column in the where clause above is “insurance_data.provider = ‘0’ ”

which is incorrect as NO row will qualify because the provider value will be either ‘’ (empty) or NULL or will have some other value but not ‘0’.

Fix:

There are few workarounds including changing the value of the provider in the database (bad idea!)to meet the current query. That is set all the unknown and null values to ‘0’ for the provider column.

The simple workaround is to change the query in the where clause:
from: insurance_data.provider = '0’

to: (insurance_data.provider = ‘’ or insurance_data.provider is NULL)

Note:
Also, the only “equality” or “inequality” where one should use in the WHERE CLAUSES are(if the data type is CHAR:
NULL, ‘’, or some valid string. It is INCORRECT to use ‘0’ or Integer 0 in the query where Clause .

This may NOT the total solution for the above query and this is one of the problem and this ONLY fixes the current problem!

-ViSolve OpenEMR Support Team

1 Like
#12

Here is what I found. In testing the system. The code creates an entry in the insurance data table for every patient that is entered into the system. The entry is whether they have insurance or not. Now, the billing manager was coded when the system in the past use to put a 0 in the provider column to indicate no insurance. This is the theory that I am working with. So, in the process of time that was replaced because no one knew why the 0 was being placed in the column where a name should go. But the billing manager code was added onto and no one looked back because they did not know. Many hands have built this castle over the years. My goal was instead of redoing all the layers of the billing manager. Restore the original functionality of storing the patient. So, I dug and dug into the process of entering a patient in the system. Now the 0 is placed in the provider column that the billing manager is looking for it to be there when searching for uninsured patients.