¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MINOR |
| Unique ID | 1030097 |
| Summary | Is this WBS missing Budget, Performance, Actuals, and Estimates? |
| Error message | Cumulative BCWS, BCWP, ACWP, and ETC are all equal to zero for this piece of work (Dollars, Hours, and FTEs). |
¶ 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 "Zero SPAEcum" is designed to identify any Work Breakdown Structure (WBS) in the DS03 Cost table that may be missing Budget, Performance, Actuals, and Estimates.
This check is performed by examining the cumulative values of Budgeted Cost of Work Scheduled (BCWS), Budgeted Cost of Work Performed (BCWP), Actual Cost of Work Performed (ACWP), and Estimate to Complete (ETC) in terms of Dollars, Hours, and Full-Time Equivalents (FTEs). If all these values are equal to zero for a particular WBS, it is flagged by the check.
The fields causing the issue are BCWSi_Dollars, BCWSi_hours, BCWSi_FTEs, BCWPi_Dollars, BCWPi_hours, BCWPi_FTEs, ACWPi_Dollars, ACWPi_hours, ACWPi_FTEs, ETCi_Dollars, ETCi_hours, and ETCi_FTEs.
The expected values for these fields should not be zero. If they are, it indicates that the WBS is missing critical budget, performance, actual cost, and estimate data, which could potentially lead to inaccurate project management decisions.
The check groups the results by WBS_ID_CA, WBS_ID_WP, and EOC to provide a detailed view of where the issue is occurring.
¶ Why do we check this?
This test is being performed to check for any Work Breakdown Structure (WBS) that might be missing Budget, Performance, Actuals, and Estimates in the 'DS03 Cost' table. The test is checking if the cumulative Budgeted Cost of Work Scheduled (BCWS), Budgeted Cost of Work Performed (BCWP), Actual Cost of Work Performed (ACWP), and Estimate to Complete (ETC) are all equal to zero for a particular WBS. This is important because if these values are all zero, it could indicate that the WBS has not been properly budgeted, tracked, or estimated, which could lead to issues in project management and financial tracking.
The severity of this check is marked as an MINOR. This means that while it is not a critical issue that would prevent the data from being reviewed, it is still a potential problem that could cause minor issues during analysis. It also indicates that the data may not be following all best practices for EVMS construction project management data. Therefore, it is recommended to review and correct this issue to ensure accurate and efficient project management and financial tracking.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsSPAECumEqualToZero] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with ToTest AS (
SELECT WBS_ID_CA CAID, ISNULL(WBS_ID_WP,'') WPID, EOC, ISNULL(is_indirect,'') IsInd
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_CA, ISNULL(WBS_ID_WP,''), EOC, ISNULL(is_indirect,'')
HAVING
SUM(BCWSi_Dollars) = 0 AND SUM(BCWSi_hours) = 0 AND SUM(BCWSi_FTEs) = 0 AND
SUM(BCWPi_Dollars) = 0 AND SUM(BCWPi_hours) = 0 AND SUM(BCWPi_FTEs) = 0 AND
SUM(ACWPi_Dollars) = 0 AND SUM(ACWPi_hours) = 0 AND SUM(ACWPi_FTEs) = 0 AND
SUM(ETCi_Dollars) = 0 AND SUM(ETCi_hours) = 0 AND SUM(ETCi_FTEs) = 0
)
SELECT C.*
FROM DS03_Cost C INNER JOIN ToTest T ON C.WBS_ID_CA = T.CAID
AND ISNULL(C.WBS_ID_WP,'') = T.WPID
AND C.EOC = T.EOC
AND ISNULL(C.is_indirect,'') = T.IsInd
WHERE upload_ID = @upload_ID
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of is_indirect field. Minor adjustment to treatment of missing 'WBS_ID_WP' values for both grouping and filtering purposes. |