¶ Basic Information
| Key | Value |
|---|---|
| Table | DS14 HDV-CI |
| Severity | MAJOR |
| Unique ID | 9140541 |
| Summary | Is the equipment for this HDV-CI missing accompanying baseline subcontract or material resources? |
| Error message | equipment_ID found where subK_ID not in DS13.subK_ID list with DS06 BL resources of EOC = material or subcontract (by DS14.subK_ID & DS13.subK_ID, and DS13.task_ID & DS06.task_ID). |
¶ What causes this error?
The following text was generated by an AI tool and hasn't been reviewed for accuracy by a human! It might be useful, but it also might have errors. Are you a human? You can help by reviewing it for accuracy! Edit it as needed then remove this message.
The Data Integrity and Quality (DIQ) check titled "Equipment Without Subcontract or Material Resources (BL)" is designed to identify any equipment in the DS14 HDV-CI table that does not have associated baseline subcontract or material resources.
The error is likely to occur when an equipment_ID is found in the DS14 HDV-CI table, but the corresponding subK_ID is not found in the DS13_subK table with associated baseline resources of type 'Material' or 'Subcontract' in the DS06_schedule_resources table.
The fields causing the issue are the 'equipment_ID' in the DS14 HDV-CI table and the 'subK_ID' in both the DS14 HDV-CI and DS13_subK tables. The 'task_ID' in the DS13_subK and DS06_schedule_resources tables is also involved in the check.
The expected values for these fields are that for every 'equipment_ID' in the DS14 HDV-CI table, there should be a corresponding 'subK_ID' in the DS13_subK table. This 'subK_ID' should also be associated with baseline resources of type 'Material' or 'Subcontract' in the DS06_schedule_resources table.
If these conditions are not met, the DIQ check will flag the data as potentially erroneous. This could indicate missing or incorrectly linked data between the tables.
¶ Why do we check this?
This test is being performed to ensure that all equipment listed in the 'DS14 HDV-CI' table is associated with the appropriate subcontract or material resources in the baseline. The test checks if there are any equipment IDs that do not have a corresponding subcontract ID in the 'DS13' table, or if they are not associated with any baseline resources of EOC (End of Contract) type 'material' or 'subcontract'.
The importance of this check is to maintain the integrity and accuracy of the project management data. It ensures that all equipment used in the project is properly accounted for in terms of its associated costs and resources. This is crucial for accurate budgeting, cost tracking, and overall project management.
The severity of this check is marked as 'MAJOR', which means that while it may not prevent the data from being reviewed, it could potentially cause problems during the analysis. For instance, if equipment is not properly linked to its associated resources, it could lead to inaccurate cost estimations or resource allocation. Therefore, it is recommended to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS14_HDV_CI_IsEquipMissingDS06SubKOrMatEOCBL] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with SubKResources as (
--list of subcontract IDs with material and subcontract resources.
SELECT S.subK_ID
FROM DS13_subK S INNER JOIN DS06_schedule_resources R ON S.task_ID = R.task_ID
WHERE S.upload_ID = @upload_ID AND R.task_ID = @upload_ID
AND R.schedule_type = 'BL' AND R.EOC IN ('Material','Subcontract')
GROUP BY S.subK_ID
)
SELECT
*
FROM
DS14_HDV_CI H
WHERE
upload_ID = @upload_ID
AND TRIM(equipment_ID) <> ''
AND subK_ID NOT IN (SELECT subK_ID FROM SubKResources)
)