¶ Basic Information
| Key | Value |
|---|---|
| Table | DS07 IPMR Header |
| Severity | MAJOR |
| Unique ID | 9070340 |
| Summary | Is there an OTB/OTS date without schedule reprogramming? |
| Error message | OTB_OTS_date is not null/blank & COUNT(DS04.RPG = Y) = 0. |
¶ 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 "OTB / OTS Without Schedule Reprogramming" is designed to ensure that there is no Over Target Baseline (OTB) or Over Target Schedule (OTS) date present without an associated schedule reprogramming. This check is performed on the DS07 IPMR Header table.
The error message "OTB_OTS_date is not null/blank & COUNT(DS04.RPG = Y) = 0" indicates that an OTB or OTS date has been entered in the DS07 IPMR Header table (OTB_OTS_date field is not null or blank), but there is no corresponding schedule reprogramming in the DS04 Schedule table (the count of records where RPG equals 'Y' is zero).
This discrepancy could be caused by a data entry error, where a date was entered for an OTB or OTS but the corresponding schedule reprogramming was not marked in the DS04 Schedule table. Alternatively, it could be due to a data processing error, where the reprogramming was marked but not properly linked to the OTB or OTS date.
To resolve this issue, ensure that every OTB or OTS date in the DS07 IPMR Header table has a corresponding schedule reprogramming marked as 'Y' in the DS04 Schedule table.
¶ Why do we check this?
This test is being performed on the 'DS07 IPMR Header' table to check for instances where there is an Over Target Baseline (OTB) or Over Target Schedule (OTS) date present, but no corresponding schedule reprogramming. The test is looking for records where the 'OTB_OTS_date' field is not null or blank, but the count of records in the 'DS04' table where 'RPG' equals 'Y' (indicating schedule reprogramming) is zero.
The severity of this test is marked as 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. This is because the presence of an OTB or OTS date without corresponding schedule reprogramming could indicate a discrepancy in the data, as it is generally expected that any OTB or OTS would be accompanied by a schedule reprogramming.
The importance of this check is to ensure the integrity and consistency of the project management data. It helps to identify potential issues in the data that could lead to inaccurate analysis or reporting. By identifying and addressing these issues, it helps to maintain the accuracy and reliability of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS07_IPMR_DoesOTBOTSDateExistWithoutDS04Rpg] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM
DS07_IPMR_header
WHERE
upload_ID = @upload_ID
AND (SELECT COUNT(*) FROM DS04_schedule WHERE upload_ID = @upload_ID AND RPG = 'Y') = 0
AND OTB_OTS_date IS NOT NULL
)