Best practice for building/maintaining compendiums

[Brandon Grudt]

I am being asked to provide a lot of compendiums for third parties that are receiving our labs, rad reports, etc.  Typically, I lean on our SME from those departments to generate compendiums, which I forward on to the third party.  Some of these are very granular (what is the result code and description of EVERY line item for your panel tests).

I started thinking that I would like to build and keep my own database of order/result codes.  I could break down interface messages to grab the necessary data.  Assuming that I have the tcl and sql knowledge to do this, what does the CloverTech community recommend?  I’m curious to see if anyone has something like this in place.  The advantages of this are many.

– I can set a task to erase codes that are over X years old, meaning that the list shouldn’t grow with invalid codes.

– I can store all these codes in a single normalized table, instead of custom building sql queries for each vendor.

– I can expand my sql capabilities, as many of our vendors probably wouldn’t grant me sql access.

– I can very easily automate the generation of new compendiums.  There’d really be no need to verify the data, as it would be built by an interface, not a person.

Our system processes around a few thousand results per day.  I don’t think this would represent a big burden on the engine at all.  

I have recently upgraded to 6.1 and am using the SMAT Database option.  Is there a way for me to look at that database instead of attaching a tcl script to a thread?  That was mostly a rhetorical question, but I’m looking for some insight before I go at this blindly.

Topic

[Peter Heggie]

Absolutely you can do this, and all your reasons are good ones. It will be easy to implement.

Our group gets these compendium requests several times a year and it is annoying to dig out the lists, especially when it is not our job to maintain these items.

But you can’t guarantee that all available services/exams/items will have been ordered or transmitted through Cloverleaf during the time window you are capturing. The longer you have this in place, the higher percentage of your capture of all available items, but you might miss some that are infrequent.

Plus you might end up being the keeper of the compendiums.. and you may be saddled with being the party responsible for notifying everyone when new codes or patient locations or whatever is added.

Peter Heggie

[Terry Kellum]

Expiring old codes will be a problem.  We have some codes that date back to the 1980’s that are still valid.

When you are not the OPR for the code, it is much more difficult to maintain the list.  You might want to think about interfacing the entire enterprise code base to an MFN for keeping codes synced.

http://www.mexi.be/documents/hl7/ch800004.htm

[Brandon Grudt]

Terry Kellum wrote:

Expiring old codes will be a problem.

[Brandon Grudt]

Here is the script I settled on.  I’ll post it here for posterity.  Any suggestions/critiques?

Code:

######################################################################
#     Name:     test_lab_251_compendium.tcl
#
#   Purpose:  Builds lab compendium for auditing purposes.
#
#   Author: Brandon Grudt
#       Memorial Health
#       brandon.grudt@memorialohio.com
#
#   File History:
#
#   1.0 12/21/2015   Brandon Grudt   Initial release
######################################################################
proc test_lab_251_compendium { args } {
   global HciConnName
   set dispList {}
   if ![info exists HciConnName] { set HciConnName UNKNOWN }
       set module $HciConnName/normalize_hl7
       keylget args MODE mode
       keylget args CONTEXT context
       switch -exact — $mode {
           start { return “” }
           run {
               keylget args MSGID mh
               set msg [msgget $mh]
               if ![cequal $context sms_ib_data] {
                      echo ${module}: Invalid Context. S/B sms_ib_data is $context
                       echo Continue message – No action
               }
######################################################################
# SET UP PRELIM VARS
######################################################################
           catch {
               set sep [csubstr $msg 3 1]    
               set sub [csubstr $msg 4 1]
               set rep [csubstr $msg 5 1]        
               set segments [split $msg r]
######################################################################
# SET REQUIRED VARS AND OPEN SQL
######################################################################
               set date [clock format [clock seconds] -format {%Y-%m-%d %T.000}]
               set test_loinc [lindex [split [lindex [split [lsearch -inline -all -regexp $segments ^OBR] $sep] 4] $sub] 0]
               set test_ldesc [lindex [split [lindex [split [lsearch -inline -all -regexp $segments ^OBR] $sep] 4] $sub] 1]
               set test_code [lindex [split [lindex [split [lsearch -inline -all -regexp $segments ^OBR] $sep] 4] $sub] 3]
               set test_desc [lindex [split [lindex [split [lsearch -inline -all -regexp $segments ^OBR] $sep] 4] $sub] 4]
               set test_ty [lindex [split [lsearch -inline -all -regexp $segments ^OBR] $sep] 24]
               package require tclodbc
               database connect hits_test hits_test MYUSERNAME MYPASSWORD
######################################################################
# IF THE LAB IS NOT A MICRO
######################################################################              
               if { $test_ty != “MICRO” } {
                   set test_type LAB
                   set lab_vals [hits_test “SELECT result_cd FROM I13_lab251_comp WHERE order_cd = ‘$test_code'”]
# SQL QUERY WAS RETURNING DOUBLE BRACKETS ON NULL VALS SO I HAD TO JOIN ONE SET IN ORDER TO MATCH THE HL7 LIST                  
                   set lab_cnt 0
                   foreach value $lab_vals {
                       set lab_vals [lreplace $lab_vals $lab_cnt $lab_cnt [join $value]]
                       incr lab_cnt
                   }
                   set hl7_vals “”
                   set hl7_cnt 0
                   foreach seg [lsearch -inline -all -regexp $segments ^OBX] {
                       set hl7_vals [lappend hl7_vals [lindex [split [lindex [split $seg $sep] 3] $sub] 3]]
                       incr hl7_cnt
                   }
# IF THE OBX RESULT CODES DO NOT MATCH THE SQL RESULT CODES – DELETE AND INSERT TEST
                   package require struct::set
                   set list_comp [struct::set equal $hl7_vals $lab_vals]                  
                   if { $list_comp < 1 } {
                       hits_test "DELETE FROM I13_lab251_comp WHERE order_cd = '$test_code'"
                       foreach seg [lsearch -inline -all -regexp $segments ^OBX] {
                           set result_loinc [lindex [split [lindex [split $seg $sep] 3] $sub] 0]
                           set result_ldesc [lindex [split [lindex [split $seg $sep] 3] $sub] 1]
                           set result_code [lindex [split [lindex [split $seg $sep] 3] $sub] 3]
                           set result_desc [lindex [split [lindex [split $seg $sep] 3] $sub] 4]
                           set result_norm [lindex [split $seg $sep] 7]
                           hits_test "INSERT INTO I13_lab251_comp (post_dt, order_cd, order_desc, order_loinc, order_ldesc, result_cd, result_desc, result_loinc, result_ldesc, result_norm, order_type) VALUES ('$date', '$test_code', '$test_desc', '$test_loinc', '$test_ldesc', '$result_code', '$result_desc', '$result_loinc', '$result_ldesc', '$result_norm', '$test_type')"
                       }
# IF THE RESULT CODES MATCH INSERT A NEW DATETIME STAMP                      
                   } else {
                       foreach seg [lsearch -inline -all -regexp $segments ^OBX] {
                           set result_code [lindex [split [lindex [split $seg $sep] 3] $sub] 3]
                           hits_test "UPDATE I13_lab251_comp SET post_dt='$date' WHERE order_cd='$test_code' AND result_cd='$result_code'"                      
                       }
                   }
######################################################################
# IF THE RESULT IS A MICRO
######################################################################                    
               } else {
                   set test_type MIC
                   set hl7_cnt 1
                   set test_nl "TEXTUAL RESULT"
                   set mic_val [hits_test "SELECT 1 FROM I13_lab251_comp WHERE order_cd = '$test_code' AND order_desc = '$test_desc' AND order_loinc = '$test_loinc'"]
# IF THE OBX RESULT CODES DO NOT MATCH THE SQL RESULT CODES – DELETE AND INSERT TEST                  
                   if { $mic_val 1 } { set panel_ind “G” } else { set panel_ind “I” }
               database connect paragon_test paragon_test MYUSERNAME MYPASSWORD
               set chg [paragon_test “SELECT charge_code FROM TOM200_ORDER_CODE WHERE order_code_ext_id = ‘$test_code'”]
               set chg_int [paragon_test “SELECT chg_cod_int_id FROM TOM200_ORDER_CODE WHERE order_code_ext_id = ‘$test_code'”]
               set cpt [paragon_test “SELECT cpt4_int_id FROM TPB900_CHG_CODE_MST WHERE chg_cod_int_id =  ‘$chg_int'”]
               set cpt  [string trim [join [join [paragon_test “SELECT cpt4_ext_id FROM TSM911_CPT4_REF WHERE cpt4_int_id = ‘$cpt'”]]]]
               paragon_test disconnect
               set main_val [hits_test “SELECT 1 FROM I11_lab_comp WHERE order_cd = ‘$test_code’ AND order_desc = ‘$test_desc’ AND order_loinc = ‘$test_loinc’ AND cpt_cd = ‘$cpt’ AND chg_cd = ‘$chg'”]
# IF THE OBX RESULT CODES DO NOT MATCH THE SQL RESULT CODES – DELETE AND INSERT TEST                  
               if { $main_val < 1 } {
                   hits_test "DELETE FROM I11_lab_comp WHERE order_cd = '$test_code'"
                   hits_test "INSERT INTO I11_lab_comp (post_dt, order_cd, order_desc, order_loinc, order_ldesc, panel_ind, cpt_cd, chg_cd) VALUES ('$date', '$test_code', '$test_desc', '$test_loinc', '$test_ldesc', '$panel_ind', '$cpt', '$chg')"
# IF THE RESULT CODES MATCH INSERT A NEW DATETIME STAMP            
               } else {
                   hits_test "UPDATE I11_lab_comp SET post_dt='$date' WHERE order_cd='$test_code'"
               }                
               hits_test disconnect
       }  
       }
       time {}        
       shutdown {}      
       default {}
   }
   return "{CONTINUE $mh}"
}

[Author]

Replies