¶ Basic Information
| Key | Value |
|---|---|
| Table | DS11 Variance |
| Severity | MINOR |
| Unique ID | 9110496 |
| Summary | Is this CVi or CVc VAR narrative missing a corrective action log entry with a cost narrative? |
| Error message | narrative_RC_CVc or narrative_RC_CVi found without DS12.narrative_cost (by CAL_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 Root Cause Narrative Missing CAL Cost Narrative" is designed to identify any instances in the DS11 Variance table where a Cost Variance (CV) root cause narrative is present, but there is no corresponding corrective action log (CAL) entry with a cost narrative in the DS12 Variance CAL table.
The fields involved in this check are 'narrative_RC_CVi' and 'narrative_RC_CVc' in the DS11 Variance table, and 'narrative_cost' in the DS12 Variance CAL table. The 'narrative_RC_CVi' and 'narrative_RC_CVc' fields should contain narratives explaining the root cause of any identified cost variances. The 'narrative_cost' field in the DS12 Variance CAL table should contain a narrative explaining the cost implications of the corrective action taken.
The error is likely to occur if a cost variance root cause narrative is entered in the DS11 Variance table, but no corresponding cost narrative is entered in the DS12 Variance CAL table. This could be due to an oversight or error when entering data, or it could indicate that a corrective action has been identified but not yet fully documented.
The expected values for the 'narrative_RC_CVi' and 'narrative_RC_CVc' fields are any non-blank text entries. For the 'narrative_cost' field in the DS12 Variance CAL table, the expected value is also any non-blank text entry. If the DIQ check identifies any instances where these conditions are not met, it will flag them for review.
¶ Why do we check this?
This test is being performed to ensure that every Cost Variance (CV) root cause narrative in the DS11 Variance table has a corresponding corrective action log entry with a cost narrative in the DS12 table. The test checks for instances where 'narrative_RC_CVc' or 'narrative_RC_CVi' are found without a corresponding 'DS12.narrative_cost' entry by 'CAL_ID'.
The importance of this check is to maintain the integrity and completeness of the data. In project management, it's crucial to have a clear understanding of the root causes of cost variances and the corrective actions taken. This includes the cost implications of those actions. If this information is missing, it could lead to inaccurate analysis and decision-making.
The severity of this test is marked as an MINOR. This means that while it's not a critical error that would prevent data review, it's still a potential issue that could cause minor problems or indicate that the data doesn't fully adhere to best practices. It's recommended to address this issue to ensure the highest quality of data analysis and reporting.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS11_VAR_IsCVNarrMissingDS12CostNarr] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with VARsByCAL as (
--WBS IDs with CAL IDs broken out into rows
--filtered for rows where there is a CV RC narrative
SELECT WBS_ID, CAL_ID
FROM DS11_variance CROSS APPLY string_split(CAL_ID, ';')
WHERE
upload_ID = @upload_ID
AND (TRIM(ISNULL(narrative_RC_CVi,'')) <> '' OR TRIM(ISNULL(narrative_RC_CVc,'')) <> '')
), Flags as (
--WBS IDs where no cost narrative exists in DS12
--either because the row is missing or because the narrative_cost is blank
SELECT V.WBS_ID
FROM VARsByCAL V LEFT OUTER JOIN DS12_variance_CAL C ON V.CAL_ID = C.CAL_ID
WHERE
C.upload_ID = @upload_ID
AND (TRIM(ISNULL(C.narrative_cost,'')) = '' OR C.CAL_ID IS NULL)
)
SELECT
*
FROM
DS11_variance
WHERE
upload_ID = @upload_ID
and WBS_ID IN (SELECT WBS_ID FROM Flags)
)