¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 9060287 |
| Summary | Are the resource labor performance units misaligned with the cost labor performance hours? |
| Error message | Resource labor performance units (actual_units where EOC or type = Labor) <> cost labor performance hours (SUM of DS03.BCWPi_dollars 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 Misaligned with Cost" is designed to identify discrepancies between the labor performance units recorded in the DS06 Resources table and the cost labor performance hours recorded in the DS03 Cost table.
The test is specifically looking for instances where the actual units of labor (where the type or EOC is 'Labor') in the DS06 Resources table do not match the sum of the cost labor performance hours (where EOC is 'Labor') in the DS03 Cost table.
If this DIQ test returns any records, it indicates that there is a misalignment between the labor performance units and the cost labor performance hours. This could be due to a variety of reasons such as data entry errors, incorrect allocation of labor hours, or discrepancies in the way labor hours are recorded in different tables.
The fields causing the issue are the 'actual_units' field in the DS06 Resources table and the 'BCWPi_hours' field in the DS03 Cost table. The expected values for these fields should be equal for each corresponding record, indicating that the labor performance units are correctly aligned with the cost labor performance hours.
To resolve any issues identified by this DIQ test, you would need to review the labor performance units and cost labor performance hours for each record returned by the test and correct any discrepancies.
¶ Why do we check this?
This test is being performed to ensure that the resource labor performance units are correctly aligned with the cost labor performance hours. In the context of EVMS (Earned Value Management System), this is crucial as it directly impacts the cost performance index (CPI) and schedule performance index (SPI), which are key indicators of project health.
The test checks if the actual units of labor (where EOC or type equals Labor) are not equal to the sum of the cost labor performance hours (where EOC equals Labor). If these two values are not equal, it could indicate that there are discrepancies in the labor cost calculations, which could lead to inaccurate project cost and schedule performance assessments.
The severity of this check is marked as a MAJOR. This means that while the data can still be reviewed, any discrepancies found could potentially cause problems during the analysis of the project's performance. It's important to address these issues to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreLaborPUnitsMisalignedWithDS03LaborPHours] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_WP WBS, SUM(BCWPi_hours) BCWP
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Labor' AND ISNULL(is_indirect,'') <> 'Y'
GROUP BY WBS_ID_WP
), ScheduleWBS as (
SELECT S.WBS_ID WBS, ISNULL(S.subproject_ID,'') SubP, SUM(R.actual_units) Performance
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,'')
), FlagsByWBS as (
SELECT S.WBS, S.SubP
FROM ScheduleWBS S INNER JOIN CostWBS C ON C.WBS = S.WBS AND C.BCWP <> S.Performance
), 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')
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | 1. Logic adjusted to account for the 'is_indirect' field being added to DS03_Cost. 2. Logic adjusted to account for the addition of 'subproject_ID' field. |