¶ Basic Information
| Key | Value |
|---|---|
| Table | DS08 WAD |
| Severity | MAJOR |
| Unique ID | 9080611 |
| Summary | Is the POP finish for this Control Account before the last recorded SPAE value in cost? |
| Error message | pop_finish_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 Finish Before Cost Finish (CA)" is designed to ensure that the Period of Performance (POP) finish date for a given Control Account (CA) is not earlier than the last recorded Schedule Performance Analysis Element (SPAE) value in cost. This check is performed on the DS08 Work Authorization Document (WAD) table.
The error message "pop_finish_date < max DS03.period_date where BCWS, BCWP, ACWP, or ETC <> 0 (by DS08.WBS_ID & DS03.WBS_ID_CA)" indicates that the POP finish date is earlier than the maximum period date in the DS03 Cost table where any of the following fields are not equal to zero: Budgeted Cost of Work Scheduled (BCWS), Budgeted Cost of Work Performed (BCWP), Actual Cost of Work Performed (ACWP), or Estimate to Complete (ETC). This comparison is made by matching the Work Breakdown Structure (WBS) ID in the DS08 WAD table with the WBS ID for the Control Account in the DS03 Cost table.
The likely cause of this error is an inconsistency between the POP finish date in the DS08 WAD table and the period dates in the DS03 Cost table. This could be due to incorrect data entry or a delay in updating one of the tables. The expected values would be for the POP finish date to be the same as or later than the maximum period date in the DS03 Cost table where BCWS, BCWP, ACWP, or ETC are not equal to zero.
To resolve this issue, review the data in the DS08 WAD and DS03 Cost tables, particularly the POP finish dates and the period dates where BCWS, BCWP, ACWP, or ETC are not equal to zero. Ensure that these dates are accurate and up-to-date.
¶ Why do we check this?
This test is being performed to ensure that the Period of Performance (POP) finish date for a given Control Account is not earlier than the last recorded Schedule Performance Analysis Element (SPAE) value in cost. In other words, it checks if there are any cost entries after the project's supposed end date.
The importance of this check lies in its ability to detect potential discrepancies in the project's timeline and cost management. If costs are being recorded after the project's end date, it could indicate issues such as delays in project completion, cost overruns, or errors in data entry.
The severity of this test is marked as a MAJOR. This means that while it may not prevent the data from being reviewed, such an issue could cause significant problems during the analysis of the project's cost performance and schedule. It is crucial to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS08_WAD_IsPOPFinishBeforeDS03FinishCARollup] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostFinish as (
--collect SPAE at CA WBS level
SELECT WBS_ID_CA CAWBS, MAX(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 finish
SELECT W.WBS_ID, MAX(W.auth_PM_date) PMAuth, MAX(POP_finish_date) PFinish
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 CostFinish C ON W.WBS_ID = C.CAWBS
AND W.PFinish < 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
)