VI Collect – eCOC Report

Report Name: VI Collect – eCOC (example1)

File Name: EarthSoft.Reports.Library.183376.dll

Dependencies: Licensed Collect Module, Vaport Intrusion Schema, and either SPM Schema or EDGE Schema

Software: EQuIS Collect, EQuIS Enterprise

Source: EQuIS Collect install + Vapor Intrusion Module (EarthSoft Community Center > Products > Vapor Intrusion)

Language: English

Output Type: XML

Description: The VI Collect – eCOC report returns Chain of Custody (COC) data exports based on data from Collect forms. The report can be run from EQuIS Professional, Enterprise, or the Collect mobile app (if the device is connected to the Internet). This report can be setup as an EQuIS Information Agent (EIA).

Installation Instructions: This report requires a licensed EQuIS Collect module, both the Collect Schema and Vapor Intrusion Schema, as well as either the SPM schema or EDGE schema applied to the database. The VI Collect – eCOC report (EarthSoft.Reports.Library.183376.dll) is available for download in the Products\Vapor Intrusion folder on the Downloads Dashboard on the EarthSoft Community Center. For Enterprise use, this report should be published to the database. When publishing the report, the assembly should be stored in the database. The report will be available for selection once it has been published.

Overview:

To enable this report to generate eCOCs in the mobile app, do the following:

1.Save a user report for the VI: Collect – eCOC report. Note: Do not set a Chain of Custody on this saved report; the Chain of Custody value will be determined dynamically by Collect mobile when the report is run for a selected form.

2.Set the user report as the Mobile Report for the Collect Template.

The report will not return any data when run without a "Chain of Custody" parameter populated.

Tables: DT_SAMPLE, DT_CHAIN_OF_CUSTODY, DT_CHAIN_OF_CUSTODY_MAG

Report Parameters

Chain of Custody
Print Latest COC Only from Multiple EBATCHs

Mapping Information

Field Name in eCOC	Mapped Database column	Field Type (Max Length)	Description
Sites
EventID			Current date.
CaseNumber	dt_chain_of_custody.custom_field_1	Text (255)	Not applicable for VI.
ProjectCode	dt_chain_of_custody.project_id	Text (50)	Job Number listed on the COC.
ProjectLeader	dt_chain_of_custody.project_manager	Text (50)	Project manager listed on the COC.
SpillID			Must always be empty.
OPUnit			Must always be empty.
DASNumber			Must always be “n/a”.
ScribeNetProjectID			Must always be empty.
EPARegionNumber	dt_facility.sys_region_code	Text (20)	Regional identifier of a specific facility.
SiteName	dt_facility.facility_name	Text (60)	The user-defined name of a specific facility.
State	dt_facility.state	Text (10)	State/Province of the facility.
SiteNumber	dt_facility.facility_code	Text (20)	The user-defined code of the facility (e.g., "Springfield").
AccountCode	dt_facility.client	Text (50)	The client responsible for a specific facility.
Action	dt_facility.facility_type	Text (20)	Type of the facility.
CERCLIS	dt_facility.program_code	Text (20)	The state or federal regulated program under which the facility falls.
Lead	dt_facility.prp_agency		The regulated agency which oversees the potential responsible party (PRP).
SamplingCompany	dt_facility.data_provider	Text (40)	The person responsible for providing data about the facility.
Station
StationName	dt_sample.sys_loc_code	Text (20)	Location identifier of sample collection.
Location
SampleSplit			Must always be empty.
LocationName	dt_sample.sys_sample_code	Text (40)	The alpha-numeric code that identifies the sample.
SampleIdentifier	dt_sample.sys_sample_code	Text (40)	The alpha-numeric code that identifies the sample.
CompositeGrab	dt_sample.sample_method	Text (40)	Set to “DEFAULT”.
Depth	dt_sample.start_depth	Number	The start depth of the sample. Always empty.
DepthTo	dt_sample.end_depth	Number	The end depth of the sample. Always empty.
DepthUnit	dt_sample.depth_unit	Text (15)	Sample depth unit. Always empty.
SampleComments	dt_field_sample.remark	Text (2000)	General remark about the sample.
SampleDate	dt_sample.sample_date	DateTime	Sample date and time.
SampleTime	dt_sample.sample_date	DateTime	Sample date and time.
SampleEndDate	dt_sample.sample_end_date	DateTime	Sample End date and time.
SampleEndTime	dt_sample.sample_end_date	DateTime	Sample End date and time.
Concentration	dt_sample.custom_field_3	Text (255)	Not Applicable for VI.
SamplerName	dt_field_sample.sampler	Text(50)	Name of the person who took sample.
SampleType	rt_sample_type.sample_type_desc	Text(70)	Description of type of sample.
LocationZone	dt_location.loc_type	Text(20)	The type of specific location (e.g., "Indoor Air”).
LocationDescription	dt_location.loc_desc	Text(255)	A general description of the location type.
LocationMethod	dt_coordinate.verification_code	Text(20)	The verification code of the coordinate system.
LatitudeX	dt_coordinate.x_coord	Text(20)	The x-coordinate in a coordinate system.
LongitudeY	dt_coordinate.y_coord	Text(20)	The y-coordinate in a coordinate system.
Datum	dt_coordinate.coord_type_code	Text(20)	LAT LONG Coordinate type code.
SurfaceElevationDatum	dt_coordinate.elev_datum_code	Text(20)	The code that describes the reference point against which elevation measurements are made.
SurfaceElevation	dt_coordinate.elev	Text(20)	The measured elevation of a specific location.
SurfaceElevationUnits	dt_coordinate.elev_unit	Text(15)	Unit of measure for elevation.
SurfaceElevationMethod	dt_coordinate.elev_collect_method_code	Text(20)	The code which identifies the method used to measure the elevation.
Matrix	rt_matrix.matrix_desc	Text(70)	The description of code that defines a matrix (media) for the specific sample.
Sample
SampleNo	dt_sample.sys_sample_code	Text(40)	The alpha-numeric code that identifies the sample.
LinkSampleNo	dt_sample. parent_sample_code	Text(40)	Sample code of the parent sample.
Bottle
FinalWeight			Must always be empty.
TaredWeight			Must always be empty.
BottleComments	dt_sample_parameter.param_value, where param_code = 'CANNISTER#'	Text(255)	Canister Number.
BottleIdentifier			Unique Bottle ID (calculated based on number of containers).
Preservative			Must always be “None”.
TagNo			Unique Tag Number (calculated based on number of containers).
ProjectCodeAnalysis	dt_chain_of_custody.project_id	Text (50)	Job Number listed on the COC.
TagPrefix	dt_facility.sys_region_code	Text (20)	Regional identifier of a specific facility.
Analysis
TurnaroundUnits			Must always be “Days”.
Turnaround	dt_chain_of_custody.turn_around_time	Text (50)	Lab turnaround time.
LabQCType			Must always be “No”.
PreliminaryResults			Must always be “No”.
TRNo	dt_chain_of_custody.chain_of_custody	Text (40)	COC number.
AnalysisName	rt_mth_anl_group.method_analyte_group_desc	Text (255)	Description for the method analyte group (MAG).
AnalysisAbbrev	rt_mth_anl_group.short_name	Text (30)	Short name for the method analyte group (MAG).
TR Info
ProgramCode	dt_facility.program_code	Text (20)	The state or federal regulated program under which the facility falls.
TRLookUp	dt_chain_of_custody.chain_of_custody	Text (40)	COC number.
CarrierName	dt_chain_of_custody.shipping_company	Text (40)	The company used to ship samples to the Lab.
AirbillNo	dt_chain_of_custody.shipping_tracking_number	Text (50)	The number used to track a sample shipment.
DateShipped	dt_chain_of_custody.shipping_date	DateTime	The date the COC was shipped to the lab.
ShipCaseComplete	dt_chain_of_custody.complete_yn	Text(1)	Denotes whether the chain of custody was complete (Y) or not (N).
SamplerSig			Must always be empty.
LabName	rt_company.company_name	Text(255)	Lab name associated with the COC.
LabAddress1	rt_company.address_1	Text(40)	Lab address line 1.
LabAddress2	rt_company.address_2	Text(40)	Lab address line 2.
LabPhone	rt_company.phone_number	Text (30)	Lab contact phone number.
LabFax	rt_company.fax_number	Text (30)	Lab fax number.
LabCity	rt_company.city	Text (30)	Lab city.
LabState	rt_company.state	Text (10)	Lab state/province.
LabZipCode	rt_company.postal_code	Text (30)	Lab postal/zip code.