¶ Basic Information
| Key | Value |
|---|---|
| Table | DS13 Subcontract |
| Severity | MAJOR |
| Unique ID | 9130514 |
| 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 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 "ACWP Misaligned With Cost (CA)" is designed to identify discrepancies between the actual costs of a subcontract and the costs recorded in the cost account (CA) for the DS13 Subcontract table.
The error is likely to occur when the actual cost of work performed (ACWP) in dollars for a subcontract does not match the sum of the ACWP in dollars for the corresponding cost account in the DS03 Cost table. This discrepancy is identified by comparing the ACWP values for each Work Breakdown Structure (WBS) ID in the DS03 Cost table and the DS04 Schedule table.
If the ACWP values for a given WBS ID do not match between these two tables, the DIQ check will flag this as an error. This could be due to incorrect data entry, a miscalculation, or a misalignment between the cost and schedule data.
To resolve this issue, you should review the ACWP values for the flagged WBS IDs in both the DS03 Cost and DS04 Schedule tables. Ensure that the actual costs for the subcontract align with the costs recorded in the cost account. The expected value for the ACWP in the DS13 Subcontract table should be equal to the sum of the ACWP for the corresponding WBS ID 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 system. 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. This comparison is done by Work Breakdown Structure ID (WBS_ID), parent WBS_ID, and WBS_ID_CA.
The importance of this check is to ensure that the costs recorded for a subcontract are accurate and consistent across different parts of the system. If the costs are misaligned, it could lead to inaccurate financial reporting and decision making. The severity of this check is a MAJOR, which means that if the issue is not addressed, it could cause problems during data analysis. However, it does not necessarily mean that the data cannot be reviewed. It is a signal that there might be potential issues that need to be addressed to ensure the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS13_SubK_IsACWPMisalignedWithDS03CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CAACWPCost as (
--Cost: CA WBS with Subcontract ACWP
SELECT WBS_ID_CA, SUM(ACWPi_dollars) A
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Subcontract'
GROUP BY WBS_ID_CA
), 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
), CAACWPSched as (
--Schedule: CA WBS with SubK ACWP
SELECT A.Ancestor_WBS_ID CAWBS, SUM(W.A) A
FROM WPACWPSched W INNER JOIN AncestryTree_Get(@upload_ID) A ON W.WBS_ID = A.WBS_ID
WHERE A.[Type] = 'WP' AND A.Ancestor_Type = 'CA'
GROUP BY A.Ancestor_WBS_ID
), ProblemCAs as (
--Comparison of Cost & Schedule
--Returned rows here are problematic CAs
SELECT WBS_ID_CA
FROM CAACWPCost C INNER JOIN CAACWPSched S ON C.WBS_ID_CA = S.CAWBS
WHERE C.A <> S.A
), ProblemTasks as (
--Use AncestryTree_Get to join back to the schedule, filter by CA IDs in ProblemCAs,
--and select the problem tasks
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
WH