¶ Basic Information
| Key | Value |
|---|---|
| Table | DS08 WAD |
| Severity | MINOR |
| Unique ID | 1080428 |
| Summary | Do multiple revisions share the same PM authorization date? |
| Error message | auth_PM_date repeated where revisions are not the same (by WBS_ID & 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 "Repeat PM Authorization Date Across Revisions" is designed to ensure that multiple revisions do not share the same Project Manager (PM) authorization date. This check is performed on the DS08 Work Authorization Document (WAD) table.
The error message "auth_PM_date repeated where revisions are not the same (by WBS_ID & WBS_ID_WP)" indicates that there are instances where different revisions of the same work package (identified by WBS_ID and WBS_ID_WP) have the same PM authorization date (auth_PM_date).
This could be caused by a data entry error where the PM authorization date was not updated correctly when a new revision was made. The expected behavior is that each revision should have a unique PM authorization date.
To resolve this issue, review the PM authorization dates for each revision of the work packages in the DS08 WAD table. Ensure that each revision has a unique PM authorization date.
¶ Why do we check this?
This test is being performed to check for any instances where multiple revisions of a project share the same Project Manager (PM) authorization date. This is important because each revision should ideally have a unique PM authorization date. If the same date is being used across different revisions, it could indicate a data entry error or a lack of proper tracking of revisions.
The severity of this check is marked as an MINOR. This means that while it's not a critical issue that would prevent the data from being reviewed, it could potentially cause minor problems during analysis. For example, it might make it harder to track the progression of revisions or to identify when specific changes were authorized. Therefore, it's recommended to address this issue to ensure the data follows best practices.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS08_WAD_IsPMAuthRepeatedAcrossRevisions] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
W1.*
FROM
DS08_WAD W1 INNER JOIN DS08_WAD W2 ON W1.WBS_ID = W2.WBS_ID
AND ISNULL(W1.WBS_ID_WP,'') = ISNULL(W2.WBS_ID_WP,'')
AND W1.revision <> W2.revision
AND W1.auth_PM_date = W2.auth_PM_date
WHERE
W1.upload_ID = @upload_ID
AND W2.upload_ID = @upload_ID
)