¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 1030099 |
| Summary | Are there estimates on this WP even though it is complete? |
| Error message | ETCi <> 0 (Dollars, Hours, or FTEs) on completed work (BCWPc / BCWSc > 99%). |
¶ 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 "Estimates On Completed Work" is designed to ensure that there are no remaining estimates on work packages (WP) that have been marked as complete. This check is performed on the DS03 Cost table.
The test identifies work packages as complete if the ratio of Budgeted Cost of Work Performed (BCWP) to Budgeted Cost of Work Scheduled (BCWS) is greater than 99% for any of the three measures: dollars, hours, or Full-Time Equivalents (FTEs).
If a work package is marked as complete, it should not have any remaining Estimate to Complete (ETC) values. Therefore, the test flags any records where ETC in dollars, hours, or FTEs is greater than zero for completed work packages.
The fields involved in this check are WBS_ID_CA, WBS_ID_WP, EOC, BCWPi (dollars, hours, FTEs), BCWSi (dollars, hours, FTEs), and ETCi (dollars, hours, FTEs).
If this test fails, it is likely due to incorrect data entry. For example, a work package might have been marked as complete (BCWP/BCWS > 99%) but still has remaining estimates (ETC > 0). This could be due to a delay in updating the ETC values or an error in marking the work package as complete.
To resolve this issue, review the flagged records and ensure that the ETC values are zero for all completed work packages. If a work package is not actually complete, correct the BCWP and BCWS values accordingly.
¶ Why do we check this?
This test is being performed to ensure that there are no estimates on a work package (WP) that has already been completed. The test checks if the Estimate to Complete (ETCi) is not equal to zero on completed work, which is indicated by the Budgeted Cost of Work Performed (BCWPc) or the Budgeted Cost of Work Scheduled (BCWSc) being greater than 99%.
The importance of this check is to maintain the accuracy and reliability of the project's cost data. If there are estimates on completed work, it could lead to misinterpretation of the project's status and financial performance, potentially leading to incorrect decision-making.
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. It is crucial to address this issue to ensure the integrity and quality of the project's cost data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_DoesETCExistOnCompletedWork] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CompleteWork As (
SELECT WBS_ID_CA, WBS_ID_WP, EOC
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_CA, WBS_ID_WP, EOC
HAVING ABS(SUM(BCWPi_dollars) / NULLIF(SUM(BCWSi_dollars),0)) > .99 OR
ABS(SUM(BCWPi_hours) / NULLIF(SUM(BCWSi_hours),0)) > .99 OR
ABS(SUM(BCWPi_FTEs) / NULLIF(SUM(BCWSi_FTEs),0)) > .99
)
SELECT
C.*
FROM
DS03_Cost C INNER JOIN CompleteWork W ON C.WBS_ID_CA = W.WBS_ID_CA
AND C.WBS_ID_WP = W.WBS_ID_WP
AND C.EOC = W.EOC
WHERE
upload_ID = @upload_ID
AND (ETCi_dollars > 0 OR ETCi_FTEs > 0 OR ETCi_hours > 0)
)