IWeb logo  IWeb 5.17.5 Administrator User Guide

Deduplication Command-Line Procedures

The deduplication procedures are native-stored Oracle procedures that are run through the SQL+ user interface. These procedures should run at least once per day and can be submitted one at a time or all at one time using the RUNALL.SQL command. These procedures run in varied time lengths (hours or minutes), depending on the number of new patient records. The progress of each procedure is updated every minute and is located in a separate table. See the Procedure Names/Progress Table Names section for more information.

Regardless of the executable procedure entered, the command-line and command content appear similar to this:

SQL>Exec[Procedure Name](Parameter):

Each portion of the command is as follows:

Procedure Names / Progress Table Names

The executable process names, descriptions, and progress-check table names are as follows:

Procedure Name Description Progress-Check Table Name
H33_Dedup_Patients(1); Moves patient records from the Pre-Reserve table into the Reserve, Master, and Possible Duplicate tables H33_Dedup_Patients_Progress
H33_Process_Possible(1); Moves patient records from the manual deduplication tables (Ambiguous ID and Possible Duplicate) into the Reserve and Master tables. Only the records that completed manual review are moved and those that have not been evaluated are skipped by the procedure.
The procedure has two steps:
  1. Processes possible match records. These are groupings found to be similar to an incoming record that points to the same master record.
  2. Processes possible mixed-match records. These are groupings found to be similar to an incoming record, but not pointing to the same master record.
 H33_Process_Possible_Progress
H33_Dedup_Vaccinations(1); Moves vaccination records from the vaccination Pre-Reserve table into the vaccination Reserve and Master tables and performs all four of these parameter steps:
Deduplication - Moves vaccination records from the vaccination Pre-Reserve table to the Reserve table. Vaccinations are checked to see if they have already been sent and, if so, are updated.
  1. Linking - Determines which patient master record a vaccination belongs to. The IRMS# and Patient ID combination is used from the vaccination Reserve record to find a corresponding patient Reserve record. Once found the Patient ID of the patient master record that the patient Reserve record is pointing to is copied to the vaccination Reserve record. Simultaneously, a vaccination record is added to the vaccination Master table to reflect this relationship.
Linking resolves duplicate vaccinations referred to as redundant vaccinations. Some examples are:
  • Vaccinations of the same type, but given at difference facilities.
  • Vaccinations of the same type, but reported a day apart.
  • Historical combination vaccinations reported as two separate vaccinations.
Note: Vaccinations that are found to be redundant or are before a patient's birthday are marked with a deletion flag to prevent them from  being viewed. The criteria is as follows:
  • Vaccinations before a patient's birthday are flagged for deletion whether they are historical or administered. If a patient's birthday is changed to an earlier date, any vaccinations that are flagged reappear.
  • Historical vaccinations:  If given on the same day (or plus/minus a day), or entered as an administered vaccination from the same family, the historical vaccination is overwritten (updated). Historical combination vaccinations are not deleted unless their families are fully represented by other vaccinations. For example, a DTaP vaccination does not cause a historical DTP/Hib to be deleted unless the Hib vaccination is also present. An administered shot cannot overwrite another administered shot because it is unclear which shot was reported from the original vaccinator.
  1. Forecasting - Involves generating a vaccination forecast for all patients whose vaccination records have changed.
  2. Geocoding - (if enabled in the registry) Runs as the last process since it can take a lengthy amount of time. Due to the lengthy process, a nightly backup may interrupt it. However, it continues where it left off during the next running of the procedure. Only patient records that are new or have had a change of address are geocoded.
The H33_Dedup_Vaccinations procedure can use these parameters for the following reasons:
  • 1 - All four steps execute
  • 2 - Disables steps 2-4
 H33_Dedup_Vaccination_Progress
H33_Dedup_Vaccinations(2); Moves vaccination records from the vaccination Pre-Reserve table into the vaccination Reserve and Master tables and does not perform an association. This non-linking process runs because the new patients have not been brought into the system and are not yet available for linking. H33_Dedup_Vaccination_Progress
H33_Master_Duplicate_Scan("); Reviews all of the records in the patient Master table and merges the similar records H33_Master_Duplicate_Scan_Progress
H33_Update_RC_Patient(1); Moves records from Remote Clinic into the registry H33_Update_RC_Patient_Progress
H33_Dedup_Simple_Masters(1); Dedup_Simple_Masters merges the two or more patient reserve records into one record. The most recent record is the one that is kept. It then moves the incoming record back to the H33_patient_pre_reserve table to be reprocessed (presumably, it matches automatically when it is reprocessed) H33_Dedup_Simple_Masters_Progress
Runall.sql This is a program that is set up by the database administrator and runs all of the above procedures:
  • H33_Process_Possible(1);
  • H33_Dedup_Vaccinations(2);
  • H33_Dedup_Patients(1);
  • H33_Dedup_Vaccinations(1);
  • H33_Master_Duplicate_Scan(");
  • H33_Dedup_Simple_Masters(1);
  • H33_Update_RC_Patient(1); - if using the Remote Clinic application
  • H33_Dedup_Lead(1); - is using the Lead application
Note: As of version 2.10, Deduplication Simple Masters (H33_Dedup_Simple_Masters(1);) also runs with the nightly deduplication process.		  
 

Check the Progress of Running Procedures

Progress can be checked in each of the corresponding Progress tables. These tables have similar fields that are used to view and control a running procedure.

The fields most commonly used for viewing and controlling are as follows:

Field Description

Start_Time

 The time the procedure was started

Pulse

 Updated with the system time every time a record is updated

Stop

 This field, used with any character, can be used to stop a running procedure

Stop_Time

 A pre-determined time to stop the running of the procedure

Related topics: