¶ Basic Information
| Key | Value |
|---|---|
| Table | DS07 IPMR Header |
| Severity | MAJOR |
| Unique ID | 9070363 |
| Summary | Are there UB dollars without UB transactions in the change control log? |
| Error message | UB_bgt_dollars <> 0 & no rows found in DS10 where category = UB. |
¶ 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 Without UB Change Control" is designed to ensure that there are no Undistributed Budget (UB) dollars recorded in the DS07 IPMR Header table without corresponding UB transactions in the DS10 Change Control Log.
The error message "UB_bgt_dollars <> 0 & no rows found in DS10 where category = UB" indicates that there are UB dollars recorded in the DS07 IPMR Header table (UB_bgt_dollars field), but no corresponding UB transactions were found in the DS10 Change Control Log.
This discrepancy could be due to a data entry error, where UB dollars were entered in the DS07 IPMR Header table but the corresponding transactions were not recorded in the DS10 Change Control Log. Alternatively, it could be due to a data processing error, where UB transactions in the DS10 Change Control Log were not correctly categorized as 'UB'.
To resolve this issue, ensure that for every UB dollar recorded in the DS07 IPMR Header table, there is a corresponding UB transaction in the DS10 Change Control Log. The category of these transactions should be correctly marked as 'UB'.
¶ Why do we check this?
This test is being performed on the 'DS07 IPMR Header' table to check for instances where there are Undistributed Budget (UB) dollars but no corresponding UB transactions in the change control log. The test is looking for discrepancies between the UB budget dollars and the entries in the DS10 table where the category is UB.
The importance of this check is to ensure that all budget changes are properly logged and accounted for. If there are UB dollars without corresponding transactions in the change control log, it could indicate that changes to the budget are not being properly tracked. This could lead to inaccuracies in budget reporting and financial analysis, and potentially cause issues during project management and decision-making processes.
The severity of this check is marked as 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. It is therefore recommended that this issue be addressed to ensure the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS07_IPMR_DoesUBExistWithoutDS10Transactions] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CCLogDetail as (
SELECT category
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_ID
)
SELECT
*
FROM
DS07_IPMR_header
WHERE
upload_ID = @upload_ID
AND (SELECT COUNT(*) FROM CCLogDetail WHERE category = 'UB') = 0
AND (SELECT COUNT(*) FROM CCLogDetail) > 0 --test only if there are any CC log detail rows
AND UB_bgt_dollars <> 0
)