¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 9060294 |
| Summary | Is the combo of resource EOCs for this task/WBS misaligned with what is in cost? |
| Error message | Combo of resource EOCs for this DS04.task's WBS ID do not align with combo of DS03 EOCs. |
¶ 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 "EOC Combo Misaligned with Cost" is designed to ensure that the combination of resource Estimate at Completion (EOC) for a specific task or Work Breakdown Structure (WBS) in the DS06 Resources table aligns with the EOCs in the DS03 Cost table.
If an error message is received stating "Combo of resource EOCs for this DS04.task's WBS ID do not align with combo of DS03 EOCs", it indicates that there is a mismatch between the EOCs listed for a specific task or WBS in the DS06 Resources table and the EOCs listed in the DS03 Cost table.
This discrepancy could be due to a variety of reasons such as data entry errors, updates in one table not being reflected in the other, or inconsistencies in the way EOCs are being assigned to tasks or WBSs.
To resolve this issue, it is recommended to review the EOCs listed for the specific task or WBS in both the DS06 Resources and DS03 Cost tables. Ensure that the EOCs are consistent across both tables. If there are any discrepancies, they should be corrected to ensure data integrity and quality.
¶ Why do we check this?
This test is being performed to ensure that the combination of resource Estimate at Completion (EOC) for a specific task or Work Breakdown Structure (WBS) aligns with the cost data. The test is checking the 'DS06 Resources' table and comparing the EOCs for a task's WBS ID in the 'DS04' table with the EOCs in the 'DS03' table.
The importance of this check is to ensure that the estimated costs of resources for a task or WBS are accurately reflected in the cost data. Misalignment could lead to inaccurate cost projections, budget overruns, or incorrect resource allocation.
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 crucial to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsEOCComboMisalignedWithDS03] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostEOCs as (
SELECT C.WBS, EOC
FROM (
SELECT WBS_ID_WP WBS, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END as EOC
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_WP, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END
) C
GROUP BY C.WBS, C.EOC
), ScheduleEOCs as (
SELECT S.WBS, SubP, S.EOC
FROM (
SELECT S.WBS_ID WBS, ISNULL(R.EOC,'') EOC, 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 = 'BL'
AND S.schedule_type = 'BL'
GROUP BY S.WBS_ID, R.EOC, ISNULL(S.subproject_ID,'')
) S
GROUP BY S.WBS, SubP, S.EOC
), FlagsByWBS as (
SELECT S.WBS, S.SubP, S.EOC
FROM ScheduleEOCs S
WHERE NOT EXISTS (
SELECT 1 FROM CostEOCs C WHERE S.WBS = C.WBS AND S.EOC = C.EOC
)
AND S.WBS IN (SELECT WBS FROM CostEOCs)
), FlagsByTask as (
SELECT S.task_ID, F.SubP, F.EOC
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 = 'BL'
)
SELECT
R.*
FROM
DS06_schedule_resources R INNER JOIN FlagsByTask F ON R.task_ID = F.task_ID AND ISNULL(R.EOC,'') = F.EOC AND ISNULL(R.subproject_ID,'') = F.SubP
WHERE
R.upload_id = @upload_ID
AND R.schedule_type = 'BL'
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | 1. Logic adjusted to account for the addition of 'is_indirect' field. All cases where 'is_indirect' = 'Y' are treated as indirect data. 2. Logic adjusted to account for the addition of 'subproject_id' field. 3. Logic adjusted to include EOC in test. |
| 2024-07-23 | Logic adjusted to exclude flagged schedule WBS IDs that doesn't exist in DS03 at all. Reason: other DIQs already exist that check for Cost WBS not found in DS04 Schedule (See: 9040223). |