¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 9060295 |
| Summary | Is this subcontract resource missing in the subcontract list? |
| Error message | Subcontract EOC task_id (EOC = subcontract) missing in DS13.task_ID list. |
¶ 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 "Subcontract Resource Missing in SubK List" is designed to ensure that all subcontract resources listed in the DS06 Resources table are also present in the DS13 Subcontract list.
The test is performed by comparing the 'task_ID' field in both tables. If a 'task_ID' associated with a subcontract resource ('EOC' field value is 'subcontract') in the DS06 Resources table is not found in the DS13 Subcontract list, the DIQ check will flag this as an error.
The likely cause of this error is a missing or incorrect entry in the DS13 Subcontract list. It is important to ensure that all subcontract resources in the DS06 Resources table have corresponding entries in the DS13 Subcontract list. The expected value for the 'task_ID' field in the DS13 Subcontract list should match the 'task_ID' of the subcontract resource in the DS06 Resources table.
Please note that this DIQ check will only run if there is data present in the DS13 Subcontract list. If the DS13 Subcontract list is empty, the DIQ check will not run.
¶ Why do we check this?
This test is being performed to ensure that all subcontract resources are properly listed in the subcontract list. The test is checking the 'DS06 Resources' table to see if there are any subcontract resources that are not included in the 'DS13.task_ID' list.
The importance of this check is to maintain data integrity and consistency across different tables. If a subcontract resource is missing from the subcontract list, it could lead to inaccuracies in project management, such as incorrect resource allocation, budgeting errors, or scheduling issues.
The severity of this test is marked as 'MAJOR', which means that while it may not immediately prevent the data from being reviewed, it is likely to cause problems during analysis if not addressed. It is crucial to ensure that all subcontract resources are accurately recorded and tracked to ensure effective project management.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsSubKTaskMissingInDS13] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with SubKs as (
SELECT task_ID
FROM DS13_subK
WHERE upload_ID = @upload_ID
)
SELECT
*
FROM
DS06_schedule_resources
WHERE
upload_id = @upload_ID
AND EOC = 'subcontract'
AND task_ID NOT IN (SELECT task_ID FROM SubKs)
AND (SELECT COUNT(*) FROM SubKs) > 0 --run only if data exists in DS13
)