EZSPM Format

<< Click to Display Table of Contents >>

Navigation:  SPM > SPM > Formats >

EZSPM Format

Format Name: EZSPM

File Name: EZSPM.zip

Software: EQuIS SPM

Source: SPM install (and xcopies)

Export(s): Not Applicable

 

Description: The EZSPM format is a simplified version of the SPM format. The EZSPM format allows users to easily create multiple planned and scheduled tasks, samples, results, containers, and activities with limited manual data entry. This format assumes the plan already exists in SPM and the SPM_CONTRACT_MAG information is already setup. Due to the simplified nature of the EZSPM format, there is not a distinct one-to-one relationship between the SPM format sections and the EZSPM format sections.

 

Installation Instructions: This format requires a licensed SPM and the SPM Schema applied to the database.

For SPM usage, no additional steps are necessary.

 

Overview: The Analysis_Request_General, Analysis_Request_Detail, and Activity_Request_Detail sections automatically create multiple Task, Sample, and Activity rows, respectively, on EDD load. The Task, Sample, and Activity sections can then be reviewed and modified before creating and committing. The Analysis_Request_General, Analysis_Request_Detail, and Activity_Request_Detail sections do not map to any database tables—these sections just create rows in other sections of the EZSPM format.

 

The EZSPM format contains the following sections:

Analysis_Request_General – No data table mappings, automatically populates and creates values in the Task section of the EDD. Some reference values can be added, but it is not advised to add reference values using this format. This section must be pre-populated before loading into EDP to populate the Task section.

Analysis_Request_Detail – No data table mappings, automatically populates and creates values in the Sample section of the EDD. Some reference values can be added, but it is not advised to add reference values using this format. This section must be pre-populated before loading into EDP to populate the Sample section.

Activity_Request_Detail – No data table mappings, automatically populates and creates values in the Activity section of the EDD. Some reference values can be added, but it is not advised to add reference values using this format. This section must be pre-populated before loading into EDP to populate the Activity section.

Task – Parts of this section can be automatically populated by the Analysis_Request_General section. This section is for data related to sampling events that were planned or scheduled in SPM.  

Sample – Parts of this section can be automatically populated by the Analysis_Request_Detail section. This section is used for data related to the samples involved in the associated SPM task.

Activity – Parts of this section can be automatically populated by the Activity_Request_Detail section. This section is used for data related to specific actions being performed at specific locations during a sampling event.

Collect_Members – EQuIS Collect users associated with the SPM plan can be defined here.

Files – Any files that are associated with a sample delivery group can be reported here.

 

The workflow for configuring SPM plans and tasks with this format may include:

1.Open the EZSPM format in EDP.

2.Add a single row to the Analysis_Request_General section. This will correspond to one Task row. Note that each EDD can only have one Analysis_Request_General row.

3.Add one or more rows to Analysis_Request_Detail section. Each row in Analysis_Request_Detail will create one to many Sample rows.  Analysis_Request_Detail.location_group or Analysis_Request_Detail.location dictates how many Sample rows will be created. One Sample row will be created for every location within the location_group, or individual location listed.  The Sample section creates samples, results, and containers when committed. One container per SPM_CONTRACT_MAG will be created.

4.Add one or more rows to Activity_Request_Detail. Each row in Activity_Request_Detail will create one to many Activity rows.  Activity_Request_Detail.location_group or Activity_Request_Detail.location dictates how many Activity rows will be created. One Activity row will be created for every location within the location_group, or individual location listed.

5.Save the EDD.

6.Clear EDP or reopen the EZSPM format.

7.Open the EDD saved in Step 5.

8.Review the Task, Sample, and Activity rows for errors.

9.Add Collect_Members rows as desired. This section allows users to add specific user_names to existing Collect groups.

10.Create and Commit.

 

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

The Samples table contains a check to ensure the records plan_code, lab_code, contract_start_date, method_analyte_group_code, and spm_contract_mag corresponds to a row in DT_SPM_CONTRACT_MAG. A "plan_code, lab_code, contract_start_date, method_analyte_group_code, and spm_contract_mag must correspond to a row in dt_spm_contract_mag" error will occur if a record does not match or fields called upon by the check are NULL in DT_SPM_CONTRACT_MAG. The fields included in this check are outlined below:

DT_SPM_CONTRACT_MAG.SPM_CONTRACT_ID must be populated for the associated DT_SPM_CONTRACT record to provide the plan_code (DT_SPM_CONTRACT.PLAN_CODE), lab_code (DT_SPM_CONTRACT.CONTRACTOR_CODE) and contract_start_date (DT_SPM_CONTRACT.CONTRACT_START_DATE).

DT_SPM_CONTRACT_MAG.REMARK must be populated with the spm_contract_mag_id to provide the spm_contract_mag.

DT_SPM_CONTRACT_MAG.METHOD_ANALYTE_GROUP_CODE must be populated to provide the method_analyte_group_code.

 

When populating the Analysis_Request_Detail and Activity_Request_Detail sections, either populate location_group or location. Do not populate both.

 

Recurrence:

 

The Task.Recurrence field can be used to define the frequency with which a task takes place. This field requires a specific notation to be properly parsed by SPM. See below for the specific notation and examples.

 

Note: There is a known issue where a "Conversion from type 'DBNull' to type 'String' is not valid" error is thrown when attempting to open tasks that were created via this format in SPM. Users can get around this behavior by opening DT_SPM_PLANNED_TASK in the database and populating the DURATION_TYPE field with ‘d’.

When using the Analysis_Request_Detail section to help populate the Task section, the recurrence field is populated by default with a value of ‘n’. This will create a single task instance that spans the entire length of the Plan, from the start date to the end date. To change this, users must configure the recurrence using one of the notations defined below.

The individual sections within the notation are separated by a pipe delimiter ( | ), the number of which will vary depending on the specifications of the recurrence.

Daily

d|x, where x = every x days| x (same value as previous x)|True/False, where True = tasks occur only on weekdays

Example: d|5|5|False – Tasks will take place on a daily schedule, every 5 days, weekends included.

Weekly/Bi-Weekly/N-Weekly

w|Comma separated list of days on which tasks occur1|x, where x = every x number of weeks

1 Days of the week must be represented by their numerical equivalents, where Monday =1, Tuesday = 2, and so on until Sunday = 7.

Example: w|2,5|2 – Tasks will take place on a weekly, bi-weekly, or n-weekly schedule, every Tuesday and Friday, every 2 weeks.

Monthly/Quarterly/Semi-annually

m|Day of the week|nth1|Day of the week2|nth3|Month Count

1 The first ‘nth’ value has 6 valid values: First, Second, Third, Fourth, Fifth, and Last.

This section should be populated if a user intends to enter a specific day of the week in the first ‘Day of the week’ section, such as “on the Second Tuesday of every Month”.

This section should be left blank if a user intends to just use ‘Day’, such as “on the 2nd Day of every Month”.

2 If a day of the week is entered at all, it must be entered twice. There should be no difference between the second section of the notation and the fourth.

3 The second ‘nth’ value has 31 valid values (1-31). This field should always be populated. If a value is entered in the first ‘nth’ section of the notation, such as ‘Second’, then enter a ‘1’ in this section.

Note: In the case where some sections of the notation will be left blank, the pipe delimiters should still be entered but nothing should come between them.

Example: m|friday|second|friday|1|2 – Tasks will occur on a monthly, quarterly, or semi-annual schedule, on the second Friday of every 2 months.

Example: m||||4|5 – Tasks will occur on a monthly, quarterly, or semi-annual basis, on the 4th day of every 5 months.
In this case, the Day of the week and first ‘nth’ sections were not necessary, so 4 pipe delimiters were entered with nothing in between to signify that the value of these 3 sections is null.

Annually

y|Day of the week|nth1|Day of the week2|nth3|Month4|x, where x = every x number of years

1 The first ‘nth’ value has 5 valid values: First, Second, Third, Fourth, and Last.

This section should be populated if a user intends to enter a specific day of the week in the first ‘Day of the week’ section, such as “on the Second Tuesday of every October”.

This section should be left blank if a user intends to just use ‘Day’, such as “on the 2nd Day of every October”.

2 If a day of the week is entered at all, it must be entered twice. There should be no difference between the second section of the notation and the fourth.

3 The second ‘nth’ value has 31 valid values (1-31). This field should always be populated. If a value is entered in the first ‘nth’ section of the notation, such as ‘Second’, then enter a ‘1’ in this section.

4 Months must be represented by their numerical equivalents, where January = 1, February = 2, and so on until December = 12.

Note: In the case where some sections of the notation will be left blank, the pipe delimiters should still be entered but nothing should come between them.

Example: y|monday|third|monday|1|10|2 – The task will occur on an annual basis, every third Monday of October every 2 years.

Example: y||||2|4|3 – Tasks will occur on an annual basis, on the 2nd of April every 3 years.
In this case, the Day of the week and first ‘nth’ sections were not necessary, so 4 pipe delimiters were entered with nothing in between to signify that the value of these 3 sections is null.

 

Note: Task, Sample, and Activity rows will be automatically created each time an EZSPM EDD is loaded with Analysis_Request_General, Analysis_Request_Detail, and Activity_Request_Detail information. If there needs to be changes to the Analysis_Request_General, Analysis_Request_Detail, or Activity_Request_Detail sections after opening the EDD (e.g., Step 7 above, which automatically populated Task, Sample, and Activity), ensure that Task, Sample, and Activity are cleared before saving the EDD. Not doing so will result in duplicate Task, Sample, and Activity rows when the EDD is opened again.

 

Note: When adding a new record to the RT_COMPANY table from the Analysis_Request_General, Analysis_Request_Detail, Task, Sample, or Activity sections of the format, the record will be generated with a COMPANY_TYPE value of "LAB" as set in the RT_COMPANY_TYPE table. The value of "LAB" or a variation (e.g., "Lab") must be present in RT_COMPANY_TYPE table. To disable the automatic addition of a new RT_COMPANY table record, set the EDP configuration settings to not accept warnings.

 

The tables below provide detailed automatic population information for the Task, Sample, and Activity sections. Blank cells indicate that the field is not automatically populated.

 

Task

Populated By

plan_code

Analysis_Request_General.plan_code

planned_task_code

Analysis_Request_General.task_code

planned_task_name


scheduled_task_code

Analysis_Request_General.task_code + _+ Analysis_Request_General.start_date

start_date

Analysis_Request_General.start_date

end_date

Analysis_Request_General.end_date

remark

Analysis_Request_General.other

task_type

Analysis_Request_General.activity_type

company_code

Analysis_Request_General.company_code

max_turnaround_days

Analysis_Request_General.max_turnaround_days

task_desc


recurrence

n

custom_field

Analysis_Request_General.use_collect

create_scheduled_tasks

Analysis_Request_General.create_scheduled_tasks

include_dates

Analysis_Request_General.start_date + | + Analysis_Request_General.start_date + ||

naming_schema_task


 

 

Sample

Populated By

plan_code

Analysis_Request_General.plan_code

lab_code

Analysis_Request_Detail.lab_code

contract_start_date

dt_spm_contract where contractor_code = Analysis_Request_Detail.lab_code and plan_code = Analysis_Request_General.plan_code or is null

method_analyte_group_code

Analysis_Request_Detail.method_analyte_group_code

spm_contract_mag

Analysis_Request_Detail.spm_contract_mag

sys_sample_code

Analysis_Request_Detail.location (or location from Analysis_Request_Detail.location_group) + _ + Analysis_Request_Detail.matrix_code + _ + Analysis_Request_General.start_date + _ + an incremental three-digit number

sample_name


scheduled_task_code

Analysis_Request_General.task_code + _+ Analysis_Request_General.start_date

parent_sample_code


sys_loc_code


start_depth


end_depth


depth_unit


matrix_code

Analysis_Request_Detail.matrix_code

sampler


sampling_company_code


sample_class


sample_source


sample_type_code

Analysis_Request_Detail.sample_type_code

sample_method


medium_code


composite_yn


fraction

Analysis_Request_Detail.total_or_dissolved

sample_remark


hold_YN


hold_release_date


 

 

Activity

Populated By

plan_code

Analysis_Request_General.plan_code

scheduled_task_code

Analysis_Request_General.task_code + _+ Analysis_Request_General.start_date

sys_loc_code

Activity_Request_Detail.location (or location from Activity_Request_Detail.location_group)

activity_code

Activity_Request_Detail.activity_code

equipment_code

Activity_Request_Detail.equipment_code

sampler


sampling_company_code


remark


task_type


activity_type

Activity_Request_Detail.activity_type

activity_order


count


unit


 

 

EQuIS Table Mappings:

DT_FILE

DT_SPM_PLAN

DT_SPM_PLANNED_ACTIVITY

DT_SPM_PLANNED_RESULT

DT_SPM_PLANNED_SAMPLE

DT_SPM_PLANNED_TASK

DT_SPM_SCHEDULED_ACTIVITY

DT_SPM_SCHEDULED_SAMPLE

DT_SPM_SCHEDULED_TASK

RT_COMPANY

RT_GROUP_MEMBER

RT_MATRIX

RT_SAMPLE_TYPE