¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 9060304 |
| Summary | Has this resource recorded performance even though actuals are not recorded in cost (by EOC, at the CA level)? |
| Error message | Resource performance (actual_dollars) > 0 even though DS03.ACWPc = 0 (SUM of ACWSi_dollars) by EOC (CA 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, even though there are no corresponding actual cost data recorded at the Cost Account (CA) level in the DS03 Cost table. Specifically, it flags when the sum of actual cost work performed in dollars (ACWPc) for a given Element of Cost (EOC) at the CA level is zero, but there is recorded performance (actual_dollars) for resources associated with that EOC.
The likely cause of this error could be due to discrepancies in data entry or data integration processes where performance data has been updated without the corresponding update to the actual cost data. This could happen for several reasons, such as delays in financial reporting, errors in cost allocation, or simply oversight in updating the cost tables after recording resource performance.
The fields causing the issue are primarily the actual_dollars field in the DS06 Resources table, which shows a positive value indicating recorded performance, and the ACWPc field in the DS03 Cost table, which should reflect the sum of actual cost work performed in dollars but shows a value of zero for the corresponding EOC at the CA level. Expected values would be a positive ACWPc value that matches or correlates with the recorded performance in actual_dollars for a given resource, ensuring that both performance and cost actuals are accurately captured and reported.
To resolve this issue, users should review and verify the accuracy of both performance and cost data entries, ensuring that all actual costs are appropriately recorded and updated in the DS03 Cost table to match the performance data recorded in the DS06 Resources table. This may involve cross-referencing financial reports, reconciling data entries, or correcting data integration workflows to ensure consistency and accuracy in project management data.
¶ Why do we check this?
This DIQ test, titled "Resource Performance without Cost Actuals (CA)," is being performed on the "DS06 Resources" table to identify instances where a resource has recorded performance, as indicated by actual dollars being greater than zero, even though there are no actual costs recorded at the Cost Account (CA) level by the End of Contract (EOC). The severity of this test is marked as an MINOR, indicating that while it may not be critical, it highlights a potential inconsistency in the data that could lead to minor problems or suggests that the data does not adhere to all best practices.
The importance of this check lies in ensuring the accuracy and completeness of project management data. Actual cost data (ACWPc) is crucial for evaluating the financial performance of a project, and discrepancies between recorded resource performance and actual costs can lead to misleading conclusions about the project's financial health and efficiency. By identifying such discrepancies, this test helps in maintaining the integrity of the project's financial data, ensuring that resource performance is accurately reflected in financial terms. This, in turn, supports better decision-making and more accurate project tracking and reporting.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_ArePDollarsMissingDS03ADollarsCA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Cost as (
--Cost WBSs with ACWP dollars
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, TRIM(ISNULL(WBS_ID_WP,'')), CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END
), CostCAs as (
--Cost CAs without ACWP dollars
SELECT CA, EOC, SUM(ACWPc) ACWPc
FROM Cost
GROUP BY CA, EOC
), ScheduleWBS as (
--resource labor performance by WBS ID
SELECT SchedWBS WBS, EOC, SubprojectID SubP, ResPerformance_dollars_FC Performance
FROM WBSRollup_WP
WHERE upload_ID = @upload_id AND ISNULL(ResPerformance_dollars_FC,0) > 0
), WBSHierarchy as (
--WBS Hierarchy
SELECT WBS_ID, Ancestor_WBS_ID
FROM AncestryTree
WHERE upload_id = @upload_ID AND [Type] = 'WP' AND Ancestor_Type = 'CA'
), ScheduleByCAs as (
--Schedule data by CA WBS ID, WP WBS, EOC, & SubP
SELECT W.Ancestor_WBS_ID CAWBS, S.WBS, SubP, S.EOC
FROM ScheduleWBS S INNER JOIN WBSHierarchy W ON S.WBS = W.WBS_ID
), FlagsByWPWBS as (
--left join the Cost CAs to the Schedule CAs by CA & EOC
--any missed join or where ACWPc = 0 is a problem CA
SELECT S.WBS, SubP, S.EOC
FROM ScheduleByCAs S LEFT OUTER JOIN CostCAs C ON S.CAWBS = C.CA AND S.EOC = C.EOC
WHERE C.CA IS NULL OR C.ACWPc = 0
), FlagsByWPWBS_WithoutScenarioDWPs as (
--exclude Scenario D WPs that have ACWPc
SELECT F.*
FROM FlagsByWPWBS F INNER JOIN Cost C ON F.WBS = C.WP AND F.EOC = C.EOC
WHERE C.WP <> '' AND C.ACWPc = 0
), FlagsByTask as (
--join the problem WP WBSs to schedule to get the tasks that make up those WBSs.
SELECT S.task_ID, F.SubP, F.EOC
FROM DS04_schedule S INNER JOIN FlagsByWPWBS_WithoutScenarioDWPs 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 EXISTS (SELECT 1 FROM Cost WHERE WP = '' AND ACWPc > 0) --run only if Actuals are collected at the CA level
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2025-07-09 | Performance optimizations. Replaced several CTEs & joins with a direct call to WBSRollup_WP. Replaced function call, AncestryTree_Get, with direct call to table, AncestryTree, which is now loaded prior to DIQs running. |