¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 9060305 |
| Summary | Has this resource recorded performance even though actuals are not recorded in cost (by EOC, at the WP level)? |
| Error message | Resource performance (actual_dollars) > 0 even though DS03.ACWPc = 0 (SUM of ACWSi_dollars) by EOC (WP level). |
¶ 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 instances within the DS06 Resources table where a resource has recorded performance in terms of actual dollars spent, even though there are no corresponding actual cost data recorded at the Work Package (WP) level in the DS03 Cost table. Specifically, it flags when the sum of actual cost work performed (ACWP) dollars is zero for a given Element of Cost (EOC) at the WP level, but there is recorded performance for that resource.
The likely cause of this error could be due to several reasons:
-
Missing Data: Actual cost data may not have been entered or uploaded correctly into the DS03 Cost table for the corresponding work package and element of cost. This could be an oversight or a data entry error.
-
Misalignment in Work Package Identification: There might be a discrepancy in how work packages are identified or recorded between the DS06 Resources and DS03 Cost tables. This could be due to inconsistencies in naming conventions, leading to performance being recorded against a work package that does not appear to have cost data.
-
Timing Issues: The actual costs might be recorded in a different period than the performance data, leading to a temporary mismatch in the data. This is more of a timing issue than an actual error but would still be flagged by this check.
-
Data Collection Practices: If actual costs are collected at a different level (e.g., at a Control Account (CA) level instead of the WP level) and not properly allocated down to the WP level, it could result in this discrepancy. This check specifically excludes cases where actuals are collected at the CA level only if there is a sum of ACWP dollars greater than zero at that level, indicating a deliberate practice of collecting data at a higher aggregation level.
The fields causing the issue are primarily the actual_dollars field in the DS06 Resources table, which records the performance of resources, and the ACWPi_dollars field in the DS03 Cost table, which should record the actual cost work performed at the WP level for each EOC. Expected values would include a positive sum of ACWPi_dollars for each WP and EOC combination that has recorded performance in actual_dollars.
To resolve these issues, users should verify the accuracy and completeness of data entry in both the DS03 Cost and DS06 Resources tables, ensure consistency in the identification and recording of work packages across tables, and review the timing and practices of data collection and entry to ensure alignment between cost and performance data.
¶ Why do we check this?
This DIQ test is performed on the 'DS06 Resources' table to check for instances where a resource has recorded performance in terms of actual dollars, even though there are no actual costs recorded at the work package (WP) level by the end of the cost (EOC) period. The importance of this check lies in ensuring the accuracy and completeness of project management data. Specifically, it aims to identify discrepancies between the recorded performance of resources and the actual costs incurred, which are crucial for accurate project tracking and financial management.
The test is flagged with an 'MINOR' severity level, indicating that while it may not immediately disrupt the project's financial analysis or reporting, it highlights a potential inconsistency in data recording practices. Such inconsistencies, if not addressed, could lead to minor problems in understanding the project's financial health, resource allocation efficiency, and cost management strategies. Ensuring that performance data is accurately matched with actual cost data is essential for maintaining the integrity of financial reporting and for making informed decisions regarding project management and adjustments.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_ArePDollarsMissingDS03ADollarsWP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Cost as (
SELECT WBS_ID_CA CA, TRIM(ISNULL(WBS_ID_WP,'')) WP, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END as EOC, SUM(ACWPi_dollars) ACWPc
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_CA, WBS_ID_WP, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END
), CostWPs as (
SELECT WP, EOC, SUM(ACWPc) ACWPc
FROM Cost
WHERE WP <> ''
GROUP BY WP, EOC
), ScheduleWBS as (
SELECT S.WBS_ID WBS, R.SubP, SUM(R.Performance) Performance, R.EOC
FROM DS04_schedule S
INNER JOIN (
SELECT task_ID, ISNULL(subproject_ID,'') SubP, EOC, SUM(actual_dollars) Performance
FROM DS06_schedule_resources
WHERE upload_ID = @upload_ID AND schedule_type = 'FC'
GROUP BY task_ID, EOC, ISNULL(subproject_ID,'')
) 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, SubP, S.EOC
FROM ScheduleWBS S LEFT OUTER JOIN CostWPs C ON S.WBS = C.WP AND S.EOC = C.EOC
WHERE C.ACWPc = 0 OR C.WP IS NULL
), 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 = 'FC'
)
SELECT
R.*
FROM
DS06_schedule_resources R INNER JOIN FlagsByTask F ON R.task_ID = F.task_ID
AND ISNULL(R.subproject_ID,'') = F.SubP
AND R.EOC = F.EOC
WHERE
R.upload_id = @upload_ID
AND R.schedule_type = 'FC'
AND actual_dollars > 0
AND NOT EXISTS (SELECT 1 FROM Cost WHERE WP = '' GROUP BY CA HAVING SUM(ACWPc) > 0)
)