¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100460 |
| Summary | Are there OTB/OTS transactions when no OTB/OTS date exists? |
| Error message | Sum of dollars_delta where category = OTB, OTS, or OTB-OTS <> 0 & DS07.OTB_OTS_date is missing. |
¶ 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 or OTS Transaction Dollars Without OTB-OTS Date" is designed to ensure that there are no OTB/OTS transactions recorded in the DS10 CC Log Detail table when there is no corresponding OTB/OTS date in the DS07 IPMR Header table.
The error message "Sum of dollars_delta where category = OTB, OTS, or OTB-OTS <> 0 & DS07.OTB_OTS_date is missing" indicates that there are transactions with a category of OTB, OTS, or OTB-OTS in the DS10 CC Log Detail table where the dollars_delta field is not equal to zero, but there is no corresponding OTB/OTS date in the DS07 IPMR Header table.
This discrepancy could be caused by a data entry error, where a transaction was recorded in the DS10 CC Log Detail table but the corresponding date was not entered in the DS07 IPMR Header table. Alternatively, it could be due to a system error where the date was not correctly linked between the two tables.
To resolve this issue, you should ensure that for every OTB, OTS, or OTB-OTS transaction in the DS10 CC Log Detail table, there is a corresponding OTB/OTS date in the DS07 IPMR Header table. If the date is missing, it should be added. If the date is present in the DS07 IPMR Header table but not correctly linked to the transaction in the DS10 CC Log Detail table, the link should be corrected.
¶ Why do we check this?
This test is being performed on the 'DS10 CC Log Detail' table to check for any Over Target Baseline (OTB) or Over Target Schedule (OTS) transactions that have been recorded without an associated OTB/OTS date. The severity level is 'MAJOR', which means that while it may not prevent the data from being reviewed, it could potentially cause problems during analysis.
The importance of this check lies in ensuring the completeness and accuracy of the data. OTB and OTS transactions are significant events in project management, indicating that the project has exceeded its original cost or schedule targets. Therefore, it's crucial to have a corresponding date for each of these transactions to understand when these overruns occurred and to facilitate accurate tracking and analysis of project performance. If the OTB/OTS date is missing, it could lead to incorrect conclusions about the project's status and progress.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_DoOTBOTSDollarsExistWithoutDS07OTBOTSDate] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM
DS10_CC_log_detail
WHERE
upload_ID = @upload_id
AND category IN ('OTB', 'OTS', 'OTB-OTS')
-- check if there is an OTB/OTS date. if so, don't run.
AND EXISTS (
SELECT 1
FROM DS07_IPMR_header
WHERE upload_ID = @upload_id AND OTB_OTS_date IS NULL
)
-- check if there are OTB/OTS transactions. if so, run.
AND EXISTS (
SELECT 1
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_id
AND category IN ('OTB', 'OTS', 'OTB-OTS')
AND dollars_delta <> 0
)
)