¶ Basic Information
| Key | Value |
|---|---|
| Table | DS07 IPMR Header |
| Severity | MINOR |
| Unique ID | 9070366 |
| Summary | Is profit fee misaligned with the dollars delta for profit fee transactions in the CC log detail? |
| Error message | profit_fee_dollars <> sum of DS10.dollars_delta where category = profit-fee. |
¶ 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 "Profit Fee Misaligned With CC Log Detail" is designed to ensure that the profit fee recorded in the DS07 IPMR Header table aligns with the sum of the dollar deltas for profit fee transactions in the DS10 CC Log Detail table.
If an error is flagged by this DIQ check, it indicates a discrepancy between the profit fee recorded in the DS07 IPMR Header table and the sum of the dollar deltas for profit fee transactions in the DS10 CC Log Detail table.
The fields causing the issue are 'profit_fee_dollars' in the DS07 IPMR Header table and 'dollars_delta' in the DS10 CC Log Detail table where the category is 'profit-fee'.
The expected value for 'profit_fee_dollars' in the DS07 IPMR Header table should be equal to the sum of 'dollars_delta' in the DS10 CC Log Detail table for entries where the category is 'profit-fee'. If these values do not match, it suggests that there may be an error in the recording or calculation of profit fees.
To resolve this issue, review the entries in both tables to ensure that profit fees are being correctly recorded and calculated.
¶ Why do we check this?
This test is being performed to ensure that the profit fee recorded in the 'DS07 IPMR Header' table aligns with the sum of the 'dollars_delta' for profit fee transactions in the 'DS10' table. The test is checking for any discrepancies between these two values, which should ideally be equal.
The importance of this check lies in maintaining the accuracy and consistency of financial data across different tables. If the profit fee in the header does not match the sum of the profit fee transactions, it could lead to inaccurate financial reporting and analysis.
The severity of this test is marked as 'MINOR', which means it is not a critical error that would prevent data review, but it is a potential issue that could cause minor problems or indicate that the data does not adhere to all best practices. Therefore, it is recommended to address this issue to ensure the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS07_IPMR_IsProfitFeeAlignedWithDS10] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM
DS07_IPMR_header
WHERE
upload_ID = @upload_ID
AND profit_fee_dollars <> (
SELECT SUM(dollars_delta)
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_ID AND category = 'profit-fee'
)
)