¶ Basic Information
| Key | Value |
|---|---|
| Table | DS13 Subcontract |
| Severity | MAJOR |
| Unique ID | 9130520 |
| Summary | Is the performance for this subcontract misaligned with what is in cost? |
| Error message | BCWPc_dollars <> sum of DS03.BCWPi_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 "BCWP Misaligned With Cost (WP)" is designed to identify any discrepancies between the performance of a subcontract and the associated costs. This check is performed on the DS13 Subcontract table.
The error message "BCWPc_dollars <> sum of DS03.BCWPi_dollars where EOC = Subcontract (by FC DS04.WBS_ID & DS03.WBS_ID_WP)" indicates that the Budgeted Cost of Work Performed (BCWP) in dollars for the subcontract does not match the sum of the individual BCWP in dollars for each work package (WP) associated with the subcontract.
This discrepancy could be caused by several factors. For instance, there may be an error in the data entry for the BCWP in dollars for the subcontract or the individual WPs. Alternatively, there may be WPs associated with the subcontract that have not been accounted for in the sum of the BCWP in dollars.
The fields causing the issue are BCWPc_dollars in the DS13 Subcontract table, BCWPi_dollars in the DS03 Cost table, and the WBS_ID_WP in both the DS03 Cost and DS04 Schedule tables.
The expected values for these fields would be that the BCWPc_dollars in the DS13 Subcontract table should equal the sum of the BCWPi_dollars for all associated WPs in the DS03 Cost table. The WBS_ID_WP in the DS03 Cost table should match the WBS_ID in the DS04 Schedule table for each WP associated with the subcontract.
If these conditions are not met, the DIQ check will flag the discrepancy for further investigation and resolution.
¶ Why do we check this?
This test is being performed to ensure that the Budgeted Cost of Work Performed (BCWP) for a subcontract aligns with the cost recorded in the system. The BCWP is a measure of the cost of the work performed, and it should match the sum of the individual costs of work performed where the Element of Cost (EOC) is a subcontract. The test is comparing the BCWP in dollars to the sum of the BCWP in dollars for individual work packages, grouped by the Work Breakdown Structure (WBS) ID.
The importance of this check is to ensure that the cost data in the system is accurate and consistent. If the BCWP for a subcontract does not match the sum of the individual costs of work performed, it could indicate a problem with the data entry or calculation of costs. This could lead to inaccurate cost reporting and financial analysis, which could impact decision-making and project management.
The severity of this check is marked as a MAJOR, which means that while it may not prevent the data from being reviewed, it could cause problems during analysis. It is important to address this issue to ensure the accuracy and reliability of the cost data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS13_SubK_IsBCWPMisalignedWithDS03] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with WPBCWPCost as (
--Cost: WP WBS with Subcontract BCWP
SELECT WBS_ID_WP, SUM(BCWPi_dollars) P
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Subcontract'
GROUP BY WBS_ID_WP
), WPBCWPSched as (
--Schedule: WP WBS with SubK BCWP
SELECT WBS_ID, SUM(BCWPc_Dollars) P
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 WPBCWPCost C INNER JOIN WPBCWPSched S ON C.WBS_ID_WP = S.WBS_ID
WHERE C.P <> S.P
), 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
)