<< 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 > Products > Formats > v7 > 22.1+.

Export(s): Yes (see section below)

Enumeration File: Not applicable

 

 

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 EarthSoft ERPIMS format was designed 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: Download the ERPIMS format from the EarthSoft Community Center and unblock the *.zip 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.

 

 

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.

 

Example RT_LOOKUP, RT_REMAP and RT_REMAP_DETAIL tables are available here in ERPIMS_RefVals.xlsx. Review and modify these reference values based upon the company, organization and/or project requirements. After making modifications, upload the reference values using the RefVals format; for more information, see Reference Values - Importing.  

 

 

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

•This section of the format does not currently map to the database, but the ‘Group 1 SCC’ export can be used to extract the DT_SUBFACILITY table data from the database into this 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.

•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.

•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.

•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.

•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.

•ERR09 error handling:

•No errors are displayed when SACODE is not populated.

•No errors are displayed when RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE is not "Y".

•An error is thown in SACODE when RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE is "Y", RT_SAMPLE_TYPE.SAMPLE_TYPE_CLASS is not populated, and SACODE is populated.

•An error is thown in PARENTFLDID when RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE is "Y", RT_SAMPLE_TYPE.SAMPLE_TYPE_CLASS is "LQ", and PARENTFLDID is populated.

•An error is thrown in PARENTLABID when RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE is "Y", RT_SAMPLE_TYPE.SAMPLE_TYPE_CLASS is "LQ", and PARENTLABID is not populated.

•An error is thrown in PARENTLABID when RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE is "Y", RT_SAMPLE_TYPE.SAMPLE_TYPE_CLASS is "FQ", and PARENTFLDID is populated.

•An error is thrown in PARENTLABID when RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE is "Y", RT_SAMPLE_TYPE.SAMPLE_TYPE_CLASS is "FQ", and PARENTFLDID is not populated.

•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.

•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.

•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.

 

 

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.