¶ Basic Information
| Key | Value |
|---|---|
| Table | DS09 CC Log |
| Severity | MAJOR |
| Unique ID | 9090443 |
| Summary | Is the dollars delta for this CC Log entry misaligned with what is in the CC Log detail table? |
| Error message | dollars_delta <> SUM(DS10.dollars_delta) (by CC_log_ID). |
¶ 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 "Log Dollars Delta Misaligned With Log Detail Delta" is designed to ensure that the dollar delta values in the DS09 CC Log table align with the corresponding values in the DS10 CC Log Detail table.
The dollar delta value in the DS09 CC Log table should be equal to the sum of the dollar delta values associated with the same CC Log entry in the DS10 CC Log Detail table. If these values do not match, it indicates a potential data integrity issue.
The error message "dollars_delta <> SUM(DS10.dollars_delta) (by CC_log_ID)" suggests that the dollar delta value in the DS09 CC Log table is not equal to the sum of the dollar delta values in the DS10 CC Log Detail table for the same CC Log entry.
This discrepancy could be caused by a variety of issues, such as incorrect data entry, data processing errors, or system glitches. It is important to investigate and resolve these discrepancies to ensure the accuracy and reliability of the project management data.
¶ Why do we check this?
This test is being performed on the 'DS09 CC Log' table to ensure that the dollar delta (the difference in dollar amount) for each Change Control (CC) Log entry aligns with the sum of the dollar deltas in the CC Log detail table. The test is checking for any discrepancies between the two values, which should ideally match.
The importance of this check is to maintain the integrity and accuracy of financial data in the project management system. Any misalignment could lead to incorrect financial reporting or decision-making based on inaccurate data.
The severity level of this test is 'MAJOR', which means that while it may not prevent the data from being reviewed, any discrepancies found could potentially cause problems during data analysis. Therefore, it is recommended to address and resolve any issues identified by this test to ensure accurate and reliable data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS09_CCLog_IsDollarsDeltaMisalignedWithDS10] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CCLogDetail as (
SELECT CC_log_ID, SUM(dollars_delta) DollDelt
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_ID
GROUP BY CC_log_ID
)
SELECT
L.*
FROM
DS09_CC_log L INNER JOIN CCLogDetail D ON L.CC_log_ID = D.CC_log_ID
WHERE
upload_ID = @upload_ID
AND L.dollars_delta <> D.DollDelt
)