¶ Basic Information
| Key | Value |
|---|---|
| Table | DS07 IPMR Header |
| Severity | MAJOR |
| Unique ID | 9070365 |
| Summary | Has it been twelve months since an OTB-OTS date without a BCP? |
| Error message | Minimum 12 month delta between CPP status date & OTB_OTS_date without DS09.type = BCP or DS04.milestone_level between 131 & 135. |
¶ 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 "12 Months Since OTB-OTS Without BCP" is designed to ensure that there is a minimum of 12 months between the CPP status date and the OTB-OTS date in the DS07 IPMR Header table. This check is performed only when there is no record of a BCP in the DS09 CC Log table or when there are no milestones between levels 131 and 135 in the DS04 Schedule table.
If this DIQ check fails, it is likely due to one of the following reasons:
-
The OTB-OTS date in the DS07 IPMR Header table is less than 12 months after the CPP status date. This could be due to an error in data entry or a change in project timelines.
-
There is no record of a BCP in the DS09 CC Log table. This could be due to a missing or incorrect entry in the table.
-
There are no milestones between levels 131 and 135 in the DS04 Schedule table. This could be due to a lack of milestones within this range or an error in the milestone level data.
To resolve these issues, ensure that the OTB-OTS date is at least 12 months after the CPP status date, a BCP is recorded in the DS09 CC Log table, and there are milestones between levels 131 and 135 in the DS04 Schedule table.
¶ Why do we check this?
This test is being performed on the 'DS07 IPMR Header' table to ensure that there is a minimum of twelve months between the CPP status date and the OTB_OTS_date, provided there is no BCP or if the milestone level is between 131 and 135. The test is titled '12 Months Since OTB-OTS Without BCP'.
The importance of this check is to maintain the integrity of the project management data. It ensures that the project is following the correct timeline and that there are no discrepancies in the data. If there is less than a twelve-month gap, it could indicate a problem with the project timeline or data entry, which could lead to issues in project management and analysis.
The severity of this test is marked as 'MAJOR', which means that while it is not an immediate threat to the data's integrity, it could cause problems during analysis if not addressed. It is crucial to rectify this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS07_IPMR_IsOTBOTSDateOverdue] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM
DS07_IPMR_header
WHERE
upload_ID = @upload_ID
AND (
(SELECT COUNT(*) FROM DS09_CC_log WHERE upload_ID = @upload_ID AND type = 'BCP') = 0
OR (SELECT COUNT(*) FROM DS04_schedule WHERE upload_ID = @upload_id AND milestone_level BETWEEN 131 AND 135) = 0
)
AND DATEDIFF(m,CPP_status_date,OTB_OTS_date) < -12
)