¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 9060307 |
| Summary | Are there labor actual hours in cost without labor performance units in resources? (CA) |
| 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) (Test runs at CA level). |
¶ 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 labor cost recorded in the DS03 Cost table, there is a corresponding entry of labor performance units in the DS06 Resources table. Specifically, the check identifies instances where there are actual labor hours reported in the cost data without matching labor performance units in the resource data. This situation indicates a potential discrepancy in the project's labor accounting, where labor costs are incurred (as seen in actual hours worked) but not reflected in the labor performance (as in the units of work performed).
The error typically arises under the following conditions:
- In the DS03 Cost table, there are entries with actual cost work performed hours (ACWPi_hours) greater than zero for labor (identified by the Element of Cost (EOC) being 'Labor'), indicating that labor costs have been incurred.
- Concurrently, in the DS06 Resources table, there are no corresponding labor performance units (actual_units) recorded for tasks associated with these costs, where either the EOC is 'Labor' or the resource type is identified as 'Labor'. This is flagged when the sum of actual_units for labor is zero.
The likely causes for this discrepancy could include:
- Missing entries in the DS06 Resources table for tasks that have incurred labor costs.
- Incorrectly classified or unrecorded labor performance data in the DS06 Resources table.
- Data entry errors where labor costs are recorded without associating them with the corresponding labor performance.
To resolve these issues, users should:
- Verify that all labor costs recorded in the DS03 Cost table have corresponding labor performance entries in the DS06 Resources table.
- Ensure that labor performance data is accurately entered and classified in the DS06 Resources table, matching the labor costs incurred as recorded in the DS03 Cost table.
- Review and correct any data entry or classification errors that may have led to the discrepancy between labor costs and labor performance data.
By addressing these issues, project management can maintain accurate and consistent records of labor costs and performance, ensuring the integrity and quality of project management data.
¶ Why do we check this?
This DIQ test is performed on the 'DS06 Resources' table to ensure that there are no discrepancies between labor actual hours reported in costs and the corresponding labor performance units in resources. Specifically, the test checks for instances where there are actual labor hours reported (as indicated by a sum greater than zero in DS03.ACWPi_hours for labor) without matching labor performance units (where actual_units are zero for entries marked as Labor in the resources table). The test runs at the Control Account (CA) level, which means it aggregates and evaluates data at this specific level of project management detail.
The importance of this check lies in its ability to identify inconsistencies between the reported cost of labor (in hours) and the actual labor performance (in units). Such discrepancies can indicate issues in project tracking or reporting, potentially leading to inaccurate project cost assessments, forecasting errors, and challenges in project management and decision-making. By flagging these discrepancies as a MAJOR, it signals that while the issue is likely to cause problems during analysis or project management, it may not necessarily invalidate the data but requires attention to ensure accurate and reliable project tracking and financial reporting. Addressing these warnings helps in maintaining the integrity of project data, ensuring that labor costs and performance are accurately captured and reported, which is crucial for effective project management and financial control.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreDS03LaborAHoursMissingResourceLaborPUnitsCA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
--cost CAs with A labor hours
SELECT WBS_ID_CA CAWBS
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Labor' AND ISNULL(is_indirect,'') <> 'Y' AND TRIM(ISNULL(WBS_ID_WP,'')) = '' AND ACWPi_hours > 0
GROUP BY WBS_ID_CA
), ScheduleWBS as (
--resource labor performance by WBS ID
SELECT SchedWBS WBS, SubprojectID SubP, ISNULL(ResPerformance_units_FC,0) LbrUnits
FROM WBSRollup_WP
WHERE upload_ID = @upload_id AND EOC = 'Labor'
), WBSHierarchy as (
--WBS Hierarchy
SELECT WBS_ID, Ancestor_WBS_ID
FROM AncestryTree
WHERE upload_id = @upload_id AND [Type] = 'WP' AND Ancestor_Type = 'CA'
), ScheduleCAs as (
--Schedule data by CA WBS ID, WP WBS, Subproject, and Labor Units
SELECT W.Ancestor_WBS_ID CAWBS, SubP, SUM(S.LbrUnits) LbrUnits
FROM ScheduleWBS S INNER JOIN WBSHierarchy W ON S.WBS = W.WBS_ID
GROUP BY W.Ancestor_WBS_ID, SubP
), FlagsByCAWBS as (
--join CostWBS to ScheduleWBS
--any joins are failures
SELECT S.CAWBS, S.SubP
FROM ScheduleCAs S INNER JOIN CostWBS C ON C.CAWBS = S.CAWBS
WHERE S.LbrUnits = 0
), FlagsByWPWBS as (
--Get the WPs that make up the problem CA WBSs
SELECT S.WBS, S.SubP
FROM WBSHierarchy W INNER JOIN ScheduleWBS S ON W.WBS_ID = S.WBS
WHERE W.Ancestor_WBS_ID IN (SELECT CAWBS FROM FlagsByCAWBS)
), FlagsByTaskID as (
--get tasks that failed using FlagsByWBS
SELECT S.task_ID, F.SubP
FROM DS04_schedule S INNER JOIN FlagsByWPWBS 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 FlagsByTaskID 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')
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2025-07-09 | Performance optimizations. Replaced several CTEs & joins with a direct call to WBSRollup_WP. Replaced function call, AncestryTree_Get, with direct call to table, AncestryTree, which is now loaded prior to DIQs running. |