¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100471 |
| Summary | Is the POP finish for this Control Account misaligned with what is in the WAD? |
| Error message | POP_finish_date <> DS08.POP_finish_date (select latest revision; check is on CA level). |
¶ 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 "POP Finish Misaligned with WAD (CA)" is designed to ensure that the Period of Performance (POP) finish date for a given Control Account (CA) aligns with the corresponding date in the Work Authorization Document (WAD). This check is performed on the DS10 CC Log Detail table.
The error message "POP_finish_date <> DS08.POP_finish_date" indicates that the POP finish date in the DS10 CC Log Detail table does not match the POP finish date in the DS08 WAD table. This discrepancy is identified at the Control Account (CA) level, and the check is grouped by the Work Breakdown Structure ID (WBS_ID).
The likely cause of this error is an inconsistency in data entry between the two tables. The expected value for the POP finish date in the DS10 CC Log Detail table should be the same as the POP finish date in the DS08 WAD table for the same Control Account.
To resolve this issue, it is recommended to review and correct the POP finish dates in both tables to ensure they are aligned.
¶ Why do we check this?
This test is being performed on the 'DS10 CC Log Detail' table to check if the Planned Completion Date (POP Finish) for a particular Control Account is misaligned with the date in the Work Authorization Document (WAD). The test is comparing the POP_finish_date in the current table with the POP_finish_date in the DS08 table. This check is performed at the Control Account (CA) level and the results are grouped by the Work Breakdown Structure ID (WBS_ID).
The severity of this test is marked as 'MAJOR', which means that if the issue is not addressed, it is likely to cause problems during the analysis of the data. This check is important because it ensures that the planned completion dates in different documents are consistent. Inconsistent dates can lead to confusion, miscommunication, and potential delays in the project. Therefore, maintaining data integrity in this aspect is crucial for effective project management.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_IsPOPFinishMisalignedWithDS08CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
WITH WADFinish AS (
--Last CA WAD with POP Finish
SELECT
W.WBS_ID, POP_finish_date
FROM
DS08_WAD W INNER JOIN (
SELECT WBS_ID, MAX(auth_PM_date) AS lastPMAuth
FROM DS08_WAD
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID
) LastRev ON W.WBS_ID = LastRev.WBS_ID
AND W.auth_PM_date = LastRev.lastPMAuth
)
SELECT
L.*
FROM
DS10_CC_log_detail L INNER JOIN WADFinish W ON L.WBS_ID = W.WBS_ID
WHERE
upload_ID = @upload_ID
AND L.POP_finish_date <> W.POP_finish_date
)