¶ Basic Information
| Key | Value |
|---|---|
| Table | DS01 WBS |
| Severity | MAJOR |
| Unique ID | 9010014 |
| Summary | Is this CA or SLPP WBS ID missing in cost? |
| Error message | WBS_ID where type = CA or SLPP missing in DS03.WBS_ID_CA list (where BCWSi_Dollars, Hours, or FTEs > 0). |
¶ 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 "CA or SLPP Missing from Cost" is designed to ensure that all Work Breakdown Structure (WBS) IDs of type 'CA' or 'SLPP' in the DS01 WBS table are also present in the DS03 cost table.
The error message "WBS_ID where type = CA or SLPP missing in DS03.WBS_ID_CA list (where BCWSi_Dollars, Hours, or FTEs > 0)" indicates that there are WBS IDs of type 'CA' or 'SLPP' in the DS01 WBS table that are not found in the DS03 cost table. This discrepancy could be due to missing or incorrect entries in the DS03 cost table.
The fields causing this issue are the 'WBS_ID' field in the DS01 WBS table and the 'WBS_ID_CA' field in the DS03 cost table. The expected values for these fields are identical WBS IDs for corresponding entries in both tables.
Please ensure that all WBS IDs of type 'CA' or 'SLPP' in the DS01 WBS table have corresponding entries in the DS03 cost table, especially if the BCWSi_Dollars, BCWSi_Hours, or BCWSi_FTEs values are greater than zero. This will ensure the integrity and quality of the project management data.
¶ Why do we check this?
This test is being performed to ensure that all Work Breakdown Structure (WBS) IDs of type 'CA' or 'SLPP' are included in the DS03.WBS_ID_CA list, specifically those with positive values for BCWSi_Dollars, Hours, or FTEs. The test is crucial because missing WBS IDs could lead to incomplete or inaccurate cost analysis, which could in turn affect project management decisions and the overall success of the project.
The severity of this check is marked as a MAJOR, which means that while it may not immediately prevent the data from being reviewed, it is likely to cause problems during analysis. This could include issues such as skewed results, inaccurate projections, or misinformed decisions. Therefore, it is important to address this issue to ensure the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS01_WBS_IsWBSMissingInDS03CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
WITH DS03 AS (
SELECT DISTINCT WBS_ID_CA
FROM DS03_cost
WHERE upload_ID = @upload_ID AND (BCWSi_dollars > 0 OR BCWSi_FTEs > 0 OR BCWSi_hours > 0)
)
SELECT DS01.*
FROM DS01_WBS AS DS01 LEFT JOIN DS03 ON DS01.WBS_ID = DS03.WBS_ID_CA
WHERE DS01.upload_ID = @upload_ID
AND DS01.type in ('CA', 'SLPP')
AND DS01.[external] = 'N'
AND DS03.WBS_ID_CA IS NULL
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to exclude external WBS, as well as for minor optimization. |