¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 9060298 |
| Summary | Are the resource remaining units flowing up to the schedule WBS misaligned with the BCWR hours in cost (by WBS_ID & EOC)? |
| Error message | Sum of resource remaining hours rolled up into DS04.WBS_ID do not align with BCWR hours in DS03 (by WBS_ID & EOC). |
¶ 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 Remaining Units Misaligned with Cost BCWR Hours" is designed to ensure that the remaining resource units in the DS06 Resources table align with the Budgeted Cost of Work Remaining (BCWR) hours in the DS03 Cost table. This alignment is checked by Work Breakdown Structure ID (WBS_ID) and Element of Cost (EOC).
If an error is flagged by this DIQ check, it indicates that the sum of the remaining resource hours, when rolled up into the DS04 Schedule table by WBS_ID, does not match the BCWR hours in the DS03 Cost table for the same WBS_ID and EOC.
This discrepancy could be caused by several factors. For instance, there may be an error in the remaining resource units in the DS06 Resources table, or the BCWR hours in the DS03 Cost table could be incorrect. Alternatively, the issue could lie in the way these values are being rolled up into the DS04 Schedule table.
To resolve this issue, you should review the remaining resource units in the DS06 Resources table and the BCWR hours in the DS03 Cost table for the flagged WBS_ID and EOC. Ensure that these values are accurate and that they align correctly when rolled up into the DS04 Schedule table.
¶ Why do we check this?
This test is being performed to ensure that the remaining resource units in the schedule align with the Budgeted Cost of Work Remaining (BCWR) hours in cost. This is done by comparing the sum of resource remaining hours rolled up into DS04.WBS_ID with the BCWR hours in DS03, both by Work Breakdown Structure ID (WBS_ID) and Estimate at Completion (EOC).
The importance of this check is to ensure that the project's remaining resources are accurately represented and aligned with the remaining budgeted cost. This is crucial for effective project management and for making accurate forecasts about the project's completion.
The severity of this test is marked as an MINOR. This means that while it may not immediately prevent data from being reviewed, it could potentially cause minor problems or indicate that the data does not follow all best practices. It's a signal that there might be inconsistencies in the data that could affect the accuracy of project management and forecasting.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreRemUnitsMisalignedWithDS03BCWRHours] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_WP WBS, SUM(BCWSi_hours) - SUM(BCWPi_hours) BCWR
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, SubP, SUM(R.RemUnits) RemUnits
FROM
DS04_schedule S INNER JOIN
(
SELECT task_ID, ISNULL(subproject_ID,'') SubP, SUM(remaining_units) RemUnits
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,'')
) R ON S.task_ID = R.task_ID AND ISNULL(S.subproject_ID,'') = SubP
WHERE S.upload_ID = @upload_ID
AND S.schedule_type = 'FC'
GROUP BY S.WBS_ID, SubP
), FlagsByWBS as (
SELECT S.WBS, S.SubP
FROM ScheduleWBS S INNER JOIN CostWBS C ON C.WBS = S.WBS AND C.BCWR <> S.RemUnits
), 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 | Logic adjusted to account for the addition of 'subproject_ID' and 'is_indirect' fields. |