¶ Basic Information
| Key | Value |
|---|---|
| Table | DS13 Subcontract |
| Severity | MAJOR |
| Unique ID | 9130515 |
| Summary | Are the actuals for this subcontract misaligned with what is in cost? |
| Error message | ACWPc_dollars <> sum of DS03.ACWPi_dollars where EOC = Subcontract (by FC DS04.WBS_ID & DS03.WBS_ID_WP). |
¶ 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 "ACWP Misaligned With Cost (WP)" for the DS13 Subcontract table is designed to identify any discrepancies between the actual costs of a subcontract and the costs recorded in the DS03 Cost table.
The error is likely to occur when the actual cost of work performed (ACWP) for a subcontract does not match the sum of the ACWP in the DS03 Cost table, where the element of cost (EOC) is 'Subcontract'. This discrepancy is identified by comparing the ACWP in dollars (ACWPc_dollars) in the DS13 Subcontract table with the sum of ACWP in dollars (ACWPi_dollars) in the DS03 Cost table.
The fields causing the issue are the ACWPc_dollars field in the DS13 Subcontract table and the ACWPi_dollars field in the DS03 Cost table. The expected values in these fields should be equal for a given work breakdown structure (WBS) ID, indicating that the actual costs and the recorded costs are aligned.
If the DIQ check identifies a discrepancy, it means that there is a misalignment between the actual costs and the recorded costs for a subcontract. This could be due to errors in data entry, incorrect cost calculations, or changes in the subcontract costs that have not been updated in the DS03 Cost table.
To resolve this issue, you should review the identified subcontracts and ensure that the actual costs are correctly recorded in the DS03 Cost table.
¶ Why do we check this?
This test is being performed to ensure that the actual cost of work performed (ACWP) for a subcontract aligns with the cost recorded in the work package (WP). The test is comparing the ACWP in dollars to the sum of the ACWP in dollars where the element of cost (EOC) is a subcontract, grouped by the work breakdown structure ID (WBS_ID) in the DS04 and DS03 tables.
The importance of this check is to ensure that the costs recorded for a subcontract are accurate and consistent across different parts of the project management system. If the ACWP for a subcontract does not match the cost recorded in the WP, it could indicate a problem with the data entry or tracking of costs, which could lead to inaccurate cost reporting and financial analysis.
The severity of this check is a MAJOR, which means that if the issue is not addressed, it could cause problems during the analysis of the project's cost data. It may not prevent the data from being reviewed, but it could lead to incorrect conclusions or decisions based on the data. Therefore, it's important to investigate and resolve any discrepancies identified by this test.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS13_SubK_IsACWPMisalignedWithDS03WP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with WPACWPCost as (
--Cost: WP WBS with Subcontract ACWP
SELECT WBS_ID_WP, SUM(ACWPi_dollars) A
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Subcontract'
GROUP BY WBS_ID_WP
), WPACWPSched as (
--Schedule: WP WBS with SubK ACWP
SELECT WBS_ID, SUM(ACWPc_Dollars) A
FROM DS04_schedule S INNER JOIN DS13_subK SK ON S.task_ID = SK.task_ID
WHERE S.upload_ID = @upload_ID AND SK.upload_ID = @upload_ID AND S.schedule_type = 'FC'
GROUP BY WBS_ID
), ProblemWPs as (
--Comparison of Cost & Schedule
--Returned rows here are problematic WPs
SELECT WBS_ID_WP
FROM WPACWPCost C INNER JOIN WPACWPSched S ON C.WBS_ID_WP = S.WBS_ID
WHERE C.A <> S.A
), ProblemTasks as (
--join back to the schedule to reveal the problem tasks
SELECT S.task_ID
FROM DS04_schedule S INNER JOIN ProblemWPs P ON S.WBS_ID = P.WBS_ID_WP
WHERE S.upload_ID = @upload_ID AND S.schedule_type = 'FC'
)
SELECT
SK.*
FROM
DS13_subK SK INNER JOIN ProblemTasks P ON SK.task_ID = P.task_ID
WHERE
SK.upload_ID = @upload_ID
)