¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030074 |
| Summary | Is a root cause narrative missing for this CA where the VAC is tripping the unfavorable dollar threshold? |
| Error message | abs(BCWSi_dollars - ACWPi_dollars - ETCi_dollars) > abs(DS07.threshold_cost_VAC_dollar_unfav) & 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 without Root Cause Narrative (Unfavorable)" is designed to identify any instances in the DS03 Cost table where a root cause narrative is missing for a Control Account (CA) that has a Variance at Completion (VAC) exceeding the unfavorable dollar threshold.
The check is performed by comparing the absolute value of the difference between the Budgeted Cost of Work Scheduled in dollars (BCWSi_dollars), the Actual Cost of Work Performed in dollars (ACWPi_dollars), and the Estimate to Complete in dollars (ETCi_dollars) for each CA. If this value exceeds the unfavorable dollar threshold set in the DS07 IPMR Header table (threshold_cost_VAC_dollar_unfav), the check then verifies if a corresponding narrative exists in the DS11 Variance table (narrative_overall) for that CA (WBS_ID_CA) and for the narrative type 120 (narrative_type).
If the check finds a CA where the VAC exceeds the unfavorable dollar threshold and no corresponding narrative is found in the DS11 Variance table, it will flag this as an error. This error is likely caused by either a missing narrative for a CA with a significant VAC, or an incorrect narrative type being used.
To resolve this error, ensure that a root cause narrative is provided in the DS11 Variance table for each CA where the VAC exceeds the unfavorable dollar threshold, and that the narrative type is correctly set to 120.
¶ 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) is exceeding the unfavorable dollar threshold. The Variance at Completion (VAC) is a measure of the cost performance of the project, and it 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).
The test checks if the absolute value of this difference is greater than the unfavorable dollar threshold set in the DS07 table. If it is, and there is no narrative overall or the narrative type is 120, then a warning is issued. The warning is grouped by the Work Breakdown Structure ID for the Cost Account (WBS_ID_CA).
The importance of this check is to ensure that any significant cost variances are properly documented with a root cause narrative. This is crucial for understanding why the cost variance occurred and for making informed decisions about how to manage the project going forward. Without this information, it would be difficult to identify and address the underlying issues causing the cost overruns. The severity of this check is a warning, indicating that the absence of a root cause narrative is likely to cause problems during the analysis of the project data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsVACMissingDS11RCNarrUnfav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_VAC_dollar_unfav,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)) VAC
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.VAC > (SELECT TOP 1 thrshld FROM threshold)
)