¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 1060261 |
| Summary | Is this FC resource missing among the BL resources? |
| Error message | Combo of task_ID, resource_ID, role_ID, & EOC (where schedule_type = BL) not found in DS06 (where schedule_type = BL). |
¶ 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 "FC Resource Missing Among BL Resources" is designed to identify any discrepancies between the resources listed in the Forecast (FC) schedule and the Baseline (BL) schedule in the DS06 Resources table.
The test checks for each combination of task_ID, resource_ID, role_ID, and EOC in the FC schedule. If a combination is not found in the BL schedule, an error is flagged.
The likely cause of this error is a mismatch between the resources assigned to tasks in the FC and BL schedules. This could be due to a resource being added or removed in the FC schedule without a corresponding update in the BL schedule, or vice versa.
To resolve this issue, ensure that for every task in the FC schedule, the same resources (identified by resource_ID and role_ID) are assigned in the BL schedule. Also, ensure that the EOC (Estimate at Completion) value for each task matches between the two schedules.
Please note that this test is case-sensitive and spaces in the resource_ID, role_ID, and EOC fields may cause mismatches. Therefore, it is recommended to trim any leading or trailing spaces in these fields before running the test.
¶ Why do we check this?
This test is being performed to ensure that the forecasted cost (FC) resources are properly accounted for in the baseline (BL) resources. The test is checking if a combination of task_ID, resource_ID, role_ID, and EOC (where schedule_type = BL) is missing in the DS06 table (where schedule_type = BL).
The importance of this check is to ensure that all resources that are expected to be used in the project are included in the baseline schedule. If a resource is missing, it could lead to inaccurate forecasting and planning, which could potentially impact the project's budget and timeline.
The severity of this test is marked as a MAJOR. This means that while the data can still be reviewed, the issue is likely to cause problems during analysis. It is recommended to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsFCResourceMissingInBL] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with BLRes as (
SELECT task_ID, ISNULL(subproject_ID,'') SubP, TRIM(ISNULL(resource_ID,'')) ResID, TRIM(ISNULL(role_ID,'')) RoleID, EOC
FROM DS06_schedule_resources
WHERE upload_id = @upload_ID AND schedule_type = 'BL'
)
SELECT
FCR.*
FROM
DS06_schedule_resources FCR LEFT OUTER JOIN BLRes ON FCR.task_ID = BLRes.task_ID
AND ISNULL(FCR.subproject_ID,'') = BLRes.SubP
AND TRIM(ISNULL(FCR.resource_ID,'')) = BLRes.ResID
AND TRIM(ISNULL(FCR.role_ID,'')) = BLRes.RoleID
AND FCR.EOC = BLRes.EOC
WHERE
upload_id = @upload_ID
AND schedule_type = 'FC'
AND BLRes.task_ID IS NULL
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of subproject_id field. |