Note: DIQ has been deleted.
¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 9060288 |
| Summary | Are there resource labor performance units recorded without labor actual hours in cost? |
| 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). |
¶ 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 Performance without Cost Labor Actuals" is designed to identify any discrepancies between the recorded resource labor performance units and the actual labor hours in cost. This check is performed on the DS06 Resources table.
The 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)" indicates that there are instances where resource labor performance units have been recorded (in the DS06 Resources table), but there are no corresponding actual labor hours recorded in the cost data (in the DS03 Cost table).
This discrepancy could be caused by a few different issues. It could be that the labor hours were not recorded correctly in the DS03 Cost table, or it could be that the labor performance units were recorded incorrectly in the DS06 Resources table. Alternatively, it could be that the EOC (Element of Cost) or type fields were not correctly set to 'Labor' in either table.
The expected values for these fields would be that if there are resource labor performance units recorded in the DS06 Resources table (i.e., actual_units > 0 where EOC or type = Labor), then there should also be corresponding actual labor hours recorded in the DS03 Cost table (i.e., SUM of DS03.ACWPi_hours where EOC = Labor > 0). If these conditions are not met, then the DIQ check will flag this as an error.
¶ Why do we check this?
This test is being performed to ensure that there are no discrepancies between the recorded resource labor performance units and the actual labor hours in cost. Specifically, it checks if there are any instances where resource labor performance units are recorded (actual units where EOC or type = Labor) but no actual labor hours in cost are recorded (SUM of DS03.ACWPi_hours where EOC = Labor).
The importance of this check lies in its ability to detect potential data inconsistencies or errors. If there are labor performance units recorded but no corresponding labor hours in cost, it could indicate a problem with data entry or tracking. This could lead to inaccurate cost calculations, budgeting errors, or misinterpretation of labor performance data.
The severity of this check is marked as a MAJOR. This means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. It is crucial to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreLaborPUnitsMissingDS03LaborAHours] (
@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'
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')
)