¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030317 |
| Summary | Is a root cause narrative missing for this CA where the SV is tripping the unfavorable dollar threshold? |
| Error message | DS03.SVc abs(BCWP - BCWS) > abs(DS07.threshold_schedule_cum_dollar_unfav) & DS11.narrative_RC_SVc 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 "SV without Root Cause Narrative (Unfavorable)" is designed to ensure that a root cause narrative is provided whenever there is a significant variance in the cost of a Control Account (CA) in the DS03 Cost table. This variance is determined by the absolute difference between the Budgeted Cost of Work Performed (BCWP) and the Budgeted Cost of Work Scheduled (BCWS). If this difference exceeds the unfavorable dollar threshold defined in the DS07 IPMR Header table, a root cause narrative should be provided in the DS11 Variance table.
If the DIQ check fails, it means that there is a significant cost variance for a CA, but no corresponding root cause narrative has been provided. The fields causing the issue are the BCWPi_dollars and BCWSi_dollars fields in the DS03 Cost table, the threshold_schedule_cum_dollar_unfav field in the DS07 IPMR Header table, and the narrative_RC_SVc field in the DS11 Variance table.
To resolve this issue, ensure that a root cause narrative is provided in the DS11 Variance table for every CA in the DS03 Cost table where the absolute difference between the BCWP and BCWS exceeds the unfavorable dollar threshold defined 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 account (CA) where the Schedule Variance (SV) is exceeding the unfavorable dollar threshold. The Schedule Variance is calculated as the difference between the Budgeted Cost of Work Performed (BCWP) and the Budgeted Cost of Work Scheduled (BCWS). If this difference is greater than the unfavorable cumulative dollar threshold, a root cause narrative should be provided to explain the variance.
The importance of this check is to ensure that any significant deviations from the planned schedule are properly documented and explained. This is crucial for understanding the reasons behind any cost overruns or delays, and for making informed decisions about how to address these issues. The severity of this check is marked as a MAJOR, which means that missing or blank root cause narratives are likely to cause problems during the analysis of the project data. It may not prevent the data from being reviewed, but it could potentially lead to misunderstandings or incorrect conclusions.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsSVMissingDS11RCNarrUnfav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_schedule_cum_dollar_unfav,0)) thrshld
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), VARs as (
SELECT WBS_ID
FROM DS11_variance
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(narrative_RC_SVc,'')) <> ''
), CASV as (
SELECT WBS_ID_CA CAWBS, ABS(SUM(BCWPi_dollars) - SUM(BCWSi_dollars)) SV
FROM DS03_cost C
WHERE upload_ID = @upload_ID
AND WBS_ID_CA NOT IN (SELECT WBS_ID FROM VARs)
GROUP BY WBS_ID_CA
)
SELECT
C.*
FROM
DS03_cost C INNER JOIN CASV SV ON C.WBS_ID_CA = SV.CAWBS
WHERE
upload_ID = @upload_ID
AND SV.SV > (SELECT TOP 1 thrshld FROM threshold)
AND (SELECT COUNT(*) FROM VARs) > 0 --test only if DS11 has records
)