NEDDCommon Format

Format Name: NEDDCommon

File Name: NEDD_Format

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: NEDDCommon-enum.xml

License Level: PlusD

Description: The EQuIS NEDDCommon (Naval Electronic Data Deliverable) Format is used to submit data to the Naval Installation Restoration Information Solution (NIRIS) database. The file structure and logic checks are based on the specifications defined by those used in the NIRIS data tables. More information on NIRIS data submission can be accessed from the Naval Facilities Engineering Systems Command (NAVFAC) Data Submittal Guidance.

Notes:

•The NEDDCommon format does not include custom checks for fields associated with vapor intrusion. The NIRIS Custom Checks Table (shown below) includes the vapor intrusion checks used by NIRIS to reference.

•The NIRIS Custom Checks Table outlines all the custom checks that are specific to the NIRIS database. It is advised to reference this table for any updates to previously used custom checks or for any additional custom checks as the NEDDCommon format does not accommodate all the NIRIS-specific custom checks.

Installation Instructions: The NEDDCommon format is available as part of a format package (NEDDCommon_FormatPackage.zip). To access the format, download the NEDDCommon format package from the EarthSoft Community Center, unblock the *.zip file, and extract the contents. The format package includes the following files:

•NEDDCommon.zip – The NEDDCommon format.

•NEDDCommon refvals.xlsx – An example reference value EDD to be used with the standard EQuIS refvals format.

•NEDDCommon remap.xlsx – An example reference value EDD that will create a NEDD remap set in the database.

•Project Example EDD.xlsx – An example EDD with contact, contract, and environmental project data to use with the NEDDCommon format.

•Well Example EDD.xlsx – An example EDD with location, borehole, and well data to use with the NEDDCommon format.

•Sample Example EDD.xlsx – An example EDD with sample and analytical result data to use with the NEDDCommon format.

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

Overview: The NEDDCommon format consists of specific sections that mimic those found in the NAVFAC EDD. This format includes the following sections for basic NIRIS submittal:

•POINT_OF_CONTACT – One record should be submitted for each Point of Contact (POC). Contractors must first verify that the POC name does not already exist by cross-checking a list of previously submitted POC name values, which can be obtained from the Naval POC.

•PROGRAM_CONTRACT – One record should be submitted for each new contract. Contractors must first verify that the contract name does not already exist by cross-checking a list of previously submitted CONTRACT_ID values. This list can be obtained from the Naval POC.

•ENVIRONMENTAL_PROJECT – A new record should be submitted for each task. For example, if the drilling was conducted under Delivery Order 12 (DO12) and the samples were collected under DO15, a submittal should be included for both DO12 and DO15. Contractors must first verify that the project name does not already exist by cross-checking a list of previously submitted project name values, which can be obtained from the Naval POC.

•LOCATION – One record should be submitted for each new location. Contractors must first verify that the location name does not already exist by cross-checking a list of previously submitted location name values, which can be obtained from the Naval POC.

•BOREHOLE – One record should be completed for each borehole. If a borehole is completed with different diameters, additional records should be submitted for each additional diameter (segment). It is not necessary to fill out fields that are not required for records associated with additional diameters. Contractors must first verify that the borehole name does not already exist by cross-checking a list of previously submitted borehole name values, which can be obtained from the Naval POC.

•WELL – A record should be submitted once for each new well at a site. Detailed information about well components should be addressed in the WELL_CONSTRUCTION NEDD. Contractors must first verify that the well name does not already exist by cross-checking a list of previously submitted well name values, which can be obtained from the Naval POC.

•SAMPLE – Records address information about sample collection (date, time, sample name, sample type, etc.). Associated field QC samples (ambient, trip, source and equipment blanks) should be recorded on the same record. Records for these QC samples must be submitted concurrently or must already exist in the system.

•ANALYTICAL_RESULT – Records address information associated with the sample results (test method, lab sample ID, qualifiers, etc.). One record should be submitted for each result.

Limitations/Assumptions: The checks and mappings in any EDD format involve assumptions. The following are assumptions and limitations for the NEDDCommon format:

•Test Alternate Key (TAK) fields available in the format: SAMPLE_ID, ANALYTIC_METHOD, ANALYSIS_DATE, FRACTION, TEST_TYPE, and COLUMN_NUMBER. The IDENTIFIER field does not exist in the NEDDCommon format. If “Identifier” is toggled in the database test-level key fields, an error message will appear for DT_TEST upon the Create step.

•The name of the facility being used for NEDD data should be the name of the INSTALLATION_ID. The facility name is what is used as the value for INSTALLATION_ID when exporting data from the NEDDCommon format.

•Prior to uploading to EDP, ensure the following:

•All default mappings in the EDD description pre-exist in the EQuIS tables. These values are used as a reference to properly map data to the corresponding tables and an error will occur if the reference is not found. (For example, RT_COMPANY.COMPANY_TYPE must be 'DATA PROVIDER').

•All values that are being used to populate lookup fields need to exist in their designated reference tables (e.g. POINT_OF_CONTACT.STATE must exist in RT_STATE as STATE_CODE).

•Ensure that the following values are present in their associated tables:

•PROGRAM_CONTRACT Section

•'CONTRACT' must exist as a valid value in the RT_SUBFACILITY_TYPE table as SUBFACILITY_TYPE.

•The CONTRACTOR_NAME must exist as a valid value in the RT_COMPANY table as COMPANY_CODE.

•ENVIRONMENTAL_PROJECT Section

•'PROJECT' must exist as a valid value in the RT_SUBFACILITY_TYPE table as SUBFACILITY_TYPE.

•The CONTRACT_ID must exist as a value in the DT_SUBFACILITY table as SUBFACILITY_CODE.

•LOCATION Section

•The LOCATION_TYPE is an enumeration and must exist as a valid value in the RT_LOCATION_TYPE table as LOCATION_TYPE_CODE.

•The PROJECT_SPECIFICATION is an enumeration and must exist as a valid value in the RT_COORD_TYPE table as COORD_TYPE_CODE.

•The SURVEY_METHOD is an enumeration and must exist as a valid value in the RT_COORD_HORZ_METHOD table as HORZ_COLLECT_METHOD_CODE.

•The SURVEY_UNITS is an enumeration and must exist as a valid value in the RT_UNIT table as UNIT_CODE.

•'INDOOR_ZONE_NAME' must exist as a valid value in the RT_LOCATION_PARAM_TYPE table as param_code.

•'SUBCONTRACTOR' must exist as a valid value in the RT_COMPANY_TYPE table as COMPANY_TYPE.

•The ESTABLISHING_CONTRACT_ID must exist as a value in the DT_SUBFACILITY table as SUBFACILITY_CODE.

•WELL Section

•The LOCATION_NAME must exist as a value in the DT_LOCATION table as SYS_LOC_CODE or in the LOCATION section of the format.

•SAMPLE Section

•The LOCATION_NAME must exist as a value in the DT_LOCATION table as SYS_LOC_CODE or in the LOCATION section of the format.

•The SAMPLING_EQUIPMENT is an enumeration and must exist as a valid value in the RT_SAMPLE_METHOD table as METHOD_CODE.

•The SAMPLE_MATRIX is an enumeration and must exist as a valid value in the RT_MATRIX table as MATRIX_CODE.

•'WATER COLUMN' must exist as a valid value in the RT_SAMPLE_PARAM_TYPE table as PARAM_CODE.

•'REMOVED' must exist as a valid value in the RT_SAMPLE_PARAM_TYPE table as PARAM_CODE.

•'EXCAVATION_NAME' must exist as a valid value in the RT_SAMPLE_PARAM_TYPE table as PARAM_CODE.

•'SEX' must exist as a valid value in the RT_SAMPLE_PARAM_TYPE table as PARAM_CODE.

•'AGE_CLASS' must exist as a valid value in the RT_SAMPLE_PARAM_TYPE table as PARAM_CODE.

•'SPECIMEN_NAME' must exist as a valid value in the RT_SAMPLE_PARAM_TYPE table as PARAM_CODE.

•'BIOLOGICAL_CLASS' must exist as a valid value in the RT_SAMPLE_PARAM_TYPE table as PARAM_CODE.

•'WATER_COLUMN' must exist as a valid value in the RT_SAMPLE_PARAM_TYPE table as PARAM_CODE.

•'vapor_intrusion' must exist as a valid value in the RT_SAMPLE_PARAM_TYPE table as PARAM_CODE.

•ANALYTICAL_RESULT Section

•'METHOD' must exist as a valid value in the RT_TEST_BATCH_TYPE table as TEST_TYPE.

•'RUN' must exist as a valid value in the RT_TEST_BATCH_TYPE table as TEST_TYPE.

•The SAMPLE_MEDIUM is an enumeration and must exist as a valid value in the RT_MATRIX table as MATRIX_CODE.

•The RESULT_TYPE is an enumeration and must exist as a valid value in the RT_TEST_TYPE table as TEST_TYPE.

•The SDG must exist as a value in the DT_SDG table as SDG_NAME.

•The SAMPLE_NAME must exist as a value in the DT_SAMPLE table as SYS_SAMPLE_CODE.

•The FINAL_QUALIFIER is an enumeration and must exist as a valid value in the RT_QUALIFIER table as QUALIFIER.

•COLLECT_TIME field in the SAMPLE section cannot have any non-zero values for seconds.

•LEACHATE_TIME, EXTRACTION_TIME, and ANALYSIS_TIME fields in the ANALYTICAL_RESULT section cannot have any non-zero values for seconds.

•NEDDCommon export notes:

•DT_SUBFACILITY.PARENT_SUBFACILITY_CODE of the project must match the SUBFACILITY_CODE associated with the contract (e.g., SUBFACILITY_CODE = ‘DO12 SHIP REM INVEST’, SUBFACILITY_TYPE = ‘PROJECT’, PARENT_SUBFACILITY_CODE = ‘D459559365800’ should correlate with SUBFACILITY_CODE = ‘D459559365800’, SUBFACILITY_TYPE = ‘CONTRACT’).

•PROJECT_SPECIFICATION in the LOCATION section of the format (maps to DT_COORDINATE.COORD_TYPE_CODE) must be the value for DT_FACILITY.COORD_TYPE_CODE.

•If generating the BOREHOLE section, DRILL_EVENT is included as one of the fields, however, this is not a field in the NIRIS schema. This field is a requirement as part of the EQuIS Schema for DT_DRILL_ACTIVITY and will throw an error when the BOREHOLE section is generated.

•To generate the BOREHOLE section, DT_LOCATION.LOC_TYPE must = 'BH', 'SS', or 'SB'.

•To generate the WELL section, DT_LOCATION.LOC_TYPE must = 'MW', 'WW', or 'WLM'.

•DT_SAMPLE.SYS_LOC_CODE must have the same value as DT_LOCATION.SYS_LOC_CODE associated with the appropriate SUBFACILITY_CODE.

•PROGRAM_CONTRACT.CONTRACTOR_NAME is generated when RT_COMPANY.CONTACT_NAME correlates with DT.PERSON.PERSON_NAME with a matching COMPANY_CODE.

•BOREHOLE.DEPTH_UNIT must be the same value as DT_FACILITY.COORD_UNIT or the value for DT_FACILITY.COORD_UNIT will overwrite the value for BOREHOLE.DEPTH_UNIT.

Note: There are visible ‘AM’ and ‘PM’ components to time-related fields in the SAMPLE and ANALYTICAL_RESULT sections when viewing the NEDDCommon export in EDP. If the export EDD is saved as an .xls, .xlsx, .csv, or .txt file, 'AM' and 'PM' are removed and the time is formatted as HH:MM:SS.

For custom checks and additional default mapping, please see the EDD Description file.

EQuIS Table Mapping:

•AT_COMPANY_PERSON

•AT_TEST_BATCH_ASSIGN

•DT_COORDINATE

•DT_DRILL_ACTIVITY

•DT_EXCAVATION

•DT_FACILITY

•DT_FIELD_SAMPLE

•DT_LOCATION

•DT_PERSON

•DT_RESULT

•DT_RESULT_QC

•DT_SAMPLE

•DT_SAMPLE_PARAMETER

•DT_SUBFACILITY

•DT_TASK

•DT_TEST

•DT_TEST_BATCH

•DT_WATER_TABLE

•DT_WELL

•RT_COMPANY

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. See the Exports from Professional EDP article for more information about running exports.

Note: Exports will not work as expected if the data were imported using a different format, as each format has different mappings, checks, etc.

Export	Format Section	Source Table(s)
NEDDCommon	POINT_OF_CONTACT	AT_COMPANY_PERSON DT_PERSON DT_SUBFACILITY RT_COMPANY
	PROGRAM_CONTRACT	AT_COMPANY_PERSON DT_PERSON DT_SUBFACILITY RT_COMPANY
	ENVIRONMENTAL_PROJECT	DT_PERSON DT_SUBFACILITY DT_TASK
	LOCATION	DT_COORDINATE DT_LOCATION DT_SUBFACILITY RT_COMPANY
	BOREHOLE	DT_FACILITY DT_DRILL_ACTIVITY DT_LOCATION DT_SUBFACILITY RT_COMPANY
	WELL	DT_FACILTY DT_SUBFACILITY DT_WATER_TABLE DT_WELL
	SAMPLE	DT_FACILITY DT_EXCAVATION DT_FIELD_SAMPLE DT_SAMPLE DT_SAMPLE_PARAMETER DT_SUBFACITY DT_TASK
	ANALYTICAL_RESULT	AT_TEST_BATCH_ASSIGN DT_FACILITY DT_FIELD_SAMPLE DT_RESULT DT_RESULT_QC DT_SUBFACILITY DT_TEST DT_TEST_BATCH RT_COMPANY

Known Issues: The following are known issues for the NEDDCommon format export:

•The default mapping of ‘WATER COLUMN’ to DT_SAMPLE_PARAMETER.PARAM_CODE in the SAMPLE section does not contain an underscore between ‘WATER’ and ‘COLUMN’. This causes the value for the ‘WATER_COLUMN’ field to not load properly when the export is generated and is currently being resolved.

•There are visible ‘AM’ and ‘PM’ components to time-related fields in the SAMPLE and ANALYTICAL_RESULT sections when viewing the NEDDCommon export in EDP. If the export EDD is saved as an .xls, .xlsx, .csv, or .txt file, 'AM' and 'PM' are removed and the time is formatted as HH:MM:SS.

Export Parameters: The NEDDCommon format export includes all eight sections and can be filtered by the following parameters.

NEDDCommon Export

Project(s)	The environmental project(s) associated with the Contract ID and Contract Task Order or Delivery Order (e.g., DO12 Shipyard Remedial Investigation).
Phase/Task	The Task Phase, Subtask Number, or Annual Quarter associated with environmental project (e.g., QTR1).
Location
Individual(s)	The unique location where sampling or other activities were conducted (e.g., MW-2R).
Group(s)	A collection of individual locations commonly grouped by sampling attributes (e.g., WG Sampling).
Results
Analytes
Individual(s)	The unique Analyte ID assigned for the analysis parameter. The analyte can be filtered by CAS number or chemical name (e.g., 71-43-2 or Benzene).
Group(s)	A collection of individual analytes organized by Method Analyte Group (e.g., Total BTEX).
Sample Delivery Group	The identification number for a group of samples received over a period of time. The sample delivery group (SDG) can be filtered by name or description (e.g., SDG 009384 or SDG for groundwater sampling performed at MW-2R 20100101).
Data Range
Start	The general start date for sample collection and/or lab analysis (e.g., 01/01/2020).
End	The general end date for sample collection and/or lab analysis (e.g., 12/30/2020).

NIRIS Custom Checks: These are the custom checks that NIRIS enforces but are not enforced in the NEDDCommon format. Please consult NAVFAC’s Data Submittal Guidance for the most updated business rules.

Format Section	NIRIS Custom Checks
POINT_OF_CONTACT	•If STREET_ADDRESS_1, CITY, STATE, or ZIP_CODE is populated, all these fields must be populated.
PROGRAM_CONTRACT	•POC_NAME is required if POC_TITLE is populated.
ENVIRONMENTAL_PROJECT	•POC_NAME is required if POC_TITLE is populated. •END_DATE must be greater than or equal to START_DATE.
LOCATION	•ESTABLISHING_CONTRACT_ID is required if ESTABLISHING_DO_CTO or ESTABLISHING_PHASE is populated. •If SITE_NAME starts with 'offsite', PROXIMITY must start with 'O'. •ELEV_UNITS is required if GROUND_ELEV is populated. •ALTITUDE_DATUM is required if GROUND_ELEV is populated. •ELEV_METHOD is required if GROUND_ELEV is populated. •ALTITUDE_DATUM is required if GROUND_ELEV is provided. •GROUND_ELEV is required if SURVEY_METHOD is 'VS'. •For Vapor Intrusion sampling associated with a building, LOCATION_TYPE must be one of: 'BLDG_BKG', 'BLDG_BR', 'BLDG_ENT', 'BLDG_OTHER', 'CRAWLSPACE', 'OUTDOOR', 'SG_EXT', 'SR', or 'SUBSLAB'. For Vapor Intrusion sampling not associated with a building, LOCATION_TYPE must be one of: 'EF_MIT', 'IN_MIT', or 'OUTDOOR'. •ALTERNATE_NAME cannot replicate LOCATION_NAME in the LOCATION_NAME Naval Master List. ALTERNATE_NAME cannot duplicate the location's LOCATION_NAME. •If LOCAL_SYSTEM_DESC is provided, then PROJECTION_SPECIFICATION must equal 'OTHER'. LOCALDESCR is required when PROJECTION_SPECIFICATION is 'OTHER'.
BOREHOLE	•CONTRACT_ID is required if DO_CTO_NUMBER or PHASE is populated. •SEG_DIAMETER required if SEG_BEG_DEPTH or SEG_END_DEPTH populated. •SEG_DIAMETER must be less than or equal to BH_DIAMETER. •SEG_DIAM_UNITS is required if SEG_DIAMETER is populated. •SEG_BEG_DEPTH is required if SEG_DIAMETER or SEG_NARRATIVE is populated. •SEG_END_DEPTH is required if SEG_BEG_DEPTH is populated. •SEG_END_DEPTH must be greater than or equal to SEG_BEG_DEPTH. •SEG_END_DEPTH must be less than or equal to TOTAL_DEPTH. •SEG_DEPTH_UNITS is required if SEG_BEG_DEPTH or SEG_END_DEPTH is populated.
WELL	•BOREHOLE_NAME is required if START_DATE is after 20201001 (is not required if before 20201001). •CONTRACT_ID is required if DO_CTO_NUBER or PHASE is populated. •DO_CTO_NUMBER is required if CONTRACT_ID or PHASE is populated. •ABANDONED_DATE is required if WELL_STATUS = 'A'.
SAMPLE	•SAMPLE_NAME cannot be used as the value for AMBIENT_BLANK, EQUIPMENT_BLANK, SOURCE_BLANK, TRIP_BLANK or PARENT_SAMPLE_NAME value of the same record. •SITE_NAME is required if SAMPLE_TYPE = 'N'. •LOCATION_NAME is required if SAMPLE_TYPE = 'N' or 'FD', or when VAPOR_INTRUSION = 'Y' or 'M'. •If SPECIMEN_NAME or BIOLOGICAL_CLASS is populated, both must be populated. •PARENT_SAMPLE_NAME is required if SAMPLE_TYPE = 'FD'. •ELEV_UNITS required if DATUM_ELEV is populated. •DATUM_DESC required if DATUM_ELEV is populated. •START_DEPTH required if SAMPLE_MATRIX = 'SO'. •END_DEPTH is required if SAMPLE_MATRIX = 'SO'. If END_DEPTH is populated, then START_DEPTH must be populated. •END_DEPTH must be greater than START_DEPTH unless vapor intrusion LOCATION_TYPE = 'CRAWLSPACE' or 'SUBSLAB'. •DEPTH_UNITS required if START_DEPTH or END_DEPTH is populated. •REMOVED_DATE required if REMOVED = 'Y'.
ANALYTICAL_RESULT	•Each value for SAMPLE_NAME should be associated with a SAMPLE_NAME from the SAMPLE section. •LEACHATE_DATE required if LEACHATE_METHOD is not 'NONE'. •LEACHATE_DATE must be less than or equal to ANALYSIS_DATE if LEACHATE_DATE and ANALYSIS_DATE are populated. •LEACHATE_TIME required if LEACHATE_METHOD is not 'NONE'. •Combination of LEACHATE_DATE and LEACHATE_TIME must be less than the combination of ANALYSIS_DATE and ANALYSIS_TIME. •EXTRACTION_DATE required if EXTRACTION_METHOD is not 'NONE'. •If EXTRACTION_DATE and ANALYSIS_DATE are populated, EXTRACTION_DATE must be less than ANALYSIS_DATE. •EXTRACTION_TIME required if EXTRACTION_METHOD does not equal 'NONE'. •The combination of EXTRACTION_DATE and EXTRACTION_TIME must be less than or equal to the combination of ANALYSIS_DATE and ANALYSIS_TIME. •ANALYSIS_DATE required if COLLECT_DATE of associated sample is greater than or equal to 20150101. •ANALYSIS_TIME required if COLLECT_DATE of associated sample is greater than or equal to 20150101. •LAB_SAMPLE_ID required if COLLECT_DATE of associated sample is greater than or equal to 20150101. •ORIGINAL_ANALYTE_VALUE required if COLLECT_DATE of associated sample is greater than or equal to 20150101. •SDG required if COLLECT_DATE of associated sample is greater than or equal to 20150101. •DILUTION must be greater than or equal to 1 or can be -999 (NULL value). •RUN_NUMBER must = 1 to 10 or -999 (NULL value). •RUN_NUMBER must not equal 1 for reanalysis. •ANALYTE_VALUE cannot equal 0. •If VALIDATOR_QUALIFIER contains 'U', DETECTED must = 'N'. •If VALIDATOR_QUALIFIER is equal to '=', DETECTED must = 'Y'. •If FINAL_QUALIFIER contains 'R', DETECTED must = 'R'. •QC_CONTROL_LIMIT_CODE required if QC_ACCURACY_UPPER, QC_ACCURACY_LOWER, CONTROL_LIMIT_DATE, RELATIVE_PCT_DIFF or QC_NARRATIVE is populated. •SDG required if LEACH_LOT, METHOD_BATCH, PREP_BATCH, RUN_BATCH, or ANALYSIS_BATCH is populated. •VALIDATOR_NAME required if VALIDATION_QC does not equal 'NO_VALIDATION' or 'UNKNOWN'. •If VAL_DATE and ANALYSIS_DATE are populated, VAL_DATE must be greater than ANALYSIS_DATE.