¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030312 |
| Summary | Is a root cause narrative missing for this CA where the CV is tripping the favorable dollar threshold? |
| Error message | DS03.CVc abs(BCWP - ACWP) > abs(DS07.threshold_cost_cum_dollar_fav) & DS11.narrative_RC_CVc is missing or blank (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 "CV 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 variance (CV) that exceeds the favorable dollar threshold.
The test is triggered when the absolute difference between the Budgeted Cost of Work Performed (BCWP) and the Actual Cost of Work Performed (ACWP) in the DS03 Cost table is greater than the favorable dollar threshold set in the DS07 IPMR Header table.
The error is likely to occur when there is no corresponding root cause narrative for the cost variance in the DS11 Variance table. This means that for the Work Breakdown Structure ID (WBS_ID_CA) in the DS03 Cost table, there is no matching WBS_ID in the DS11 Variance table, or the narrative_RC_CVc field in the DS11 Variance table is blank or missing.
To resolve this issue, ensure that for every cost variance that exceeds the favorable dollar threshold, there is a corresponding root cause narrative in the DS11 Variance table. The narrative should be detailed and provide an explanation for the cost variance.
¶ Why do we check this?
This test is being performed to ensure that there is a root cause narrative for every cost variance (CV) that exceeds the favorable dollar threshold. The test checks if the absolute difference between the Budgeted Cost of Work Performed (BCWP) and the Actual Cost of Work Performed (ACWP) is greater than the favorable cost threshold. If it is, and there is no corresponding root cause narrative, a warning is issued.
The importance of this check is to ensure that all significant cost variances are properly documented with a root cause narrative. This is crucial for understanding why the variance occurred and for making informed decisions about how to manage costs in the future. Without this information, it would be difficult to identify and address the underlying issues causing the variances. The severity level is set to MAJOR, indicating that missing narratives are likely to cause problems during data analysis.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsCVMissingDS11RCNarrFav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_cum_dollar_fav,0)) thrshld
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), CACV as (
SELECT WBS_ID_CA CAWBS, ABS(SUM(BCWPi_dollars) - SUM(ACWPi_dollars)) CV
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_RC_CVc,'')) <> ''
)
GROUP BY WBS_ID_CA
)
SELECT
C.*
FROM
DS03_cost C INNER JOIN CACV CV ON C.WBS_ID_CA = CV.CAWBS
WHERE
upload_ID = @upload_ID
AND CV.CV > (SELECT TOP 1 thrshld FROM threshold)
)