Note: DIQ has been deleted.
¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 1030110 |
| Summary | Does this SLPP, PP, or WP mingle Subcontract with other EOC types (excluding Overhead)? |
| Error message | EOC = Subcontract & Material, 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 "Subcontract 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 Subcontract, PP, or WP is improperly mixed with other types of EOCs (excluding Overhead).
The error message "EOC = Subcontract & Material, ODC, or Labor by WBS_ID_WP or WBS_ID_CA" indicates that there are instances where the EOC field, which should only contain 'Subcontract', also includes 'Material', 'ODC', or 'Labor'. This is likely due to incorrect data entry or categorization.
The fields causing the issue are the EOC, WBS_ID_WP, and WBS_ID_CA fields in the DS03 Cost table. The EOC field should only contain 'Subcontract' for the records in question, while the WBS_ID_WP and WBS_ID_CA fields should correspond to the correct Work Breakdown Structure (WBS) identifiers in the DS01 WBS table.
To resolve this issue, ensure that the EOC field only contains 'Subcontract' for the relevant records, and that the WBS_ID_WP and WBS_ID_CA fields match the correct WBS identifiers in the DS01 WBS table.
¶ Why do we check this?
This test is being performed to ensure that the data in the 'DS03 Cost' table is correctly segregated. Specifically, it is checking whether Subcontract costs are being improperly mixed with other types of Element of Cost (EOC) such as Material, Other Direct Costs (ODC), or Labor, under the same Work Breakdown Structure (WBS) ID for Work Package (WP) or Control Account (CA). This is important because each type of cost should be tracked separately for accurate cost accounting and project management.
The severity of this test is marked as 'MAJOR', which means that if this issue is not addressed, it is likely to cause problems during data analysis. It may lead to inaccurate cost calculations, misallocation of resources, or incorrect financial reporting. Therefore, it is crucial to ensure that Subcontract costs are not comingled with other EOC types, except for Overhead costs.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_IsSubKComingledWithNonOvhdEOCs] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with NonSubcontract AS (
SELECT WBS_ID_CA CAID, ISNULL(WBS_ID_WP,'') WPID
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EOC NOT IN ('Subcontract','Overhead')
GROUP BY WBS_ID_CA, WBS_ID_WP
)
SELECT
C.*
FROM
DS03_Cost C LEFT OUTER JOIN NonSubcontract N ON C.WBS_ID_CA = N.CAID
AND ISNULL(C.WBS_ID_WP,'') = N.WPID
WHERE
upload_ID = @upload_ID
AND EOC = 'Subcontract'
AND N.CAID IS NOT NULL
AND (
WBS_ID_WP IN (SELECT WBS_ID FROM DS01_WBS WHERE upload_ID = @upload_ID AND type IN ('WP','PP')) OR
WBS_ID_CA IN (SELECT WBS_ID FROM DS01_WBS WHERE upload_ID = @upload_ID AND type = 'SLPP')
)
)