¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 9040140 |
| Summary | Is the actual finish for this WP misaligned with last recorded ACWP or BCWP in cost? |
| Error message | Max AF_Date <> max period_date where DS03.ACWPi or DS03.BCWPi > 0 (dollars, hours, or FTEs) by DS04.WBS_ID & DS03.WBS_ID_WP. |
¶ 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 (WP)" is designed to identify any discrepancies between the actual finish date of a work package (WP) in the DS04 Schedule table and the last recorded Actual Cost of Work Performed (ACWP) or Budgeted Cost of Work Performed (BCWP) in the DS03 Cost table.
The test is performed by comparing the maximum actual finish date (AF_Date) in the DS04 Schedule table with the maximum period date where either ACWP or BCWP is greater than zero in the DS03 Cost table. The comparison is made for each work package, identified by the WBS_ID field.
If the difference between these two dates is greater than 31 days, the test will flag this as a potential issue. This could be caused by a delay in updating the actual finish date in the DS04 Schedule table after costs have been recorded in the DS03 Cost table, or it could indicate that costs are being recorded after the work package has been completed.
To resolve this issue, you should ensure that the actual finish dates in the DS04 Schedule table are updated promptly when a work package is completed, and that costs are recorded accurately and timely in the DS03 Cost table.
¶ Why do we check this?
This test is being performed to ensure that the actual finish date for a work package (WP) aligns with the last recorded actual cost of work performed (ACWP) or budgeted cost of work performed (BCWP) in cost. The test is checking if the maximum actual finish date is not equal to the maximum period date where the ACWP or BCWP is greater than zero. This is done by grouping the data by the work breakdown structure ID (WBS_ID).
The importance of this check is to ensure that the cost and schedule data are in sync. If the actual finish date for a work package does not align with the cost data, it could lead to inaccurate project cost and schedule performance analysis. This could further lead to incorrect decision making, impacting the overall project management.
The severity of this check is marked as a MAJOR. This means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. Therefore, it is recommended to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsAFMisalignedWithDS03WP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostAF as (
SELECT WBS_ID_WP, 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_WP
),
SchedAF 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
),
WBSFails as (
SELECT S.WBS_ID
FROM SchedAF S INNER JOIN CostAF C ON S.WBS_ID = C.WBS_ID_WP
WHERE ABS(DATEDIFF(d,C.CostAF,S.SchedAF))>31
)
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 WBSFails)
)