¶ Basic Information
| Key | Value |
|---|---|
| Table | DS02 OBS |
| Severity | MINOR |
| Unique ID | 9020041 |
| Summary | Is this CA marked as external? |
| Error message | CA is marked as external. |
¶ 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 "CA Marked as External" is designed to verify the integrity of data in the DS02 OBS table. This check specifically looks at whether a Control Account (CA) is marked as external.
An error message "CA is marked as external" will be generated if a CA is indeed marked as external. This is likely caused by the 'external' field in the DS02 OBS table being set to 'Y' (Yes) for a CA.
The fields involved in this check are the 'OBS_ID' field from both the DS02 OBS and DS01 WBS tables, the 'external' field from the DS02 OBS table, and the 'type' field from the DS01 WBS table.
The expected values for these fields should be as follows:
- The 'OBS_ID' field in both tables should not be null or empty.
- The 'external' field in the DS02 OBS table should not be marked as 'Y' for a CA.
- The 'type' field in the DS01 WBS table should be marked as 'CA'.
If these conditions are not met, it indicates that there may be an issue with the data integrity and quality in the DS02 OBS table.
¶ Why do we check this?
This test is being performed to check if the Control Account (CA) in the DS02 OBS (Organizational Breakdown Structure) table is marked as external. The severity level is an MINOR, which means it's not a critical issue but it might cause minor problems or indicate that the data doesn't follow all best practices.
The importance of this check is to ensure the correct classification of the Control Account. If a CA is marked as external, it may not be directly managed within the project, which could impact the accuracy of project management data and subsequent analysis. This check helps to maintain the integrity and quality of the EVMS (Earned Value Management System) construction project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS02_OBS_IsCAMarkedAsExternal] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
O.*
FROM
DS02_OBS O
INNER JOIN DS01_WBS W ON O.OBS_ID = W.OBS_ID
WHERE O.upload_ID = @upload_ID
AND TRIM(O.OBS_ID)<>''
AND O.OBS_ID IS NOT NULL
AND O.[external] = 'Y'
AND W.type = 'CA'
)