sunsetsystems wrote on Wednesday, August 18, 2010:
Distributed amount: “select sum(pay_amount) from ar_activity where session_id = $session_id”. –- This will work fine if we know the session ID. What if we want to find out all the checks for which there is an undistributed amount. if there is a ar_session table with 1000 records we have to loop through entire 1000 records. In a small practice with a monthly average of $10000 collection will have these number of entries in a matter of months, the thing will become much worse for big practices/hospitals.
No, this is a common type of problem that can be solved with a single query using aggregate functions. See this tutorial for example:
http://www.mysqltutorial.org/mysql-aggregate-functions.aspx
patient_id: If the payment is from the patient then only the patient_id will be there. Presently the patient payments are recorded in Billing table, we feel that its not the right thing. It should also be recorded through ar_session and Ar_activity, if we are accepting this point at that time this is required.
Agreed that it would be cleaner to put all patient payments in ar_activity. Co-pays in the billing table are an inherited “feature”.
Anyway, if you need to keep track of payers who are not insurance companies and who are not the patient associated with the encounter, then I think something more general is needed - it doesn’t seem valid to assume that the payer must be some other patient in that case. This merits more discussion.
Monies that are not related to any encounters entered in the Open Emr system are stored in the Global Account. This becomes particularly useful when there is a transition between one billing company to another and collections need be tracked separately . This will also make sure the ERA are balanced out properly and an undistributed amount will only reflect values in relation to actual encounters entered in the Open EMR system. Also when previous data cannot be readily converted to open EMR (ie due s/w vendor/paper system etc) the global account comes to play until a date it can be sorted out properly.
Pardon my confusion, but how are you going to sort it out later if you don’t also save information from the EOB about the patient, encounter and line items that are being paid for? And if you do save that information, the natural places to do it are patient_data, form_encounter, billing, etc.
I think the preferred method for billing would be to have a separate location for payments unrelated to encounters as suggested by zhhealthcare. I am not sure of any of the technical aspects of the code but I know that there is a need to account for monies distributed by ie Medicare for bonus payments, PQRI payments, Stimulus monies, etc and as mentioned monies from previous systems that should be tied with a patient chart but not related to an encounter.
Keep in mind OpenEMR is not a general purpose accounting system. Payments for things that are not visit-related charges should not be put into it.
Suppose there is an encounter in which four CPT codes are there and the insurance paid for only two and the balance they have denied. They have denied it for some reasons which we can correct. That means the fate of first two CPT’s are known and if any balance is there it has to be moved to secondary insurance, if any or has to be moved to patient balance, but the undecided CPT’s has to be remained in the primary insurance balance. How we will track it in OpenEMR. Presently the fields available for tracking this is “last_level_billed” and “last_level_closed” in “form_encounter” table . This is a header row and we cannot deal with the situation mentioned. Our suggestion is to move these field to billing table from form_encounter table. We also like to add a field in billing table which decide whether all the insurance has completed and the balance is of patient responsibility. A true or false fiield.(name something like “Move_to_patient_responsibility”. This will reduce the calculations for arriving at patient balances, and patient statements. Also we feel it a good idea to have a remainder field in the billing table, which records the present remainder of the charge value(ie after adjusting insurance payments, adjustments etc.)
Not sure I understand all this, but I really hate the idea of storing redundant data for the sake of “simplifying calculations.” The way to simplify is to create functions or classes that compute desired values from more complex data. This goes to the core of proper database design.
Rod
www.sunsetsystems.com