¶ Basic Information
| Key | Value |
|---|---|
| Table | DS17 WBS EU |
| Severity | MINOR |
| Unique ID | 9170577 |
| Summary | Are the EU maximum dollars less than the cost EAC (at the CA level)? |
| Error message | EU_max_dollars < DS03.EAC by WBS_ID_CA & EOC. |
¶ 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 "EU Max Dollars Less Than EAC (CA)" is designed to ensure that the maximum dollars at the EU level (EU_max_dollars) in the DS17 WBS EU table are less than the cost estimate at completion (EAC) at the control account (CA) level in the DS03 cost table.
The check is performed by comparing the sum of EU_max_dollars for each control account and EOC in the DS17 WBS EU table with the sum of ACWPi_dollars and ETCi_dollars (which together form the EAC) for the corresponding control account and EOC in the DS03 cost table.
If the check finds any instances where EU_max_dollars is greater than the EAC, it flags these as errors. The fields causing the issue are EU_max_dollars in the DS17 WBS EU table and ACWPi_dollars and ETCi_dollars in the DS03 cost table.
The expected values are that for each control account and EOC, the sum of EU_max_dollars should be less than the sum of ACWPi_dollars and ETCi_dollars. If this is not the case, it may indicate that the maximum dollars at the EU level have been overestimated, or that the cost estimate at completion at the control account level has been underestimated.
¶ Why do we check this?
This test is being performed to ensure that the maximum dollars allocated to each work breakdown structure element (EU) are less than the estimated cost at completion (EAC) at the control account (CA) level. This is important because it helps to prevent overspending and ensures that the budget is being managed effectively. If the maximum dollars for an EU are greater than the EAC, it could indicate a potential budget overrun or misallocation of funds.
The severity of this check is classified as an MINOR. This means that while it may not immediately prevent the data from being reviewed, it could potentially cause minor problems or indicate that the data does not adhere to all best practices. It's a signal to review the budget allocation and make necessary adjustments to ensure proper financial management of the project.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS17_WBS_EU_IsEUMaxLtDS03EACCA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
--Check to see if ACWP is collected at the CA level first.
WITH CostCAEAC AS (
--Cost: CA EAC by CA WBS ID & EOC
SELECT WBS_ID_CA CAWBS, EOC, SUM(ACWPi_dollars) + SUM(ETCi_dollars) EAC
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_CA, EOC
), CAEUWBS AS (
--EU: CA EU Max dollars
SELECT A.Ancestor_WBS_ID CAWBS, E.EOC, SUM(EU_max_dollars) EUMax
FROM DS17_WBS_EU E INNER JOIN AncestryTree_Get(@upload_ID) A ON E.WBS_ID = A.WBS_ID
WHERE upload_ID = @upload_ID AND A.[Type] IN ('WP','PP') AND A.Ancestor_WBS_ID = 'CA'
GROUP BY A.Ancestor_WBS_ID, E.EOC
), FlagsByCA AS (
--Problem CAs
SELECT EU.CAWBS, EU.EOC
FROM CAEUWBS EU INNER JOIN CostCAEAC C ON EU.CAWBS = C.CAWBS AND EU.EOC = C.EOC
WHERE EU.EUMax < C.EAC
), FlagsByWP AS (
--WP's making up the problem CAs
SELECT A.WBS_ID, F.EOC
FROM FlagsByCA F INNER JOIN AncestryTree_Get(@upload_ID) A ON F.CAWBS = A.Ancestor_WBS_ID
WHERE A.[Type] IN ('WP','PP') AND A.Ancestor_WBS_ID = 'CA'
)
SELECT
E.*
FROM
DS17_WBS_EU E INNER JOIN FlagsByWP F ON E.WBS_ID = F.WBS_ID AND E.EOC = F.EOC
WHERE
upload_ID = @upload_ID
AND EXI