| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | CRITICAL |
| Unique ID | 1030106 |
| Summary | Is the WP or PP found across multiple Control Accounts? |
| Error message | WBS_ID_WP found across distinct WBS_ID_CA. |
The Data Integrity and Quality (DIQ) check titled "WP or PP Found Across Multiple CAs" is designed to ensure that each Work Package (WP) or Planning Package (PP) is associated with only one Control Account (CA) in the DS03 Cost table.
The test identifies instances where the same WP or PP is found across multiple CAs, which is not expected in the standard data structure. This is indicated by the message "WBS_ID_WP found across distinct WBS_ID_CA."
The fields involved in this check are WBS_ID_CA and WBS_ID_WP. WBS_ID_CA represents the Control Account, and WBS_ID_WP represents the Work Package or Planning Package.
If the test identifies an issue, it means that a WP or PP (WBS_ID_WP) is associated with more than one CA (WBS_ID_CA). This could be due to a data entry error or a problem with the data import process.
To resolve this issue, each WP or PP should be reviewed to ensure it is associated with the correct CA. The expected value for each WP or PP is a single, unique CA.
This test is being performed to ensure that each Work Package (WP) or Planning Package (PP) is not found across multiple Control Accounts (CAs) in the DS03 Cost table. The test is checking for instances where the same WBS ID for a WP or PP (WBS_ID_WP) is found across different CAs (WBS_ID_CA).
The importance of this check is to maintain the integrity of the project's cost structure. In the context of EVMS, each Control Account should have unique Work Packages or Planning Packages associated with it. If a WP or PP is found across multiple CAs, it could lead to confusion, double counting of costs, and inaccuracies in project cost management and reporting.
The severity of this test is marked as 'CRITICAL', which is the highest level of severity. This means that if this issue is found, it must be corrected before the data can be further reviewed or used. This underlines the critical importance of maintaining clear and unique associations between Control Accounts and their respective Work Packages or Planning Packages.
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsWPorPPRepeatedAcrossCAs] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with RepeatWPs as (
SELECT C1.WBS_ID_CA, C1.WBS_ID_WP
FROM DS03_cost C1 INNER JOIN DS03_cost C2 ON C1.WBS_ID_WP = C2.WBS_ID_WP
AND C1.WBS_ID_CA <> C2.WBS_ID_CA
WHERE TRIM(ISNULL(C1.WBS_ID_WP,'')) <> ''
AND C1.upload_id = @upload_ID
AND C2.upload_ID = @upload_ID
group by
C1.WBS_ID_WP, C1.WBS_ID_CA
)
SELECT
C.*
FROM
DS03_Cost C INNER JOIN RepeatWPs R ON R.WBS_ID_CA = C.WBS_ID_CA
AND R.WBS_ID_WP = C.WBS_ID_WP
WHERE
C.upload_ID = @upload_ID
)