¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 9060291 |
| Summary | Are the labor budget units in resources misaligned with the labor budget hours in cost? |
| Error message | Resource labor budget units (budget_units where EOC or type = Labor) <> cost labor DB (SUM of DS03.BCWSi_dollars where EOC = Labor) by WBS_ID. |
¶ 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 Labor Units Misaligned with Cost" is designed to identify discrepancies between the labor budget units in the DS06 Resources table and the labor budget hours in the DS03 Cost table.
The test is performed by comparing the sum of labor budget units (where the EOC or type is 'Labor') in the DS06 Resources table with the sum of labor budget hours (where EOC is 'Labor') in the DS03 Cost table. The comparison is done by the Work Breakdown Structure ID (WBS_ID).
If the test identifies a discrepancy, it means that the labor budget units in the DS06 Resources table do not match the labor budget hours in the DS03 Cost table for the same WBS_ID. This could be due to an error in data entry or a miscalculation in either of the tables.
The expected values for the labor budget units in the DS06 Resources table should be equal to the labor budget hours in the DS03 Cost table for each WBS_ID. If the values are not equal, it indicates a misalignment between the labor resources and cost, which could potentially lead to inaccurate project cost estimations and resource allocations.
To resolve this issue, you should review the labor budget units in the DS06 Resources table and the labor budget hours in the DS03 Cost table for each WBS_ID, and correct any discrepancies to ensure data integrity and quality.
¶ Why do we check this?
This test is being performed to ensure that the labor budget units in the resources are correctly aligned with the labor budget hours in cost. This is important because any misalignment could lead to inaccurate cost projections and budgeting errors in the EVMS construction project management data.
The severity of this check is marked as a MAJOR. This means that while it may not immediately prevent the data from being reviewed, any discrepancies found could potentially cause problems during the analysis of the data. It is crucial to address these issues to ensure accurate and reliable project management data.
In essence, this check is important to maintain the integrity and quality of the data, which in turn supports effective decision-making and project management within the US Department of Energy.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreLaborSUnitsMisalignedWithDS03LaborSHours] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_WP WBS, SUM(BCWSi_hours) DB
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Labor' AND ISNULL(is_indirect,'') <> 'Y'
GROUP BY WBS_ID_WP
), ScheduleWBS as (
SELECT S.WBS_ID WBS, ISNULL(S.subproject_ID,'') SubP, SUM(R.budget_units) Budget
FROM DS04_schedule S INNER JOIN DS06_schedule_resources R ON S.task_ID = 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'
AND (R.EOC = 'Labor' OR R.[type] = 'Labor')
GROUP BY S.WBS_ID, ISNULL(S.subproject_ID,'')
), FlagsByWBS as (
SELECT S.WBS, S.SubP
FROM ScheduleWBS S INNER JOIN CostWBS C ON C.WBS = S.WBS AND C.DB <> S.Budget
), FlagsByTask as (
SELECT S.task_ID, 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
WHERE
R.upload_id = @upload_ID
AND R.schedule_type = 'BL'
AND (R.EOC = 'Labor' OR R.[type] = 'Labor')
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for addition of 'is_indirect' and 'subproject_id' fields. |