¶ Basic Information
| Key | Value |
|---|---|
| Table | DS01 WBS |
| Severity | MINOR |
| Unique ID | 9010001 |
| Summary | Is this WP or PP missing in the WBS EU log? |
| Error message | WBS_ID not in DS17.WBS_ID list where DS01.type = WP or PP. |
¶ 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 "WP or PP Missing EU" is designed to identify any Work Packages (WP) or Planning Packages (PP) in the DS01 WBS table that are missing in the WBS EU log (DS17).
The error message "WBS_ID not in DS17.WBS_ID list where DS01.type = WP or PP" indicates that there are instances where the Work Breakdown Structure ID (WBS_ID) from the DS01 WBS table, specifically those with a type of WP or PP, are not found in the DS17 WBS EU log.
This discrepancy could be due to a variety of reasons such as missing entries, incorrect entries, or data synchronization issues between the DS01 WBS and DS17 WBS EU tables.
The expected values for the 'type' field in the DS01 WBS table are 'WP' or 'PP'. The WBS_ID in the DS01 WBS table should match with the WBS_ID in the DS17 WBS EU log.
Please note that this check does not apply to WBS_IDs that are only associated with the 'SVT' subtype in the DS04 schedule table. The check is only performed if the DS17 WBS EU log contains data.
In case of an error, please review the data entries in the DS01 WBS and DS17 WBS EU tables for any inconsistencies or missing information.
¶ Why do we check this?
This test is being performed to check if there are any Work Packages (WP) or Planning Packages (PP) that are missing in the Work Breakdown Structure (WBS) Earned Value (EU) log. The test is checking if the WBS_ID from the DS01 WBS table is present in the DS17.WBS_ID list where the type is either WP or PP.
The importance of this check is to ensure that all work packages and planning packages are properly logged and accounted for in the WBS EU log. This is crucial for accurate project management and tracking of project progress. If any WP or PP is missing, it could lead to inaccurate project status reporting and could potentially impact project timelines and budget.
The severity of this check is marked as an MINOR. This means that while it may not immediately prevent data from being reviewed, it could potentially cause minor problems or indicate that the data does not follow all best practices. It is a signal to review the data and correct any discrepancies to ensure accurate and efficient project management.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS01_WBS_IsWPOrPPMissingInDS17] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with WBSEU as (
SELECT WBS_ID
FROM DS17_WBS_EU
WHERE upload_ID = @upload_ID
), BLTasks as (
SELECT WBS_ID, ISNULL(subtype,'') Subtype
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND schedule_type = 'BL'
GROUP BY WBS_ID, subtype
), SVTWBSs as (
-- get WBSs that are only SVT
SELECT WBS_ID
FROM BLTasks
GROUP BY WBS_ID
HAVING MIN(Subtype) = MAX(Subtype) AND MIN(Subtype) = 'SVT'
)
SELECT
*
FROM
DS01_WBS
WHERE
upload_ID = @upload_ID
AND type in ('PP','WP')
AND WBS_ID NOT IN (SELECT WBS_ID FROM WBSEU)
AND WBS_ID NOT IN (SELECT WBS_ID FROM SVTWBSs) --ignore SVT WBSs
AND (SELECT COUNT(*) FROM WBSEU) > 0 -- run only if DS17 has data
)