¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030326 |
| Summary | Is a root cause narrative missing for this CA where the incremental SV is tripping the unfavorable dollar threshold? |
| Error message | DS03.SVi abs(BCWPi - BCWSi) > abs(DS07.threshold_schedule_inc_dollar_unfav) & DS11.narrative_RC_SVi 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 SV without Root Cause Narrative (Unfavorable)" is designed to ensure that a root cause narrative is provided whenever the incremental Schedule Variance (SVi) exceeds the unfavorable dollar threshold. This check is performed on the DS03 Cost table.
The SVi is calculated as the absolute difference between the Budgeted Cost of Work Performed (BCWPi) and the Budgeted Cost of Work Scheduled (BCWSi) for each Control Account (CA). The unfavorable dollar threshold is retrieved from the DS07 IPMR Header table.
The DIQ check identifies any CAs where the SVi exceeds the unfavorable dollar threshold and where a root cause narrative for the SVi is missing or blank in the DS11 Variance table. The check groups the results by the Control Account Work Breakdown Structure ID (WBS_ID_CA).
If this DIQ check fails, it is likely because a root cause narrative has not been provided for a CA where the SVi has exceeded the unfavorable dollar threshold. To resolve this issue, a root cause narrative should be provided in the DS11 Variance table for each CA where the SVi exceeds the unfavorable dollar threshold.
¶ 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 incremental Schedule Variance (SV) exceeds the unfavorable dollar threshold. The Schedule Variance is calculated by subtracting the Budgeted Cost of Work Scheduled (BCWS) from the Budgeted Cost of Work Performed (BCWP). If the absolute value of this difference is greater than the unfavorable dollar threshold, a root cause narrative should be provided.
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 why the project is not proceeding as planned and for making necessary adjustments. The severity level is set to MAJOR, which means that missing or blank root cause narratives are likely to cause problems during the analysis of the data. This is because without these narratives, it would be difficult to understand the reasons behind the observed schedule variances.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsSViMissingDS11RCNarrUnfav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_schedule_inc_dollar_unfav,0)) thrshld
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), CASV as (
SELECT
WBS_ID_CA CAWBS,
ABS(SUM(BCWPi_dollars) - SUM(BCWSi_dollars)) SVi
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_SVi,'')) <> ''
)
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.SVi > (SELECT TOP 1 thrshld FROM threshold)
AND period_date = CPP_status_date
)