¶ Basic Information
| Key | Value |
|---|---|
| Table | DS14 HDV-CI |
| Severity | MAJOR |
| Unique ID | 9140542 |
| Summary | Is the equipment for this HDV-CI missing accompanying forecast subcontract or material resources? |
| Error message | equipment_ID found where subK_ID not in DS13.subK_ID list with DS06 FC 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 (FC)" is designed to identify any equipment in the DS14 HDV-CI table that does not have associated forecast 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 DS06 FC resources of EOC (End of Contract) type 'Material' or 'Subcontract'.
In simpler terms, this means that there is equipment listed in the DS14 HDV-CI table that does not have the necessary forecasted resources (either subcontract or material) listed in the DS06 schedule resources table.
The fields causing the issue are the 'equipment_ID' field in the DS14 HDV-CI table and the 'subK_ID' field in the DS13 subK table. The expected values for these fields would be matching entries in both tables, with the DS06 schedule resources table showing 'FC' schedule type and 'Material' or 'Subcontract' as the EOC type for the corresponding task_ID.
To resolve this issue, ensure that each equipment_ID in the DS14 HDV-CI table has a corresponding subK_ID in the DS13 subK table, and that the DS06 schedule resources table lists the necessary forecasted resources for each task.
¶ 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 necessary forecast subcontract or material resources. The test checks if there are any equipment IDs that do not have a corresponding subcontract ID in the 'DS13' table, and if these equipment IDs are associated with forecast resources of EOC (End of Contract) that are either material or subcontract.
The importance of this check is to ensure that all equipment used in the EVMS construction project is properly accounted for in terms of necessary resources. If equipment is not associated with the correct subcontract or material resources, it could lead to inaccuracies in project planning, budgeting, and execution. This could potentially cause problems during the analysis of the project data, hence the severity of this check is marked as 'MAJOR'. It is crucial to rectify this issue to maintain the integrity and quality of the project data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS14_HDV_CI_IsEquipMissingDS06SubKOrMatEOCFC] (
@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 = 'FC' 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)
)