Patient Ledger Inaccurate

@juggernautsei,
One possible scenario that concerns me is if a pre-payment is made and later check out is used but undo check out is used(say to rectify a mistake), then the entire sessions for that checkout are deleted(I feel these should be soft deletes because session deletes are not recorded to log here) plus the previous pre-payment. This is an issue with the deleter, maybe! Pre-payments are handle special and that relies on encounter=0. The point is that through deletes of current check out being abandoned, the ar session for a previous pre payment is also deleted but leaving the payment for the pre payment in payment table intact.
This is why it seems necessary that you speak with the billing folks and get the work flow(especially dealing with pre-payments) for your billing. Some questions could be; How and where are pre-payments applied(eob or batch payments); Do you use check out? I assume pre-payments are done in front payment but they can also be applied in batch payments.

I am concerned this may be an issue that is floating around in installed base that just has not been detected 'til now. Knowing check out can be an issue there are probable others dealing with deletes as well.

@sjpadgett, I will post this to the EU and update you as soon as they reply.

hi @sjpadgett @juggernautsei, hereā€™s the bug iā€™m familiar with,

OIC said the blind man. Is there a way to keep payments out of this hole?

I thought issue was with missing pre-payment. The other issue with unapplied payment could not happen with a pre-payment.

A pre-payment is an unapplied payment, right?

I suggested to the EU that they tell the staff not always enter a payment against an encounter not the top line with no encounter number next to it.

Yes pre-payment is not applied and that is why we prob should find out from your user why they are using it. I assumed it is something like a health account to build credit for long term patients which was applied through posting. Could be something simply as just did not know to apply payments at checkout if against visit encounter.
Wearing my support hat I say, informing the user a procedure to avoid problem is good but, when I put on my engineering hat, I prefer to stop it from happening in the first place.
Thatā€™s why Iā€™m being nosy.

Just to be clear. A pre-payment will still show up in ledger. This is because it has a payment type besides patient payment that is unapplied. Different scenarios.

The reason that they are using pre-payment because they are in the Cayman Islands. There is not always insurance. So, patients come in and find out how much a procedure cost and pay for it up front. The front desk collects these payments until the patient has paid for their procedure to be done.

So then I assume that once the procedure is done, to apply the payments either they use check out/front payment with a chance they donā€™t apply to the encounter procedures but instead may apply to open encounter(0). This way will still add payment to both payments and sessions tables except not applied. Thatā€™s one way to get in trouble. If check out is used and then undo to fix a mistake then the pre payments in sessions are deleted but payments table left intact. Making for your missing ar session for certain pre-payments
Does this sum it up?

To my knowledge, they are not deleting any records.

I meant that an ā€œUn doā€ of a check out will inadvertently delete the ar session for pre-payment and leave abandoned pre-payment in payments table. Not deletes by user.

I have received word from the EU that they do not use the checkout feature at all. They just go to fees then payment.

Then Sir Iā€™m just not sure how you would be missing pre-payments in ar_sessions but recorded in payments. Even so, check out has a bug that needs addressed.
I understand how using the top block in payments can cause payment not to show in ledger but still yet, the payment will still get recorded to ar_sessions but ledger report does not pick it up. However, if the payment type is self then front payment will not allow the entry, thus making the entry being allowed for insurance payment type, a bug as well.

So, for sure, if patient does not have insurance assigned then the insurance radio should not be available in front payments.
Also, need to fix if payment type is insurance and top block(no encounter payment) is used then it is handled the same way as if the payment type is self, that is, by warning that there is not an encounter for payment.

Iā€™m still concerned I canā€™t explain what happened to the pre_payments missing from ar_session but are recorded in payments. For me this is serious.

I have not put a lot of time into trying to figure out why the payments are not making it to the ar_session table. I just know that it is and I have set a cron job for now to pickup the payments and record them in the ar_session table.

If I have time in the next week, I will try to investigate. Thank you for your help.

@juggernautsei I keep coming back to this as it has me somewhat concerned that this could be an issue in OpenEMR installed user base that has not been seen 'till now. Also, certainly donā€™t want to carry forward in next release.
Iā€™m going to setup a PR to fix the Ledger issue, abandoned checkout wrongly deleting prepayments and accepting payments to wrong payment type. Iā€™d like to add a fix for this as well.

Iā€™m going to review code again to try to spot where maybe a table write could error without reporting(seen it before). I think the biggest clue could come from the erroneous tables by looking for patterns, such as; is it same user, time of day or could even be server traffic issue. I donā€™t know except, when we do find it weā€™ll say NUTZ! why didnā€™t I think of that sooner?:slight_smile:

@sjpadgett
When this was first brought to my attention by the EU and I started investigating. It is not the same user every time. I figured out a couple of scripts to find the missing payments. That is what got their attention is that the staff knew they were recording the pre-payment and the system was not showing those pre-payments on the patient ledger.

The accountant had singled out a patient for me to find her pre-payment. I did find the pre-payment in the payment table and with that information, I looked for others. Below is the first list that I scraped out of the payment table. This should give you a glimpse into the payments being recorded. As you can see the third piece of information is the person recording the transaction. And the time of day is all over the place. Let me know if I can share any other information with you. BTW, the first number is the patient ID.

11154 - 2016-10-08 10:48:33 - Bertha39 - debit_card - 660.00

2760 - 2016-10-12 08:24:26 - DARCY42 - debit_card - 779.77

1876 - 2016-10-12 08:28:43 - DARCY42 - debit_card - 422.66

22725 - 2016-10-12 15:54:39 - DARCY42 - cash - 318.59

10381 - 2016-10-12 16:43:10 - Avril47 - debit_card - 240.07

2550 - 2016-10-18 08:35:17 - Avril47 - debit_card - 98.65

11414 - 2016-10-21 14:34:31 - Bertha39 - credit_card - 126.56

17185 - 2016-11-01 14:19:04 - Avril47 - debit_card - 4767.00

15672 - 2016-11-01 15:47:54 - Avril47 - debit_card - 346.00

22905 - 2016-11-01 17:30:29 - Mark43 - debit_card - 468.42

2639 - 2016-11-01 21:47:44 - DARCY42 - cash - 527.33

14407 - 2016-11-03 14:00:13 - Avril47 - check_payment - 545.52

14407 - 2016-11-03 19:09:02 - Mark43 - check_payment - 545.52

13213 - 2016-11-12 14:55:37 - Avril47 - debit_card - 560.00

21469 - 2016-11-27 01:27:07 - Sue-AnnM57 - debit_card - 496.88

21765 - 2016-12-30 17:50:07 - Avril47 - cash - 210.00

23389 - 2017-01-03 18:13:51 - Avril47 - debit_card - 793.18

18041 - 2017-01-19 16:04:21 - Avril47 - debit_card - 285.71

21604 - 2017-01-21 11:44:36 - MMaylor64 - debit_card - 181.25

23942 - 2017-01-24 12:44:11 - Avril47 - debit_card - 299.09

23885 - 2017-01-24 16:56:37 - DARCY42 - debit_card - 77.25

17014 - 2017-02-02 16:12:29 - Avril47 - debit_card - 811.40

17014 - 2017-02-02 16:22:03 - DARCY42 - credit_card - 275.75

17014 - 2017-02-02 16:26:00 - DARCY42 - credit_card - 220.60

23810 - 2017-02-06 11:42:09 - Mark43 - cash - 805.78

23667 - 2017-02-09 15:47:55 - DARCY42 - debit_card - 105.08

5898 - 2017-02-09 16:28:49 - Gloria23 - debit_card - 660.00

23152 - 2017-02-13 17:04:59 - DARCY42 - check_payment - 92.04

24289 - 2017-02-20 12:26:31 - Bertha39 - debit_card - 175.58

16333 - 2017-02-20 15:44:41 - DARCY42 - debit_card - 1215.76

24289 - 2017-02-21 09:20:42 - Bertha39 - debit_card - 175.58

14626 - 2017-02-21 10:32:07 - Avril47 - debit_card - 17.26

14626 - 2017-02-21 10:32:07 - Avril47 - debit_card - 17.45

14626 - 2017-02-21 10:32:07 - Avril47 - debit_card - 14.95

14626 - 2017-02-21 10:32:07 - Avril47 - debit_card - 39.37

19940 - 2017-02-25 12:25:11 - Bertha39 - debit_card - 114.55

23794 - 2017-03-02 12:41:20 - Gloria23 - debit_card - 660.00

11737 - 2017-03-10 17:47:10 - Mark43 - credit_card - 581.18

23857 - 2017-03-15 13:24:15 - Mark43 - debit_card - 534.62

14232 - 2017-03-27 15:51:26 - Avril47 - check_payment - 667.02

23993 - 2017-03-27 16:13:33 - Avril47 - credit_card - 165.77

23993 - 2017-03-27 16:14:48 - Avril47 - cash - 480.00

10682 - 2017-03-29 14:46:59 - MMaylor64 - debit_card - 660.00

19770 - 2017-03-29 15:44:34 - MMaylor64 - cash - 327.63

14232 - 2017-03-31 17:44:51 - Mark43 - credit_card - 578.25

24258 - 2017-03-31 18:23:40 - DARCY42 - check_payment - 141.45

24258 - 2017-03-31 18:23:40 - DARCY42 - check_payment - 15.82

24040 - 2017-04-12 12:39:35 - Mark43 - debit_card - 289.08

24294 - 2017-04-12 12:46:17 - Mark43 - debit_card - 121.56

24738 - 2017-04-13 10:59:50 - Avril47 - debit_card - 545.18

23694 - 2017-04-25 15:36:26 - MMaylor64 - check_payment - 869.64

18391 - 2017-04-26 13:58:35 - Mark43 - debit_card - 468.22

24709 - 2017-05-01 17:57:45 - Mark43 - check_payment - 468.36

24709 - 2017-05-02 10:36:44 - MMaylor64 - check_payment - 71.32

1759 - 2017-05-04 19:18:23 - Mark43 - debit_card - 526.67

19806 - 2017-05-09 11:08:14 - Jenzen65 - cash - -500.00

10114 - 2017-05-10 18:53:15 - DARCY42 - cash - 2341.12

13962 - 2017-05-12 12:25:25 - Avril47 - cash - 545.18

8924 - 2017-05-13 12:32:05 - Avril47 - debit_card - 238.04

8924 - 2017-05-13 12:33:23 - Avril47 - debit_card - 261.96

23152 - 2017-05-17 12:10:10 - DARCY42 - check_payment - 5973.32

15321 - 2017-05-18 17:28:33 - DARCY42 - debit_card - 411.78

25222 - 2017-05-19 16:40:48 - Gloria23 - credit_card - 310.20

24294 - 2017-05-19 18:39:47 - Jenzen65 - debit_card - -121.56

20015 - 2017-05-22 19:41:27 - Jenzen65 - debit_card - -121.56

24294 - 2017-05-22 19:43:09 - Jenzen65 - debit_card - -121.56

24294 - 2017-05-22 19:48:05 - Jenzen65 - debit_card - -121.56

15026 - 2017-05-23 11:30:08 - Jenzen65 - debit_card - -700.00

1073 - 2017-05-25 13:08:39 - Avril47 - cash - 29.34

12685 - 2017-05-29 12:17:35 - Avril47 - cash - 1675.51

18823 - 2017-05-29 13:26:20 - BConnor62 - debit_card - 1600.00

24738 - 2017-05-30 15:00:41 - Jenzen65 - debit_card - -545.18

23857 - 2017-05-30 15:04:22 - Jenzen65 - debit_card - -534.62

3691 - 2017-05-30 17:05:32 - Avril47 - cash - 685.00

12685 - 2017-06-01 17:48:21 - Avril47 - check_payment - 1024.49

23334 - 2017-06-06 13:31:44 - Avril47 - debit_card - 1033.33

17727 - 2017-06-06 13:57:14 - Avril47 - debit_card - 1033.33

24286 - 2017-06-08 17:00:41 - Gloria23 - credit_card - 264.00

11910 - 2017-06-13 13:26:41 - MMaylor64 - debit_card - 971.80

24788 - 2017-06-14 15:40:25 - Avril47 - debit_card - 878.00

21642 - 2017-06-17 12:41:49 - ErikaSB48 - debit_card - 660.00

21950 - 2017-06-20 13:20:00 - Sue-AnnM57 - debit_card - 254.47

16804 - 2017-06-28 10:21:32 - Gloria23 - debit_card - 941.00

16613 - 2017-06-29 14:09:50 - Sue-AnnM57 - debit_card - 128.66

16613 - 2017-06-29 14:09:50 - Sue-AnnM57 - debit_card - -1.98

16613 - 2017-06-29 14:09:50 - Sue-AnnM57 - debit_card - 22.50

16613 - 2017-06-29 14:09:50 - Sue-AnnM57 - debit_card - 301.87

20868 - 2017-07-06 13:16:47 - ErikaSB48 - credit_card - 205.51

25572 - 2017-07-11 13:04:45 - Avril47 - cash - 245.00

Am I crazy or is Patient Ledgerā€™s arithmetic just wrong for calculating prepayments? Say just add two or three prepayment without any patient balance and check grands. What accounting trick am I missing here? Pretty sure those payments should be carried forward and should reflect balance in payments so user knows account standing at checkout/payment. To me the prepayment should look like a patient payment with any account receivables applied against prepayment balance with any credit carried forward. @brady.miller Iā€™d like your opinion on this. Iā€™m trying to understand how this went this long without notice which makes me think I may be missing something.
@stephenwaite In your forays into this thread have you noticed above?

Remember this is only starting showing up once we upgraded to version 5.0.0. It was fine in 4.2.2.

Yes maybe the missing payment ar_session but Iā€™m speaking to how ledger is handling prepayments. Iā€™m looking at 4.2.2, 5.0.0(3) and current master. Try:
make some prepayments for a test patient. check ledger and note grand totals(do they make sense?) then create encounter and fee sheet for a charge and make a payment against encounter. Check ledger.
This may be a little off topic but iā€™m looking to anything.