| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | CRITICAL |
| Unique ID | 9030078 |
| Summary | Is the WBS ID to which this work is apportioned mismatched in cost and schedule? |
| Error message | EVT = J or M where EVT_J_to_WBS_ID does not equal the WBS ID in Schedule. |
The Data Integrity and Quality (DIQ) check titled "Apportionment IDs Mismatch Between Cost and Schedule" is designed to identify any discrepancies between the Work Breakdown Structure (WBS) ID in the DS03 Cost table and the DS04 Schedule table for the apportioned WBS.
This check applies when the Earned Value Technique (EVT) is either J or M. In these cases, the DS03 WBS ID apportioned to (EVT_J_to_WBS_ID) should match the WBS ID of the task apportioned to (EVT_J_to_task_ID) in the DS04 Schedule table. If there is a mismatch, it indicates a potential data integrity and integration issue. If the task apportioned to (EVT_J_to_task_ID) is NULL in the DS04 Schedule table, it means that the task is not apportioned to anything, which could also trigger this DIQ check.
The error is likely to be caused by incorrect data entry or data transfer between the DS03 Cost and DS04 Schedule tables. The fields causing the issue are the EVT, EVT_J_to_WBS_ID in the DS03 Cost table and the EVT_J_to_task_ID and associated WBS_ID in the DS04 Schedule table.
For EVT values of J or M the EVT_J_to_WBS_ID in the DS03 Cost table should match the WBS ID for the EVT_J_to_task_ID in the DS04 Schedule table.
To resolve this issue, ensure that the WBS IDs match between the DS03 Cost and DS04 Schedule tables for all tasks with EVT J or M. If a task is not apportioned to anything, the apportioned to fields (DS03 EVT_J_to_WBS_ID abd DS04 EVT_J_to_task_ID) should be NULL in both tables.
This test is being performed to ensure that the Work Breakdown Structure (WBS) ID, to which the work is apportioned, is consistent between the cost and schedule data. The test checks if the Earned Value Technique (EVT) is either J or M, and if so, it verifies that the WBS ID apportioned to in the cost data associated with this EVT matches the WBS ID apportioned to in the schedule data.
The importance of this check lies in maintaining data integrity and ensuring accurate project management. The WBS ID is a critical element in project management as it helps in organizing, defining, and planning the work scope of the project. If there is a mismatch between the WBS IDs in the cost and schedule data, it could lead to incorrect project tracking, budgeting, and scheduling, which could ultimately impact the successful completion of the project.
The severity of this test is marked as 'CRITICAL', which is the highest level of severity. This means that if there is a mismatch, it is a critical issue that must be fixed immediately. If not corrected, the data cannot be reviewed or used for further analysis, which could significantly hinder project management and decision-making processes.
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsApportionedWBSIDMismatchedWithDS04] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with ScheduleApportioned AS (
SELECT S1.WBS_ID WBSID, S2.WBS_ID ApportionedToWBSId
FROM DS04_schedule S1 INNER JOIN DS04_schedule S2 ON S1.EVT_J_to_task_ID = S2.task_ID
WHERE
S1.upload_ID = @upload_ID
AND S2.upload_ID = @upload_ID
AND S1.schedule_type = 'FC'
AND S2.schedule_type = 'FC'
AND S1.EVT_J_to_task_ID IS NOT NULL
)
SELECT
C.*
FROM
DS03_Cost C LEFT OUTER JOIN ScheduleApportioned S ON C.WBS_ID_WP = S.WBSID
WHERE
upload_ID = @upload_ID
AND EVT IN ('J','M')
AND C.EVT_J_to_WBS_ID IS NOT NULL
AND (
TRIM(ISNULL(C.EVT_J_to_WBS_ID,'')) <> TRIM(ISNULL(S.ApportionedToWBSId,''))
OR S.ApportionedToWBSId IS NULL -- If the WBS ID is NULL, then the task is not apportioned to anything.
)
)