Note: DIQ has been deleted.
¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 9060286 |
| Summary | Has this resource recorded performance even though actuals are not recorded in cost (by EOC)? |
| Error message | Resource performance (actual_dollars) > 0 even though DS03.ACWPc = 0 (SUM of ACWSi_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 without Cost Actuals" is designed to identify any discrepancies between the recorded resource performance and the actual costs in the DS06 Resources and DS03 Cost tables respectively.
The test is specifically looking for instances where the resource performance (actual dollars) is greater than zero, but the actual cost (ACWPc) is not recorded or is zero. This discrepancy could be due to a variety of reasons such as data entry errors, system glitches, or missing data.
The fields causing the issue are the 'actual_dollars' field in the DS06 Resources table and the 'ACWPi_dollars' field in the DS03 Cost table. The expected values for these fields should be consistent with each other. That is, if the 'actual_dollars' field in the DS06 Resources table is greater than zero, then the 'ACWPi_dollars' field in the DS03 Cost table should also be greater than zero.
If this DIQ check identifies any discrepancies, it is recommended to review the data entry process for these fields and ensure that all data is being accurately recorded and updated.
¶ Why do we check this?
This test is being performed to ensure that there is no discrepancy between the recorded resource performance and the actual costs recorded in the DS06 Resources table. Specifically, it checks if there is any resource performance recorded (actual_dollars > 0) even though no actual costs (DS03.ACWPc = 0) have been recorded by the end of the contract (EOC).
The importance of this check lies in maintaining the accuracy and consistency of the data. If a resource has recorded performance, it should logically have some associated costs. If no costs are recorded, it could indicate a data entry error or a problem with the cost tracking system.
The severity of this check is marked as an MINOR, which means it's not a critical issue that would prevent the data from being reviewed. However, it's still important to address as it could potentially cause minor problems in the data analysis or indicate that the data doesn't fully adhere to best practices.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_ArePDollarsMissingDS03ADollars] (
@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
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_WP, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END
HAVING SUM(ACWPi_dollars) = 0
), 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, S.EOC, S.SubP
FROM ScheduleWBS S INNER JOIN CostWBS C ON C.EOC = S.EOC
AND C.WBS = S.WBS
), FlagsByTask as (
SELECT S.task_ID, F.EOC, F.SubP
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'
)