¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 9040214 |
| Summary | Is this SVT or ZBA resource loaded? |
| Error message | SVT or ZBA (subtype = SVT or ZBA) task_id found in DS06 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 "Resource Loaded SVT or ZBA" is designed to verify whether the tasks in the DS04 Schedule table that are marked as SVT or ZBA are resource loaded. This check is important to ensure that all tasks have the necessary resources assigned to them for successful completion.
The check operates by comparing the task IDs in the DS04 Schedule table with those in the DS06 Schedule Resources table. If a task ID from the DS04 Schedule table, which is marked as SVT or ZBA, is found in the DS06 Schedule Resources table, it indicates that the task is resource loaded.
If an error is flagged by this check, it is likely due to one of two reasons:
-
A task marked as SVT or ZBA in the DS04 Schedule table does not have a corresponding entry in the DS06 Schedule Resources table. This would mean that the task is not resource loaded, which is a requirement for tasks of these types.
-
The 'subtype' field in the DS04 Schedule table for a task marked as SVT or ZBA is empty or contains invalid data. The 'subtype' field should always contain a valid subtype for tasks of these types.
To resolve these issues, ensure that all tasks marked as SVT or ZBA in the DS04 Schedule table have corresponding entries in the DS06 Schedule Resources table and that the 'subtype' field for these tasks contains valid data.
¶ Why do we check this?
This test is being performed to check if the tasks identified as SVT or ZBA in the 'DS04 Schedule' table are resource loaded. The test is looking for any instances where the task_id for SVT or ZBA tasks are found in the DS06 task_ID list.
The importance of this check is to ensure that all tasks, including SVT or ZBA tasks, are properly resource loaded. Resource loading is a critical aspect of project management as it helps in planning, scheduling, and budgeting the project. If a task is not resource loaded, it could lead to inaccurate project timelines, cost overruns, and inefficient resource allocation.
The severity of this check is marked as 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. If SVT or ZBA tasks are not resource loaded, it could lead to inaccurate analysis of the project's progress and performance. Therefore, it is important to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsSVTOrZBAResourceLoaded] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Resources as (
SELECT schedule_type, task_ID, ISNULL(subproject_ID,'') SubP
FROM DS06_schedule_resources
WHERE upload_ID = @upload_ID
)
SELECT
S.*
FROM
DS04_schedule S INNER JOIN Resources R ON S.schedule_type = R.schedule_type
AND S.task_ID = R.task_ID
AND ISNULL(S.subproject_ID,'') = R.SubP
WHERE
upload_id = @upload_ID
AND subtype IN ('SVT', 'ZBA')
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | 1. Logic adjusted to account for the addition of subproject_id field. |
| 2. Minor filter adjustment for 'SVT' or 'ZBA' records |