| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 1040114 |
| Summary | Does this WBS comingle SVT & Non-SVT tasks? |
| Error message | WBS has SVT tasks & Non-SVT tasks (subtype = SVT & subtype >< SVT by WBS_ID). |
The Data Integrity and Quality (DIQ) check titled "Comingling of SVT & Non-SVT" is designed to identify any instances in the DS04 Schedule where Work Breakdown Structure (WBS) tasks are improperly mixed, specifically where SVT (Schedule Visibility) tasks and Non-SVT tasks are comingled under the same WBS ID and schedule type.
The error message "WBS has SVT tasks & Non-SVT tasks (subtype = SVT & subtype >< SVT by WBS_ID)" indicates that the DIQ check has found a WBS ID that contains both SVT and Non-SVT tasks. This is not expected as each WBS ID should only contain either SVT tasks or Non-SVT tasks, but not both.
The fields causing this issue are the 'subtype' field, which should only contain 'SVT' for SVT tasks and any other value (or no value) for Non-SVT tasks, and the 'WBS_ID' field, which should uniquely identify a set of tasks that are all either SVT or Non-SVT.
To resolve this issue, ensure that each WBS ID in the DS04 Schedule only contains tasks of the same subtype, either all SVT or all Non-SVT.
This test is being performed to check if there is a mix of SVT and Non-SVT tasks within the same Work Breakdown Structure (WBS). The test is checking for instances where the subtype of tasks is both SVT and Non-SVT under the same WBS_ID.
The severity of this test is marked as a MAJOR, which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis if not addressed. It is crucial to maintain the integrity of the WBS and the categorization of tasks within it for accurate project management and reporting.
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_AreSVTsAndNonSVTsComingled] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with NonSVT as (
SELECT WBS_ID, schedule_type
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND ISNULL(subtype,'') <> 'SVT'
GROUP BY WBS_ID, schedule_type
), ToFlag as (
SELECT S.WBS_ID, S.schedule_type
FROM
DS04_schedule S LEFT JOIN NonSVT N ON S.schedule_type = N.schedule_type
AND S.WBS_ID = N.WBS_ID
WHERE
S.upload_ID = @upload_ID
AND ISNULL(S.subtype,'') = 'SVT'
AND N.WBS_ID IS NOT NULL
)
SELECT
S.*
FROM
DS04_schedule S INNER JOIN ToFlag F ON S.WBS_ID = F.WBS_ID
AND S.schedule_type = F.schedule_type
WHERE
upload_id = @upload_ID
)