¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030321 |
| Summary | Is a root cause narrative missing for this CA where the incremental CV is tripping the unfavorable dollar threshold? |
| Error message | DS03.CVi abs(BCWPi - ACWPi) > abs(DS07.threshold_cost_inc_dollar_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 without Root Cause Narrative (Unfavorable)" is designed to ensure that a root cause narrative is provided whenever the incremental cost variance (CVi) exceeds the unfavorable dollar threshold. This check is performed on the DS03 Cost table.
The error message "DS03.CVi (|BCWPi - ACWPi|) > |DS07.threshold_cost_inc_dollar_unfav| & DS11.narrative_RC_CVi is missing or blank (by DS03.WBS_ID_CA & DS11.WBS_ID)" indicates that the check has found instances where the absolute value of the 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 unfavorable dollar threshold set in the DS07 IPMR Header table. However, there is no corresponding root cause narrative in the DS11 Variance table.
The fields causing this issue are DS03.CVi, DS07.threshold_cost_inc_dollar_unfav, and DS11.narrative_RC_CVi. The expected values are that if DS03.CVi exceeds DS07.threshold_cost_inc_dollar_unfav, there should be a corresponding narrative in DS11.narrative_RC_CVi. If this narrative is missing or blank, it will trigger the error.
To resolve this issue, ensure that a root cause narrative is provided in the DS11 Variance table whenever the incremental cost variance in the DS03 Cost table exceeds the unfavorable dollar threshold set in the DS07 IPMR Header table.
¶ Why do we check this?
This test is being performed to ensure that there is a root cause narrative for any cost variance (CV) that exceeds the unfavorable dollar threshold in the DS03 Cost table. The test checks if the absolute difference between the budgeted cost of work performed (BCWPi) and the actual cost of work performed (ACWPi) is greater than the unfavorable dollar threshold. If it is, and there is no root cause narrative (DS11.narrative_RC_CVi) for this cost variance, a warning is issued.
The importance of this check is to ensure that any 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 on how to address it. Without this information, it would be difficult to manage costs effectively and prevent similar issues in the future. The severity of this check is a warning, indicating 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 analysis.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsCViMissingDS11RCNarrUnfav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_inc_dollar_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)) CVi
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.CVi > (SELECT TOP 1 thrshld FROM threshold)
AND period_date = CPP_status_date
)