<< Click to Display Table of Contents >> Navigation:  Geotech > Functions > Geotechnical Function – EQUIS.GEOTECH_LAB_ASSIGN_SAMPLES

Purpose

 

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.

 

 

 

Function Details

 

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:

•FACILITY_ID

•SAMPLE_ID

•SYS_LOC_CODE

•START_DEPTH

•SYS_SAMPLE_CODE

•TASK_CODE

•SAMPLE_NAME

•TEST_CODE

•TEST_NAME

•LAB_CODE

•DEFAULT_FEE

•OVERRIDE_FEE

•LAB_NAME

•LAB_SOURCE

•APPROVED_YN

•REMARK

 

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.

 

 

 

The APPROVED_YN field is populated from DT_GEO_LAB_ASSIGN_TEST.APPROVAL_CODE.