¶ Basic Information
| Key | Value |
|---|---|
| Table | DS13 Subcontract |
| Severity | MAJOR |
| Unique ID | 9130528 |
| Summary | Is the estimate at completion for this subcontract misaligned with what is in cost? |
| Error message | EAC_dollars <> sum of DS03.ACWPi_dollars + DS03.ETCi_dollars where EOC = Subcontract (by DS01.WBS_ID, DS01.parent_WBS_ID, FC DS04.WBS_ID, & DS03.WBS_ID_CA). |
¶ 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 "EAC Misaligned With Cost (CA)" for the DS13 Subcontract table is designed to identify any discrepancies between the estimated completion cost (EAC) for a subcontract and the actual costs recorded in the DS03 Cost table.
The test works by comparing the sum of the Actual Cost of Work Performed (ACWPi_dollars) and the Estimate to Complete (ETCi_dollars) in the DS03 Cost table with the EAC_dollars in the DS13 Subcontract table. The comparison is made at the Control Account (CA) level, which is identified by the WBS_ID_CA field.
If the EAC in the DS13 Subcontract table does not match the sum of ACWPi_dollars and ETCi_dollars in the DS03 Cost table for the same CA, the test will flag this as an error.
The likely cause of this error is an incorrect entry in either the EAC_dollars field in the DS13 Subcontract table or the ACWPi_dollars and ETCi_dollars fields in the DS03 Cost table. The expected value for the EAC_dollars field in the DS13 Subcontract table should be equal to the sum of the ACWPi_dollars and ETCi_dollars fields for the same CA in the DS03 Cost table.
Please review the flagged entries and ensure that the EAC, ACWPi, and ETCi values are correctly entered and aligned at the CA level.
¶ Why do we check this?
This test is being performed to ensure that the Estimate at Completion (EAC) for a subcontract aligns with the actual costs recorded in the system. The test checks if the EAC in dollars is not equal to the sum of the Actual Cost of Work Performed (ACWP) and the Estimate to Complete (ETC) for the same subcontract. This is done by comparing the EAC and the sum of ACWP and ETC for each Work Breakdown Structure (WBS) ID and parent WBS ID.
The importance of this check is to ensure that the financial data in the system is accurate and consistent. If the EAC does not align with the actual costs, it could indicate a problem with the cost estimation or recording process. This could lead to inaccurate financial reporting and decision-making. The severity of this check is marked as a MAJOR, which means that if this issue is not addressed, it could cause problems during the analysis of the project's financial data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS13_SubK_IsEACMisalignedWithDS03CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CAEACCost as (
SELECT WBS_ID_CA, SUM(ISNULL(ACWPi_dollars,0) + ISNULL(ETCi_dollars,0)) EAC
FROM DS03_cost
WHERE upload_ID = @upload_Id AND EOC = 'Subcontract'
GROUP BY WBS_ID_CA
), WPEACSched as (
SELECT WBS_ID, SUM(EAC_dollars) EAC
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
), CAEACSched as (
SELECT A.Ancestor_WBS_ID CAWBS, SUM(S.EAC) EAC
FROM WPEACSched S INNER JOIN AncestryTree_Get(@upload_Id) A ON S.WBS_ID = A.WBS_ID
WHERE A.[Type] = 'WP' AND A.Ancestor_Type = 'CA'
GROUP BY A.Ancestor_WBS_ID
), ProblemCAs as (
SELECT WBS_ID_CA
FROM CAEACCost C INNER JOIN CAEACSched S ON C.WBS_ID_CA = S.CAWBS
WHERE C.EAC <> S.EAC
), ProblemTasks as (
SELECT S.task_ID
FROM DS04_schedule S INNER JOIN AncestryTree_Get(@upload_Id) A ON S.WBS_ID = A.Ancestor_WBS_ID
WHERE S.upload_ID = @upload_Id AND S.schedule_type = 'FC' AND A.[Type] = 'WP' AND A.Ancestor_Type = 'CA'
AND A.Ancestor_WBS_ID IN (SELECT WBS_ID_CA FROM ProblemCAs)
)
SELECT
SK.*
FROM
DS13_subK SK INNER JOIN ProblemTasks P ON SK.task_ID = P.task_ID
WHERE
SK.upload_ID = @upload_Id
AND EXISTS (
SELECT 1
FROM DS03_cost
WHERE upload_ID = @upload_Id AND TRIM(ISNULL(WBS_ID_WP,'')) = '' AND ACWPi_Dollars > 0
)
)