¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 9060292 |
| Summary | Are there non-labor budget hours in resources recorded in the same WBS as labor budget hours in cost? |
| Error message | Resource labor budget hours > 0 (Sum of budget_units where type <> Labor and UOM = h) and cost labor DB hours > 0 (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 Non-Labor Hours Recorded Alongside Cost Labor Hours" is designed to ensure that there are no non-labor budget hours recorded in the same Work Breakdown Structure (WBS) as labor budget hours. This check is performed on the DS06 Resources table.
The error message "Resource labor budget hours > 0 (Sum of budget_units where type <> Labor and UOM = h) and cost labor DB hours > 0 (Sum of DS03.BCWSi_dollars where EOC = Labor) by WBS_ID" indicates that there are non-labor budget hours (where the unit of measure is hours) recorded in the same WBS as labor budget hours.
This error is likely caused by incorrect data entry in the DS06 Resources table and the DS03 Cost table. Specifically, the fields causing the issue are the 'type' field in the DS06 Resources table and the 'EOC' field in the DS03 Cost table. The expected values for these fields should be 'Labor' for labor budget hours and any other value for non-labor budget hours.
To resolve this issue, you should review the data in these fields and ensure that labor and non-labor budget hours are not recorded in the same WBS.
¶ Why do we check this?
This test is being performed to ensure that non-labor budget hours and labor budget hours are not recorded in the same Work Breakdown Structure (WBS). The test checks if there are any instances where non-labor budget hours (where the type is not Labor and the Unit of Measure is hours) and labor budget hours (where the Estimate at Completion is Labor) are both greater than zero for the same WBS ID.
The importance of this check is to maintain the accuracy and consistency of the data. Mixing labor and non-labor hours in the same WBS can lead to confusion and inaccuracies in cost estimation and project management. It could potentially cause problems during data analysis, hence the severity is marked as a MAJOR. It is crucial to separate these two types of hours to ensure clear and accurate tracking of labor and non-labor costs.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_DoNonLaborSHoursExistWithDS03LaborSHours] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_WP WBS
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Labor' AND ISNULL(is_indirect,'') <> 'Y'
GROUP BY WBS_ID_WP
HAVING SUM(BCWSi_hours) > 0
), ScheduleWBS as (
SELECT S.WBS_ID WBS, ISNULL(S.subproject_ID, '') SubP
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.[type] <> 'labor'
AND R.EOC <> 'Labor'
AND R.UOM = 'h'
GROUP BY S.WBS_ID, ISNULL(S.subproject_ID, '')
HAVING SUM(R.budget_units) > 0
), FlagsByWBS as (
SELECT S.WBS, S.SubP
FROM ScheduleWBS S INNER JOIN CostWBS C ON C.WBS = S.WBS
), 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.[type] <> 'labor'
AND R.EOC <> 'Labor'
AND R.UOM = 'h'
AND R.budget_units > 0
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | 1. Logic adjusted to exclude indirect labor costs by filtering out records where 'is_indirect' is 'Y' in the 'CostWBS' section. 2. Logic adjusted to account for the addition of 'subproject_ID' field. 3. Logic adjusted to exclude resources with an 'EOC' value of 'Labor' in both the 'ScheduleWBS' and final selection sections, where previously there was no such filter. |