¶ Basic Information
| Key | Value |
|---|---|
| Table | DS08 WAD |
| Severity | MAJOR |
| Unique ID | 9080424 |
| Summary | Is the PM authorization date for this Work Package later than the WP' first recorded instance of either actuals or performance? |
| Error message | auth_PM_date > minimum DS03.period_date where ACWPi or BCWPi > 0 (by WBS_ID_WP). |
¶ 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 "PM Authorization After Earliest Recorded WP Performance or Actuals" is designed to ensure that the Project Manager's authorization date for a Work Package (WP) is not later than the first recorded instance of either actuals or performance for that WP.
This check is performed on the DS08 WAD table and involves comparing the 'auth_PM_date' field with the earliest 'period_date' from the DS03 cost table where either 'ACWPi' or 'BCWPi' is greater than 0. These fields represent the actual cost of work performed and the budgeted cost of work performed respectively.
If the DIQ check fails, it indicates that the Project Manager's authorization date for a WP is later than when work was first recorded on that WP. This could be due to a data entry error or a delay in the authorization process.
The expected values for the 'auth_PM_date' should be a date that is on or before the earliest 'period_date' where 'ACWPi' or 'BCWPi' is greater than 0. If the 'auth_PM_date' is later, it suggests that work was performed on the WP before it was officially authorized, which is a potential issue that needs to be addressed.
This DIQ check is crucial to ensure that all work performed is properly authorized and recorded in a timely manner, which is essential for accurate project management and reporting.
¶ Why do we check this?
This test is being performed to ensure that the Project Manager's (PM) authorization date for a Work Package (WP) is not later than the first recorded instance of either actuals or performance for that WP. This is important because it checks for chronological consistency in the data. If the PM's authorization date is later than when actual work or performance was first recorded, it suggests that work may have started before it was officially authorized, which could lead to issues in project management and accountability.
The severity of this test is marked as a MAJOR. This means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. For instance, it could lead to inaccurate project timelines or budget calculations. Therefore, it is crucial to address this issue to ensure the integrity and quality of the EVMS construction project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS08_WAD_IsPMAuthAfterDS03WPAS] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with WPActStart as (
--CA & WP WBS IDs with earliest Actual Start
SELECT WBS_ID_CA CAWBS, WBS_ID_WP WPWBS, MIN(period_date) ActStart
FROM DS03_cost
WHERE
upload_ID = @upload_ID
AND (
ACWPi_dollars <> 0 OR ACWPi_dollars <> 0 OR ACWPi_hours <> 0 OR
BCWPi_dollars <> 0 OR BCWPi_dollars <> 0 OR BCWPi_hours <> 0
)
GROUP BY WBS_ID_CA, WBS_ID_WP
), WADByMinAuth as (
--CA & WP WBS IDs with earliest revision date
SELECT WBS_ID CAWBS, WBS_ID_WP WPWBS, MIN(auth_PM_date) PMAuth
FROM DS08_WAD
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(WBS_ID_WP,'')) <> ''
GROUP BY WBS_ID, WBS_ID_WP
), Composite as (
--CA & WP WBS IDs, earliest PM auth, and earliest actual start
SELECT W.CAWBS, W.WPWBS, W.PMAuth, C.ActStart
FROM WADByMinAuth W INNER JOIN WPActStart C ON W.CAWBS = C.CAWBS AND W.WPWBS = C.WPWBS
)
SELECT
W.*
FROM
DS08_WAD W INNER JOIN Composite C ON W.WBS_ID = C.CAWBS
AND W.WBS_ID_WP = C.WPWBS
AND W.auth_PM_date > C.ActStart
WHERE
upload_ID = @upload_ID
AND TRIM(ISNULL(W.WBS_ID_WP,'')) <> ''
)