¶ Basic Information
| Key | Value |
|---|---|
| Table | DS08 WAD |
| Severity | MAJOR |
| Unique ID | 9080614 |
| Summary | Is the POP start for this Control Account after the first recorded SPAE value in cost? |
| Error message | pop_start_date > max DS03.period_date where BCWS, BCWP, ACWP, or ETC <> 0 (by DS08.WBS_ID & DS03.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 "POP Start After Cost Start (CA)" is designed to ensure that the Period of Performance (POP) start date for a Control Account (CA) does not occur after the first recorded SPAE value in cost. This check is performed on the DS08 WAD table.
The error message "pop_start_date > max DS03.period_date where BCWS, BCWP, ACWP, or ETC <> 0 (by DS08.WBS_ID & DS03.WBS_ID_CA)" indicates that the POP start date is later than the maximum period date in the DS03 table where the values for Budgeted Cost of Work Scheduled (BCWS), Budgeted Cost of Work Performed (BCWP), Actual Cost of Work Performed (ACWP), or Estimate to Complete (ETC) are not equal to zero. This is determined by matching the Work Breakdown Structure (WBS) ID in the DS08 table with the WBS ID for the Control Account in the DS03 table.
The likely cause of this error is an incorrect entry in the POP start date field in the DS08 WAD table or in the period date field in the DS03 table. The expected values for these fields should be such that the POP start date is not later than the first recorded SPAE value in cost.
To resolve this issue, review the entries in the POP start date field in the DS08 WAD table and the period date field in the DS03 table. Ensure that the POP start date for each Control Account is not later than the first recorded SPAE value in cost.
¶ Why do we check this?
This test is being performed to ensure that the Planned Order of Production (POP) start date for a specific Control Account is not set after the first recorded Schedule Performance Analysis Evaluation (SPAE) value in cost. This is important because if the POP start date is after the first cost recording, it could indicate that costs are being incurred before the project has officially started, which could lead to budget overruns or misallocation of resources.
The severity of this test 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. For instance, it could skew cost performance metrics or lead to inaccurate forecasting. Therefore, it's crucial to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS08_WAD_IsPOPStartAfterDS03StartCARollup] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostStart as (
--collect SPAE at CA WBS level
SELECT WBS_ID_CA CAWBS, MIN(period_date) Period
FROM DS03_cost
WHERE upload_ID = @upload_ID AND (
BCWSi_dollars <> 0 OR BCWSi_hours <> 0 OR BCWSi_FTEs <> 0 OR
BCWPi_dollars <> 0 OR BCWPi_hours <> 0 OR BCWPi_FTEs <> 0 OR
ACWPi_dollars <> 0 OR ACWPi_hours <> 0 OR ACWPi_FTEs <> 0 OR
ETCi_dollars <> 0 OR ETCi_hours <> 0 OR ETCi_FTEs <> 0
)
GROUP BY WBS_ID_CA
), CAWADRollup as (
--rollup WP-level WADs to their CAs & collect latest POP start
SELECT W.WBS_ID, MAX(W.auth_PM_date) PMAuth, MAX(POP_start_date) PStart
FROM DS08_WAD W INNER JOIN LatestWPWADRev_Get(@upload_ID) R ON W.WBS_ID_WP = R.WBS_ID_WP
AND W.auth_PM_date = R.PMAuth
WHERE upload_ID = @upload_id
GROUP BY W.WBS_ID
), Flags as (
-- compare
SELECT W.WBS_ID, PMAuth
FROM CAWADRollup W INNER JOIN CostStart C ON W.WBS_ID = C.CAWBS
AND W.PStart > C.[Period]
)
SELECT
W.*
FROM
DS08_WAD W INNER JOIN Flags F ON W.WBS_ID = F.WBS_ID
AND W.auth_PM_date = F.PMAuth
WHERE
upload_ID = @upload_ID
AND ( --return only if WADs are at the WP level
SELECT COUNT(*)
FROM DS08_WAD
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(WBS_ID_WP,'')) = ''
) = 0
)