¶ Basic Information
| Key | Value |
|---|---|
| Table | DS08 WAD |
| Severity | MAJOR |
| Unique ID | 9080426 |
| Summary | Is the PM authorization date for this Work Package WAD later than the WP's Actual Start date? |
| Error message | auth_PM_date > DS04.AS_date (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 WP Actual Start" is designed to ensure that the Project Manager's authorization date for a Work Package (WP) in the DS08 WAD table is not later than the actual start date of the WP in the DS04 Schedule table.
The error is likely to occur when the 'auth_PM_date' in the DS08 WAD table is greater than the 'AS_date' in the DS04 Schedule table. This means that the Project Manager's authorization date for a WP is later than the actual start date of the WP, which is not expected.
The fields causing the issue are 'auth_PM_date' in the DS08 WAD table and 'AS_date' in the DS04 Schedule table. The expected values should be such that the 'auth_PM_date' is not later than the 'AS_date'.
The DIQ check is performed by comparing the earliest Project Manager's authorization date for each WP in the DS08 WAD table with the earliest actual start date for the same WP in the DS04 Schedule table. If the Project Manager's authorization date is later than the actual start date for any WP, the DIQ check will flag an error.
Please ensure that the Project Manager's authorization date for each WP is not later than the actual start date of the WP to maintain data integrity and quality.
¶ Why do we check this?
This test is being performed to ensure that the Project Manager's (PM) authorization date for a particular Work Package (WP) is not later than the actual start date of the WP. The test is checking if the PM authorization date is greater than the actual start date of the WP, which would be an incorrect sequence of events.
The importance of this check is to maintain the integrity and accuracy of the project timeline. In proper project management, a WP should not start before it has been authorized by the PM. If the authorization date is later than the actual start date, it could indicate a problem in the project management process, such as tasks being started without proper authorization or issues with the recording of dates.
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. It could potentially lead to incorrect conclusions about the project timeline and management process. Therefore, it is important to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS08_WAD_IsPMAuthAfterDS04WPAS] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with WPActStart as (
--WP WBS Id with earliest actual start
SELECT WBS_ID WBS, MIN(AS_date) ActStart
FROM
DS04_schedule S
WHERE
upload_ID = @upload_ID
AND AS_date IS NOT NULL
AND schedule_type = 'FC'
AND WBS_ID IN (
SELECT WBS_ID
FROM DS01_WBS
WHERE upload_ID = @upload_ID AND type = 'WP'
)
GROUP BY
WBS_ID
), WADByMinAuth as (
--WP WBS IDs with earliest revisions
SELECT 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_WP
), Composite as (
--WP WBS ID with earliest PM auth and Actual Start
SELECT W.WPWBS, W.PMAuth, ActS.ActStart
FROM WADByMinAuth W INNER JOIN WPActStart ActS ON W.WPWBS = ActS.WBS
)
SELECT
W.*
FROM
DS08_WAD W INNER JOIN Composite C ON W.WBS_ID_WP = C.WPWBS
AND W.auth_PM_date = C.PMAuth
AND W.auth_PM_date > C.ActStart
WHERE
upload_ID = @upload_ID
AND TRIM(ISNULL(W.WBS_ID_WP,'')) <> ''
)