¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100459 |
| Summary | Are the dollars delta for UB-related transactions misaligned with the UB dollar value in the IPMR header? |
| Error message | Sum of dollars_delta where category = UB <> DS07.UB_bgt_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 "UB Transaction Dollars Misaligned With Project Level UB" is designed to ensure that the dollar value changes (dollars_delta) for Undistributed Budget (UB) related transactions in the DS10 CC Log Detail table align with the UB 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 the dollar value changes for UB-related transactions and the UB dollar value at the project level. This discrepancy could be due to incorrect entries in the 'dollars_delta' field for UB-related transactions in the DS10 CC Log Detail table or in the 'UB_bgt_dollars' field in the DS07 IPMR header table.
The expected value for the sum of 'dollars_delta' for UB-related transactions in the DS10 CC Log Detail table should be equal to the 'UB_bgt_dollars' in the DS07 IPMR header table. If these values do not match, it suggests that there may be errors in the data entry or processing for these fields.
To resolve this issue, review the entries in the 'dollars_delta' field for UB-related transactions in the DS10 CC Log Detail table and the 'UB_bgt_dollars' field in the DS07 IPMR header table to ensure they are accurate and correctly aligned.
¶ Why do we check this?
This test is being performed to ensure that the Unpriced Budget (UB) transaction dollars are correctly aligned with the project level UB. The Unpriced Budget refers to the budget for which definitive cost and schedule estimates have not been established. The test checks if the sum of the dollar differences (dollars_delta) for UB-related transactions is not equal to the UB budget dollars in the Integrated Program Management Report (IPMR) header.
The importance of this check is to ensure that the budget data is consistent and accurate across different parts of the project management data. If the UB transaction dollars are misaligned with the project level UB, it could lead to incorrect financial analysis and decision-making. The severity level is a MAJOR, which means that this issue is likely to cause problems during analysis if not addressed. It may not stop the data from being reviewed, but it could potentially lead to inaccurate conclusions or decisions based on the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_AreUBDollarDeltasMisalignedWithDS07UB] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with UBBgt as (
SELECT ISNULL(UB_bgt_dollars,0) UB
FROM DS07_IPMR_header
WHERE upload_ID = @upload_id
), UBDelta as (
SELECT SUM(ISNULL(dollars_delta,0)) DDelt
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_id AND category = 'UB'
)
SELECT
*
FROM
DS10_CC_log_detail
WHERE
upload_ID = @upload_id
AND category = 'UB'
AND (SELECT UB FROM UBBgt) <> (SELECT DDelt FROM UBDelta)
)