¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 9060303 |
| Summary | Are there resource labor performance units recorded without labor actual hours in cost at the CA 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_CA. |
¶ 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) Control Account (CA) 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 identifying discrepancies where labor performance units are greater than zero, indicating work has been performed, but the labor actual hours are recorded as zero, suggesting no labor cost has been accounted for at the CA level.
The likely cause of the error identified by this DIQ check could be due to one of the following scenarios:
- Actual labor hours have not been recorded or updated in the DS03 Cost table despite labor work being performed, as indicated by actual labor units in the DS06 Resources table.
- There is a misalignment or incorrect mapping between the WBS IDs in the DS06 Resources table and the DS03 Cost table, leading to a situation where labor performance is recorded without corresponding labor costs at the CA level.
- Data entry errors or omissions in the DS03 Cost table, where labor actual hours are either mistakenly entered as zero or not entered at all.
The fields causing the issue are primarily the actual labor units (actual_units) in the DS06 Resources table where the Entry of Cost (EOC) or type is labeled as 'Labor', and the labor actual hours (ACWPi_hours) in the DS03 Cost table for the corresponding WBS ID at the CA level. Expected values should show a positive number of labor actual hours in the DS03 Cost table wherever there are actual labor units recorded in the DS06 Resources table, ensuring that labor performance is accurately reflected in labor costs.
¶ Why do we check this?
This DIQ test is being performed on the 'DS06 Resources' table to ensure that there are no discrepancies between the recorded resource labor performance units and the labor actual hours reported in costs at the Control Account (CA) level. The test checks for instances where there are positive labor performance units (indicating work has been performed) without corresponding labor actual hours being recorded in the cost data. The importance of this check lies in its ability to highlight inconsistencies that could lead to inaccurate reporting of labor costs and performance metrics. Such discrepancies can affect project budgeting, forecasting, and overall project management effectiveness. The severity level of 'MAJOR' indicates that while this issue may not immediately invalidate the data, it is likely to cause problems during analysis, such as misinterpretation of project progress and labor efficiency. Addressing these discrepancies is crucial for maintaining accurate and reliable project management data, which in turn supports effective decision-making and project control.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreLaborPUnitsMissingDS03LaborAHoursCA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_CA WBS, SUM(ISNULL(ACWPi_hours,0)) AHours
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Labor' AND ISNULL(is_indirect,'') <> 'Y'
GROUP BY WBS_ID_CA
), 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
), WBSHierarchy as (
SELECT WBS_ID, Ancestor_WBS_ID
FROM dbo.AncestryTree_Get(@upload_id)
WHERE [Type] = 'WP' AND Ancestor_Type = 'CA'
), ScheduleCAs as (
SELECT W.Ancestor_WBS_ID CAWBS, SubP
FROM ScheduleWBS S INNER JOIN WBSHierarchy W ON S.WBS = W.WBS_ID
GROUP BY W.Ancestor_WBS_ID, SubP
), FlagsByCAWBS as (
SELECT S.CAWBS, SubP
FROM ScheduleCAs S LEFT OUTER JOIN CostWBS C ON S.CAWBS = C.WBS
WHERE C.AHours = 0 OR C.WBS IS NULL
), FlagsByWPWBS as (
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)
), FlagsByTask as (
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 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 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)
)
)