Patient Ledger Inaccurate

Day-to-Day Users
,
#1

We are having an issue with the patient ledger showing all of the payments that have been made. I verified that the payment is in the payment table. The legacy system 4.2.2 ledger shows the two pre-payment payments but the new report does not. Here is a comparison screenshot.

pre-payment-8728-ledger.png1823×327 36.5 KB

I have been comparing the old code with the new code and there are minor changes to the code. There doesn’t look like a reason it should have changed the display. I have made no modifications to the system.

Apache 2.4.18 - PHP 5.6.19 - MySQL 5.7.11

#2

HI juggernautsei,

It works good for us in 4.2.2 and 5.0 stable versions.
Can you please share your OpenEMR version ?

Thanks,
ViSolve

#3

The top image is 4.2.2 and the bottom image is 5.0.0(3).

This is occurring on several charts, not just this one.

#4

Solved, it is a data issue not a code issue.

#5

@brady @stephenwaite @robert.down
Upon further investigation into the problem, the real issue was uncovered. Somewhere between the release of 5.0.0 and patch 3, the payments that were being entered into the system were not being recorded in the ar_sessions table. So there were hundreds of entries in the payment table that are not recorded in the ar_session table so the patient ledger information was incorrect.

I have attached a script that I used to copy those payments from the payment table into the ar_session table. The down side to the script is that the type of payment is not recorded in the payment table. So can’t tell the difference between a COPAY and a pre_payment. So the script makes all the payments pre_payments. The accounting personnel should be able to apply the payment as needed in the patient’s account.

fix_ar_session.php (3.2 KB)

#6

hi @juggernautsei @brady.miller

correct me if i’m wrong but i don’t see how any of the patched files would have corrected the problem. it seems like this is the offending code

we’re inserting into payments but not ar_session with the front payment function

#7

@stephenwaite @brady.miller

That is why I tread lightly because I don’t know the whole picture all the time and I am short sited.
But during our test of the system yesterday with a dummy patient. We entered a pre_payment and that was recorded in the ar_session table. I need to figure out how that payment got to the ar table.

I think my issue is around the pre_payments not just payments in general. The thing is I may have made a bad situation worse because I don’t have full knowledge of how the billing system works from front to back. And it is difficult to get answers. Thanks for your reply Stephen.

So, I see that in the code you mentioned, the front payments are not recording in the patient chart. Is that what you are saying, Stephen?

but in the pat_ledger.php the pre_payment is recorded in the ar_session table in line 141.

#8

Here we are at the end of August and I find out the problem is not fixed. Here is an example.

Here is a query of the payment table for a patient.

payments_table_query
payments_table_query986×428 41 KB

Here is a query of the ar_sessions table for the same patient.

ar_session_query
ar_session_query1873×413 95 KB

It should be obvious that the last entry for the patient was not recorded in the ar_session table.
The original solution that I posted is not panning out long term.

So I tried a different approach of including a third identifier. The date time stamp was going to be used but. There is inconsistency in the data time stamp in the two tables.

fix_ar_session.php (874 Bytes)

The results of the query are not correct using the query results for the above-posted patient as my litmus test. I can’t seem to get it down to show me the last missing payment.

I see in my image I have the last payment circled. It is the one above it that is missing from the ar_session table.

Look at payment amount 238.04, the creation time is 2017-07-21 00:26:59 in the ar_session table and it is 2017-05-13 12:33:23 in the payment table.

So, if a patient makes the same payment amount multiple times. That has to be a separate loop to find all of the same payments and see if the ar_session table has the same number of payments.

All of this is to try and clean up the accounting error before addressing the code that is causing the issue to begin with.

#9

If this was the offending code then why does the system write to the ar_session sometimes and sometimes not?

#10

it’s a bug in the front_payment.php file, see how the encounter # is 0

#11

@stephenwaite from what I know the encounter should be zero because the payment is not being entered against an encounter. It is a free standing payment that assigned to anything.

#12

but should at least be entered in ar_activity then?

#13

i wrote an app for a lab a couple years ago to pay commissions to sale reps against v4.2.2 and seem to recall certain payments weren’t recorded in A.R. which I think were copays or payments not posted against insurance. I remember questioning why at the time.

#14

I have checked the ar_activity table and the data is not recorded in there. The patient ledger gets its information from the ar_session table. I have verified that it does. The billing staff noticed it after the upgrade to 5.0.0. The patient ledger was correct before the upgrade and after they noticed that payments were not showing on the patient ledger.

So I played with it enough to find the right code to find the missing payments. I have not written the insert part yet. The deposit date is where I am able to match missing payments. Using the patient that I know payment is missing from the ledger as the guide.

Here is the data fix.

fix_ar_session_data.php (2.0 KB)

Drop this in any folder and run it. What I did was to set a cron job to run this file during backup session every day.

I hope the new billing module fixes this issue.

#15

Okay, ignoring for the moment cleaning up your general ledger(accounting’s) I’m going to look at some code and see if I see how this came to be in my head.

#16

Hi @juggernautsei ,
I can not figure out how this came to be. I’ve generated many, many pre-payments against several pids and users with posting payment and session correctly. The only way I see an ar session being updated(created_date differs) is through the edit_payment script which I haven’t tracked down the various ways it is called yet.
It is possible to have a payment appear in payments table and not ar sessions if a payment is made and then at checkout, the checkout is aborted through undo checkout! This will cause the session to be deleted but payment is left alone. However log should show all of this. It’s amazing the scraps the U.I can get us into sometimes.

#17

@sjpadgett, thanks for chasing this issue. But from the data side, I see that all the missing payments from the patient ledger are listed in the deposit_date column. If you could track down what records the payment to that column. You might be able to solve the intermittent records issue. Because it fails some time but not all the time.
It wasn’t until query below that I was able to find the missing entry from the patient ledger.

   $findEntry = "SELECT pay_total FROM `ar_session` WHERE `deposit_date` LIKE ?  AND `adjustment_code` LIKE 'pre_payment' AND `patient_id` = ?";
#18

@juggernautsei I can do that but how are the pre-payments disposed? Do they ever get posted against insurance balance or where does the payment get reconciled if even ever in OpenEMR?

#19

That part I do not know. I can ask the accounting people and see if they can tell me an answer I can repeat to you.

#20

No, for now I’ll just make some assumptions. Somehow I think the session is being deleted and I do know that deposit_date is set from front payment in current version but I think in previous version deposit_date was set at posting. I’m also pretty sure that posting was drastically changed either in 5.0.0 or a pre release. Just not sure as my memory is not what it once was. I’ll check clinic I support as I’ve migrated it’s database since v4.1 to v5.01.