¶ Basic Information
| Key | Value |
|---|---|
| Table | DS01 WBS |
| Severity | MAJOR |
| Unique ID | 9010013 |
| Summary | Is this WP or PP WBS ID missing in cost? |
| Error message | WBS_ID where type = WP or PP missing in DS03.WBS_ID_WP 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 "WP or PP Missing from Cost" is designed to identify any instances where a Work Breakdown Structure (WBS) ID of type 'WP' (Work Package) or 'PP' (Planning Package) in the DS01 WBS table is not found in the DS03 Cost table. This check is particularly important when there are budgeted costs, hours, or full-time equivalents (FTEs) associated with the WBS ID in the DS03 Cost table.
If this DIQ check fails, it indicates that there is a WBS ID of type 'WP' or 'PP' in the DS01 WBS table that does not have a corresponding entry in the DS03 Cost table. This could be due to a data entry error, a missing record, or a mismatch between the WBS IDs in the two tables.
The expected values for the fields in question are as follows:
- In the DS01 WBS table, the 'type' field should contain either 'WP' or 'PP'.
- In the DS03 Cost table, the 'WBS_ID_WP' field should contain the same WBS ID as in the DS01 WBS table. Additionally, at least one of the 'BCWSi_dollars', 'BCWSi_FTEs', or 'BCWSi_hours' fields should be greater than 0.
To resolve this issue, ensure that all WBS IDs of type 'WP' or 'PP' in the DS01 WBS table have a corresponding entry in the DS03 Cost table, and that there are budgeted costs, hours, or FTEs associated with these WBS IDs.
¶ Why do we check this?
This test is being performed to ensure that all Work Packages (WP) or Planning Packages (PP) that have associated costs (indicated by BCWSi_Dollars, Hours, or FTEs > 0) are properly referenced in the DS03.WBS_ID_WP list. The test is checking for any instances where a WP or PP WBS ID is missing from this list, which could indicate a data integrity issue.
The importance of this check lies in the need for accurate cost tracking and allocation in project management. If a WP or PP with associated costs is not properly referenced, it could lead to inaccuracies in cost reporting and analysis, potentially impacting project budgeting and financial decision-making.
The severity level of this test is 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. Therefore, it is crucial to address this issue to ensure accurate and reliable project cost management.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS01_WBS_IsWBSMissingInDS03WP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
WITH DS03WBS AS (
SELECT DISTINCT WBS_ID_WP WBS
FROM DS03_cost
WHERE upload_ID = @upload_ID AND (BCWSi_dollars > 0 OR BCWSi_FTEs > 0 OR BCWSi_hours > 0)
)
SELECT W.*
FROM DS01_WBS AS W LEFT JOIN DS03WBS AS C ON W.WBS_ID = C.WBS
WHERE W.upload_ID = @upload_ID
AND W.type in ('WP', 'PP')
AND W.[external] = 'N'
AND C.WBS IS NULL
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to exclude external WBS, as well as for minor optimization. |