¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 9060289 |
| Summary | Are there labor actual hours in cost without labor performance units in resources? (WP) |
| 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 WP 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.
The Data Integrity and Quality (DIQ) check titled "Cost Labor Actuals without Resource Labor Performance" is designed to identify discrepancies between labor actual hours and labor performance units in the DS06 Resources and DS03 Cost tables.
The test is checking for instances where there are actual labor hours recorded in the DS03 Cost table (where EOC or type is 'Labor'), but there are no corresponding labor performance units in the DS06 Resources table. This could indicate a data entry error or a missing update in one of the tables.
The fields causing the issue are the 'actual_units' field in the DS06 Resources table and the 'ACWPi_hours' field in the DS03 Cost table. The expected values would be that for every labor actual hour recorded in the DS03 Cost table, there should be a corresponding labor performance unit in the DS06 Resources table.
If the test returns a positive result, it means that there are labor actual hours in the DS03 Cost table that do not have corresponding labor performance units in the DS06 Resources table. This discrepancy should be investigated and corrected to ensure data integrity and quality.
¶ Why do we check this?
This test is being performed to ensure that there is a match between the actual labor hours recorded in the cost data and the labor performance units in the resource data. The test is checking for instances where there are actual labor hours recorded in the cost data (DS03.ACWPi_hours where EOC = Labor) but no corresponding labor performance units in the resource data (actual_units where EOC or type = Labor).
The importance of this check is to maintain the integrity and accuracy of the labor cost and resource data. If there are labor hours recorded in the cost data but no corresponding labor performance units in the resource data, it could indicate a data entry error or a discrepancy between the cost and resource data. This could potentially lead to inaccurate cost calculations or misinterpretation of the labor performance data.
The severity of this check is marked as a MAJOR, which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. It is therefore recommended that this issue be addressed to ensure the accuracy and reliability of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreDS03LaborAHoursMissingResourceLaborPUnitsWP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_WP WBS
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
HAVING SUM(ACWPi_hours) > 0
), Resources as (
SELECT task_ID, ISNULL(subproject_ID,'') SubP, SUM(ISNULL(actual_units,0)) ResLbrUnits
FROM DS06_schedule_resources
WHERE upload_ID = @upload_id AND schedule_type = 'FC' AND (EOC = 'Labor' Or [type] = 'Labor')
GROUP BY task_ID, ISNULL(subproject_ID,'')
), ScheduleWBS as (
SELECT S.WBS_ID WBS, ISNULL(S.subproject_ID,'') SubP, SUM(ISNULL(ResLbrUnits,0)) LbrUnits
FROM DS04_schedule S LEFT OUTER JOIN Resources R ON S.task_ID = R.task_ID AND ISNULL(S.subproject_ID,'') = R.SubP
WHERE S.upload_ID = @upload_id AND S.schedule_type = 'FC'
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
WHERE S.LbrUnits = 0
), FlagsByTaskID 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 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 |
|---|---|
| 2024-04-30 | DIQ converted into two checks, one at CA level and one at WP level. UID maintained from prior version for WP level check. Metadata updated accordingly. |