| Key | Value |
|---|---|
| Table | DS00 Metadata |
| Severity | CRITICAL |
| Unique ID | 1000001 |
| Summary | Is the CPP Status Date missing from the period dates in the cost file? |
| Error message | DS00.CPP_Status_Date not in DS03.period_date list. |
The Data Integrity and Quality (DIQ) check titled "CPP Status Date Missing From Cost Periods" is designed to ensure that the CPP Status Date is included in the list of period dates in the DS03 Cost table.
If the CPP Status Date is missing from the period dates, it could indicate an error in data entry or a discrepancy between the metadata and cost data. This could potentially lead to inaccuracies in project management and cost tracking.
The fields causing the issue are the 'CPP_status_date' field and the 'period_date' field in the DS03 Cost table. The expectation is that the CPP Status Date should be present in the list of period dates.
If the CPP Status Date is not found in the period dates, or if the CPP Status Date is later than the latest period date in the cost file, the DIQ check will flag this as a potential issue. This is to ensure that the cost periods are accurately reflecting the status of the project as indicated in the metadata.
This test is being performed to ensure that the CPP status date represents a real period date. This ensures that data is current and prevents accidental entry of the wrong status date or uploading of data after the status date.
The severity of this check is marked as 'CRITICAL', which is the highest level of severity. This means that if this issue is not resolved, the data cannot be reviewed or analyzed accurately. It is crucial to fix this error to maintain the integrity and quality of the EVMS construction project management data.
CREATE FUNCTION [dbo].[fnDIQ_DS00_Meta_IsCPPMissingInDS03PeriodDate] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Periods as (
SELECT cpp_status_date, period_date
FROM DS03_cost
WHERE upload_ID = @upload_ID
)
SELECT
*
FROM
DummyRow_Get(@upload_ID)
WHERE
-- are there any rows where CPP SD = period_dates? If not, possible flag.
(SELECT COUNT(*) FROM Periods WHERE period_date = CPP_status_date) = 0
-- is the CPP SD after the last period_date in the cost file? If not, flag.
AND (SELECT COUNT(*) FROM Periods WHERE cpp_status_date > (SELECT MAX(period_date) from periods)) = 0
)