¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MINOR |
| Unique ID | 9030095 |
| Summary | Is this period date after PMB end? |
| Error message | Period_date > DS04.ES_Date for milestone_level = 175. |
¶ 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 Period After PMB End" is designed to ensure that the period date in the DS03 Cost table is not later than the end date of the Performance Measurement Baseline (PMB). This check is important because it helps to maintain the accuracy and reliability of the project's cost data.
The error message "Period_date > DS04.ES_Date for milestone_level = 175" indicates that the period date in the DS03 Cost table is later than the end date in the DS04 Schedule table for the milestone level 175. This could be due to an incorrect entry in either the period date in the DS03 Cost table or the end date in the DS04 Schedule table.
The fields causing this issue are the 'period_date' field in the DS03 Cost table and the 'ES_Date' field in the DS04 Schedule table. The expected value for the 'period_date' field should be a date that is not later than the 'ES_Date' field.
To resolve this issue, you should review and correct the dates in these fields as necessary. Ensure that the period date in the DS03 Cost table is not later than the end date in the DS04 Schedule table for the milestone level 175. This will help to maintain the integrity and quality of the project's cost data.
¶ Why do we check this?
This test is being performed on the 'DS03 Cost' table to check if the period date is after the Performance Measurement Baseline (PMB) end date. The test is checking if 'Period_date' is greater than 'DS04.ES_Date' for a milestone level of 175.
The importance of this check is to ensure that the cost period does not extend beyond the PMB end date. If the cost period is after the PMB end date, it could indicate that there are costs associated with the project that were not included in the original baseline, which could lead to inaccurate cost performance analysis.
The severity of this test is marked as an 'MINOR'. This means that while it is not a critical error that would prevent the data from being reviewed, it is still a potential issue that could cause minor problems or indicate that the data does not follow all best practices. It is advisable to address this issue to ensure the accuracy and integrity of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsPeriodAfterPMBEnd] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with PMBEndDates as (
SELECT schedule_type, COALESCE(MAX(ES_Date),MAX(EF_DATE)) MSDate
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND milestone_level = 175
GROUP BY schedule_type
), Flags as (
SELECT WBS_ID_CA, WBS_ID_WP, period_date, EOC
FROM CostRollupByEOC_Get(@upload_ID)
WHERE (( -- if BCWS > 0, compare period_date to BL schedule
(BCWSi_dollars > 0 OR BCWSi_FTEs > 0 OR BCWSi_hours > 0) AND
period_date > (SELECT MSDate from PMBEndDates WHERE schedule_type = 'BL')
) OR ( -- if BCWP, ACWP, or ETC > 0, compare period_date to FC schedule
(
BCWPi_dollars > 0 OR BCWPi_FTEs > 0 OR BCWPi_hours > 0 OR
ACWPi_dollars > 0 OR ACWPi_FTEs > 0 OR ACWPi_hours > 0 OR
ETCi_dollars > 0 OR ETCi_FTEs > 0 OR ETCi_hours > 0
) AND
period_date > (SELECT MSDate from PMBEndDates WHERE schedule_type = 'FC')
))
)
SELECT
C.*
FROM
DS03_Cost C INNER JOIN Flags F ON C.WBS_ID_CA = F.WBS_ID_CA
AND C.WBS_ID_WP = F.WBS_ID_WP
AND C.EOC = F.EOC
AND C.period_date = F.period_date
WHERE
upload_ID = @upload_ID
)