¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030329 |
| Summary | Is a root cause narrative missing for this CA where the VAC % is tripping the favorable percent threshold? |
| Error message | abs((BCWSi_dollars - ACWPi_dollars - ETCi_dollars) / BCWSi_dollars) > abs(DS07.threshold_cost_VAC_pct_fav) & DS11.narrative_overall is missing or blank where DS11.narrative_type = 120 (by DS03.WBS_ID_CA & DS11.WBS_ID). |
¶ 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 "VAC Percent without Root Cause Narrative (Favorable)" is designed to identify any instances in the DS03 Cost table where a root cause narrative is missing for a cost account (CA) that has a Variance at Completion (VAC) percentage exceeding the favorable threshold.
The VAC percentage is calculated as the absolute value of the difference between the Budgeted Cost of Work Scheduled (BCWSi_dollars), the Actual Cost of Work Performed (ACWPi_dollars), and the Estimate to Complete (ETCi_dollars), divided by the BCWSi_dollars. This percentage is then compared to the favorable threshold value from the DS07 IPMR Header table.
The error is likely to occur when the calculated VAC percentage is greater than the favorable threshold, and the corresponding root cause narrative in the DS11 Variance table is either missing or blank. The narrative type should be 120 for these records.
The fields causing the issue are the BCWSi_dollars, ACWPi_dollars, and ETCi_dollars in the DS03 Cost table, the threshold_cost_VAC_pct_fav in the DS07 IPMR Header table, and the narrative_overall and narrative_type in the DS11 Variance table.
To resolve this issue, ensure that a root cause narrative is provided in the DS11 Variance table for all cost accounts where the VAC percentage exceeds the favorable threshold.
¶ Why do we check this?
This test is being performed to ensure that there is a root cause narrative for any cost account (CA) where the Variance at Completion (VAC) percentage is exceeding the favorable percent threshold. The Variance at Completion is calculated by subtracting the Actual Cost of Work Performed (ACWP) and the Estimate to Complete (ETC) from the Budgeted Cost of Work Scheduled (BCWS), and then dividing by the BCWS. If this value is greater than the threshold set in DS07, and there is no narrative or the narrative is blank for a narrative type of 120, then a warning is issued.
The importance of this check is to ensure that any significant favorable variances are properly explained. This is crucial for understanding why the project is under budget, which could be due to efficiencies, cost savings, or potentially missing or incomplete work. Without this narrative, it would be difficult to accurately analyze the project's performance and make informed decisions. The severity of this check is a warning, indicating that while it may not prevent the data from being reviewed, it could cause problems during analysis if not addressed.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsVACPctMissingDS11RCNarrFav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_VAC_pct_fav,0)) Thrshld
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), CAVAC as (
SELECT
WBS_ID_CA CAWBS,
ABS(
(SUM(BCWSi_dollars) - SUM(ACWPi_dollars) - SUM(ETCi_dollars)) /
NULLIF(SUM(BCWSi_dollars),0)
) VACPct
FROM
DS03_cost C
WHERE
upload_ID = @upload_ID
AND WBS_ID_CA NOT IN (
SELECT WBS_ID
FROM DS11_variance
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(narrative_overall,'')) <> '' AND narrative_type = 120
)
GROUP BY WBS_ID_CA
)
SELECT
C.*
FROM
DS03_cost C INNER JOIN CAVAC V ON C.WBS_ID_CA = V.CAWBS
WHERE
upload_ID = @upload_ID
AND V.VACPct > (SELECT TOP 1 Thrshld FROM threshold)
)