*Screenshots are from the UltraPort SQL Schema Engine version 5.2.3
In your inbound processor if the box 'Always check for duplicate HL7 messages' is checked (it IS checked by default) then the Schema Engine will attempt to dynamically determine if it has already imported the message into the schema. We HIGHLY recommend that you use this setting.
If it is NOT checked, then every message encountered is assumed to be unique and you need read no further.
Inbound Processor Window
HL7 Message Uniqueness
When the Schema Engine imports an HL7 message into the database, it first has to determine if it has already imported the message (IE is the message a duplicate). If a message is deemed to be a duplicate of a previously imported message then ALL message data for the previous message is first DELETED, then the message is reloaded into the underlying schema tables, then in the <PREFIX>_HL7Data table the LoadCount field is incremented by 1 and the LastLoaded field is set to the current date/time. The two key database fields used to determine message uniqueness are the PartnerAPP and MsgControl fields which are in your <PREFIX>_HL7Data table, read on for more information about how they are used.
Example: Assume that you have an empty database. If you have 1 file containing 1 HL7 message and you copy it into the Inbound Folder of your processor it will be imported into the database and the file will be deleted (or archived if you've got the Archive flag checked). If you then copy the SAME file into the Inbound Folder 1000 times allowing the processor to import the file, then copy it in again, etc. When you have completed this process you will ONLY HAVE ONE HL7 MESSAGE in your database. There would only be 1 single row in the <PREFIX>_HL7Data table and the LoadCount column in that table would be set to 1001 and the LastLoaded column would be the system date/time the last time that message was imported.
HL7 Message UNIQUENESS is determined by looking at 7 different data points in the MSH Segment.
1) The Message Control ID/NUMBER (Field 10). The Message Control ID is supposed to be an absolutely unique, never repeating identifier for each message (not test result, or patient, etc. but each message). It doesn't have to be 'globally' unique (like a GUID) but it is supposed to be unique between the sender and the receiver. IE, if I send you a message with the Message Control ID = "ABC123" then I'm not EVER supposed to send you another message with that control ID.
2-3) The Sending Application / Sending Facility (MSH Fields 3 and 4).
4-5) The Receiving Application / Receiving Facility (MSH Fields 5 and 6)
6-7) The Message Type and Message Event (MSH Field 9 Components 1 and 2)
At least ONE of these data points is different on each message (typically the message control ID) to get the schema engine to import them separately.
The Schema Engine concatenates the values in data points 2-7 into 1 long string and inserts it into the PartnerApp field in the <PREFIX>_HL7Data table (which you will notice IS indexed).
With the above information in mind, here is how an Inbound Processor imports an HL7 message when checking for duplicate messages is enabled.
Step 1: Determine if the message is a duplicate. It does this by building the compound key (datapoints 2 - 7 above) from the MSH data and extracting the Message Control ID from Field 10. It then executes a query into the <PREFIX>_HL7Data table which is basically Select * From <PREFIX>_HL7Data Where PartnerApp = '<COMPOUNDKEY>' AND MsgControl = '<Message Control ID>'. If this query returns > 0 rows then the message being processed is deemed to be a DUPLICATE message and all underlying data is first deleted. If it's not a duplicate then new messageID (GUID) is generated and a row is inserted into the <PREFIX>_HL7Data Table with the Processed field set to 0, the Loaded field set to 0, the Inbound field set to 1 and the LastLoaded field set to the current date/time.
Step 2: The <PREFIX>_MessageManifest table is populated. This basically a sequential listing of all segments in the message (MSH is always #1).
Step 3: The appropriate underlying data tables are populated with the HL7 data.
Step 4: The <PREFIX>_HL7Data Table is updated and Loaded field is set to 1.