¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100477 |
| Summary | Is the POP start for this Control Account misaligned with what is in the WAD? |
| Error message | POP_start_date <> DS08.POP_start_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 Start Misaligned with WAD (CA)" is designed to ensure that the Period of Performance (POP) start date for a Control Account (CA) in the DS10 CC Log Detail table aligns with the corresponding date in the DS08 WAD table.
The test is performed at the Control Account (CA) level, and the grouping is based on the Work Breakdown Structure ID (WBS_ID). The test compares the POP start date in the DS10 CC Log Detail table with the latest revision of the POP start date in the DS08 WAD table for each Control Account.
If the test returns an error, it indicates that there is a misalignment between the two dates. This could be due to an error in data entry, a delay in updating one of the tables, or a discrepancy in the data sources.
The fields causing the issue are the POP_start_date in the DS10 CC Log Detail table and the POP_start_date in the DS08 WAD table. The expected values for these fields should be identical for each Control Account.
To resolve the issue, you should review the data in both tables and correct any discrepancies. This will ensure that the POP start dates for each Control Account are consistent across the two tables, thereby maintaining the integrity and quality of the project management data.
¶ Why do we check this?
This test is being performed to ensure that the Period of Performance (POP) start date for a specific Control Account (CA) aligns with the date recorded in the Work Authorization Document (WAD). The test is comparing the POP start date in the 'DS10 CC Log Detail' table with the POP start date in the 'DS08' table.
The importance of this check is to maintain consistency and accuracy in the project timeline across different data sources. Misalignment of dates can lead to confusion, miscommunication, and potential delays in the project timeline.
The severity of this test is marked as 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. Therefore, it is recommended to address this issue to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_IsPOPStartRollupMisalignedWithDS08CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
WITH WADStart AS (
--Last CA WAD with POP Start
SELECT
W.WBS_ID, POP_start_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 earliest POP start
--Filter for only items of type WP/PP where the ancestor is a CA/SLPP
SELECT A.Ancestor_WBS_ID CAWBS, MIN(POP_start_date) PopStart
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 start <> CC log POP start
SELECT W.WBS_ID
FROM WADStart W INNER JOIN CACCLog C ON W.WBS_ID = C.CAWBS
WHERE W.POP_start_date <> C.PopStart
), FlagsByWPWBS AS (
--WP WBS IDs that make up the above CA WBSs
--Join back to AncestryTree, but this time by