¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030311 |
| Summary | Is a root cause narrative missing for this CA where the incremental CV is tripping the favorable dollar threshold? |
| Error message | DS03.CVi abs(BCWPi - ACWPi) > abs(DS07.threshold_cost_inc_dollar_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 without Root Cause Narrative (Favorable)" is designed to ensure that a root cause narrative is provided whenever the incremental cost variance (CVi) exceeds a favorable dollar threshold. This check is performed on the DS03 Cost table.
The error message "DS03.CVi (|BCWPi - ACWPi|) > |DS07.threshold_cost_inc_dollar_fav| & DS11.narrative_RC_CVi is missing or blank (by DS03.WBS_ID_CA & DS11.WBS_ID)" indicates that there is a missing or blank root cause narrative in the DS11 Variance table for a cost account (CA) where the absolute difference between the budgeted cost of work performed (BCWPi) and the actual cost of work performed (ACWPi) in the DS03 Cost table is greater than the absolute value of the favorable incremental dollar threshold in the DS07 IPMR Header table.
The fields causing the issue are DS03.CVi, DS07.threshold_cost_inc_dollar_fav, and DS11.narrative_RC_CVi. The expected values are that DS03.CVi should not exceed DS07.threshold_cost_inc_dollar_fav, and DS11.narrative_RC_CVi should not be missing or blank.
The error is likely caused by either a data entry error, where a root cause narrative was not provided for a CA with a favorable CVi, or a calculation error, where the CVi was incorrectly calculated to be greater than the favorable dollar threshold. To resolve this issue, ensure that a root cause narrative is provided for all CAs with a favorable CVi, and verify the calculations for CVi and the favorable dollar threshold.
¶ 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 dollar threshold, and if so, whether a root cause narrative is missing or blank.
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 project management. The severity level is set to MAJOR, which means that missing or blank narratives are likely to cause problems during data analysis. It is not the most severe level, but it is still a significant issue that needs to be addressed to ensure data integrity and quality.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsCViMissingDS11RCNarrFav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
With VARs as (
-- WBSs with VARs
SELECT WBS_ID
FROM DS11_variance
WHERE upload_ID = @upload_id AND TRIM(ISNULL(narrative_RC_CVi,'')) <> ''
), CAsWithoutVARs as (
-- CA WBSs with CVi that do not have VARs
SELECT WBS_ID_CA CAWBS, ABS(SUM(isnull(BCWPi_dollars,0)) - SUM(isnull(ACWPi_dollars,0))) CVi
FROM DS03_cost
WHERE upload_ID = @upload_ID
AND period_date = CPP_status_date
AND WBS_ID_CA NOT IN (SELECT WBS_ID FROM VARs)
GROUP BY WBS_ID_CA
), Threshold as (
-- DS07 favorable inc dollar threshold
SELECT ABS(ISNULL(threshold_cost_inc_dollar_fav,0)) AS threshold
FROM DS07_IPMR_header
WHERE upload_ID = @upload_id
)
SELECT
C.*
FROM
DS03_cost C INNER JOIN CAsWithoutVARs CV ON C.WBS_ID_CA = CV.CAWBS
INNER JOIN Threshold T ON CV.CVi > T.threshold
WHERE
upload_ID = @upload_id
AND period_date = CPP_status_date
)