| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | CRITICAL |
| Unique ID | 1030058 |
| Summary | Are actuals collected at both the CA and WP level? |
| Error message | CAs and WPs found with ACWPi <> 0 (Dollars, Hours, or FTEs) |
The Data Integrity and Quality (DIQ) check titled "Actuals At CA and WP Level" is performed on the DS03 Cost table. This check is designed to identify if actual costs are being collected at both the Control Account (CA) and Work Package (WP) levels.
The check identifies any Control Accounts (CAs) and Work Packages (WPs) where the Actual Cost of Work Performed (ACWP) is not equal to zero. This includes ACWP in terms of Dollars, Hours, or Full-Time Equivalents (FTEs).
If the check finds a mix of CAs and WPs where the ACWP is not equal to zero, it will return these records. This indicates that actual costs are being collected at both the CA and WP levels,a potential issue with the data.
The fields causing this issue are likely to be the WBS_ID_CA and WBS_ID_WP fields, which identify the Control Account and Work Package respectively, and the ACWPi_Dollars, ACWPi_Hours, and ACWPi_FTEs fields, which represent the Actual Cost of Work Performed in terms of Dollars, Hours, and Full-Time Equivalents respectively.
The expected values are for ACWP to be collected at the same level throughout the entire data set. Either all ACWP is at the CA level or all ACWP is at the WP level.
This test, titled "Actuals At CA and WP Level", is being performed on the 'DS03 Cost' table to ensure that actual costs are being collected at both the Control Account (CA) and Work Package (WP) levels. The severity of this test is marked as 'CRITICAL', which is the highest level of severity, indicating that this issue must be resolved before the data can be reviewed further.
The importance of this check lies in the need for accurate cost tracking at one level (CA or WP). This is crucial for effective project management, as it allows for detailed cost analysis and helps in identifying any cost overruns or underruns at an early stage. If actual costs are being collected at both levels, it could lead to inaccurate cost reporting and potentially mislead project management decisions.
The test is looking for instances where the Actual Cost of Work Performed incremental (ACWPi) is not equal to zero, in terms of Dollars, Hours, or Full-Time Equivalents (FTEs) and found on both CA and WP levels. This could lead to significant discrepancies in the project's financial management and needs to be corrected immediately.
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_DoesACWPExistAtCAAndWPLevels] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with AtCA as (
SELECT WBS_ID_CA
FROM DS03_cost
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(WBS_ID_WP,'')) = ''
AND (ACWPi_Dollars <> 0 OR ACWPi_Hours <> 0 OR ACWPi_FTEs <> 0)
GROUP BY WBS_ID_CA
), AtWP as (
SELECT WBS_ID_WP
FROM DS03_cost
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(WBS_ID_WP,'')) <> ''
AND (ACWPi_Dollars <> 0 OR ACWPi_Hours <> 0 OR ACWPi_FTEs <> 0)
GROUP BY WBS_ID_WP
)
SELECT
*
FROM
DS03_Cost
WHERE
upload_ID = @upload_ID
AND (ACWPi_dollars <> 0 OR ACWPi_FTEs <> 0 OR ACWPi_hours <> 0)
AND (SELECT COUNT(*) FROM AtCA) > 0
AND (SELECT COUNT(*) FROM AtWP) > 0
)