¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 9040223 |
| Summary | Is this WBS ID missing in Cost? |
| Error message | WBS_ID is not in cost (DS03.WBS_ID). |
¶ 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 "WBS Missing in Cost" is designed to verify the consistency of data between the DS04 Schedule and DS03 Cost tables. Specifically, it checks whether there are any Work Breakdown Structure (WBS) IDs in the DS04 Schedule table that are not present in the DS03 Cost table.
If this DIQ check fails, it indicates that there are WBS IDs in the DS04 Schedule table that do not have corresponding entries in the DS03 Cost table. This discrepancy could be due to a variety of reasons such as data entry errors, missing data, or inconsistencies in data management practices.
The fields causing the issue are the WBS_ID field in the DS04 Schedule table and the WBS_ID_WP field in the DS03 Cost table. The expected values for these fields are identical WBS IDs in both tables, ensuring that each WBS ID in the schedule has a corresponding cost entry.
To resolve this issue, you should review the data entry process for both tables and ensure that each WBS ID entered in the DS04 Schedule table has a corresponding entry in the DS03 Cost table.
¶ Why do we check this?
This test is being performed to check if there are any Work Breakdown Structure (WBS) IDs missing in the cost data. The Work Breakdown Structure is a key project deliverable that organizes the team's work into manageable sections. Each WBS ID corresponds to a specific task or set of tasks within the project. If a WBS ID is missing in the cost data, it means that the cost associated with that particular task or set of tasks is not being accounted for, which could lead to inaccuracies in the overall project cost estimation.
The severity of this check is marked as a MAJOR. This implies that while the issue may not immediately prevent the data from being reviewed, it is likely to cause problems during the analysis. For instance, it could lead to underestimation of the project cost, incorrect allocation of resources, or misinformed decision-making. Therefore, it is important to address this issue to ensure the integrity and quality of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsWBSMissingInDS03] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
WITH Cost_WBS AS (
SELECT WBS_ID_CA 'CA', TRIM(ISNULL(WBS_ID_WP,'')) 'WP'
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_CA, WBS_ID_WP
)
SELECT S.*
FROM DS04_schedule AS S
WHERE S.upload_id = @upload_ID
AND TRIM(ISNULL(S.subtype,'')) = ''
AND WBS_ID NOT IN (SELECT WP FROM Cost_WBS)
AND (EVT = 'K' AND WBS_ID NOT IN (SELECT CA FROM Cost_WBS))
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to filter out records from 'DS04_schedule' that have a matching 'WBS_ID' in 'DS03_cost', using a new method. Previously, it directly excluded 'WBS_ID' present in 'DS03_cost'. Now, it creates a temporary list of distinct 'WBS_ID_WP' from 'DS03_cost' and then performs a left join to exclude those 'WBS_ID'. |
| 2024-09-09 | Logic adjusted to exclude SLPPs if found in DS03.WBS_ID_CA |