As you may have noticed when you created your schema tables an UltraPort SQL Schema contains a number of tables. It's important not to let this intimidate you. For ordinary HL7 interfaces you might never have to deal with more than a dozen or so different HL7 segments. As to WHICH dozen, well that depends on what medical specialties you and your trading partner are trying to support.
Part 1 - Schema Table Naming Convention
In your schema profile you selected a schema 'Prefix'. When the schema tables were generated every object created begins with that prefix followed by an underscore (_). This was done to allow you to create multiple schemas within the same SQL database or to allow you to build your schema tables inside of a pre-existing SQL database without fear of overwriting any existing SQL objects (a remote chance, but stranger things have happened).
Part 2 - Global Tables
Global tables are any schema tables which are NOT segment data tables. Of the global tables most are used (or reserved for use) by the Schema Engine itself. There are 3 global tables which you will want to become familar with.
1) Your MAIN table, the one you will (probably) deal with more than any other is called <prefix>_HL7Data. This table will contain one (1) row for each HL7 message contained in your schema and should be your starting point when navigating the schema.
2) The <prefix>_MessageManifest Table.This table will contain a 'vertical' listing for every HL7 message indicating each HL7 segment which that message contains and it's ordinal position within the message.
Part 3 - Correctly identifying newly imported HL7 messages
When the Schema Engine imports an HL7 message the inbound processor does it in 3 steps for a NEW (ie not duplicate) message:
1.A row is INSERTED into the <prefix>_HL7Data table (refer to Part 2 above for the table structure) with a new GUID in the MessageID field. In this row the HL7Message field will contain the entire raw HL7 message, there are several fields from the MSH segment of the message to help you in making a quick decision without having to join to the MSH segment table like MsgType (MSH 9.1) MsgEvent (MSH 9.2) and MsgControl (MSH 10.1). The Inbound flag will be 1 (indicating an inbound message) and the Outbound flag will be 0. Finally, and VERY important the Loaded flag will be 0, indicating that the Schema Engine is IN PROCESS and loading the underlying Segment tables and the <prefix>_MessageManifest table.
2.The <prefix>_MessageManifest table and all of the underlying Segment data tables are populated with the HL7 message data.
3.Finally, when all of the above is completed the inbound processor will then UPDATE the <prefix>_HL7Data table setting the Loaded flag to 1 and the Processed flag to 0.
If an HL7 message is identified as a DUPLICATE message the message is imported in four (4) steps in 1 transaction:
1.The row in the <prefix>_HL7Data Table is identified and UPDATED. The Processed flag is set to 0 and the Loaded flag is set to 0, and the HL7Message field is updated with the raw value of the incoming message.
2.ALL of the data from the <prefix>_MessageManifest table and the Segment data tables is purged (deleted)
3.The <prefix>_MessageManifest table and all of the underlying Segment data tables are populated with the HL7 message data.
4.Finally, when all of the above is completed the inbound processor will then UPDATE the <prefix>_HL7Data table setting the Loaded flag to 1 and the Processed flag to 0 AND the LoadCount field is incremented by 1 AND the LastLoaded field is updated with the system date/time and the transaction will be committed.
When you process messages which have been imported into an UltraPort MS SQL Schema we assume that your goal is to:
1.IDENTIFY new HL7 messages which have been imported into the schema tables which YOU have not yet processed.
2.FOR EACH HL7 message, you want to extract any/all data elements and import them into YOUR OWN separate database tables. For instance you wish to extract patient information from the Segment data table for the PID segment, insurance information from the IN1 segment tables etc, etc.
3.FLAG each HL7 message which you have extracted so that you don't inadvertently repeat the process.
To do this we recommend (but by no means require) that your process work like so:
•Your process should be a polling process rather than based on database triggers. In that it should periodically poll the <prefix>_HL7Data table (the more frequently the better, every 5 or 10 seconds) with a SQL Statement to identify new messages see the example below.
•If your polling process returns NO RECORDS, then you have no messages to extract so just exit and try again later (5 or 10 seconds).
•If your polling process DOES RETURN RECORDS then you should iterate through EACH ROW returned, use the MessageID as the key value to access the HL7 message data in the Segment data tables and in the <prefix>_MessageManifest tables. After you have done this FOR EACH ROW you should then UPDATE the <prefix>_HL7Data table and set the PROCESSED flag to a non-zero value and only then MOVE ON TO THE NEXT ROW. After every row returned in your polling process has been evaluated and marked as processed you should then execute your polling SQL Statement again and repeat this process UNTIL YOUR POLLING PROCESS RETURNS NO RECORDS.
Part 4 - HL7 Segment Data Tables
HL7 messages are made up of HL7 segments. The vast majority of the SQL tables that make up your schema are HL7 segment data tables which will be named <prefix>_SEGMENT_<segmentname>_<A or B>. Example for the MSH segment in a schema with the prefix ABC the schema table would be named ABC_SEGMENT_MSH_A.
NOTE: If the HL7 definition of any segment has 40 or more HL7 fields the Schema Engine will create TWO segment data tables. HL7 fields 1-39 will be stored in the table ending with _A and HL7 fields 41 and greater will be stored in the table ending with _B.
Layout: As generated by the Schema Engine the first 2 columns of each segment data table are: [MessageID] (the GUID assigned to the message) and [IDX] an integer which will be the ordinal position within the message (see the SegmentIDX column in the <prefix>_MessageManifest table). All of the remaining fields will be HL7 segment data columns.
Part 5 - HL7 Segment Data Table Columns
In all of your segment data tables other than the MessageID and IDX columns all other columns will be segment data columns which will be named like so:
<Segmentname>_F<HL7 Field #>_C<HL7 Field Component#>
Example. The patient's last name is stored in field #5 component #1 of the HL7 standard PID segment. So since the field number is < 40 we know that the column we want will be in the segment data table for the PID segment ending in _A. The column name will be PID_F5_C1 (PID segment_Field #5_Component #1)
In all of your HL7 Segment Data tables you will notice that for every HL7 Field represented (Example: PID_F8_C2 represents field #8 component #2), there will also be a C0 column (Example: PID_F8_C0). This column will always be defined as a VarChar(max) and in MOST cases will always be null when your messages are imported.
When the data for a HL7 Field is imported into your CORE SQL Schema it is broken up into it's individual HL7 Components. This Component data is then stored in the appropriate data column (PID_F5_C1, C2, C3, etc). If the column is too small Schema Training will be invoked to make it larger. If there is no data column for that component (Example: PID_F5_18 if data is encountered in component #18), Schema Training will be invoked to ADD that column dynamically. This means that your Schema Engine software will automatically handle any data truncation or missing database column issues.
What the Schema Engine cannot handle directly are instances of REPEATING HL7 Fields OR instances where there are multiple SUBCOMPONENTS.
Repeating Fields. Consider the PID segment field #3 (Patient ID) which could contain example data that looks like this:
H83777^^^MR^AUTH1~999-99-9999^^^SS^AUTH2~A1-8888^^^PI^AUTH3
This example data actually contains THREE different Patient ID fields and is called a repeating field. In your PID data table you will only ever see the FIRST instance represented in the individual data columns:
PID_F3_C1 = H83777
PID_F3_C2 = null
PID_F3_C3 = null
PID_F3_C4 = MR
PID_F3_C5 = AUTH1
While for this value the PID_F3_C0 column will NOT be null and will contain the entire RAW value of the field in question (IE H83777^^^MR^AUTH1~999-99-9999^^^SS^AUTH2~A1-8888^^^PI^AUTH3).
Sub-Component Data. Any HL7 Components which contain multiple SUBCOMPONENT instances are handled in the same way. Only the FIRST Sub-Component instance will show up in the individual data column while the RAW value of the entire field will be in the C0 column. Example of a common misuse of HL7 in field #13 of the PID segment (Home Phone) we often see a value like this: 215-555-1212&HOME. In this instance you would see this in your data tables:
PID_F13_C1 = 215-555-1212
PID_F13_C0 = 215-555-1212&HOME
