¶ Basic Information
| Key | Value |
|---|---|
| Table | DS07 IPMR Header |
| Severity | MINOR |
| Unique ID | 9070364 |
| Summary | Is EAC greater than BAC without an OTB / OTS date? |
| Error message | DS03.ACWPc + DS03.ETCc > DS03.BCWSc & OTB_OTS_date is missing or blank. |
¶ 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 "EAC > BAC without OTB / OTS Date" is designed to ensure the accuracy and consistency of the data in the DS07 IPMR Header and DS03 Cost tables. This check is specifically looking for instances where the Estimate at Completion (EAC) is greater than the Budget at Completion (BAC), but there is no Over Target Baseline (OTB) or Over Target Schedule (OTS) date provided.
The EAC is calculated as the sum of the Actual Cost of Work Performed (ACWP) and the Estimate to Complete (ETC) from the DS03 Cost table, plus the Undistributed Budget Estimate (UBE) from the DS07 IPMR Header table. The BAC is calculated as the sum of the Budgeted Cost of Work Scheduled (BCWS) from the DS03 Cost table, plus the Undistributed Budget Budget (UBB) from the DS07 IPMR Header table.
If the EAC is greater than the BAC, this indicates that the project is expected to cost more than the budgeted amount. In such cases, an OTB or OTS date should be provided to indicate when the project was re-baselined to account for the overage. If this date is missing or blank, it could indicate a data entry error or oversight.
To resolve this issue, review the relevant records in the DS07 IPMR Header and DS03 Cost tables. Ensure that all financial figures are accurate and that an OTB or OTS date is provided for any project where the EAC exceeds the BAC.
¶ Why do we check this?
This test is being performed to ensure that the Estimated at Completion (EAC) is not greater than the Budget at Completion (BAC) without an Over Target Baseline (OTB) or Over Target Schedule (OTS) date. This is important because if the EAC is greater than the BAC, it indicates that the project is over budget. However, if there is no OTB or OTS date, it could mean that the project is not properly tracking its budget or schedule overruns.
The severity of this check is an MINOR, which means it's not a critical issue that would prevent the data from being reviewed, but it could potentially cause minor problems or indicate that the data doesn't follow all best practices. For example, if this issue is not addressed, it could lead to inaccurate financial reporting or project management decisions based on incorrect budget or schedule data. Therefore, it's important to ensure that all projects have an OTB or OTS date if their EAC is greater than their BAC.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS07_IPMR_IsEACGtBACWithoutOTBOTSDate] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with UBEst as (
SELECT ISNULL(UB_est_dollars,0) UBEst
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), UBBgt as (
SELECT ISNULL(UB_bgt_dollars,0) UBBgt
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), EAC as (
SELECT SUM(ISNULL(ETCi_dollars,0) + ISNULL(ACWPi_dollars,0)) + (SELECT TOP 1 UBEst FROM UBest) EAC
FROM DS03_cost
WHERE upload_ID = @upload_ID
), BAC as (
SELECT SUM(ISNULL(BCWSi_dollars,0)) + (SELECT TOP 1 UBBgt FROM UBBgt) BAC
FROM DS03_cost
WHERE upload_ID = @upload_ID
)
SELECT
*
FROM
DS07_IPMR_header
WHERE
upload_ID = @upload_ID
AND (SELECT TOP 1 EAC FROM EAC) > (SELECT TOP 1 BAC FROM BAC)
AND OTB_OTS_date IS NULL
)