¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 9040149 |
| Summary | Is the actual finish for this WBS misaligned with what is in cost? (Note: Comparison is at the CA WBS level, where ACWP has been collected) |
| Error message | Max AF_Date <> max 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 Finish Misaligned With Cost (CA)" is designed to identify any discrepancies between the actual finish date of a Work Breakdown Structure (WBS) and the cost data associated with it. This check is performed on the DS04 Schedule table and is particularly focused on the WBS at the Control Account (CA) level where Actual Cost of Work Performed (ACWP) has been collected.
The error message "Max AF_Date <> max 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" indicates that the maximum actual finish date (AF_Date) in the DS04 Schedule table does not match the maximum period date in the DS03 Cost table where the ACWP or Budgeted Cost of Work Performed (BCWP) is greater than zero. This discrepancy is identified by comparing the WBS_ID fields in the DS04 Schedule, DS01 WBS, and DS03 Cost tables.
The likely cause of this error is a misalignment between the actual finish dates and the cost data. This could be due to incorrect data entry, delays in updating one of the tables, or a mismatch in the time periods being compared. The expected values would be that the maximum actual finish date in the DS04 Schedule table should match the maximum period date in the DS03 Cost table for the same WBS_ID.
To resolve this issue, you should review the data in the DS04 Schedule and DS03 Cost tables for the identified WBS_IDs. Ensure that the actual finish dates and cost data are correctly aligned and that all updates have been properly recorded.
¶ Why do we check this?
This test is being performed to ensure that the actual finish date for a Work Breakdown Structure (WBS) aligns with the cost data. In other words, it checks if the date when a task was actually completed matches with the date when the cost was recorded for that task. This is important because any misalignment between the actual finish date and the cost data can lead to inaccurate financial reporting and project management.
The severity of this test is marked as a MAJOR, which means that while it may not prevent the data from being reviewed, any discrepancies found could potentially cause problems during the analysis of the data. This could lead to incorrect conclusions being drawn about the project's cost efficiency and timeline. Therefore, it's crucial to address any warnings to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsAFMisalignedWithDS03CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostAF as (
SELECT WBS_ID_CA, MAX(period_date) CostAF
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
),
SchedWPAF as (
SELECT WBS_ID, MAX(AF_Date) SchedAF
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
),
SchedCAAF as (
SELECT A.Ancestor_WBS_ID CAWBS, MAX(S.SchedAF) SchedAF
FROM SchedWPAF 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
),
CAFails as (
SELECT S.CAWBS
FROM SchedCAAF S INNER JOIN CostAF C ON S.CAWBS = C.WBS_ID_CA
WHERE ABS(DATEDIFF(d,C.CostAF,S.SchedAF))>31
),
WPFails as (
SELECT A.WBS_ID
FROM CAFails 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)
)