| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | CRITICAL |
| Unique ID | 1030072 |
| Summary | Does this WP, PP, or SLPP have more than one EVT group? |
| Error message | WP, PP, or SLPP where EVT group is not uniform (EVTs are not all LOE, Discrete, Apportioned, or Planning Package for this WP, PP, or SLPP data). |
The Data Integrity and Quality (DIQ) check titled "WP or PP with Multiple EVT Groups" is designed to ensure that each Work Package (WP), Planning Package (PP), and Summary Level Planning Package (SLPP) in the DS03 Cost table is associated with a single, uniform EVT group.
EVT groups are categorized as 'Discrete', 'LOE' (Level of Effort), 'Apportioned', or 'PP' (Planning Package). The DIQ check identifies any WP or PP where the EVTs within a given WP, PP, or SLPP are not of the same group.
The fields involved in this check are 'WBS_ID_CA', 'WBS_ID_WP', and 'EVT'. The 'WBS_ID_WP' field identifies the WP or PP, and the 'EVT' field indicates the earned value technique. When the 'WBS_ID_WP' field is blank and the 'WBS_ID_CA' is identified in DS01 type as an SLPP, then the row is considered an SLPP.
If the DIQ check fails, it means that a WP, PP, or SLPP has been associated with more than one EVT group. This could be due to a data entry error or a misunderstanding of EVT categorization.
To resolve this issue, review the EVT assignments for each WP, PP, or SLPP in the DS03 Cost table. Ensure that each WP or PP is associated with a single, uniform EVT group.
This test is being performed on the 'DS03 Cost' table to check for WPs, PPs, or SLPPs that have more than one Earned Value Technique (EVT) group. The test is checking for uniformity in the EVT group, meaning all EVTs should be either LOE (Level of Effort), Discrete, Apportioned, or Planning Package for each particular WP, PP, or SLPP.
The severity of this test is marked as 'CRITICAL', which is the highest level of severity. This means that if this issue is not resolved, the data cannot be reviewed or analyzed further.
The importance of this check lies in ensuring the consistency and accuracy of the data. WPs support accurate performance measurement by assigning the appropriate EVT. EVTs are to be consistent with how the resource budgets (all elements of cost) are planned to be performed and progress measured. Having multiple EVT groups for a single WP, PP, or SLPP can lead to confusion, inaccuracies, and distortion in measuring performance and lead to incorrect decision-making. Therefore, it is crucial to fix this error to maintain the integrity and quality of the project management data.
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_AreWPOrPPEVTsComingled] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with EVTGroups as (
-- WP WBS & EOC by EVTs by group
SELECT
WBS_ID_CA,
ISNULL(WBS_ID_WP,'') as WBS_ID_WP,
CASE
WHEN EVT IN ('B', 'C', 'D', 'E', 'F', 'G', 'H', 'L', 'N', 'O', 'P') THEN 'Discrete'
WHEN EVT = 'A' THEN 'LOE'
WHEN EVT IN ('J', 'M') THEN 'Apportioned'
WHEN EVT = 'K' THEN 'PP'
ELSE ''
END as EVT
FROM
DS03_cost
WHERE
upload_ID = @upload_ID AND TRIM(ISNULL(EVT,'')) <> ''
), Flags AS (
SELECT G1.WBS_ID_CA, G1.WBS_ID_WP
FROM EVTGroups G1 INNER JOIN EVTGroups G2 ON G1.WBS_ID_CA = G2.WBS_ID_CA
AND G1.WBS_ID_WP = G2.WBS_ID_WP
AND ISNULL(G1.EVT,'') <> ISNULL(G2.EVT,'')
GROUP BY G1.WBS_ID_CA, G1.WBS_ID_WP
)
SELECT
C.*
FROM
DS03_Cost C INNER JOIN Flags F ON C.WBS_ID_CA = F.WBS_ID_CA AND ISNULL(C.WBS_ID_WP,'') = F.WBS_ID_WP
WHERE
upload_ID = @upload_ID AND AND (ISNULL(C.WBS_ID_WP,'') <> '' OR C.WBS_ID_CA IN (SELECT WBS_ID FROM DS01_WBS WHERE upload_ID = @upload_ID AND type = 'SLPP') )
)
| Date | Description of Changes |
|---|---|
| 2024-10-03 | Logic updated to include SLPPs in the test (remove criteria: ISNULL(WBS_ID_WP,'') <> '') |