<< Click to Display Table of Contents >> Navigation:  EQuIS Library > Formats > EarthSoft Interface Formats > ERPIMS Format

Format Name: ERPIMS

File Name: ERPIMS.zip

Source: Available for the EarthSoft Primary Point of Contact (POC) on the EarthSoft Community Center Downloads dashboard from the All Items > Formats folder.

Export(s): Yes (see section below)

Enumeration File: Not applicable

License Level: PlusD

 

Description: Environmental Resources Program Info Management System (ERPIMS) (https://www.afcec.af.mil/What-We-Do/Environment/Restoration/ERPIMS.aspx) is the Air Force system for validation and management of data from environmental projects at all Air Force bases. The EQuIS ERPIMS format was designed by EarthSoft for use by organizations receiving or delivering data in this format. This format allows ERPIMS data to be converted to EQuIS data and loaded to an EQuIS database through remaps and lookups, if needed.

 

Installation Instructions: The ERPIMS format is available as part of the ERPIMS format package (ERPIMS_FormatPackage.zip) file on the EarthSoft Community Center. To access the format, download the ERPIMS format package, unblock the *.zip file, and extract the contents. The ERPIMS format package contains the following files:

•ERPIMS.zip – The EQuIS ERPIMS format file.

•ERPIMS refvals.xlsx – An example reference value EDD that will load commonly used reference values to the most prevalent reference tables called by the EQuIS ERPIMS format.

•ERPIMS Remaps.xlsx – An example reference value EDD that will create an ERPIMS remap set in the database that can be used to substitute common lab values with ERPIMS-compliant values.

•EarthSoft.Reports.Library.d42685.dll – The ERPIMS Group 3 Prime report DLL file that can be published to the database using the Report Publisher.

•ReadMe.txt – Readme text file to explain the purpose of each file.

 

See the Interfaces page on how to use the ERPIMS button in EQuIS Professional to export data.

 

 

Overview: The EQuIS ERPIMS format supports tables from ERPIMS Groups 1, 2, and 3.

•Group 1 – Contains sections for the import of location and well data.

•Group 2 – Contains sections for the import of lithologic and hydrologic data.

•Group 3 – Contains sections for the import of sample/test/result data.

 

This format currently supports the import of the standard EDD file types (*.xls, *.xlsx, *.xml, *.zip, *.edd, *.mdb, and *.accdb). In addition, the ERPIMS format supports the import of *.txt files with the following delimiters: comma (,), pipe (|), tilde (~), and tab. The format does not currently support the import of fixed-width, space-delimited *.txt files.

 

​A single .txt file can be used to populate the Sample_Lab, Test_Lab, and Result_Lab sections simultaneously if each line of data begins with either SAMPLE, TEST, or RESULT. This cue must be followed by the same delimiter used by the rest of the document. Any lines that do not start with one of the specified cues will be ignored. The .txt file must include "Lab_Sample_Data" in the file name.

 

Setup: Many fields in the ERPIMS format use RT_LOOKUP for the reference value list. The VVLs used by ERPIMS may differ from the reference values in the database reference (RT_) tables. To accommodate this, remapping can be used to remap VVLs used in the EDD/RT_LOOKUP to the EQuIS RT_ table value. Remapping will automatically occur during the Create step if RT_REMAP and RT_REMAP_DETAIL contain records for REMAP_CODE = ‘ERPIMS’. See Remap for additional information.

 

Recommended Workflow:

1.Receive an ERPIMS EDD formatted as a single flat file containing sample, test, and result data from the laboratory.

2.Load the lab EDD into EDP using the ERPIMS format, which is able to parse through the single flat file and populate the data into the 3 Lab sections.

 

3.Resolve errors as necessary.

4.Create the package and commit the data.

5.Reload EDP and the ERPIMS format.

6.Using the Group 3 Prime export, pull the data back into the format.

7.Populate the empty fields in the Prime sections of the format and then create the package and commit the data to the database once again, making sure to select a commit type that includes either Update or Merge.

8.Using the ERPIMS Group 3 Prime report, export a zip file with the fully populated deliverable that adheres to the specifications for ERPIMS deliverables.

 

Limitations/Assumptions: The checks and mappings in any EDD format involve assumptions. Here is a list of those assumptions and limitations for the ERPIMS format:

•Data can only be loaded to a single facility at a time.

•The format uses the SAMPLE_ID, ANALYTIC_METHOD, ANALYSIS_DATE, FRACTION, COLUMN_NUMBER, TEST_TYPE  fields in its Test Alternate Key (TAK).

•The AFIID field is required to be populated in every section in which it is found and must be populated with the DT_FACILITY.FACILITY_CODE value of the facility to which data are being loaded. The maximum character count for this field is five (5). Therefore, any facility to which data are to be loaded using the ERPIMS format must have a DT_FACILITY.FACILITY_CODE value that is exactly 5 characters or fewer in length.

•LDI

•The following fields rely on enumerations to populate valid values. The ERPIMS format does not check that the values in the enumerations pre-exist in the database, but EDP will throw an error on Commit if they do not.

•ESCCODE – Must exist in the RT_COMPANY table.

•DRLCODE – Must exist in the RT_COMPANY table.

•EXCCODE – Must exist in the RT_COMPANY table.

•LTCCODE – Must exist in the RT_LOCATION_TYPE table.

•CRDMETH – Must exist in the RT_COORD_HORZ_METHOD table.

•The value in the DATUM field must pre-exist in the RT_COORD_HORZ_DATUM table or an error will be thrown on Commit.

•There are default mappings of ‘BHDIAM’ and ‘CP_DIAM’ to DT_LOCATION_PARAMETER.PARAM_CODE. If ‘BHDIAM’ and/or ‘CP_DIAM’ do not pre-exist in RT_LOCATION_PARAM_TYPE.PARAM_CODE, EDP will throw an error on Commit.

•SLX

•When populated, the SPCODE value must pre-exist in the RT_SUBFACILITY_TYPE table.

•The value populated in LOCID must pre-exist in the DT_LOCATION table or be present in the LDI section of the EDD.

•SCC

•When populated, the SITEID value must pre-exist in the DT_SUBFACILITY table or be present in the SLX section of the EDD.

•The ‘Group 1 SCC’ export can be used to extract the DT_SUBFACILITY table data from the database into the SCC section.

•ZLX

•The ZONEID value must pre-exist in RT_GROUP.GROUP_CODE.

•The value populated in LOCID must pre-exist in the DT_LOCATION table or be present in the LDI section of the EDD.

•MRI

•The following are default mappings to DT_FACILITY_PARAMETER.PARAM_CODE. If these values do not pre-exist in RT_FACILITY_PARAM_TYPE.PARAM_CODE, EDP will throw an error on Commit.

•WCI

•The value populated in LOCID must pre-exist in the DT_LOCATION table or be present in the LDI section of the EDD.

•WINT

•The combination of values populated in the CLASS and MATERIAL fields must pre-exist in RT_WELL_SEGMENT_TYPE.SEGMENT_TYPE and RT_WELL_SEGMENT_TYPE.MATERIAL_TYPE_CODE, respectively.

•The value populated in LOCID must pre-exist in the DT_LOCATION table or be present in the LDI section of the EDD.

•WMI

•Records in this section require a parent record in the WCI section. The parent-child relationship is linked through the LOCID field.

•The value populated in LOCID must pre-exist in the DT_LOCATION table or be present in the LDI section of the EDD.

•HSU

•This section does not currently map to any tables in the database.

•GWD

•Records in this section require a parent record in the WINT section where WINT.CLASS = ‘SCRN’. The parent-child relationship is linked through the LOCID field.

•The value populated in LOCID must pre-exist in the DT_LOCATION table or be present in the LDI section of the EDD.

•ATI

•The value populated in LOCID must pre-exist in the DT_LOCATION table or be present in the LDI section of the EDD.

•PUMPRATE

•The value populated in LOCID must pre-exist in the DT_LOCATION table or be present in the LDI section of the EDD.

•Sample_Prime

•The SMCODE field pulls its list of valid values from the enumeration file, but the selected value must pre-exist in the RT_SAMPLE_METHOD table or EDP will throw an error on Commit.

•When populated, the ABLOT, EBLOT, and TBLOT fields will create default mappings to DT_SAMPLE_PARAMETER.PARAM_CODE. These values must pre-exist in RT_SAMPLE_PARAM_TYPE.PARAM_CODE or EDP will throw an error on Commit.

•The value populated in LOCID must pre-exist in the DT_LOCATION table or be present in the LDI section of the EDD.

•Depending on the value populated in the SACODE field, "Field" or "Lab" will be mapped to DT_SAMPLE.SAMPLE_SOURCE.

•If the SAMPLE_TYPE_CLASS for SACODE value's associated SAMPLE_TYPE is LQ, LABSAMPID becomes a required field and maps to DT_SAMPLE.SYS_SAMPLE_CODE. A corresponding record linked by SAMPLE_ID will also be created in DT_FIELD_SAMPLE.

•If the SAMPLE_TYPE_CLASS for SACODE value's associated SAMPLE_TYPE is NF, FQ, or NULL, FIELDSAMPID is a required field and is mapped to DT_SAMPLE.SYS_SAMPLE_CODE. A corresponding record linked by SAMPLE_ID will also be created in DT_FIELD_SAMPLE.

•Test_Prime

•The RUN_NUMBER field maps to DT_TEST.TEST_TYPE, which is a lookup to RT_TEST_TYPE in the database. Therefore, the value populated in RUN_NUMBER must pre-exist in RT_TEST_TYPE.TEST_TYPE or EDP will throw an error on Commit.

•Records in this section require a parent record in Sample_Prime. The records in these two sections are linked via the FLDSAMPID field.

•If the SAMPLE_TYPE_CLASS is FQ, NF, or null, the SAMPID in Test_Prime is set to the FLDSAMPID and the SAMPLE_SOURCE value is 'FIELD'.

•If the SAMPLE_TYPE_CLASS is LQ, the SAMPID in Test_Prime is set to the LABSAMPLEID And the SAMPLE_SOURCE value is 'LAB'.

•Result_Prime

•Records in this section require a parent record in Test_Prime. The values populated in the ANMCODE, RUN_NUMBER, FLDSAMPID, ANADATE, and ANATIME fields are all required to match those populated in the fields of the same name in the Test_Prime parent record. All five fields must match before the “Orphan row” errors are resolved.

•A table refresh may be necessary if the “Orphan row” errors are not resolved once all necessary fields match.

•If the SAMPLE_TYPE_CLASS is FQ, NF, or null, the SAMPID in Result_Prime is set to the FLDSAMPID and the SAMPLE_SOURCE value is 'FIELD'.

•If the SAMPLE_TYPE_CLASS is LQ, the SAMPID in Result_Prime is set to the LABSAMPLEID And the SAMPLE_SOURCE value is 'LAB'.

•When populated, the following fields will create default mappings to DT_SAMPLE_PARAMETER.PARAM_CODE. These values must pre-exist in RT_SAMPLE_PARAM_TYPE.PARAM_CODE or EDP will throw an error on Commit.

•Sample_Lab

•This section does not contain the AFIID field that is required in previous sections. Rather, it includes a LABSAMPID field that is meant to be populated with a unique sample number assigned by a laboratory.

•The SACODE field is used to find the associated RT_SAMPLE_TYPE record and get the SAMPLE_TYPE_CLASS value. Based on the value retrieved, either FLDSAMPID or LABSAMPLEID is mapped to DT_SAMPLE.SYS_SAMPLE_CODE. The other is mapped to DT_SAMPLE.CUSTOM_FIELD_2.

•If the SAMPLE_TYPE_CLASS is FQ, NF, or null, the FLDSAMPID is mapped to DT_SAMPLE.SYS_SAMPLE_CODE.

•If the SAMPLE_TYPE_CLASS is populated with a value other than FQ or NF (e.g., LQ), the LABSAMPLEID is mapped to DT_SAMPLE.SYS_SAMPLE_CODE.

•If the SAMPLE_TYPE_CLASS is FQ, NF, or null, ​and the Sample_Lab.FLDSAMPID field is not populated, the Sample_Lab.FLDSAMPID field is not required.

•If the SAMPLE_TYPE_CLASS is null, ​and the Sample_Lab.FLDSAMPID field is populated, the Sample_Lab.FLDSAMPID field provides a warning indicating that RT_SAMPLE_TYPE.SAMPLE_TYPE_CLASS is null for the SACODE and that EDP will treat the record as a field sample.

•This section contains less required fields than its prime counterpart, Sample_Prime.

•The LOCID, SBD, and SED fields that are required in the Sample_Prime section are not included in Sample_Lab.

•SAMPNO is included in the Sample_Lab section but it is not required as it is in Sample_Prime.

•Depending on the value populated in the SACODE field, "Field" or "Lab" will be mapped to DT_SAMPLE.SAMPLE_SOURCE.

•Based on the value populated in the SACODE field, a parent sample code may be required:

•If RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE is "N" for the SACODE record, no parent sample is required.

•If RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE is "Y" for the SACODE record and RT_SAMPLE_TYPE.SAMPLE_TYPE_CLASS is "FQ", then PARENTFLDID is required.

•If RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE is "Y" for the SACODE record and RT_SAMPLE_TYPE.SAMPLE_TYPE_CLASS is "LQ", then PARENTLABID is populated.

•If RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE is "Y" for the SACODE record, but the RT_SAMPLE_TYPE.SAMPLE_TYPE_CLASS field is NULL, an error will be thrown stating that a SAMPLE_TYPE_CLASS value is required.

•Test_Lab

•This section does not contain the AFIID field that is required in previous sections. Rather, it includes a LABSAMPID field that is meant to be populated with a unique sample number assigned by a laboratory.

•This section contains less required fields than its prime counterpart, Test_Prime.

•The LOCID, LOGDATE, LOGTIME, SBD, SED, SACODE, SAMPNO, and LABCODE fields are required in Test_Prime but are not included in Test_Lab.

•MATRIX is included in Test_Lab but is not required as it is in Test_Prime.

•Records in this section require a parent record in Sample_Lab. The records in these two sections are linked via the FLDSAMPID field.

•If the SAMPLE_TYPE_CLASS is FQ, NF, or null, the SAMPID in Test_Lab is set to the FLDSAMPID and the SAMPLE_SOURCE value is 'FIELD'.

•If the SAMPLE_TYPE_CLASS is LQ, the SAMPID in Test_Lab is set to the LABSAMPLEID And the SAMPLE_SOURCE value is 'LAB'.

•The RUN_NUMBER field maps to DT_TEST.TEST_TYPE, which is a lookup to RT_TEST_TYPE in the database. Therefore, the value populated in RUN_NUMBER must pre-exist in RT_TEST_TYPE.TEST_TYPE or EDP will throw an error on Commit.

•Result_Lab

•This section does not contain the AFIID field that is required in previous sections. Rather, it includes a LABSAMPID field that is meant to be populated with a unique sample number assigned by a laboratory.

•This section contains less required fields than its prime counterpart, Result_Prime.

•The LOCID, LOGDATE, LOGTIME, SBD, SED, SACODE, SAMPNO, and LABCODE fields are required in Result_Prime but are not included in Result_Lab.

•MATRIX is included in Result_Lab but is not required as it is in Result_Prime.

•Records in this section require a parent record in Test_Lab. The values populated in the ANMCODE, RUN_NUMBER, FLDSAMPID, ANADATE, and ANATIME fields are all required to match those populated in the fields of the same name in the Test_Lab parent record. All five fields must match before the “Orphan row” errors are resolved.

•A table refresh may be necessary if the “Orphan row” errors are not resolved once all necessary fields match.

•If the SAMPLE_TYPE_CLASS is FQ, NF, or null, the SAMPID in Result_Lab is set to the FLDSAMPID and the SAMPLE_SOURCE value is 'FIELD'.

•If the SAMPLE_TYPE_CLASS is LQ, the SAMPID in Result_Lab is set to the LABSAMPLEID And the SAMPLE_SOURCE value is 'LAB'.

•When populated, the following fields will create default mappings to DT_SAMPLE_PARAMETER.PARAM_CODE. These values must pre-exist in RT_SAMPLE_PARAM_TYPE.PARAM_CODE or EDP will throw an error on Commit.

 

 

Known Issues: The following are behaviors that may impact data loading/exporting with the ERPIMS format:

1.The WINT.IBDEPTH and IEDEPTH fields are configured to permit 7 digits to the left of the decimal point without throwing an error. However, the DT_WELL_SEGMENT.START_DEPTH and END_DEPTH fields will throw "arithmetic overflow" errors if attempting to commit values with more than 6 digits to the left of the decimal point. These fields are therefore limited to values less than 1 million.

2.Both WCI.MPELEV and WMI.ELEV write to DT_WELL.HISTORICAL_REFERENCE_ELEV. If both fields are populated in the format, the WMI.ELEV value will be prioritized and the WCI.MPELEV value overwritten.

 

 

Exports: The data returned will be automatically filtered by the facility to which the user is connected, as well as by the parameters selected prior to running the report.