¶ Basic Information
| Key | Value |
|---|---|
| Table | DS13 Subcontract |
| Severity | MAJOR |
| Unique ID | 9130521 |
| Summary | Is the cumulative budget for this subcontract misaligned with what is in cost? |
| Error message | BCWSc_dollars <> sum of DS03.BCWSi_dollars where EOC = Subcontract & period_date <= CPP_Status_Date (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 "BCWS Misaligned With Cost (WP)" is designed to identify any discrepancies between the cumulative budget for a subcontract and the cost data in DS03. This check is performed on the DS13 Subcontract table.
The error is likely to be caused by a misalignment between the Budgeted Cost of Work Scheduled (BCWS) in dollars (BCWSc_dollars) and the sum of BCWSi_dollars in DS03 where the Element of Cost (EOC) is 'Subcontract' and the period date is less than or equal to the Current Period Plan (CPP) Status Date. This comparison is made for each Work Breakdown Structure (WBS) ID in DS04 and DS03.
If the sum of BCWSi_dollars in DS03 does not match the BCWSc_dollars in DS13 for the same WBS ID, the DIQ check will flag this as an issue. This could be due to incorrect data entry, a change in the subcontract budget that has not been reflected in the cost data, or a discrepancy in the period dates.
The expected value for BCWSc_dollars in DS13 should be equal to the sum of BCWSi_dollars in DS03 for the same WBS ID, where the EOC is 'Subcontract' and the period date is less than or equal to the CPP Status Date. If these values do not match, it is recommended to review and correct the data in DS03 and DS13 to ensure data integrity and quality.
¶ Why do we check this?
This test is being performed to ensure that the cumulative budget for a subcontract aligns with the cost recorded in the system. The test checks if the Budgeted Cost of Work Scheduled (BCWS) in dollars for a particular subcontract is not equal to the sum of BCWS in dollars where the Element of Cost (EOC) is Subcontract and the period date is less than or equal to the Current Physical Percent (CPP) Status Date. This check is performed by the Funding Code (FC) for Work Breakdown Structure (WBS) ID and WBS ID for Work Package (WP).
The importance of this check is to ensure that the budgeted cost and the actual cost for a subcontract are in alignment. If they are not, it could indicate errors in budgeting, cost recording, or both. This could lead to financial discrepancies, misallocation of resources, and potential project delays. The severity of this check is marked as a MAJOR, which means that if this issue is not addressed, it could cause significant problems during the analysis of the project's financial data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS13_SubK_IsBCWSMisalignedWithDS03] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with WPBCWSCost as (
--Cost: WP WBS with Subcontract BCWSc (which is BCWSi up to the current period)
SELECT WBS_ID_WP, SUM(BCWSi_dollars) S
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Subcontract' AND period_date <= CPP_status_date
GROUP BY WBS_ID_WP
), WPBCWSSched as (
--Schedule: WP WBS with SubK BCWS
SELECT WBS_ID, SUM(BCWSc_Dollars) S
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 WPBCWSCost C INNER JOIN WPBCWSSched S ON C.WBS_ID_WP = S.WBS_ID
WHERE C.S <> S.S
), 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
)