OSCAR Lab Schema Tips

Share this

Here’s some info on how the hl7TextInfo, hl7_link, hl7_msh, hl7_obx, hl7_obr, hl7_pid, providerLabRouting, patientLabRouting tables and measurements relate in OSCAR.

Lab comes in and writes to hl7TextMessage. I’ll call this hmessage
The lab is “dissected” and inputted into:
hl7TextInfo I’ll call this hinfo (hinfo.lab_no = hmessage.lab_id)
It checks for providers and then adds the record to providerLabRouting (lab_type=’HL7′ and lab_no = hinfo.lab_no)
It checks the patient’s Last Name, DOB, HIN, and Sex and then routes the lab to the patient if one exists in patientLabRouting
It then checks to see if any of the lab codes are mapped using measurementMap
If the ident_code from hl7TextMessage matches a ident_code in measurementMap, it grabs the loinc code and maps the measurements. If the ident code matches, it’ll look for the loinc and add the value to measurements and pull it up on grid display ect
To get the lab values you’d need to decrypt the lab using base64 eg.
select FROM_BASE64(message) from hl7TextMessage order by lab_id desc limit 1;
This will decrypt the lab and allow you to pull the data (currently done by Java code, not by SQL Code).