¶ 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 (
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 (
SELECT CA, EOC, SUM(ACWPc) ACWPc
FROM Cost
GROUP BY CA, 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
), WBSHierarchy as (
SELECT WBS_ID, Ancestor_WBS_ID
FROM dbo.AncestryTree_Get(@upload_id)
WHERE [Type] = 'WP' AND Ancestor_Type = 'CA'
), ScheduleByCAs as (
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 (
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 (
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 (
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
)