¶ Basic Information
| Key | Value |
|---|---|
| Table | DS07 IPMR Header |
| Severity | MINOR |
| Unique ID | 9070303 |
| Summary | Is the PM EAC Best date earlier than the last recorded ETC plus estimated UB? |
| Error message | EAC_PM_best_date < last DS03.period_date where ETCi > 0 (hours, dollars, or FTEs) + DS07.UB_EST_days. |
¶ 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 "PM EAC Best Date Misaligned with Cost Estimates" is designed to ensure that the Project Manager's (PM) best estimate at completion (EAC) date is not earlier than the last recorded estimate to complete (ETC) plus the estimated unburdened (UB) days. This check is performed on the DS07 IPMR Header table.
If an error is flagged by this DIQ check, it suggests that the PM's EAC date is set earlier than it should be, considering the last recorded ETC and the estimated UB days. This discrepancy could be due to an error in data entry or a miscalculation in the EAC, ETC, or UB estimates.
The fields involved in this check are the EAC_PM_best_date from the DS07 IPMR Header table, the period_date and ETCi (in hours, dollars, or FTEs) from the DS03 Cost table, and the UB_est_days from the DS07 IPMR Header table.
The expected values for these fields should align such that the PM's EAC date is not earlier than the last recorded ETC plus the estimated UB days. If the EAC_PM_best_date is earlier, it suggests that the project is projected to complete before all estimated work and costs are accounted for, which could lead to inaccurate project planning and management.
¶ Why do we check this?
This test is being performed to ensure that the Project Manager's (PM) Estimate at Completion (EAC) Best date is not earlier than the last recorded Estimate to Complete (ETC) plus the estimated Undistributed Budget (UB). The test is checking if the PM's best estimate for project completion is logically consistent with the remaining work and budget.
The importance of this check is to maintain the integrity and accuracy of the project's timeline and budget estimates. If the PM's EAC Best date is earlier than the last recorded ETC plus the estimated UB, it could indicate a potential error or inconsistency in the project's planning or tracking. This could lead to inaccurate forecasting, budgeting, and scheduling, which could negatively impact the project's management and outcomes.
The severity of this check is an MINOR, which means it is less severe but indicates there might be minor problems or that the data doesn't follow all best practices. It's not a critical error, but it's a potential issue that should be reviewed and corrected to ensure the accuracy and reliability of the project's data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS07_IPMR_IsEACPMBestLtLastDS03ETCPlusUBEst] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with LastETCi as (
SELECT MAX(period_date) LastPrd
FROM DS03_cost
WHERE upload_ID = @upload_ID AND (ETCi_dollars > 0 OR ETCi_FTEs > 0 OR ETCi_hours > 0)
), UBEstWkndFactor as (
SELECT (ISNULL(UB_est_days,0) / 5) * 2 WkndF
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
)
SELECT
*
FROM
DS07_IPMR_header
WHERE
upload_ID = @upload_ID
AND EAC_PM_best_date < DATEADD(day, (SELECT TOP 1 WkndF FROM UBEstWkndFactor), (SELECT TOP 1 LastPrd FROM LastETCi))
)