¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 9060285 |
| Summary | Are there actuals in cost without performance in resources (by WP WBS & EOC)? |
| Error message | Resource performance (actual_dollars) = 0 even though DS03.ACWPc > 0 (SUM of ACWSi_dollars) by WBS_ID_WP & 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 Actuals without Resource Performance" is designed to identify any discrepancies between the actual costs recorded in the DS03 Cost table and the performance figures in the DS06 Resources table.
The test is specifically looking for instances where there are actual costs (ACWPi_dollars) recorded in the DS03 Cost table, but the corresponding performance (actual_dollars) in the DS06 Resources table is zero. This is done by comparing the sum of ACWPi_dollars by EOC in the DS03 Cost table with the sum of actual_dollars by EOC in the DS06 Resources table.
If the test identifies any such discrepancies, it means that there are actual costs being recorded without corresponding resource performance. This could be due to a variety of reasons, such as data entry errors, system glitches, or issues with the allocation of costs and resources.
The fields causing the issue are the ACWPi_dollars field in the DS03 Cost table and the actual_dollars field in the DS06 Resources table. The expected values for these fields should be consistent with each other, i.e., if there are actual costs recorded in the DS03 Cost table, there should be corresponding resource performance recorded in the DS06 Resources table.
Please ensure that the data entered in these fields is accurate and consistent to maintain the integrity and quality of the project management data.
¶ Why do we check this?
This test is being performed to ensure that there are no discrepancies between the actual costs and the resource performance in the DS06 Resources table. Specifically, it checks if there are instances where actual costs are recorded (DS03.ACWPc > 0) but the resource performance (actual_dollars) is zero. This is important because such discrepancies can lead to inaccurate cost-performance analysis, which can in turn affect project management decisions.
The severity of this test is marked as an MINOR, which means that while it may not immediately prevent data review or analysis, it could potentially cause minor problems or indicate that the data does not adhere to all best practices. It is crucial to address these alerts to ensure the overall integrity and quality of the data, and to prevent any potential issues in future data analysis or project management.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreDS03ADollarsMissingResourcePDollarsWP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_WP WBS, 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_WP, 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
), FlagsByWBS as (
SELECT S.WBS, S.EOC, S.SubP
FROM ScheduleWBS S INNER JOIN CostWBS C ON C.EOC = S.EOC AND C.WBS = S.WBS
), FlagsByTaskID as (
SELECT S.task_ID, F.EOC, F.SubP
FROM FlagsByWBS F INNER JOIN DS04_schedule S ON F.WBS = S.WBS_ID AND F.SubP = ISNULL(S.subproject_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'
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | DIQ converted into two checks, one at CA level and one at WP level. UID maintained from prior version for WP level check. Metadata updated accordingly. |