¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030313 |
| Summary | Is a root cause narrative missing for this CA where the CV is tripping the unfavorable dollar threshold? |
| Error message | DS03.CVc abs(BCWP - ACWP) > abs(DS07.threshold_cost_cum_dollar_unfav) & 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 (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 a cost variance (CV) exceeding the unfavorable dollar threshold.
The CV is calculated as the absolute difference between the Budgeted Cost of Work Performed (BCWP) and the Actual Cost of Work Performed (ACWP). If this CV is greater than the unfavorable dollar threshold specified in the DS07 IPMR Header table, a root cause narrative should be provided in the DS11 Variance table.
The error is likely to occur when the CV for a CA in the DS03 Cost table exceeds the unfavorable dollar threshold in the DS07 IPMR Header table, but the corresponding root cause narrative in the DS11 Variance table is missing or blank.
The fields causing the issue are the BCWPi_dollars and ACWPi_dollars fields in the DS03 Cost table, the threshold_cost_cum_dollar_unfav field in the DS07 IPMR Header table, and the narrative_RC_CVc field in the DS11 Variance table.
The expected values are that if the CV (|BCWP - ACWP|) is greater than the unfavorable dollar threshold, there should be a corresponding root cause narrative in the DS11 Variance table. If the narrative is missing or blank, this will trigger the DIQ check.
¶ 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 root cause narrative is crucial for understanding why the cost variance occurred and for making informed decisions on how to address it.
The severity of this test is marked as a MAJOR, which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. If a root cause narrative is missing for a significant cost variance, it could lead to misunderstandings or misinterpretations of the data, potentially resulting in incorrect decisions or actions.
Therefore, this check is important to ensure the completeness and accuracy of the data, and to facilitate effective project management and decision-making.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsCVMissingDS11RCNarrUnfav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_cost_cum_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)) 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)
)