¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100450 |
| Summary | Are the dollars delta for AUW-related transactions misaligned with the AUW dollar value in the IPMR header? |
| Error message | Sum of dollars_delta where AUW = Y <> DS07.AUW_dollars. |
¶ 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 "AUW Transaction Dollars Misaligned With Project Level AUW" is designed to ensure that the dollar value changes (dollars_delta) for Authorized Unpriced Work (AUW) transactions in the DS10 CC Log Detail table align with the AUW dollar value in the DS07 IPMR Header table.
If an error is flagged by this DIQ check, it indicates a discrepancy between the sum of AUW-related transaction dollar changes in the DS10 table and the AUW dollar value in the DS07 table. This could be due to incorrect entries in either of the tables, or a mismatch between the two.
The fields causing the issue are the 'dollars_delta' field in the DS10 CC Log Detail table and the 'AUW_dollars' field in the DS07 IPMR Header table. The expected value is that the sum of 'dollars_delta' where AUW equals 'Y' in the DS10 table should be equal to the 'AUW_dollars' in the DS07 table.
To resolve this issue, review the AUW-related transactions in the DS10 CC Log Detail table and the AUW dollar value in the DS07 IPMR Header table to ensure they are correctly entered and align with each other.
¶ Why do we check this?
This test is being performed to ensure that the dollar value changes (delta) for Authorized Unpriced Work (AUW) transactions align with the AUW dollar value in the Integrated Program Management Report (IPMR) header. The test is checking if the sum of dollar deltas where AUW equals 'Y' is not equal to the AUW dollar value in the DS07 table.
The importance of this check is to maintain the accuracy and consistency of financial data across different parts of the project. If the AUW transaction dollars are misaligned with the project level AUW, it could lead to incorrect financial reporting and decision making. This could potentially cause problems during analysis, hence the severity level is marked as 'MAJOR'. It is crucial to address this issue to ensure the integrity and reliability of the project's financial data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_AreAUWDollarDeltasMisalignedWithDS07AUW] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with AUW as (
SELECT ISNULL(AUW_dollars,0) AUW
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
), AUWTransactions as (
SELECT SUM(ISNULL(dollars_delta,0)) DollarsDelta
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_ID AND AUW = 'Y'
)
SELECT
*
FROM
DS10_CC_log_detail
WHERE
upload_ID = @upload_ID
AND AUW = 'Y'
AND (SELECT AUW FROM AUW) <> (SELECT DollarsDelta FROM AUWTransactions)
)