¶ Basic Information
| Key | Value |
|---|---|
| Table | DS13 Subcontract |
| Severity | MAJOR |
| Unique ID | 9130519 |
| Summary | Is the budget for this subcontract misaligned with what is in cost? |
| Error message | BAC_dollars <> sum of DS03.BCWSi_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 "BAC Misaligned With Cost (WP)" is designed to identify any discrepancies between the budgeted cost for a subcontract and the actual cost recorded in the DS03 Cost table. This check is performed on the DS13 Subcontract table.
The error message "BAC_dollars <> sum of DS03.BCWSi_dollars where EOC = Subcontract (by FC DS04.WBS_ID & DS03.WBS_ID_WP)" indicates that the total budgeted cost (BAC_dollars) for a subcontract does not match the sum of the individual cost work scheduled (BCWSi_dollars) entries for that subcontract in the DS03 Cost table.
This discrepancy could be caused by several factors. For instance, there may be errors in the data entry process, where the budgeted cost for a subcontract was not correctly recorded in the DS13 Subcontract table. Alternatively, the individual cost work scheduled entries in the DS03 Cost table may not have been correctly summed up.
The fields causing this issue are the BAC_dollars field in the DS13 Subcontract table and the BCWSi_dollars field in the DS03 Cost table. The expected value for the BAC_dollars field should be equal to the sum of the BCWSi_dollars entries for the corresponding subcontract in the DS03 Cost table.
To resolve this issue, you should review the data in these fields and correct any discrepancies. This may involve adjusting the budgeted cost for the subcontract in the DS13 Subcontract table, or correcting the individual cost work scheduled entries in the DS03 Cost table.
¶ Why do we check this?
This test is being performed to ensure that the Budget at Completion (BAC) for a subcontract in the DS13 Subcontract table aligns with the cost as recorded in the DS03 table. The test checks if the BAC in dollars is not equal to the sum of the Budgeted Cost of Work Scheduled (BCWSi) in dollars where the Element of Cost (EOC) is Subcontract, grouped by the Fund Center (FC) and Work Breakdown Structure ID (WBS_ID & WBS_ID_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 or cost recording, which could lead to financial discrepancies and mismanagement. This could potentially cause problems during financial analysis and decision-making processes.
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 if not addressed. It is therefore recommended that this issue be resolved to ensure accurate and reliable data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS13_SubK_IsBACMisalignedWithDS03] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with WPBACCost as (
--Cost: WP WBS with Subcontract DB (which is sum of BCWSi)
SELECT WBS_ID_WP, SUM(BCWSi_dollars) BAC
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Subcontract'
GROUP BY WBS_ID_WP
), WPBACSched as (
--Schedule: WP WBS with SubK BCWS
SELECT WBS_ID, SUM(BAC_dollars) BAC
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 WPBACCost C INNER JOIN WPBACSched S ON C.WBS_ID_WP = S.WBS_ID
WHERE C.BAC <> S.BAC
), 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
)