¶ Basic Information
| Key | Value |
|---|---|
| Table | DS13 Subcontract |
| Severity | MAJOR |
| Unique ID | 9130529 |
| 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 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 "EAC Misaligned With Cost (WP)" for the DS13 Subcontract table is designed to identify any discrepancies between the estimated completion cost for a subcontract and the actual costs recorded in the DS03 Cost table.
The error is likely to occur when the sum of the Actual Cost of Work Performed (ACWPi_dollars) and the Estimate to Complete (ETCi_dollars) in the DS03 Cost table does not match the Estimated Cost at Completion (EAC_dollars) in the DS13 Subcontract table. This discrepancy is checked for each Work Breakdown Structure (WBS) ID associated with a subcontract.
The fields causing the issue are EAC_dollars in the DS13 Subcontract table and ACWPi_dollars and ETCi_dollars in the DS03 Cost table. The expected value for EAC_dollars in the DS13 Subcontract table should be equal to the sum of ACWPi_dollars and ETCi_dollars in the DS03 Cost table for the same WBS ID.
If the DIQ check identifies a discrepancy, it will return the problematic tasks from the DS04 Schedule table where the schedule type is 'FC'. This will help in identifying the specific tasks that are causing the misalignment between the estimated and actual costs.
¶ 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 cost in the DS13 Subcontract table. The test checks if the EAC in dollars is not equal to the sum of the actual cost of work performed (ACWP) in dollars and the estimate to complete (ETC) in dollars where the element of cost (EOC) is Subcontract. This is done by comparing the work breakdown structure (WBS) ID in the DS04 and DS03 tables.
The importance of this check is to ensure that the estimated cost at completion of a subcontract is accurately reflecting the sum of the actual cost of work performed and the estimated cost to complete the work. If these values are misaligned, it could lead to inaccurate financial forecasting and budgeting, potentially causing financial risks and project delays.
The severity of this check is marked as a MAJOR. This means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. It is crucial to address this issue to ensure accurate and reliable project cost management and reporting.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS13_SubK_IsEACMisalignedWithDS03WP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with WPEACCost as (
--Cost: WP WBS with Subcontract EAC (which is sum of ACWPi + ETCi)
SELECT WBS_ID_WP, 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_WP
), WPEACSched as (
--Schedule: WP WBS with SubK EAC
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
), ProblemWPs as (
--Comparison of Cost & Schedule
--Returned rows here are problematic WPs
SELECT WBS_ID_WP
FROM WPEACCost C INNER JOIN WPEACSched S ON C.WBS_ID_WP = S.WBS_ID
WHERE C.EAC <> S.EAC
), ProblemTasks as (
--join 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
)