| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030100 |
| Summary | Is this WP or PP missing accompanying Resources (DS06) by EOC? |
| Error message | WP or PP with BCWSi <> 0 (Dollars, Hours, or FTEs) is missing Resources (DS06) by EOC. |
This DIQ check identifies cost records that have budget but lack corresponding resource assignments, indicating incomplete resource loading or data synchronization issues.
The check verifies that Work Packages (WP) or Planning Packages (PP) with budgeted cost (BCWSi) have matching resources in DS06. The linkage works through:
A record fails when:
Common causes:
Note: This check excludes Summary Level Planning Packages (SLPPs) which may legitimately lack detailed resource loading.
To resolve: Verify that scheduled tasks exist for all budgeted work packages and that resources are properly assigned to those tasks with matching EOCs.
This test ensures data consistency between cost budgets and resource plans, a fundamental requirement for earned value management.
Why this alignment matters:
The check validates that your integrated cost/schedule/resource data tells a consistent story:
Common data quality issues found:
As a MAJOR check, this identifies significant data integration issues that will impact cost/schedule analysis. While some small gaps may exist in complex projects, widespread failures indicate systematic data management problems requiring correction.
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsWorkMissingResourcesInDS06] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Resources As (
SELECT S.WBS_ID, R.EOC
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 S.schedule_type = 'BL'
AND R.schedule_type = 'BL'
AND (R.budget_dollars > 0 OR R.budget_units > 0)
GROUP BY S.WBS_ID, R.EOC
)
SELECT C.*
FROM DS03_Cost C LEFT OUTER JOIN Resources R ON C.WBS_ID_WP = R.WBS_ID
AND CASE WHEN ISNULL(C.is_indirect,'') = 'Y' THEN 'Indirect' ELSE C.EOC END = R.EOC
WHERE
upload_ID = @upload_ID
AND (BCWSi_dollars <> 0 OR BCWSi_FTEs <> 0 AND BCWSi_hours <> 0)
AND TRIM(ISNULL(WBS_ID_WP,'')) <> ''
AND R.wbs_ID IS NULL -- Any missing joins are our failed rows
)
| Date | Description of Changes |
|---|---|
| 2024-04-30 | 1. Logic adjusted to account for the addition of subproject_id field. 2. Logic adjusted to account for the addition of 'is_indirect' field. All cases where 'is_indirect' = 'Y' are treated as indirect data. |
| 2024-09-19 | Logic adjusted to exclude SLPPs. |