¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 9060290 |
| Summary | Are the resource budget dollars misaligned with what is in cost? |
| Error message | Resource budget_dollars <> cost DB (SUM of DS03.BCWSi_dollars) by WBS_ID & EOC. |
¶ 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 "Resource Budgets Misaligned with Cost" is designed to identify discrepancies between the resource budget dollars and the cost database (DB) in the DS06 Resources and DS03 Cost tables. This check is performed by comparing the sum of BCWSi_dollars in the DS03 Cost table with the budget_dollars in the DS06 Resources table for each Work Breakdown Structure (WBS) ID and Estimate at Completion (EOC).
If the DIQ check identifies a discrepancy, it means that the resource budget dollars do not match the cost DB for a specific WBS ID and EOC. This could be due to an error in data entry, a miscalculation, or a misalignment between the budgeting and costing processes.
The fields causing the issue are the BCWSi_dollars field in the DS03 Cost table and the budget_dollars field in the DS06 Resources table. The expected values for these fields should be equal for each WBS ID and EOC. If they are not, it indicates a misalignment between the resource budget and the cost DB.
To resolve this issue, you should review the budgeting and costing processes for the affected WBS ID and EOC, and ensure that the resource budget dollars and cost DB are correctly calculated and aligned.
¶ Why do we check this?
This test is being performed to ensure that the budgeted resources for the project align with the actual costs. In the context of the EVMS (Earned Value Management System), this is crucial for accurate project management and forecasting. The test is checking if the sum of the budgeted cost for work scheduled (BCWSi_dollars) in the DS03 table is not equal to the resource budget dollars for each Work Breakdown Structure ID (WBS_ID) and Estimate at Completion (EOC).
The importance of this check is to identify any discrepancies between the planned budget and the actual cost. If these values are misaligned, it could lead to inaccurate project cost projections, which could potentially impact the project's financial management and completion timeline.
The severity of this check is marked as an MINOR. This means that while it may not immediately halt data review or analysis, it could potentially cause minor problems or indicate that the data does not adhere to all best practices. It's a signal to review the data and correct any discrepancies to ensure accurate project management and financial forecasting.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreSDollarsMisalignedWithDS03SDollars] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_WP WBS, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END as EOC, SUM(BCWSi_dollars) DB
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_WP, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END
), ScheduleWBS as (
SELECT S.WBS_ID WBS, R.EOC, ISNULL(S.subproject_ID,'') SubP, SUM(R.budget_dollars) Budget
FROM DS04_schedule S INNER JOIN DS06_schedule_resources R ON S.task_ID = R.task_ID AND ISNULL(S.task_ID,'') = ISNULL(R.task_ID,'') AND ISNULL(S.subproject_ID,'') = ISNULL(R.subproject_ID,'')
WHERE S.upload_ID = @upload_ID
AND R.upload_ID = @upload_ID
AND R.schedule_type = 'BL'
AND S.schedule_type = 'BL'
GROUP BY S.WBS_ID, R.EOC, ISNULL(S.subproject_ID,'')
), FlagsByWBS as (
SELECT S.WBS, S.EOC, S.SubP
FROM ScheduleWBS S INNER JOIN CostWBS C ON C.WBS = S.WBS
AND C.EOC = S.EOC
AND C.DB <> S.Budget
), FlagsByTask as (
SELECT S.task_ID, F.EOC, F.SubP
FROM DS04_schedule S INNER JOIN FlagsByWBS F ON S.WBS_ID = F.WBS AND ISNULL(S.subproject_ID,'') = F.SubP
WHERE upload_ID = @upload_ID AND schedule_type = 'BL'
)
SELECT
R.*
FROM
DS06_schedule_resources R INNER JOIN FlagsByTask F ON R.task_ID = F.task_ID
AND ISNULL(R.subproject_ID,'') = F.SubP
AND R.EOC = F.EOC
WHERE
R.upload_id = @upload_ID
AND R.schedule_type = 'BL'
AND R.EOC = F.EOC
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for addition of 'is_indirect' and 'subproject_id' fields. |