¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030065 |
| Summary | Are there estimates on this CA or PP? |
| Error message | CA or PP with ETCi <> 0 (Dollars, Hours, or FTEs). |
¶ 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 "Estimates on PP or CA" is designed to ensure that there are estimates on the Control Account (CA) or Planning Package (PP) in the DS03 Cost table.
The test checks for any CA or PP that has an Estimate to Complete (ETC) value not equal to zero. This ETC value can be in terms of dollars, hours, or Full-Time Equivalents (FTEs).
If the test fails, it means that there are CAs or PPs in the DS03 Cost table that have an ETC value of zero. This could be due to an error in data entry or a missing estimate.
The fields causing the issue are the ETCi_dollars, ETCi_FTEs, and ETCi_hours fields in the DS03 Cost table. The expected values for these fields should be any number other than zero.
The test also checks the WBS_ID_WP and WBS_ID_CA fields in the DS03 Cost table to ensure they match with the WBS_ID field in the DS01_WBS table. If these fields do not match, it could indicate a misalignment between the work breakdown structure and the cost data.
In summary, to pass this DIQ check, ensure that all CAs or PPs have an ETC value not equal to zero and that the WBS_ID_WP and WBS_ID_CA fields in the DS03 Cost table correctly correspond to the WBS_ID field in the DS01_WBS table.
¶ Why do we check this?
This test is being performed on the 'DS03 Cost' table to check if there are any estimates on the Contractual Agreement (CA) or Project Plan (PP). The test is looking for any instances where the Estimate to Complete (ETCi) is not equal to zero, which would indicate that there are still estimated costs, hours, or Full-Time Equivalents (FTEs) associated with the CA or PP.
The severity of this test is marked as a 'MAJOR', which means that while it may not prevent the data from being reviewed, it could potentially cause problems during the analysis. If there are estimates on the CA or PP, it could mean that the project is not yet complete or that there are still outstanding costs or resources that need to be accounted for.
This check is important because it helps to ensure the accuracy and completeness of the project management data. If estimates are not properly accounted for, it could lead to inaccurate reporting or financial discrepancies. Therefore, it's crucial to identify and address any instances where the ETCi is not equal to zero.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_DoesETCExistOnPPOrCA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with ToTest (WBSID) AS (
SELECT WBS_ID
FROM DS01_WBS
WHERE upload_ID = @upload_ID AND type in ('CA','PP')
)
SELECT
*
FROM
DS03_Cost
WHERE
upload_ID = @upload_ID
AND (ETCi_dollars <> 0 OR ETCi_FTEs <> 0 OR ETCi_hours <> 0)
AND (
(TRIM(ISNULL(WBS_ID_WP,'')) <> '' AND WBS_ID_WP IN (SELECT WBSID FROM ToTest)) OR
(TRIM(ISNULL(WBS_ID_WP,'')) = '' AND WBS_ID_CA IN (SELECT WBSID FROM ToTest))
)
)