¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030320 |
| Summary | Is a root cause narrative missing for this CA where the VAC is tripping the favorable dollar threshold? |
| Error message | abs(BCWSi_dollars - ACWPi_dollars - ETCi_dollars) > abs(DS07.threshold_cost_VAC_dollar_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 without Root Cause Narrative (Favorable)" is designed to identify instances in the DS03 Cost table where a root cause narrative is missing for a cost account (CA) where the Variance at Completion (VAC) is exceeding the favorable dollar threshold.
The VAC is calculated as the absolute 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). This value is then compared to the favorable dollar threshold set in the DS07 IPMR Header table (threshold_cost_VAC_dollar_fav).
The error is likely to occur when the calculated VAC for a CA in the DS03 Cost table is greater than the favorable dollar threshold, and there is no corresponding narrative in the DS11 Variance table (narrative_overall) for that CA (WBS_ID_CA) with a narrative type of 120.
To resolve this issue, ensure that for each CA in the DS03 Cost table where the VAC exceeds the favorable dollar threshold, there is a corresponding narrative in the DS11 Variance table with a narrative type of 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 favorable dollar threshold. The Variance at Completion (VAC) is a measure of the cost performance of the project, and it is calculated as the difference between the Budgeted Cost of Work Scheduled (BCWS), the Actual Cost of Work Performed (ACWP), and the Estimate to Complete (ETC).
The test checks if the absolute value of this difference is greater than the favorable dollar threshold set in the DS07 table, and if so, it checks if there is a corresponding narrative in the DS11 table. If the narrative is missing or blank, the test raises a warning.
The importance of this check is to ensure that there are explanations for any significant variances in the project's cost performance. This is crucial for understanding the reasons behind the variances and for making informed decisions about the project's future. The severity of this check is set to MAJOR, which means that while the absence of a narrative might not prevent the data from being reviewed, it is likely to cause problems during the analysis of the project's cost performance.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsVACMissingDS11RCNarrFav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_VAC_dollar_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)) 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)
)