Note: DIQ has been deleted.
¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030092 |
| Summary | Does this SLPP, PP, or WP comingle Material with other EOC types (excluding Overhead)? |
| Error message | EOC = Material & Subcontract, ODC, or Labor by WBS_ID_WP or 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 "Material Comingled with Non-Overhead EOCs" is designed to ensure that the data in the DS03 Cost table is correctly categorized. Specifically, it checks whether any single SLPP, PP, or WP is incorrectly combining Material with other types of EOCs (excluding Overhead).
The error message "EOC = Material & Subcontract, ODC, or Labor by WBS_ID_WP or WBS_ID_CA" indicates that there is a mix of Material and other EOC types (Subcontract, ODC, or Labor) within the same WBS_ID_WP or WBS_ID_CA. This is not expected as per the standard data categorization rules.
The fields causing the issue are EOC, WBS_ID_WP, and WBS_ID_CA in the DS03 Cost table, and the WBS_ID and type fields in the DS01_WBS table. The EOC field should only contain 'Material' or 'Overhead' for each WBS_ID_WP or WBS_ID_CA. The WBS_ID_WP and WBS_ID_CA fields should not be associated with both 'Material' and other EOC types. The WBS_ID field in the DS01_WBS table should match with either WBS_ID_WP or WBS_ID_CA in the DS03 Cost table, and the type field should correspond to the correct categorization ('WP', 'PP', or 'SLPP').
If the DIQ check fails, it suggests that there is a data integrity issue that needs to be addressed. The data in the DS03 Cost table should be reviewed and corrected to ensure that Material is not comingled with other EOC types within the same WBS_ID_WP or WBS_ID_CA.
¶ Why do we check this?
This test is being performed on the 'DS03 Cost' table to check if there is any commingling of Material with other types of EOCs (excluding Overhead) in the SLPP, PP, or WP. The test is looking for instances where EOC equals Material & Subcontract, ODC, or Labor by WBS_ID_WP or WBS_ID_CA.
The importance of this check is to ensure that the data is correctly categorized and there is no mixing of different types of EOCs. This is crucial for accurate cost tracking and project management. If Material costs are mixed with other EOC types, it could lead to inaccurate cost calculations and misinterpretation of the data.
The severity of this check is marked as 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. It is important to address this issue to ensure the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsMatComingledWithNonOvhdEOCs] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with NonMaterial AS (
SELECT WBS_ID_CA CAID, ISNULL(WBS_ID_WP,'') WPID
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC NOT IN ('Material','Overhead')
GROUP BY WBS_ID_CA, WBS_ID_WP
), WBS as (
SELECT WBS_ID, type
FROM DS01_WBS
WHERE upload_ID = @upload_ID
)
SELECT
C.*
FROM
DS03_Cost C LEFT OUTER JOIN NonMaterial N ON C.WBS_ID_CA = N.CAID
AND ISNULL(C.WBS_ID_WP,'') = N.WPID
WHERE
upload_ID = @upload_ID
AND EOC = 'Material'
AND (
WBS_ID_WP IN (SELECT WBS_ID FROM WBS WHERE type IN ('WP','PP'))
OR WBS_ID_CA IN (SELECT WBS_ID FROM WBS WHERE type = 'SLPP')
)
)