The EQUIS.GEOTECH_LAB_ASSIGN_SAMPLES provides information on the test method, sample, lab test fee, and approval of an assigned lab test. The purpose of this function is to provide a summary of the lab tests assigned to samples within a facility. This function creates a function report called Geotechnical Lab Test Assignments.
Note: This function only supports a single select TASK_CODE input. The function filters all samples by the task selected.
The data produced in this report are gathered from the DT_SAMPLE, DT_GEO_LAB_ASSIGN, DT_GEO_LAB_ASSIGN_TEST, RT_GEO_TEST_METHOD and DT_COST tables.
For this function to properly run, the function requires the assigned lab test(s) (TEST_METHOD_CODE) to have an associated record in the DT_COST table where DT_COST.COST_TYPE = ‘geolab_corporate_fees’. The DT_COST record must have a DT_COST.START_DATE that is equal to or later than the DT_TASK.START_DATE.
The function returns the following fields:
The sample information (SAMPLE_ID, SYS_LOC_CODE, START_DEPTH, SYS_SAMPLE_CODE, SAMPLE_NAME) is gathered from the DT_SAMPLE table where DT_SAMPLE.SAMPLE_ID = DT_GEO_LAB_ASSIGN_TEST.SAMPLE_ID.
The lab test information (TEST_CODE, TEST_NAME) is gathered from the DT_GEO_LAB_ASSIGN_TEST and RT_GEO_TEST_METHOD tables. The TEST_CODE is populated from DT_GEO_LAB_ASSIGN_TEST.TEST_METHOD_CODE, which is a lookup to the RT_GEO_TEST_METHOD table. The TEST_NAME value is populated from RT_GEO_TEST_METHOD.TEST_METHOD_NAME associated with DT_GEO_LAB_ASSIGN_TEST.TEST_METHOD_CODE = RT_GEO_TEST_METHOD.TEST_METHOD_CODE.
The lab information (LAB_CODE, LAB_SOURCE, LAB_NAME) is gathered from the DT_GEO_LAB_ASSIGN and RT_COMPANY tables. The LAB_CODE is based on the associated DT_GEO_LAB_ASSIGN.LAB_CODE where DT_GEO_LAB_ASSIGN.GEO_LAB_ASSIGN_ID = DT_GEO_LAB_ASSIGN_TEST.GEO_LAB_ASSIGN_ID.
The LAB_SOURCE is based on the associated DT_GEO_LAB_ASSIGN.LAB_SOURCE where DT_GEO_LAB_ASSIGN.GEO_LAB_ASSIGN_ID = DT_GEO_LAB_ASSIGN_TEST.GEO_LAB_ASSIGN_ID.
The LAB_NAME is based on the RT_COMPANY.COMPANY_NAME associated with the RT_COMPANY.COMPANY_CODE = DT_GEO_LAB_ASSIGN.LAB_CODE.
The lab test fees are based on if the fee is associated to a project, office, or corporate fee. The project fee takes precedence over the office and corporate fee associated with a test method. To determine the associated fees for a test method, the function queries the data in the following order:
1.The function searches the DT_COST table where DT_COST.COST_TYPE = ‘geolab_project_fees’ and DT_COST.FACILITY_ID = the selected FACILITY_ID and DT_COST.START_DATE is equal to or later than the DT_TASK.START_DATE. If a record is present for the test method, then this fee value is populated into the OVERRIDE_FEE field.
2.The function searches the DT_COST table where DT_COST.COST_TYPE = ‘geolab_office_fees’ and DT_COST.COMPANY_CODE = the selected AT_FACILITY.COMPANY_CODE (where AT_FACILITY.ASSOCIATION = ‘office_designation’ and AT_FACILITY.FACILITY_ID = DT_COST.FACILITY_ID) and DT_COST.START_DATE is equal to or later than the DT_TASK.START_DATE. If a record is present for the test method, then this fee value is populated into the DEFAULT_FEE field.
3.The function searches the DT_COST table where DT_COST.COST_TYPE = ‘geolab_corporate_fees’ and DT_COST.START_DATE is equal to or later than the DT_TASK.START_DATE. If a record is present for the test method and Step 2 does not return anything, then this fee value is populated into the DEFAULT_FEE field.
Note: Test methods and their associated costs are related by DT_COST.OBJECT_EUID = RT_GEO_TEST_METHOD.EUID.
The APPROVED_YN field is populated from DT_GEO_LAB_ASSIGN_TEST.APPROVAL_CODE.
Copyright © 2023 EarthSoft, Inc. • Modified: 30 Aug 2023