The Hach WIMS Direct Server-Side Interface to Custom LIMS (Q13598) is used to transfer lab results from an Oracle based LIMS system.  Result records are stored in separate Oracle Views for each facility (LIMS_DATA_PL, LIMS_DATA_NC, etc...). 

WIMS variables are cross referenced on:

LIMS_DATA_XX.SOURCE = VARDESC.LIMS_LOC (Source)
LIMS_DATA_XX.ANALYTE = VARDESC.LIMS_TEST (Analyte)
LIMS_DATA_XX.MATRIX = VARDESC.LIMS_UD1 (Matrix)
LIMS_DATA_XX.COLLECTION_METHOD = VARDESC.LIMS_UD2 (Collection Method)

Required View Fields for LIMS_DATA_xx:

"NAME" VARCHAR2(30 BYTE)
"SAMPLE_DATE" DATE
"SAMPLE_ID" VARCHAR2(30 BYTE)
"UNITS" VARCHAR2(30 BYTE)
"SOURCE" VARCHAR2(30 BYTE)
"ANALYTE" VARCHAR2(30 BYTE)
"COLLECTION_METHOD" VARCHAR2(30 BYTE)
"MATRIX" VARCHAR2(30 BYTE)
"INIT_VALUE" VARCHAR2(31 BYTE)
"VALUE" VARCHAR2(31 BYTE)
"APPROVED_CODE" VARCHAR2(31 BYTE)

Example Source Query:

Source records are found by the Sample_Date field, therefore when running as a service the interface always runs for the LAST 30 DAYS.  When running in interactive mode, the interface runs for the dates the user specifies.

SELECT SAMPLE_DATE,SOURCE,ANALYTE,COLLECTION_METHOD,MATRIX,INIT_VALUE,VALUE,UNITS,APPROVED_CODE,SAMPLE_ID
FROM LIMS_DATA_PL
WHERE SAMPLE_DATE >= TO_DATE('01-May-2012 00:00:00 AM', 'dd-Mon-yyyy HH:MI:SS AM') AND SAMPLE_DATE <= TO_DATE('30-May-2012 08:22:00 AM', 'dd-Mon-yyyy HH:MI:SS AM')
AND (INIT_VALUE IS NOT NULL OR VALUE IS NOT NULL)
ORDER BY SOURCE,ANALYTE,MATRIX,COLLECTION_METHOD,SAMPLE_DATE

Import Rules:

Approved_Code: "A" means approved, and "U" means unapproved data.  Data with a Approved_Code of "U" is imported with a WIMS data approval status of -1024.  In Hach WIMS, select System Setup, System Tables, This Facility, Data Approval Setup to review your setup and verify you have a Status of -1024.  Data with an Approved_Code of "A" is imported as 0, which always means Approved in WIMS. 

VALUE/INIT_VALUE:  Typically unapproved result is held in the Init_Value field, approved data is held in the Value field.  Therefore, we check the Value field for a result (holds the formatted result, ie <2, 7.4), if it is null we would then use the Init_Value. 

More than one result for a day: The LIMS_DATA_XX table can have more that 1 record for the same sample/analyte (i.e. the SOURCE,ANALYTE,MATRIX,COLLECTION_METHOD,SAMPLE_DATE are all the same) in cases where a test is reanalyzed, etc...  The interface will import the average of the Value field for all Values found, if no values are found it will import the average of the INIT_VALUE field. 

Handling of qualifiers when more than one result: Taking the average of results with qualifiers uses the following rules:

Value 1	Value 2	Average	Notes
<2	6	<4	<2 is treated as a 2, average of 6 and 2 is 4 and push the < qualifier to the result
ND	<2	<1	ND is treated as zero,<2 as 2, average is 1.  Push <.
ND	ND	ND	All must be ND for ND to be the Average.
E4	<2	<3	E4 treated as 4, <2 as 2.  Average is 3 and then push the <.  < qualifier takes precedence over the E.
DNQ4	5	E4.5	DNQ4 is treated as a 4.  Average is 4.5.  The DNQ is replaced with E.

NOTE:  All other qualifiers are invalid, value is ignored and a Result Comment is created.  For example, the PLR TSS record shown below with an INIT_VALUE of NS is imported as a Result Comment of NS and no value is imported to WIMS.