¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 9040150 |
| Summary | Is the actual start for this WBS misaligned with what is in cost? (Note: Comparison is at the CA WBS level, where ACWP has been collected) |
| Error message | Min AS_Date <> min period_date where DS03.ACWPi or DS03.BCWPi > 0 (dollars, hours, or FTEs) by DS04.WBS_ID, DS01.WBS_ID, DS01.parent_WBS_ID, & DS03.WBS_ID_CA. |
¶ 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 "Actual Start Misaligned With Cost (CA)" is designed to identify any discrepancies between the actual start date of a Work Breakdown Structure (WBS) and the corresponding cost data. This check is performed on the DS04 Schedule table.
The test compares the minimum actual start date (AS_Date) from the DS04 Schedule table with the minimum period date from the DS03 Cost table, where the Actual Cost of Work Performed (ACWP) or the Budgeted Cost of Work Performed (BCWP) is greater than zero. This comparison is made at the Control Account (CA) WBS level, where ACWP has been collected.
If the minimum actual start date does not match the minimum period date, an error is flagged. This discrepancy could be due to incorrect data entry, a delay in updating the schedule or cost data, or a misalignment between the schedule and cost data at the CA WBS level.
The fields causing the issue are the AS_Date field in the DS04 Schedule table and the period_date field in the DS03 Cost table. The expected values for these fields should be the same, indicating that the actual start of the WBS aligns with the cost data.
The grouping for this DIQ check is done by the WBS_ID, which is the unique identifier for each Work Breakdown Structure. This allows for easy identification and correction of any discrepancies found during the check.
¶ Why do we check this?
This test is being performed to ensure that the actual start date for a Work Breakdown Structure (WBS) aligns with the cost data. The test checks if the minimum actual start date is not equal to the minimum period date where the actual cost of work performed (ACWP) or the budgeted cost of work performed (BCWP) is greater than zero. This is done by grouping the data by WBS ID, parent WBS ID, and WBS ID at the control account (CA) level.
The importance of this check is to ensure that the project's schedule and cost data are in sync. If the actual start date does not align with the cost data, it could lead to inaccurate project cost analysis and forecasting. This could potentially cause problems during the analysis of the project's performance and financial health.
The severity of this check is marked as a MAJOR, which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis if not addressed. It is crucial to ensure that the project's schedule and cost data are accurate and aligned to maintain the integrity of the project's management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsASMisalignedWithDS03CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostAS as (
SELECT WBS_ID_CA, MIN(period_date) CostAS
FROM DS03_cost
WHERE upload_ID = @upload_ID AND (
ACWPi_dollars > 0 OR ACWPi_FTEs > 0 OR ACWPi_hours > 0 OR
BCWPi_dollars > 0 OR BCWPi_FTEs > 0 OR BCWPi_hours > 0
)
GROUP BY WBS_ID_CA
),
SchedWPAS as (
SELECT WBS_ID, MIN(AS_Date) SchedAS
FROM DS04_schedule
WHERE
upload_ID = @upload_ID
AND schedule_type = 'FC'
AND ISNULL(subtype,'') NOT IN ('SVT', 'ZBA')
AND type NOT IN ('WS','SM','FM')
GROUP BY WBS_ID
),
SchedCAAS as (
SELECT A.Ancestor_WBS_ID CAWBS, MIN(S.SchedAS) SchedAS
FROM SchedWPAS S INNER JOIN AncestryTree_Get(@upload_ID) A ON S.WBS_ID = A.WBS_ID
WHERE A.[Type] = 'WP' AND A.Ancestor_Type = 'CA'
GROUP BY A.Ancestor_WBS_ID
),
CASails as (
SELECT S.CAWBS
FROM SchedCAAS S INNER JOIN CostAS C ON S.CAWBS = C.WBS_ID_CA
WHERE ABS(DATEDIFF(d,C.CostAS,S.SchedAS))>31
),
WPFails as (
SELECT A.WBS_ID
FROM CASails C INNER JOIN AncestryTree_Get(@upload_ID) A ON C.CAWBS = A.Ancestor_WBS_ID
WHERE A.[Type] = 'WP'
)
SELECT
*
FROM
DS04_schedule
WHERE
upload_id = @upload_ID
AND schedule_type = 'FC'
AND ISNULL(subtype,'') NOT IN ('SVT', 'ZBA')
AND type NOT IN ('WS','SM','FM')
AND WBS_ID IN (SELECT WBS_ID FROM WPFails)
)