¶ Basic Information
| Key | Value |
|---|---|
| Table | DS08 WAD |
| Severity | MAJOR |
| Unique ID | 9080390 |
| Summary | Are the material budget dollars for this CA WAD misaligned with what is in cost? |
| Error message | budget_material_dollars <> SUM(DS03.BCWSi_dollars) where EOC = material (by 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 "CA Material Dollars Misaligned With Cost" is designed to ensure that the material budget dollars for a Control Account (CA) Work Authorization Document (WAD) align with the cost data. This check is performed on the DS08 WAD table.
The error message "budget_material_dollars <> SUM(DS03.BCWSi_dollars) where EOC = material (by WBS_ID_CA)" indicates that the total budgeted cost for work scheduled (BCWSi_dollars) in the DS03 cost table, specifically for material costs (EOC = 'Material'), does not match the budgeted material dollars for the corresponding control account in the DS08 WAD table.
The fields causing the issue are 'budget_material_dollars' in the DS08 WAD table and 'BCWSi_dollars' in the DS03 cost table. The expected values for these fields should be equal for each control account when the type of cost is 'Material'.
If this error occurs, it suggests that there may be discrepancies between the budgeted material costs in the WAD and the actual scheduled costs in the cost table. This could be due to data entry errors, changes in material costs that have not been updated in both tables, or issues with cost allocation to the correct control account.
To resolve this issue, you should review and verify the budgeted material dollars in the DS08 WAD table and the scheduled material costs in the DS03 cost table for each control account. Ensure that any changes in costs are accurately reflected in both tables.
¶ Why do we check this?
This test is being performed to ensure that the material budget dollars for a particular Control Account Work Authorization Document (CA WAD) align with the cost data. The test is comparing the budgeted material dollars with the sum of the Budgeted Cost of Work Scheduled (BCWS) for items where the Element of Cost (EOC) is material, grouped by the Work Breakdown Structure ID for the Control Account (WBS_ID_CA).
The importance of this check is to ensure that the budgeted costs for materials in the project align with the scheduled costs. If these values are misaligned, it could indicate errors in budgeting or scheduling, which could lead to financial discrepancies and potential project delays.
The severity of this check is marked as a MAJOR. This means that while the data can still be reviewed, any misalignment between the material budget dollars and the cost data is likely to cause problems during analysis. It is recommended that this issue be addressed to ensure accurate project management and financial reporting.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS08_WAD_AreMatDollarsMisalignedWithDS03CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with MaterialCA as (
SELECT WBS_ID_CA, SUM(BCWSi_dollars) BCWSc
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Material'
GROUP BY WBS_ID_CA
)
SELECT
W.*
FROM
DS08_WAD W INNER JOIN MaterialCA C ON W.WBS_ID = C.WBS_ID_CA
AND budget_material_dollars <> C.BCWSc
WHERE
upload_ID = @upload_ID
AND TRIM(ISNULL(W.WBS_ID_WP,'')) = ''
)