¶ Basic Information
| Key | Value |
|---|---|
| Table | DS07 IPMR Header |
| Severity | MAJOR |
| Unique ID | 9070369 |
| Summary | Are the PM and MR equal to something other than CBB plus overrun? |
| Error message | CBB_dollars != sum of DS08.budget_dollars + UB_bgt + MR_bgt + MR_rpg - sum DS03.BAC_rpg. |
¶ 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 "PMB + MR <> CBB + Overrun" is designed to ensure that the sum of the Performance Measurement Baseline (PMB) and Management Reserve (MR) is not equal to something other than the sum of the Contract Budget Base (CBB) and any overrun. This check is performed on the DS07 IPMR Header table.
The error message "CBB_dollars != sum of DS08.budget_dollars + UB_bgt + MR_bgt + MR_rpg - sum DS03.BAC_rpg" indicates that the total Contract Budget Base (CBB) in dollars is not equal to the sum of the budget dollars from DS08, the Undistributed Budget (UB), the Management Reserve budget (MR_bgt), and the Management Reserve at the reporting period (MR_rpg), minus the sum of the Budget at Completion at the reporting period (BAC_rpg) from DS03.
This discrepancy could be caused by incorrect or missing values in any of the fields mentioned above. For example, if the budget dollars in DS08 are not correctly calculated, or if the Management Reserve budget or the Undistributed Budget are not accurately reported, it could lead to a mismatch between the CBB and the sum of the PMB and MR.
The expected values for these fields would be such that the sum of the PMB and MR equals the sum of the CBB and any overrun. If this is not the case, it may indicate a problem with the data in these fields that needs to be addressed.
¶ Why do we check this?
This test is being performed on the 'DS07 IPMR Header' table to ensure that the sum of the Performance Measurement Baseline (PMB) and Management Reserve (MR) is not equal to something other than the Contract Budget Base (CBB) plus overrun. This is a critical check to ensure that the budgeting and cost management of the project is being handled correctly.
The importance of this check lies in its ability to detect potential discrepancies in the project's financial data. If the PMB and MR do not equal the CBB plus any overrun, it could indicate errors in budget allocation, cost reporting, or financial management. This could lead to inaccurate financial projections, misallocation of resources, or other financial issues that could negatively impact the project's success.
The severity of this check is marked as 'MAJOR', which means that while it may not immediately prevent the data from being reviewed, it is likely to cause problems during analysis if not addressed. It is therefore important to correct this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS07_IPMR_ArePMBAndMRNotEqToCBB] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with WADBAC as (
SELECT
SUM(ISNULL(budget_labor_dollars,0) +
ISNULL(budget_material_dollars,0) +
ISNULL(budget_ODC_dollars,0) +
ISNULL(budget_indirect_dollars,0) +
ISNULL(budget_subcontract_dollars,0)
) as BAC
FROM DS08_WAD
WHERE upload_ID = @upload_ID
), CostBAC as (
SELECT SUM(ISNULL(BCWSi_dollars,0)) BAC
FROM DS03_cost
WHERE upload_ID = @upload_ID
), Overrun as (
SELECT SUM(ISNULL(BAC_Rpg,0)) Overrun
FROM DS03_cost
WHERE upload_ID = @upload_ID
)
SELECT
*
FROM
DS07_IPMR_header
WHERE
upload_ID = @upload_ID
AND ABS(ISNULL(CBB_dollars,0) -
COALESCE((SELECT TOP 1 BAC FROM WADBAC),(SELECT TOP 1 BAC FROM CostBAC)) -
ISNULL(UB_bgt_dollars,0) -
ISNULL(MR_bgt_dollars,0) -
ISNULL(MR_rpg_dollars,0) +
(SELECT TOP 1 Overrun FROM Overrun)
) > 100
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to replace 'budget_overhead_dollars' with 'budget_indirect_dollars'. |