| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100457 |
| Summary | Are the dollars delta for MR-related transactions misaligned with the MR dollar value in the IPMR header? |
| Error message | Sum of dollars_delta where category = MR <> DS07.MR_dollars. |
The error in the data is potentially due to a misalignment between two datasets, DS10 CC Log Detail and DS07 IPMR Header. It appears that the total amount of dollars pertaining to 'MR' (Material Release) transactions in DS10 does not match with the corresponding 'MR' dollar value from the IPMR Header (DS07).
DS07 consists of MR_bgt_dollars which should ideally be equivalent to the sum of dollars_delta, for 'MR' category, in DS10. Therefore, the discrepancy between these two values is likely causing the error.
The DIQ test firstly isolates the 'MR' dollar value in DS07 (as MRBgt). In a similar fashion, it extracts the dollars_delta values from DS10 CC Log Details table where the category is 'MR' and cumulates these 'MR' dollar values (as MRDelta). It then checks whether the sum of 'MR' dollar values from DS10 (MRDelta) is equal to the corresponding 'MR' dollar value in DS07 (MRBgt).
If there is a discrepancy between the 'MR' values from both the tables, all the rows associated with the 'MR' category from DS10 are returned as failing the DIQ check.
It should be noted that the test only runs when there are rows in DS10. If the table is empty, the test will not be performed, hence will not highlight the error.
In order to ensure data integrity and quality checks pass, it's important to ensure that the 'MR' Dollar Transactions align correctly with the Project level 'MR' values. Any anomalies or discrepancies can cause potential issues and should be corrected before proceeding with further analysis. The DS10 CC Log Detail's dollars_delta field for 'MR' category transactions should sum up to the corresponding DS07 MR_bgt_dollars value.
This data quality check, "MR Transaction Dollars Misaligned With Project Level MR", is essential as it ensures that the processed data regarding Material Requisition (MR) transactions aligns with the project's overall or header value. Data integrity and quality are assured in two major ways here.
First, by verifying that the sum of the dollar_delta where category equals MR is equal to the MR_dollars in the DS07 (IPMR header), it ensures measurements are recorded and reported correctly. An inconsistency between the MR transactions' summed value and the project level MR might indicate a data entry error, missing transactions, or calculation mistakes occurring in the MR dollar values. Thus, this check validates the data's completeness, accuracy, and consistency.
Second, the DIQ check improves the accountability of the data by ensuring that all the dollar deltas linked to MR transactions are correctly reflected in the project-level MR, thereby fostering trust in the data.
The given DIQ check is in line with the DOE Order 413.3b guidelines, which emphasize project monitoring and control mechanisms. It helps protect the integrity of the project's financial tracking, which is vital for project performance reviews and overall project management success.
Finally, this check not only identifies the rows of the data that fail but provides a mechanism (UID '9100457') for managing and tracking the issue until it's resolved, enhancing data reliability and project management efficiency. Improving data quality and consistency will deliver accurate and reliable reporting, which is mandatory for better decision-making processes.
Explanation:
The Data Integrity Quality (DIQ) test identified as 'MR Transaction Dollars Misaligned With Project Level MR' checks for consistency between the Management Reserve (MR) transactions in Deltek Cobra and the overall MR dollar value reported in the Integrated Program Management Report (IPMR). The issue arises when the sum of the MR related transactions, i.e., the total dollar changes where the category is MR (Management Reserve), does not match the total MR dollars reported in the IPMR.
To avoid this issue in Deltek Cobra, you might need to reconcile your MR transactions with the project level MR budget value.
Fixing Steps:
Identify the rows that failed the DIQ check from your DIQ Reports in PARS. Specifically, check the DS10 CC log detail table for rows categorized as 'MR', management reserve.
Open Deltek Cobra and navigate to your project.
Review the project's MR budget and the detailed log of MR transactions. This can usually be found in the project's budgeting or cost control sections.
Compare the total dollar value from the MR transactions with the overall MR dollar value reported in the project's IPMR.
If a difference is observed, determine which transactions are causing the discrepancy. This could be due to incorrect transaction entries or modified budgets that have not been correctly reported.
Correct the discrepancies by updating the transaction values, adding or deleting transactions, or adjusting the project's MR budget to reflect the correct total MR dollar value.
After the changes are made, re-export the data and rerun the DIQ check. If your adjustments were correct, the error should not repeat.
Remember to maintain a detailed log of MR transactions and to update the project's budget whenever changes to the MR occur to avoid such discrepancies.
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_AreMRDollarDeltasMisalignedWithDS07MR] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with MRBgt as (
select ISNULL(MR_bgt_dollars,0) MR
from DS07_IPMR_header
where upload_ID = @upload_ID
), CCLogDetail as (
select dollars_delta, category
from DS10_CC_log_detail
where upload_ID = @upload_ID
), MRDelta as (
select SUM(dollars_delta) MR
from CClogDetail
where category = 'MR'
)
SELECT
*
FROM
DS10_CC_log_detail
WHERE
upload_ID = @upload_ID
AND category = 'MR'
AND (SELECT MR FROM MRDelta) <> (SELECT MR FROM MRBgt)
AND (SELECT COUNT(*) FROM CCLogDetail) > 0 -- run only if there are rows in DS10
)