¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030323 |
| Summary | Is a root cause narrative missing for this CA where the incremental CV percent is tripping the unfavorable percent threshold? |
| Error message | DS03.CVi abs((BCWPi - ACWPi) / BCWPi) > abs(DS07.threshold_cost_inc_pct_unfav) & DS11.narrative_RC_CVi 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 "Incremental CV Percent 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 an incremental cost variance (CV) percentage exceeding the unfavorable threshold.
The error is likely to occur when the absolute value of the difference between the Budgeted Cost of Work Performed (BCWPi) and the Actual Cost of Work Performed (ACWPi), divided by BCWPi, is greater than the unfavorable threshold set in the DS07 IPMR Header table. This calculation is represented as |(BCWPi - ACWPi) / BCWPi|.
The fields causing the issue are the BCWPi and ACWPi fields in the DS03 Cost table, the threshold_cost_inc_pct_unfav field in the DS07 IPMR Header table, and the narrative_RC_CVi field in the DS11 Variance table.
The expected values for the BCWPi and ACWPi fields are numerical values representing the budgeted and actual costs, respectively. The threshold_cost_inc_pct_unfav field should contain the unfavorable threshold value for the cost variance percentage. The narrative_RC_CVi field should contain a root cause narrative for the cost variance. If this field is missing or blank, it indicates that a root cause narrative has not been provided for a control account where the incremental CV percent is exceeding the unfavorable threshold.
The DIQ check groups the results by the WBS_ID_CA and period_date fields. This means that the check is performed for each unique combination of control account and period date.
¶ Why do we check this?
This test is being performed to ensure that there is a root cause narrative provided whenever the incremental Cost Variance (CV) percent exceeds the unfavorable percent threshold. The Cost Variance is a measure of the cost performance of the project, calculated as the difference between the Budgeted Cost of Work Performed (BCWP) and the Actual Cost of Work Performed (ACWP). If this variance is too high, it indicates that the project is over budget, and a root cause narrative is required to explain why this has occurred.
The importance of this check is to ensure that any significant cost overruns are properly documented and explained. This is crucial for understanding why the project is over budget and for making necessary adjustments to bring the project back on track. The severity level is set to MAJOR, which means that while the data can still be reviewed, the absence of a root cause narrative for significant cost variances is likely to cause problems during the analysis of the project's cost performance.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsCViPctMissingDS11RCNarrUnfav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_inc_pct_unfav,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)) / NULLIF(SUM(BCWPi_dollars),0)) CViPct
FROM DS03_cost C
WHERE upload_ID = @upload_ID
AND period_date = CPP_status_date
AND WBS_ID_CA NOT IN (
SELECT WBS_ID
FROM DS11_variance
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(narrative_RC_CVi,'')) <> ''
)
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.CViPct > (SELECT TOP 1 thrshld FROM threshold)
AND period_date = CPP_status_date
)