¶ Basic Information
| Key | Value |
|---|---|
| Table | DS11 Variance |
| Severity | MAJOR |
| Unique ID | 9110485 |
| Summary | Is the CAL ID missing in the Corrective Action Log? |
| Error message | CAL_ID missing in DS12.CAL_ID list. |
¶ 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 "CAL ID Missing in Corrective Action Log" is designed to verify the presence of Corrective Action Log (CAL) IDs in the DS11 Variance table. This check is crucial to ensure that all CAL IDs listed in the DS11 Variance table are also present in the DS12 Variance CAL table.
If the DIQ check returns an error message stating "CAL_ID missing in DS12.CAL_ID list", it indicates that there are one or more CAL IDs in the DS11 Variance table that are not found in the DS12 Variance CAL table. This discrepancy could be due to data entry errors, missing entries in the DS12 Variance CAL table, or incorrect CAL IDs in the DS11 Variance table.
The fields causing this issue are the CAL_ID field in the DS11 Variance table and the CAL_ID field in the DS12 Variance CAL table. The expected values in the DS11 Variance table's CAL_ID field should match the values in the DS12 Variance CAL table's CAL_ID field.
To resolve this issue, you should cross-verify the CAL IDs in both tables and correct any discrepancies. This could involve adding missing CAL IDs to the DS12 Variance CAL table or correcting any incorrect CAL IDs in the DS11 Variance table.
¶ Why do we check this?
This test is being performed to check if the Corrective Action Log (CAL) ID is missing in the 'DS11 Variance' table. The CAL ID is a unique identifier for each corrective action, and it is crucial for tracking and managing these actions. If the CAL ID is missing, it could lead to difficulties in tracking the progress and status of corrective actions, which could potentially impact the overall project management and decision-making process.
The severity of this test is marked as 'MAJOR', which means that while it may not immediately prevent the data from being reviewed, it is likely to cause problems during analysis. This could include issues such as incorrect data associations, inability to track specific corrective actions, and potential inaccuracies in reporting and analysis. Therefore, it is important to address this issue to ensure the integrity and quality of the EVMS construction project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS11_VAR_IsCALIDMissingInDS12] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with VARsByCAL as (
SELECT WBS_ID, CAL_ID
FROM DS11_variance CROSS APPLY string_split(CAL_ID, ';')
WHERE upload_ID = @upload_ID
), Flags as (
SELECT WBS_ID
FROM VARsByCAL
WHERE CAL_ID NOT IN (
SELECT CAL_ID
FROM DS12_variance_CAL
WHERE upload_ID = @upload_ID
)
)
SELECT
*
FROM
DS11_variance
WHERE
upload_ID = @upload_ID
AND WBS_ID IN (SELECT WBS_ID FROM Flags)
)