¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030325 |
| Summary | Is a root cause narrative missing for this CA where the incremental SV is tripping the favorable dollar threshold? |
| Error message | DS03.SVi abs(BCWPi - BCWSi) > abs(DS07.threshold_schedule_inc_dollar_fav) & 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 (Favorable)" is designed to ensure that a root cause narrative is provided whenever the incremental Schedule Variance (SVi) exceeds the favorable 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). If this value exceeds the favorable dollar threshold specified 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 for one or more CAs, the SVi exceeds the favorable dollar threshold, but no root cause narrative has been provided. The fields causing the issue are the BCWPi and BCWSi fields in the DS03 Cost table, the threshold_schedule_inc_dollar_fav field in the DS07 IPMR Header table, and the narrative_RC_SVi field in the DS11 Variance table.
To resolve this issue, ensure that a root cause narrative is provided in the DS11 Variance table for each CA where the SVi exceeds 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 Account (CA) where the incremental Schedule Variance (SV) exceeds the favorable 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 favorable dollar threshold and there is no root cause narrative, a warning is issued.
The importance of this check lies in maintaining the integrity and quality of the project management data. It ensures that all significant variances in the project schedule are properly documented with a root cause narrative. This is crucial for understanding why these variances occurred and for making informed decisions on how to manage them. Without this information, the project management team may not be able to effectively control costs and keep the project on schedule. The severity level of 'MAJOR' indicates that this issue could cause problems during data analysis if not addressed.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsSViMissingDS11RCNarrFav] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with threshold as (
SELECT ABS(ISNULL(threshold_schedule_inc_dollar_fav,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
)