¶ Basic Information
| Key | Value |
|---|---|
| Table | DS08 WAD |
| Severity | MAJOR |
| Unique ID | 9080425 |
| Summary | Is the PM authorization date for this Control Account WAD later than the CA's Actual Start date? |
| Error message | auth_PM_date > DS04.AS_date (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 CA Actual Start" is designed to ensure that the Project Manager's authorization date for a Control Account Work Authorization Document (WAD) is not later than the Control Account's actual start date. This check is performed on the DS08 WAD table.
If an error is flagged by this check, it indicates that the Project Manager's authorization date (auth_PM_date) for a Control Account WAD is later than the actual start date (AS_date) of the Control Account. This discrepancy is identified by matching the Work Breakdown Structure ID (WBS_ID) in the DS08 WAD table with the corresponding WBS_ID in the DS04 Schedule table.
The expected value for the Project Manager's authorization date should ideally be on or before the actual start date of the Control Account. If it is later, it suggests that the Project Manager authorized the Control Account after work had already begun, which is a breach of standard project management procedures.
To resolve this issue, review the Project Manager's authorization dates and the actual start dates for all Control Accounts. Ensure that authorization is granted before work commences. If the dates are correct and the error persists, it may indicate a deeper issue with the data entry or data management process.
¶ Why do we check this?
This test is being performed to ensure that the Project Manager's (PM) authorization date for a specific Control Account Work Authorization Document (WAD) is not later than the Control Account's (CA) actual start date. This is important because it ensures that all work is properly authorized before it begins, which is crucial for maintaining proper project management and control.
The severity of this check 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. If the PM's authorization date is later than the CA's actual start date, it could indicate that work was started without proper authorization, which could lead to issues with accountability and project tracking. Therefore, it's important to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS08_WAD_IsPMAuthAfterDS04CAAS] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CAActStart as (
--Schedule CA WBS IDs with earliest actual start
SELECT A.CAWBS, MIN(S.AS_date) ActStart
FROM
(
SELECT WBS_ID WBS, AS_date
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND AS_date IS NOT NULL AND schedule_type = 'FC'
) S INNER JOIN
(
SELECT Ancestor_WBS_ID CAWBS, WBS_ID WBS
FROM AncestryTree_Get(@upload_ID)
WHERE type = 'WP' AND Ancestor_Type = 'CA'
) A ON S.WBS = A.WBS
GROUP BY A.CAWBS
), WADByMinAuth as (
--CA-level WADs with earliest revision
SELECT WBS_ID CAWBS, 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 IDs, with earliest PM auth, and earliest cost actual start.
SELECT W.CAWBS, W.PMAuth, C.ActStart
FROM WADByMinAuth W INNER JOIN CAActStart C ON W.CAWBS = C.CAWBS
)
SELECT
W.*
FROM
DS08_WAD W INNER JOIN Composite C ON W.WBS_ID = C.CAWBS
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,'')) = ''
)