¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 9060302 |
| Summary | Are there resource labor performance units recorded without labor actual hours in cost at the WP level? |
| Error message | Resource labor performance units > 0 (actual_units where EOC or type = Labor) while cost labor actuals hours = 0 (SUM of DS03.ACWPi_hours where EOC = Labor) by WBS_ID_WP. |
¶ 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.
This Data Integrity and Quality (DIQ) check is designed to ensure that for every Work Breakdown Structure (WBS) Work Package (WP) level, if there are actual labor performance units recorded in the DS06 Resources table, there should also be corresponding labor actual hours recorded in the DS03 Cost table. The focus is on verifying that labor performance, in terms of units, is accurately reflected with actual labor hours to maintain data integrity and quality in project management.
The error identified by this DIQ check indicates that there are instances where labor performance units are greater than zero, suggesting that labor work has been performed, but the labor actual hours are recorded as zero. This discrepancy occurs at the WP level, which could lead to inaccurate project cost and performance tracking.
The likely cause of this error could be due to one of the following reasons:
- Labor actual hours were not entered into the DS03 Cost table despite labor performance being recorded in the DS06 Resources table. This could be an oversight or a data entry error.
- The WBS Work Package (WP) identifiers in the DS03 Cost table might not correctly match those in the DS06 Resources table, leading to a failure in accurately associating labor hours with the correct labor performance units.
- There might be a misunderstanding or misclassification of labor-related entries, where labor performance is recorded under a different category or not correctly marked as 'Labor' in either the DS03 Cost or DS06 Resources tables.
To resolve this issue, it is recommended to:
- Verify that all labor actual hours are accurately recorded in the DS03 Cost table whenever labor performance units are reported in the DS06 Resources table.
- Ensure that the WBS Work Package (WP) identifiers are consistent and correctly matched between the DS03 Cost and DS06 Resources tables.
- Review the classification of labor entries to ensure that all labor performance units and labor actual hours are correctly identified as 'Labor' in their respective tables.
By addressing these potential causes, project management data integrity and quality can be maintained, ensuring accurate tracking and reporting of labor performance and costs.
¶ Why do we check this?
This DIQ test is being performed to ensure that for every Work Package (WP) within the DS06 Resources table, if there are recorded labor performance units (indicating work has been performed), there should correspondingly be actual labor hours recorded in the cost data. The importance of this check lies in maintaining the accuracy and reliability of project management data, specifically in the context of labor resource tracking and cost management.
The test identifies a potential discrepancy where there are labor performance units reported (suggesting that labor work has been performed), but no actual labor hours are recorded in the cost data. This situation could indicate issues such as data entry errors, misalignment between resource tracking and cost accounting practices, or other systemic issues that could lead to inaccurate project cost tracking and performance measurement.
The severity level of MAJOR indicates that while this issue may not immediately invalidate the data review process, it is likely to cause problems during project cost analysis and performance evaluation. It suggests that corrective action should be taken to investigate and rectify the discrepancies to ensure that labor resources and costs are accurately captured and reported. This is crucial for effective project management, as accurate data is essential for monitoring project progress, evaluating performance, and making informed decisions.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreLaborPUnitsMissingDS03LaborAHoursWP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_WP WBS, SUM(ISNULL(ACWPi_hours,0)) AHours
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Labor' AND ISNULL(is_indirect,'') <> 'Y' AND TRIM(ISNULL(WBS_ID_WP,'')) <> ''
GROUP BY WBS_ID_WP
), 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 = 'FC'
AND S.schedule_type = 'FC'
AND (R.EOC = 'Labor' Or R.[type] = 'Labor')
GROUP BY S.WBS_ID, ISNULL(S.subproject_ID,'')
HAVING SUM(R.actual_units) > 0
), FlagsByWBS as (
SELECT S.WBS, SubP
FROM ScheduleWBS S LEFT OUTER JOIN CostWBS C ON S.WBS = C.WBS
WHERE C.AHours = 0 OR C.WBS IS NULL
), 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 = 'FC'
)
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 = 'FC'
AND (R.EOC = 'Labor' OR R.[type] = 'Labor')
AND NOT EXISTS (
SELECT 1
FROM DS03_cost
WHERE upload_ID = @upload_id AND TRIM(ISNULL(WBS_ID_WP,'')) = '' AND (ACWPi_dollars > 0 OR ACWPi_hours > 0 OR ACWPi_FTEs > 0)
)
)