¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 9060284 |
| Summary | Is the performance recorded for this task misaligned with what is in cost (by EOC)? |
| Error message | Resource performance (actual_dollars) <> DS03.BCWPc (BCWPi_dollars) 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 "Resource Performance Misaligned with Cost" is designed to identify discrepancies between the recorded performance of a task and the associated cost at the end of the contract (EOC). This check is performed on the DS06 Resources table.
The error is likely to be caused by a mismatch between the actual dollars spent on a task (as recorded in the DS06 Resources table) and the budgeted cost of work performed (BCWPi_dollars) as recorded in the DS03 Cost table. This discrepancy is identified by comparing the sum of actual dollars spent on each task in the DS06 Resources table with the sum of BCWPi_dollars for each task in the DS03 Cost table, grouped by the task's Work Breakdown Structure (WBS) and EOC.
If the sums do not match, it indicates that the recorded performance of the task is not aligned with the cost at the EOC. This could be due to errors in data entry, incorrect allocation of resources, or discrepancies in the cost and schedule data.
To resolve this issue, you should review the data in the DS06 Resources and DS03 Cost tables, particularly the actual_dollars and BCWPi_dollars fields, respectively. Ensure that the actual dollars spent on each task match the budgeted cost of work performed. If necessary, correct any errors or discrepancies in the data to ensure that the performance and cost data are aligned.
¶ Why do we check this?
This test is being performed to ensure that the recorded performance of a resource aligns with the cost recorded in the DS03 table by the end of the contract (EOC). The test is checking if the actual dollars spent on a resource is not equal to the budgeted cost of work performed in constant dollars (BCWPc) by the EOC.
The importance of this check is to ensure that there is no discrepancy between the actual cost and the budgeted cost of work performed. This is crucial for maintaining accurate financial records and for effective project management. If the actual cost is higher than the budgeted cost, it could indicate overspending or inefficiencies in resource utilization. On the other hand, if the actual cost is lower, it could indicate underutilization of resources or overestimation of costs.
The severity of this check is marked as an MINOR. This means that while it may not immediately prevent data from being reviewed, it could potentially cause minor problems or indicate that the data does not follow all best practices. It's a signal to review the data and correct any discrepancies to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_ArePDollarsMisalignedWithDS03] (
@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(BCWPi_dollars) BCWP
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_WP, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END
), Resources as (
SELECT task_ID, EOC, SUM(actual_dollars) Performance, ISNULL(subproject_ID,'') SubP
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, SUM(R.Performance) Performance, 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
), 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
AND C.BCWP <> S.Performance
), FlagsByTask as (
SELECT S.task_ID, F.EOC, F.SubP
FROM DS04_schedule S INNER JOIN FlagsByWBS F ON S.WBS_ID = F.WBS
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 R.EOC = F.EOC
AND ISNULL(R.subproject_ID,'') = F.SubP
WHERE
R.upload_id = @upload_ID
AND R.schedule_type = 'FC'
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for addition of 'is_indirect' and 'subproject_ID' fields. |