¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100473 |
| 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 identify any discrepancies between the Planned Completion Date (POP Finish) for a Control Account (CA) in the DS10 CC Log Detail table and the corresponding date in the DS08 WAD table.
The test operates by comparing the POP Finish Date for each Control Account in the DS10 CC Log Detail table with the latest revision of the same date in the DS08 WAD table. If there is a mismatch between these two dates, the test will flag an error.
The error is likely caused by inconsistent updates between the two tables. For instance, if the POP Finish Date for a Control Account is updated in the DS10 CC Log Detail table but not in the DS08 WAD table, or vice versa, the test will identify a discrepancy.
The fields causing the issue are the POP_finish_date in both the DS10 CC Log Detail and DS08 WAD tables. The expected values for these fields should be identical for each Control Account, reflecting the most recent planned completion date.
To resolve this issue, ensure that any updates to the POP Finish Date for a Control Account are consistently applied to both the DS10 CC Log Detail and DS08 WAD tables.
¶ 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 not aligned 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 could potentially cause problems during the analysis of the data. The misalignment of dates could lead to incorrect project timelines, budget overruns, or misallocation of resources.
The importance of this check lies in ensuring the integrity and accuracy of the project management data. It helps in maintaining a consistent and accurate project schedule, which is crucial for effective project management and for making informed decisions. It also ensures that the project is being executed as per the authorized plan.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_IsPOPFinishRollupMisalignedWithDS08CA] (
@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
), CACCLog AS (
--WP level CC log entries rolled up to the CA level, along with latest POP finish
--Filter for only items of type WP/PP where the ancestor is a CA/SLPP
SELECT A.Ancestor_WBS_ID CAWBS, MAX(POP_finish_date) PopFinish
FROM DS10_CC_log_detail L INNER JOIN AncestryTree_Get(@upload_ID) A ON L.WBS_ID = A.WBS_ID
WHERE L.upload_ID = @upload_ID AND A.[Type] IN ('WP','PP') AND A.Ancestor_Type IN ('CA','SLPP')
GROUP BY A.Ancestor_WBS_ID
), FlagsByCAWBS AS (
--CA WBS IDs where WAD POP finish <> CC log POP finish
SELECT W.WBS_ID
FROM WADFinish W INNER JOIN CACCLog C ON W.WBS_ID = C.CAWBS
WHERE W.POP_finish_date <> C.PopFinish
), FlagsByWPWBS AS (
--WP WBS IDs that make up the above CA WBSs
--Join back to AncestryTree, bu