¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MINOR |
| Unique ID | 9030073 |
| Summary | Are there period dates after CD-4 approved? |
| Error message | Period Dates found after CD-4 approved (DS04.milestone_level = 190). |
¶ 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 "Cost Periods Found After CD-4 Approved" is designed to ensure that there are no period dates in the DS03 Cost table that occur after the CD-4 approval date. This is important because once a project reaches the CD-4 milestone level (as indicated in the DS04 Schedule table), no further cost periods should be recorded.
If this DIQ check fails, it means that there are entries in the DS03 Cost table with period dates that occur after the CD-4 approval date. This could be due to an error in data entry or a delay in updating the DS04 Schedule table to reflect the CD-4 approval.
The fields causing the issue are the 'period_date' field in the DS03 Cost table and the 'ES_Date' and 'EF_Date' fields in the DS04 Schedule table. The expected values for these fields should be such that all 'period_date' entries in the DS03 Cost table are earlier than or equal to the CD-4 approval date (which is the later of 'ES_Date' and 'EF_Date' in the DS04 Schedule table where 'milestone_level' is 190 and 'schedule_type' is 'BL').
To resolve this issue, review the entries in both tables and correct any discrepancies.
¶ Why do we check this?
This test is being performed to check if there are any cost periods that are recorded after the CD-4 approval in the DS03 Cost table. The CD-4 approval signifies the completion of a project, so any cost periods after this point could indicate an error or inconsistency in the data.
The importance of this check is to ensure the accuracy and consistency of the project cost data. If costs are being recorded after the project completion, it could lead to inaccurate financial reporting or analysis. This could potentially impact decision-making processes and the overall management of the project.
The severity of this test is marked as an MINOR. This means that while it's not a critical error that would prevent the data from being reviewed, it's still a potential issue that could cause minor problems or indicate that the data isn't following best practices. It's a signal that the data should be reviewed for potential inaccuracies or inconsistencies.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_DoPeriodDatesExistAfterDS04MS190] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
-- Insert statements for procedure here
SELECT
*
FROM
DS03_Cost C
WHERE
upload_ID = @upload_ID
AND period_date > (
SELECT COALESCE(ES_Date,EF_Date)
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND milestone_level = 190 AND schedule_type = 'BL'
)
)