¶ Basic Information
| Key | Value |
|---|---|
| Table | DS08 WAD |
| Severity | MAJOR |
| Unique ID | 9080423 |
| Summary | Is the PM authorization date for this Control Account later than the CA' 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). |
¶ 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 CA Performance or Actuals" is designed to ensure that the Project Manager's (PM) authorization date for a Control Account (CA) is not later than the first recorded instance of either actuals or performance for that CA. This check is performed on the DS08 WAD table.
The error message 'auth_PM_date > minimum DS03.period_date where ACWPi or BCWPi > 0 (by WBS_ID)' indicates that the PM's authorization date is later than the earliest date where either actual cost of work performed (ACWPi) or budgeted cost of work performed (BCWPi) is greater than zero. This is determined by comparing the 'auth_PM_date' field in the DS08 WAD table with the 'period_date' field in the DS03 cost table.
The likely cause of this error is that the PM's authorization date for a particular CA was recorded after work had already begun on that CA, as indicated by the presence of actuals or performance data. This could be due to a data entry error, or it could indicate a procedural issue where work is being started before proper authorization is obtained.
To resolve this issue, you should review the 'auth_PM_date' for the affected CAs in the DS08 WAD table and ensure that they are not later than the earliest 'period_date' where either ACWPi or BCWPi is greater than zero in the DS03 cost table. If necessary, correct the 'auth_PM_date' to a date that is earlier than or equal to the earliest 'period_date' with actuals or performance data.
¶ Why do we check this?
This test is being performed to ensure that the Project Manager's (PM) authorization date for a Control Account (CA) is not later than the first recorded instance of either actuals or performance for that CA. 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 recorded, it could indicate a data entry error or a procedural issue, as work should not be performed or recorded before it has been authorized.
The severity of this test is marked as a MAJOR. This means that while it may not prevent the data from being reviewed, it could cause problems during analysis. For instance, it could skew the timeline of events or cause confusion about when work was actually authorized versus when it was performed. Therefore, it's important to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS08_WAD_IsPMAuthAfterDS03CAAS] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CAActStart as (
--CA WBS IDs with earliest AS date
SELECT WBS_ID_CA CAWBS, 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
), WADByMinAuth as (
--Earilest WAD revisions by CA WBS ID
SELECT WBS_ID, MIN(auth_PM_date) PMAuth
FROM DS08_WAD
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(WBS_ID_WP,'')) = ''
GROUP BY WBS_ID
), Composite as (
--Composite with CA WBS ID, earliest PM auth, and cost Actual start.
SELECT W.WBS_ID, W.PMAuth, C.ActStart
FROM WADByMinAuth W INNER JOIN CAActStart C ON W.WBS_ID = C.CAWBS
)
SELECT
W.*
FROM
DS08_WAD W INNER JOIN Composite C ON W.WBS_ID = C.WBS_ID
AND W.auth_PM_date = C.PMAuth
AND W.auth_PM_date > C.ActStart
WHERE
upload_ID = @upload_ID
AND TRIM(ISNULL(WBS_ID_WP,'')) = ''
)