¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030322 |
| Summary | Is a root cause narrative missing for this CA where the incremental CV percent is tripping the favorable percent threshold? |
| Error message | DS03.CVi abs((BCWPi - ACWPi) / BCWPi) > abs(DS07.threshold_cost_inc_pct_fav) & 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 (Favorable)" is designed to ensure that a root cause narrative is provided whenever the incremental cost variance (CV) percentage exceeds a certain favorable threshold. This check is performed on the DS03 Cost table.
The error message "DS03.CVi (|(BCWPi - ACWPi) / BCWPi|) > |DS07.threshold_cost_inc_pct_fav| & DS11.narrative_RC_CVi is missing or blank (by DS03.WBS_ID_CA & DS11.WBS_ID)" indicates that the incremental CV percent, calculated as the absolute value of the difference between BCWPi (Budgeted Cost of Work Performed) and ACWPi (Actual Cost of Work Performed) divided by BCWPi, is greater than the absolute value of the favorable threshold cost increment percentage specified in the DS07 table. Additionally, the root cause narrative for the incremental CV (narrative_RC_CVi) in the DS11 Variance table is either missing or blank.
The fields causing this issue are the BCWPi and ACWPi fields in the DS03 Cost table, the threshold_cost_inc_pct_fav field in the DS07 IPMR Header table, and the narrative_RC_CVi field in the DS11 Variance table.
The expected values for these fields are as follows: BCWPi and ACWPi should contain valid cost data, threshold_cost_inc_pct_fav should contain a valid percentage value, and narrative_RC_CVi should contain a narrative explanation whenever the incremental CV percent exceeds the favorable threshold.
If these conditions are not met, the DIQ check will flag the data for review. This helps ensure that all cost variances are properly explained and documented, which is crucial for effective project management and financial control.
¶ 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 favorable percent threshold. The test checks the 'DS03 Cost' table for instances where the absolute value of the difference between the Budgeted Cost of Work Performed (BCWP) and the Actual Cost of Work Performed (ACWP), divided by BCWP, is greater than the absolute value of the favorable threshold for incremental cost percent. If such instances are found, the test then checks if a root cause narrative is missing or blank for these instances.
The importance of this check is to ensure that any significant favorable cost variances are properly explained. This is crucial for understanding why the actual costs are lower than the budgeted costs, which could be due to factors such as efficiency improvements, cost savings initiatives, or estimation errors. Without a root cause narrative, it would be difficult to understand and replicate the factors leading to favorable cost variances, or to correct any estimation errors.
The severity of this check is classified as a 'MAJOR', which means that while the data can still be reviewed, the absence of root cause narratives for significant favorable cost variances is likely to cause problems during analysis. It is therefore recommended to address this issue to ensure the integrity and quality of the EVMS construction project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsCViPctMissingDS11RCNarrFav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_inc_pct_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)) / 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
)