¶ Basic Information
| 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. |
¶ 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 Missing Resources" is designed to identify any Work Packages (WP) or Planning Packages (PP) in the DS03 Cost table that are missing corresponding resources in the DS06 Schedule Resources table.
This check is triggered when a WP or PP in the DS03 Cost table has a non-zero value for BCWSi (Budgeted Cost of Work Scheduled) in either dollars, hours, or FTEs (Full-Time Equivalents), but there are no corresponding resources listed in the DS06 Schedule Resources table for the same Work Breakdown Structure (WBS) ID and Estimate at Completion (EOC).
The likely cause of this error is a data entry or import error where resources for a WP or PP have not been correctly entered or linked in the DS06 Schedule Resources table.
To resolve this issue, you should ensure that for every WP or PP in the DS03 Cost table with a non-zero BCWSi value, there is a corresponding entry in the DS06 Schedule Resources table with the same WBS ID and EOC. The DS06 entry should also have a non-zero value for either budget dollars or budget units.
¶ Why do we check this?
This test is being performed to check for missing resources in the 'DS03 Cost' table for work packages (WP) or planning packages (PP) that have a budgeted cost of work scheduled (BCWSi) not equal to zero. This means that there are work or planning packages that have been budgeted for, but no resources have been allocated to them by the end of the contract (EOC).
The importance of this check is to ensure that all budgeted work or planning packages have the necessary resources allocated to them. If resources are not allocated, it could lead to delays in the project, cost overruns, or even failure to complete the project. This check is classified as a MAJOR, indicating that while it may not immediately prevent data review, it is likely to cause problems during analysis if not addressed.
¶ Code
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
)
¶ Changelog
| 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. |