¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 9040144 |
| Summary | Is the actual start for this WP misaligned with last recorded ACWP or BCWP in cost? |
| Error message | Min AS_Date <> min 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 Start Misaligned With Cost (WP)" is designed to identify any discrepancies between the actual start date of a work package (WP) in the DS04 Schedule table and the earliest recorded cost in the DS03 Cost table.
The test is checking if the minimum actual start date (AS_Date) in the DS04 Schedule table is not equal to the minimum period date in the DS03 Cost table where either the actual cost of work performed (ACWPi) or the budgeted cost of work performed (BCWPi) is greater than zero. This is done for each work breakdown structure (WBS) ID in both tables.
If the difference between the earliest cost date and the actual start date for a given WBS ID is greater than 31 days, the test will flag this as a potential issue. This could be caused by a delay in recording costs or an error in the actual start date.
The fields that are likely causing the issue are the AS_Date field in the DS04 Schedule table and the period_date, ACWPi, and BCWPi fields in the DS03 Cost table. The expected values for these fields would be dates that align closely with each other, and cost values that are greater than zero.
Please note that this test excludes certain types and subtypes in the DS04 Schedule table, specifically 'SVT', 'ZBA', 'WS', 'SM', and 'FM'. This is to ensure that the test is only run on relevant data.
¶ Why do we check this?
This test is being performed to ensure that the actual start 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 checks if the minimum actual start date is not equal to the minimum 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) and the WBS_ID_WP.
The importance of this check is to ensure that the cost and schedule data are in sync. If the actual start date of 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, project delays, and cost overruns.
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_IsASMisalignedWithDS03WP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostAS as (
SELECT WBS_ID_WP, 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_WP
),
SchedAS 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
),
WBSFails as (
SELECT S.WBS_ID
FROM SchedAS S INNER JOIN CostAS C ON S.WBS_ID = C.WBS_ID_WP
WHERE ABS(DATEDIFF(d,C.CostAS,S.SchedAS))>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)
)