¶ Basic Information
| Key | Value |
|---|---|
| Table | DS08 WAD |
| Severity | MAJOR |
| Unique ID | 9080416 |
| Summary | Are the material budget dollars for this CA WAD missing in cost? |
| Error message | budget_material_dollars > 0 & DS03.BCWSi_dollars = 0 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 Missing In Cost" is designed to identify any discrepancies in the DS08 WAD table where the budgeted material dollars for a Control Account Work Authorization Document (CA WAD) are not reflected in the cost data in the DS03 cost table.
The error is likely to occur when the 'budget_material_dollars' field in the DS08 WAD table has a value greater than zero, indicating that there is a budget for materials, but the corresponding 'BCWSi_dollars' field in the DS03 cost table is zero. This discrepancy suggests that the budgeted material costs for the CA WAD are not being accounted for in the cost data.
The check also considers the 'EOC' field in the DS03 cost table, which should have the value 'Material' for these records. If the 'EOC' field does not have this value, it could be another reason for the discrepancy.
The 'WBS_ID_CA' field is used to link the DS08 WAD and DS03 cost tables. If there is a mismatch in these fields between the two tables, it could also lead to this error.
To resolve this issue, ensure that the 'budget_material_dollars' in the DS08 WAD table matches the 'BCWSi_dollars' in the DS03 cost table for each CA WAD, and that the 'EOC' field in the DS03 cost table is correctly set to 'Material'. Also, verify that the 'WBS_ID_CA' fields in both tables correctly correspond to each other.
¶ 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) are not missing in the cost. The test checks if there are material budget dollars allocated (budget_material_dollars > 0) but no corresponding budgeted cost for work scheduled (BCWSi_dollars = 0) for material at the end of the control (EOC = material) by Work Breakdown Structure ID Control Account (WBS_ID_CA).
The importance of this check is to ensure that all costs associated with materials are accurately captured and accounted for in the project's budget. If material costs are not properly accounted for, it could lead to inaccurate budgeting and financial reporting, which could in turn impact the project's financial health and completion timeline.
The severity of this check is marked as a MAJOR. This means that while it may not immediately prevent the data from being reviewed, it is likely to cause problems during analysis. It is crucial to address this issue to ensure accurate and reliable project cost management and reporting.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS08_WAD_IsMatBudgetMissingInDS03CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with NonMaterialCA as (
SELECT DISTINCT WBS_ID_CA
FROM DS03_cost
WHERE
upload_ID = @upload_ID
AND WBS_ID_WP NOT IN (
SELECT WBS_ID_WP
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC = 'Material' AND BCWSi_dollars <> 0
)
)
SELECT
W.*
FROM
DS08_WAD W INNER JOIN NonMaterialCA C ON W.WBS_ID = C.WBS_ID_CA
WHERE
upload_ID = @upload_ID
AND TRIM(ISNULL(W.WBS_ID_WP,'')) = ''
AND budget_material_dollars <> 0
)