¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 9060306 |
| Summary | Are there actuals in cost without performance in resources (by CA WBS & EOC)? |
| Error message | Resource performance (actual_dollars) = 0 even though DS03.ACWPc > 0 (SUM of ACWSi_dollars) by WBS_ID_CA & 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.
This Data Integrity and Quality (DIQ) check is designed to identify discrepancies in the DS06 Resources table related to cost actuals and resource performance. Specifically, it looks for instances where there are actual costs recorded in the DS03 Cost table (ACWPi_dollars > 0) without corresponding resource performance (actual_dollars = 0) in the DS06 Resources table. This situation occurs when costs have been incurred (as indicated by the sum of ACWPi_dollars in DS03) for specific Work Breakdown Structure (WBS) elements and Elements of Cost (EOC), but there is no recorded performance (actual_dollars) against those costs in the resources data.
The likely cause of this error could be due to several reasons:
- Data Entry Errors: Actual dollars might not have been entered or updated correctly in the DS06 Resources table for tasks associated with the incurred costs.
- Misalignment Between Cost and Schedule Data: There might be a misalignment between the cost data in DS03 and the schedule or resource data in DS06, possibly due to incorrect or missing WBS elements or EOC classifications.
- Incomplete Data: The resource performance data might be incomplete or not yet updated to reflect the actual costs incurred.
The fields causing the issue are primarily:
WBS_ID_CAandEOCin the DS03 Cost table, indicating where costs have been recorded without corresponding performance.actual_dollarsin the DS06 Resources table, which should reflect the performance against the incurred costs but shows zero value.
Expected values would be that for every cost recorded in DS03 (ACWPi_dollars > 0) for a given WBS_ID_CA and EOC, there should be a corresponding non-zero actual_dollars value in DS06 for the related tasks, ensuring that cost and performance data are aligned and accurately reflected across the project management tables.
¶ Why do we check this?
This test is being performed to identify instances where there are actual costs recorded without corresponding resource performance data in the DS06 Resources table. Specifically, it checks for situations where the sum of actual cost of work performed (ACWP) in dollars is greater than zero for a given Work Breakdown Structure (WBS) ID and Element of Cost (EOC), but the resource performance (measured in actual dollars) is recorded as zero.
The importance of this check lies in ensuring the accuracy and completeness of project management data. Actual costs without matching resource performance data can indicate missing or incorrectly recorded information, which could lead to inaccurate project performance assessments and financial reporting. By flagging these discrepancies as an MINOR, the test highlights potential minor problems in the data that, while not immediately critical, could affect the overall quality and reliability of project management insights if not addressed. This level of severity suggests that the issue should be reviewed and corrected to adhere to best practices, but it does not halt the review process entirely.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreDS03ADollarsMissingResourcePDollarsCA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_CA CAWBS, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END as EOC, SUM(ACWPi_dollars) ACWP
FROM DS03_cost
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(WBS_ID_WP,'')) = ''
GROUP BY WBS_ID_CA, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END
HAVING SUM(ACWPi_dollars) > 0
), Resources as (
SELECT task_ID, EOC, ISNULL(subproject_ID,'') SubP, SUM(ISNULL(actual_dollars,0)) Performance
FROM DS06_schedule_resources
WHERE upload_ID = @upload_ID AND schedule_type = 'FC'
GROUP BY task_ID, EOC, ISNULL(subproject_ID,'')
), ScheduleWBS as (
SELECT S.WBS_ID WBS, R.EOC, R.SubP
FROM DS04_schedule S INNER JOIN Resources R ON S.task_ID = R.task_ID AND ISNULL(S.subproject_ID,'') = R.SubP
WHERE S.upload_ID = @upload_ID AND S.schedule_type = 'FC'
GROUP BY S.WBS_ID, R.EOC, R.SubP
HAVING SUM(R.Performance) = 0
), WBSHierarchy as (
SELECT WBS_ID, Ancestor_WBS_ID
FROM dbo.AncestryTree_Get(@upload_id)
WHERE [Type] = 'WP' AND Ancestor_Type = 'CA'
), ScheduleCAs as (
SELECT W.Ancestor_WBS_ID CAWBS, SubP, EOC
FROM ScheduleWBS S INNER JOIN WBSHierarchy W ON S.WBS = W.WBS_ID
GROUP BY W.Ancestor_WBS_ID, SubP, EOC
), FlagsByCAWBS as (
SELECT S.CAWBS, SubP
FROM ScheduleCAs S INNER JOIN CostWBS C ON S.CAWBS = C.CAWBS AND S.EOC = C.EOC
), FlagsByWPWBS as (
SELECT S.WBS, S.SubP, S.EOC
FROM WBSHierarchy W INNER JOIN ScheduleWBS S ON W.WBS_ID = S.WBS
WHERE W.Ancestor_WBS_ID IN (SELECT CAWBS FROM FlagsByCAWBS)
), FlagsByTaskID as (
SELECT S.task_ID, F.EOC, F.SubP
FROM FlagsByWPWBS F INNER JOIN DS04_schedule S ON F.WBS = S.WBS_ID
WHERE S.upload_ID = @upload_ID AND S.schedule_type = 'FC'
)
SELECT R.*
FROM DS06_schedule_resources R INNER JOIN FlagsByTaskID F ON R.task_ID = F.task_ID
AND ISNULL(subproject_ID,'') = F.SubP
AND R.EOC = F.EOC
WHERE R.upload_id = @upload_ID AND R.schedule_type = 'FC'
)